[Home] [Help]
PACKAGE BODY: APPS.ZX_AR_POPULATE_PKG
Source
1 PACKAGE BODY ZX_AR_POPULATE_PKG AS
2 /* $Header: zxrirpopulatpvtb.pls 120.41.12010000.4 2008/12/03 19:34:04 bibeura ship $ */
3
4
5 --Populate party info into global variables
6 GT_BILLING_TP_NUMBER ZX_EXTRACT_PKG.BILLING_TP_NUMBER_TBL;
7 GT_BILLING_TP_TAX_REG_NUM ZX_EXTRACT_PKG.BILLING_TP_TAX_REG_NUM_TBL;
8 GT_BILLING_SITE_TAX_REG_NUM ZX_EXTRACT_PKG.BILLING_TP_SITE_TX_REG_NUM_TBL;
9 GT_BILLING_TP_TAXPAYER_ID ZX_EXTRACT_PKG.BILLING_TP_TAXPAYER_ID_TBL;
10 GT_BILLING_TP_SITE_NAME_ALT ZX_EXTRACT_PKG.BILLING_TP_SITE_NAME_ALT_TBL;
11 GT_BILLING_TP_NAME ZX_EXTRACT_PKG.BILLING_TP_NAME_TBL;
12 GT_BILLING_TP_NAME_ALT ZX_EXTRACT_PKG.BILLING_TP_NAME_ALT_TBL;
13 GT_BILLING_TP_SIC_CODE ZX_EXTRACT_PKG.BILLING_TP_SIC_CODE_TBL;
14 GT_BILLING_TP_CITY ZX_EXTRACT_PKG.BILLING_TP_CITY_TBL;
15 GT_BILLING_TP_COUNTY ZX_EXTRACT_PKG.BILLING_TP_COUNTY_TBL;
16 GT_BILLING_TP_STATE ZX_EXTRACT_PKG.BILLING_TP_STATE_TBL;
17 GT_BILLING_TP_PROVINCE ZX_EXTRACT_PKG.BILLING_TP_PROVINCE_TBL;
18 GT_BILLING_TP_ADDRESS1 ZX_EXTRACT_PKG.BILLING_TP_ADDRESS1_TBL;
19 GT_BILLING_TP_ADDRESS2 ZX_EXTRACT_PKG.BILLING_TP_ADDRESS2_TBL;
20 GT_BILLING_TP_ADDRESS3 ZX_EXTRACT_PKG.BILLING_TP_ADDRESS3_TBL;
21 GT_BILLING_TP_ADDR_LINES_ALT ZX_EXTRACT_PKG.BILLING_TP_ADDR_LINES_ALT_TBL;
22 GT_BILLING_TP_COUNTRY ZX_EXTRACT_PKG.BILLING_TP_COUNTRY_TBL;
23 GT_BILLING_TP_POSTAL_CODE ZX_EXTRACT_PKG.BILLING_TP_POSTAL_CODE_TBL;
24 GT_BILLING_TP_PARTY_NUMBER ZX_EXTRACT_PKG.BILLING_TP_PARTY_NUMBER_TBL;
25 GT_BILLING_TP_ID ZX_EXTRACT_PKG.BILLING_TP_ID_TBL;
26 GT_BILLING_TP_SITE_ID ZX_EXTRACT_PKG.BILLING_TP_SITE_ID_TBL;
27 GT_BILLING_TP_ADDRESS_ID ZX_EXTRACT_PKG.BILLING_TP_ADDRESS_ID_TBL;
28 -- GT_SHIPPING_TP_ID ZX_EXTRACT_PKG.BILLING_TP_ID_TBL;
29 -- GT_SHIPPING_TP_SITE_ID ZX_EXTRACT_PKG.BILLING_TP_SITE_ID_TBL;
30 -- GT_SHIPPING_TP_ADDRESS_ID ZX_EXTRACT_PKG.BILLING_TP_ADDRESS_ID_TBL;
31 GT_BILLING_TP_TAX_REP_FLAG ZX_EXTRACT_PKG.BILLING_TP_TAX_REP_FLAG_TBL;
32 GT_BILLING_TP_SITE_NAME ZX_EXTRACT_PKG.BILLING_TP_SITE_NAME_TBL;
33 GT_GDF_RA_ADDRESSES_BILL_ATT9 ZX_EXTRACT_PKG.GDF_RA_ADDRESSES_BILL_ATT9_TBL;
34 GT_GDF_PARTY_SITES_BILL_ATT8 ZX_EXTRACT_PKG.GDF_PARTY_SITES_BILL_ATT8_TBL;
35 GT_GDF_RA_CUST_BILL_ATT10 ZX_EXTRACT_PKG.GDF_RA_CUST_BILL_ATT10_TBL;
36 GT_GDF_RA_CUST_BILL_ATT12 ZX_EXTRACT_PKG.GDF_RA_CUST_BILL_ATT12_TBL;
37 GT_GDF_RA_ADDRESSES_BILL_ATT8 ZX_EXTRACT_PKG.GDF_RA_ADDRESSES_BILL_ATT8_TBL;
38 GT_TAX_REG_NUM ZX_EXTRACT_PKG.HQ_ESTB_REG_NUMBER_TBL;
39 GT_HQ_ESTB_REG_NUMBER ZX_EXTRACT_PKG.HQ_ESTB_REG_NUMBER_TBL;
40 GT_DOC_SEQ_ID ZX_EXTRACT_PKG.DOC_SEQ_ID_TBL;
41
42 GT_DOC_SEQ_NAME ZX_EXTRACT_PKG.DOC_SEQ_NAME_TBL;
43 GT_SHIPPING_TP_NUMBER ZX_EXTRACT_PKG.SHIPPING_TP_NUMBER_TBL;
44 GT_SHIPPING_TP_TAX_REG_NUM ZX_EXTRACT_PKG.SHIPPING_TP_TAX_REG_NUM_TBL;
45 GT_SHIPPING_TP_TAXPAYER_ID ZX_EXTRACT_PKG.SHIPPING_TP_TAXPAYER_ID_TBL;
46 GT_SHIPPING_SITE_TAX_REG_NUM ZX_EXTRACT_PKG.SHIPPING_TP_SITE_TX_RG_NUM_TBL;
47 -- GT_SHIPPING_TP_SITE_NAME_ALT ZX_EXTRACT_PKG.SHIPPING_TP_SITE_NAME_ALT_TBL;
48 GT_SHIPPING_TP_NAME ZX_EXTRACT_PKG.SHIPPING_TP_NAME_TBL;
49 GT_SHIPPING_TP_NAME_ALT ZX_EXTRACT_PKG.SHIPPING_TP_NAME_ALT_TBL;
50 GT_SHIPPING_TP_SIC_CODE ZX_EXTRACT_PKG.SHIPPING_TP_SIC_CODE_TBL;
51 GT_SHIPPING_TP_CITY ZX_EXTRACT_PKG.SHIPPING_TP_CITY_TBL;
52 GT_SHIPPING_TP_COUNTY ZX_EXTRACT_PKG.SHIPPING_TP_COUNTY_TBL;
53 GT_SHIPPING_TP_STATE ZX_EXTRACT_PKG.SHIPPING_TP_STATE_TBL;
54 GT_SHIPPING_TP_PROVINCE ZX_EXTRACT_PKG.SHIPPING_TP_PROVINCE_TBL;
55 GT_SHIPPING_TP_ADDRESS1 ZX_EXTRACT_PKG.SHIPPING_TP_ADDRESS1_TBL;
56 GT_SHIPPING_TP_ADDRESS2 ZX_EXTRACT_PKG.SHIPPING_TP_ADDRESS2_TBL;
57 GT_SHIPPING_TP_ADDRESS3 ZX_EXTRACT_PKG.SHIPPING_TP_ADDRESS3_TBL;
58 GT_SHIPPING_TP_ADDR_LINES_ALT ZX_EXTRACT_PKG.SHIPPING_TP_ADDR_LINES_ALT_TBL;
59 GT_SHIPPING_TP_COUNTRY ZX_EXTRACT_PKG.SHIPPING_TP_COUNTRY_TBL;
60 GT_SHIPPING_TP_POSTAL_CODE ZX_EXTRACT_PKG.SHIPPING_TP_POSTAL_CODE_TBL;
61 -- GT_SHIPPING_TP_PARTY_NUMBER ZX_EXTRACT_PKG.SHIPPING_TP_PARTY_NUMBER_TBL;
62 GT_SHIPPING_TP_ID ZX_EXTRACT_PKG.SHIPPING_TP_ID_TBL;
63 GT_SHIPPING_TP_SITE_ID ZX_EXTRACT_PKG.SHIPPING_TP_SITE_ID_TBL;
64 GT_SHIPPING_TP_ADDRESS_ID ZX_EXTRACT_PKG.SHIPPING_TP_ADDRESS_ID_TBL;
65 -- GT_SHIPPING_TP_TAX_REP_FLAG ZX_EXTRACT_PKG.SHIPPING_TP_TAX_REP_FLAG_TBL;
66 GT_SHIPPING_TP_SITE_NAME ZX_EXTRACT_PKG.SHIPPING_TP_SITE_NAME_TBL;
67 GT_GDF_RA_ADDRESSES_SHIP_ATT9 ZX_EXTRACT_PKG.GDF_RA_ADDRESSES_SHIP_ATT9_TBL;
68 GT_GDF_PARTY_SITES_SHIP_ATT8 ZX_EXTRACT_PKG.GDF_PARTY_SITES_SHIP_ATT8_TBL;
69 GT_GDF_RA_CUST_SHIP_ATT10 ZX_EXTRACT_PKG.GDF_RA_CUST_SHIP_ATT10_TBL;
70 GT_GDF_RA_CUST_SHIP_ATT12 ZX_EXTRACT_PKG.GDF_RA_CUST_SHIP_ATT12_TBL;
71 GT_GDF_RA_ADDRESSES_SHIP_ATT8 ZX_EXTRACT_PKG.GDF_RA_ADDRESSES_SHIP_ATT8_TBL;
72 GT_TAX_RATE_VAT_TRX_TYPE_DESC ZX_EXTRACT_PKG.TAX_RATE_VAT_TRX_TYPE_DESC_TBL;
73 GT_TAX_RATE_CODE_REG_TYPE_MNG ZX_EXTRACT_PKG.TAX_RATE_CODE_REG_TYPE_MNG_TBL;
74 GT_TRX_CLASS_MNG ZX_EXTRACT_PKG.TRX_CLASS_MNG_TBL;
75 GT_TAX_EXCEPTION_REASON_MNG ZX_EXTRACT_PKG.TAX_EXCEPTION_REASON_MNG_TBL;
76 GT_TAX_EXEMPT_REASON_MNG ZX_EXTRACT_PKG.TAX_EXEMPT_REASON_MNG_TBL;
77 GT_LEDGER_NAME ZX_EXTRACT_PKG.LEDGER_NAME_TBL;
78 GT_BANKING_TP_TAXPAYER_ID ZX_EXTRACT_PKG.BANKING_TP_TAXPAYER_ID_TBL;
79
80 GT_DETAIL_TAX_LINE_ID ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL;
81 GT_LEDGER_ID ZX_EXTRACT_PKG.LEDGER_ID_TBL;
82 GT_TRX_ID ZX_EXTRACT_PKG.TRX_ID_TBL;
83 GT_BANK_ACCOUNT_ID ZX_EXTRACT_PKG.BANK_ACCOUNT_ID_TBL;
84 GT_TRX_TYPE_ID ZX_EXTRACT_PKG.TRX_TYPE_ID_TBL;
85 GT_TRX_CLASS ZX_EXTRACT_PKG.TRX_LINE_CLASS_TBL;
86 GT_TRX_BATCH_SOURCE_ID ZX_EXTRACT_PKG.BATCH_SOURCE_ID_TBL;
87 GT_TAX_RATE_ID ZX_EXTRACT_PKG.TAX_RATE_ID_TBL;
88 GT_TAX_RATE_VAT_TRX_TYPE_CODE ZX_EXTRACT_PKG.TAX_RATE_VAT_TRX_TYPE_CODE_TBL;
89 GT_TAX_RATE_REG_TYPE_CODE ZX_EXTRACT_PKG.TAX_RATE_REG_TYPE_CODE_TBL;
90 GT_TAX_EXEMPTION_ID ZX_EXTRACT_PKG.TAX_EXEMPTION_ID_TBL;
91 GT_TAX_EXCEPTION_ID ZX_EXTRACT_PKG.TAX_EXCEPTION_ID_TBL;
92 GT_TAX_LINE_ID ZX_EXTRACT_PKG.TAX_LINE_ID_TBL;
93 GT_TAX_AMT ZX_EXTRACT_PKG.TAX_AMT_TBL;
94 GT_TAX_AMT_FUNCL_CURR ZX_EXTRACT_PKG.TAX_AMT_FUNCL_CURR_TBL;
95 GT_TAX_LINE_NUMBER ZX_EXTRACT_PKG.TAX_LINE_NUMBER_TBL;
96 GT_TAXABLE_AMT ZX_EXTRACT_PKG.TAXABLE_AMT_TBL;
97 GT_TAXABLE_AMT_FUNCL_CURR ZX_EXTRACT_PKG.TAXABLE_AMT_FUNCL_CURR_TBL;
98 GT_TRX_LINE_ID ZX_EXTRACT_PKG.TRX_LINE_ID_TBL;
99 GT_TAX_EXCEPTION_REASON_CODE ZX_EXTRACT_PKG.TAX_EXCEPTION_REASON_CODE_TBL;
100 GT_EXEMPT_REASON_CODE ZX_EXTRACT_PKG.EXEMPT_REASON_CODE_TBL;
101 GT_RECONCILIATION_FLAG ZX_EXTRACT_PKG.RECONCILIATION_FLAG_TBL;
102 GT_INTERNAL_ORGANIZATION_ID ZX_EXTRACT_PKG.INTERNAL_ORGANIZATION_ID_TBL;
103 GT_TAX_DATE ZX_EXTRACT_PKG.TAX_DATE_TBL;
104 GT_BR_REF_CUSTOMER_TRX_ID ZX_EXTRACT_PKG.BR_REF_CUSTOMER_TRX_ID_TBL;
105 GT_REVERSE_FLAG ZX_EXTRACT_PKG.REVERSE_FLAG_TBL;
106 GT_AMOUNT_APPLIED ZX_EXTRACT_PKG.AMOUNT_APPLIED_TBL;
107 GT_TAX_RATE ZX_EXTRACT_PKG.TAX_RATE_TBL;
108 GT_TAX_RATE_CODE ZX_EXTRACT_PKG.TAX_RATE_CODE_TBL;
109 GT_TAX_TYPE_CODE ZX_EXTRACT_PKG.TAX_TYPE_CODE_TBL;
110 GT_TRX_DATE ZX_EXTRACT_PKG.TRX_DATE_TBL;
111 GT_TRX_CURRENCY_CODE ZX_EXTRACT_PKG.TRX_CURRENCY_CODE_TBL;
112 GT_CURRENCY_CONVERSION_RATE ZX_EXTRACT_PKG.CURRENCY_CONVERSION_RATE_TBL;
113 GT_APPLICATION_ID ZX_EXTRACT_PKG.APPLICATION_ID_TBL;
114 GT_DOC_EVENT_STATUS ZX_EXTRACT_PKG.DOC_EVENT_STATUS_TBL;
115 GT_EXTRACT_SOURCE_LEDGER ZX_EXTRACT_PKG.EXTRACT_SOURCE_LEDGER_TBL;
116 GT_FUNCTIONAL_CURRENCY_CODE ZX_EXTRACT_PKG.FUNCTIONAL_CURRENCY_CODE_TBL;
117 GT_MINIMUM_ACCOUNTABLE_UNIT ZX_EXTRACT_PKG.MINIMUM_ACCOUNTABLE_UNIT_TBL;
118 GT_PRECISION ZX_EXTRACT_PKG.PRECISION_TBL;
119 GT_RECEIPT_CLASS_ID ZX_EXTRACT_PKG.RECEIPT_CLASS_ID_TBL;
120 GT_EXCEPTION_RATE ZX_EXTRACT_PKG.EXCEPTION_RATE_TBL;
121 GT_SHIP_FROM_PARTY_TAX_PROF_ID ZX_EXTRACT_PKG.SHIP_FROM_PTY_TAX_PROF_ID_TBL;
122 GT_SHIP_FROM_SITE_TAX_PROF_ID ZX_EXTRACT_PKG.SHIP_FROM_SITE_TAX_PROF_ID_TBL;
123 GT_SHIP_TO_PARTY_TAX_PROF_ID ZX_EXTRACT_PKG.SHIP_TO_PARTY_TAX_PROF_ID_TBL;
124 GT_SHIP_TO_SITE_TAX_PROF_ID ZX_EXTRACT_PKG.SHIP_TO_SITE_TAX_PROF_ID_TBL;
125 GT_BILL_TO_PARTY_TAX_PROF_ID ZX_EXTRACT_PKG.BILL_TO_PARTY_TAX_PROF_ID_TBL;
126 GT_BILL_TO_SITE_TAX_PROF_ID ZX_EXTRACT_PKG.BILL_TO_SITE_TAX_PROF_ID_TBL;
127 GT_BILL_FROM_PARTY_TAX_PROF_ID ZX_EXTRACT_PKG.BILL_FROM_PTY_TAX_PROF_ID_TBL;
128 GT_BILL_FROM_SITE_TAX_PROF_ID ZX_EXTRACT_PKG.BILL_FROM_SITE_TAX_PROF_ID_TBL;
129 -- GT_BILLING_TP_ID ZX_EXTRACT_PKG.BILLING_TP_ID_TBL;
130 -- GT_BILLING_TP_SITE_ID ZX_EXTRACT_PKG.BILLING_TP_SITE_ID_TBL;
131 --GT_BILLING_TP_ADDRESS_ID ZX_EXTRACT_PKG.BILLING_TP_ADDRESS_ID_TBL;
132 GT_BILL_TO_PARTY_ID ZX_EXTRACT_PKG.BILL_TO_PARTY_ID_TBL;
133 GT_BILL_TO_PARTY_SITE_ID ZX_EXTRACT_PKG.BILL_TO_PARTY_SITE_ID_TBL;
134 GT_SHIP_TO_PARTY_ID ZX_EXTRACT_PKG.SHIP_TO_PARTY_ID_TBL;
135 GT_SHIP_TO_PARTY_SITE_ID ZX_EXTRACT_PKG.SHIP_TO_PARTY_SITE_ID_TBL;
136 GT_HISTORICAL_FLAG ZX_EXTRACT_PKG.HISTORICAL_FLAG_TBL;
137 --GT_INTERNAL_ORGANIZATION_ID ZX_EXTRACT_PKG.INTERNAL_ORGANIZATION_ID_TBL;
138 -- apai GT_REP_CONTEXT_ID ZX_EXTRACT_PKG.REP_CONTEXT_ID_TBL;
139 G_FUN_CURRENCY_CODE gl_ledgers.currency_code%TYPE;
140
141 --Accounting global variables declaration --
142 GT_ACTG_EXT_LINE_ID ZX_EXTRACT_PKG.ACTG_EXT_LINE_ID_TBL;
143 GT_ACTG_EVENT_TYPE_CODE ZX_EXTRACT_PKG.ACTG_EVENT_TYPE_CODE_TBL;
144 GT_ACTG_EVENT_NUMBER ZX_EXTRACT_PKG.ACTG_EVENT_NUMBER_TBL;
145 GT_ACTG_EVENT_STATUS_FLAG ZX_EXTRACT_PKG.ACTG_EVENT_STATUS_FLAG_TBL;
146 GT_ACTG_CATEGORY_CODE ZX_EXTRACT_PKG.ACTG_CATEGORY_CODE_TBL;
147 GT_ACCOUNTING_DATE ZX_EXTRACT_PKG.ACCOUNTING_DATE_TBL;
148 GT_GL_TRANSFER_FLAG ZX_EXTRACT_PKG.GL_TRANSFER_FLAG_TBL;
149 GT_GL_TRANSFER_RUN_ID ZX_EXTRACT_PKG.GL_TRANSFER_RUN_ID_TBL;
150 GT_ACTG_HEADER_DESCRIPTION ZX_EXTRACT_PKG.ACTG_HEADER_DESCRIPTION_TBL;
151 GT_ACTG_LINE_NUM ZX_EXTRACT_PKG.ACTG_LINE_NUM_TBL;
152 GT_ACTG_LINE_TYPE_CODE ZX_EXTRACT_PKG.ACTG_LINE_TYPE_CODE_TBL;
153 GT_ACTG_LINE_DESCRIPTION ZX_EXTRACT_PKG.ACTG_LINE_DESCRIPTION_TBL;
154 GT_ACTG_STAT_AMT ZX_EXTRACT_PKG.ACTG_STAT_AMT_TBL;
155 GT_ACTG_ERROR_CODE ZX_EXTRACT_PKG.ACTG_ERROR_CODE_TBL;
156 GT_GL_TRANSFER_CODE ZX_EXTRACT_PKG.GL_TRANSFER_CODE_TBL;
157 GT_ACTG_DOC_SEQUENCE_ID ZX_EXTRACT_PKG.ACTG_DOC_SEQUENCE_ID_TBL;
158 GT_ACTG_DOC_SEQUENCE_NAME ZX_EXTRACT_PKG.ACTG_DOC_SEQUENCE_NAME_TBL;
159 GT_ACTG_DOC_SEQUENCE_VALUE ZX_EXTRACT_PKG.ACTG_DOC_SEQUENCE_VALUE_TBL;
160 GT_ACTG_PARTY_ID ZX_EXTRACT_PKG.ACTG_PARTY_ID_TBL;
161 GT_ACTG_PARTY_SITE_ID ZX_EXTRACT_PKG.ACTG_PARTY_SITE_ID_TBL;
162 GT_ACTG_PARTY_TYPE ZX_EXTRACT_PKG.ACTG_PARTY_TYPE_TBL;
163 GT_ACTG_EVENT_ID ZX_EXTRACT_PKG.ACTG_EVENT_ID_TBL;
164 GT_ACTG_HEADER_ID ZX_EXTRACT_PKG.ACTG_HEADER_ID_TBL;
165 GT_ACTG_SOURCE_ID ZX_EXTRACT_PKG.ACTG_SOURCE_ID_TBL;
166 GT_ACTG_SOURCE_TABLE ZX_EXTRACT_PKG.ACTG_SOURCE_TABLE_TBL;
167 GT_ACTG_LINE_CCID ZX_EXTRACT_PKG.ACTG_LINE_CCID_TBL;
168 GT_PERIOD_NAME ZX_EXTRACT_PKG.PERIOD_NAME_TBL;
169
170
171 AGT_ACTG_EXT_LINE_ID ZX_EXTRACT_PKG.ACTG_EXT_LINE_ID_TBL;
172 AGT_DETAIL_TAX_LINE_ID ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL;
173 AGT_ACTG_EVENT_TYPE_CODE ZX_EXTRACT_PKG.ACTG_EVENT_TYPE_CODE_TBL;
174 AGT_ACTG_EVENT_NUMBER ZX_EXTRACT_PKG.ACTG_EVENT_NUMBER_TBL;
175 AGT_ACTG_EVENT_STATUS_FLAG ZX_EXTRACT_PKG.ACTG_EVENT_STATUS_FLAG_TBL;
176 AGT_ACTG_CATEGORY_CODE ZX_EXTRACT_PKG.ACTG_CATEGORY_CODE_TBL;
177 AGT_ACCOUNTING_DATE ZX_EXTRACT_PKG.ACCOUNTING_DATE_TBL;
178 AGT_GL_TRANSFER_FLAG ZX_EXTRACT_PKG.GL_TRANSFER_FLAG_TBL;
179 AGT_GL_TRANSFER_RUN_ID ZX_EXTRACT_PKG.GL_TRANSFER_RUN_ID_TBL;
180 AGT_ACTG_HEADER_DESCRIPTION ZX_EXTRACT_PKG.ACTG_HEADER_DESCRIPTION_TBL;
181 AGT_ACTG_LINE_NUM ZX_EXTRACT_PKG.ACTG_LINE_NUM_TBL;
182 AGT_ACTG_LINE_TYPE_CODE ZX_EXTRACT_PKG.ACTG_LINE_TYPE_CODE_TBL;
183 AGT_ACTG_LINE_DESCRIPTION ZX_EXTRACT_PKG.ACTG_LINE_DESCRIPTION_TBL;
184 AGT_ACTG_STAT_AMT ZX_EXTRACT_PKG.ACTG_STAT_AMT_TBL;
185 AGT_ACTG_ERROR_CODE ZX_EXTRACT_PKG.ACTG_ERROR_CODE_TBL;
186 AGT_GL_TRANSFER_CODE ZX_EXTRACT_PKG.GL_TRANSFER_CODE_TBL;
187 AGT_ACTG_DOC_SEQUENCE_ID ZX_EXTRACT_PKG.ACTG_DOC_SEQUENCE_ID_TBL;
188 AGT_ACTG_DOC_SEQUENCE_NAME ZX_EXTRACT_PKG.ACTG_DOC_SEQUENCE_NAME_TBL;
189 AGT_ACTG_DOC_SEQUENCE_VALUE ZX_EXTRACT_PKG.ACTG_DOC_SEQUENCE_VALUE_TBL;
190 AGT_ACTG_PARTY_ID ZX_EXTRACT_PKG.ACTG_PARTY_ID_TBL;
191 AGT_ACTG_PARTY_SITE_ID ZX_EXTRACT_PKG.ACTG_PARTY_SITE_ID_TBL;
192 AGT_ACTG_PARTY_TYPE ZX_EXTRACT_PKG.ACTG_PARTY_TYPE_TBL;
193 AGT_ACTG_EVENT_ID ZX_EXTRACT_PKG.ACTG_EVENT_ID_TBL;
194 AGT_ACTG_HEADER_ID ZX_EXTRACT_PKG.ACTG_HEADER_ID_TBL;
195 AGT_ACTG_SOURCE_ID ZX_EXTRACT_PKG.ACTG_SOURCE_ID_TBL;
196 AGT_ACTG_SOURCE_TABLE ZX_EXTRACT_PKG.ACTG_SOURCE_TABLE_TBL;
197 AGT_ACTG_LINE_CCID ZX_EXTRACT_PKG.ACTG_LINE_CCID_TBL;
198 AGT_PERIOD_NAME ZX_EXTRACT_PKG.PERIOD_NAME_TBL;
199
200 GT_ACCOUNT_FLEXFIELD ZX_EXTRACT_PKG.ACCOUNT_FLEXFIELD_TBL;
201 GT_ACCOUNT_DESCRIPTION ZX_EXTRACT_PKG.ACCOUNT_DESCRIPTION_TBL;
202
203
204 --GT_ACTG_SOURCE_ID ZX_EXTRACT_PKG.ACTG_SOURCE_ID_TBL;
205 GT_AE_HEADER_ID ZX_EXTRACT_PKG.ACTG_HEADER_ID_TBL;
206 GT_EVENT_ID ZX_EXTRACT_PKG.ACTG_EVENT_ID_TBL;
207 GT_LINE_CCID ZX_EXTRACT_PKG.ACTG_LINE_CCID_TBL;
208 GT_TRX_ARAP_BALANCING_SEGMENT ZX_EXTRACT_PKG.TRX_ARAP_BALANCING_SEG_TBL;
209 GT_TRX_ARAP_NATURAL_ACCOUNT ZX_EXTRACT_PKG.TRX_ARAP_NATURAL_ACCOUNT_TBL;
210 GT_TRX_TAXABLE_BAL_SEG ZX_EXTRACT_PKG.TRX_TAXABLE_BALANCING_SEG_TBL;
211 GT_TRX_TAXABLE_BALSEG_DESC ZX_EXTRACT_PKG.TRX_TAXABLE_BALSEG_DESC_TBL;
212 GT_TRX_TAXABLE_NATURAL_ACCOUNT ZX_EXTRACT_PKG.TRX_TAXABLE_NATURAL_ACCT_TBL;
213 GT_TRX_TAX_BALANCING_SEGMENT ZX_EXTRACT_PKG.TRX_TAX_BALANCING_SEG_TBL;
214 GT_TRX_TAX_NATURAL_ACCOUNT ZX_EXTRACT_PKG.TRX_TAX_NATURAL_ACCOUNT_TBL;
215 ---- GT_TAX_AMT ZX_EXTRACT_PKG.TAX_AMT_TBL;
216 -- GT_TAX_AMT_FUNCL_CURR ZX_EXTRACT_PKG.TAX_AMT_FUNCL_CURR_TBL;
217 -- GT_TAXABLE_AMT ZX_EXTRACT_PKG.TAXABLE_AMT_TBL;
218 -- GT_TAXABLE_AMT_FUNCL_CURR ZX_EXTRACT_PKG.TAXABLE_AMT_FUNCL_CURR_TBL;
219 GT_POSTED_DATE ZX_EXTRACT_PKG.POSTED_DATE_TBL;
220 GT_TRX_CONTROL_ACCFLEXFIELD ZX_EXTRACT_PKG.TRX_CONTROL_ACCT_FLEXFLD_TBL ; --Bug 5510907
221 GT_TAX_DETERMINE_DATE ZX_EXTRACT_PKG.TAX_DETERMINE_DATE_TBL ; --Bug 5622686
222
223 TYPE TRX_TAXABLE_ACCOUNT_DESC_tbl IS TABLE OF
224 ZX_REP_ACTG_EXT_T.TRX_TAXABLE_ACCOUNT_DESC%TYPE INDEX BY BINARY_INTEGER;
225
226 TYPE TRX_TAXABLE_NATACCT_DESC_tbl IS TABLE OF
227 ZX_REP_ACTG_EXT_T.TRX_TAXABLE_NATACCT_SEG_DESC%TYPE INDEX BY BINARY_INTEGER;
228
229 GT_TRX_TAXABLE_ACCOUNT_DESC TRX_TAXABLE_ACCOUNT_DESC_tbl ; --Bug 5650415
230 GT_TRX_TAXABLE_NATACCT_DESC TRX_TAXABLE_NATACCT_DESC_tbl ;
231 GT_TAX_TYPE_MNG ZX_EXTRACT_PKG.TAX_TYPE_MNG_TBL;
232
233 -- Accounting---
234 G_CREATED_BY NUMBER(15);
235 G_CREATION_DATE DATE;
236 G_LAST_UPDATED_BY NUMBER(15);
237 G_LAST_UPDATE_DATE DATE;
238 G_LAST_UPDATE_LOGIN NUMBER(15);
239 G_PROGRAM_APPLICATION_ID NUMBER;
240 G_PROGRAM_ID NUMBER;
241 G_PROGRAM_LOGIN_ID NUMBER;
242 g_request_id NUMBER;
243 g_coa_id NUMBER;
244
245 G_RETCODE NUMBER :=0;
246
247 C_LINES_PER_COMMIT CONSTANT NUMBER := 5000;
248 L_MSG VARCHAR2(500);
249 G_REP_CONTEXT_ID NUMBER;
250 g_current_runtime_level NUMBER;
251 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
252 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
253 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
254 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
255 g_error_buffer VARCHAR2(100);
256
257 PROCEDURE convert_amounts(P_CURRENCY_CODE IN VARCHAR2,
258 P_EXCHANGE_RATE IN NUMBER,
259 P_PRECISION IN NUMBER,
260 P_MIN_ACCT_UNIT IN NUMBER,
261 P_INPUT_TAX_AMOUNT IN NUMBER,
262 P_INPUT_TAXABLE_AMOUNT IN NUMBER,
263 P_INPUT_EXEMPT_AMOUNT IN NUMBER,
264 i IN binary_integer);
265
266
267
268 PROCEDURE APP_FUNCTIONAL_AMOUNTS(
269 P_TRX_ID IN NUMBER,
270 P_TAX_CODE_ID IN NUMBER,
271 P_CURRENCY_CODE IN VARCHAR2,
272 P_EXCHANGE_RATE IN NUMBER,
273 P_PRECISION IN NUMBER,
274 P_MIN_ACCT_UNIT IN NUMBER,
275 P_INPUT_TAX_AMOUNT IN OUT NOCOPY NUMBER,
276 P_INPUT_TAXABLE_AMOUNT IN OUT NOCOPY NUMBER,
277 P_SUMMARY_LEVEL IN VARCHAR2,
278 P_REGISTER_TYPE IN VARCHAR2,
279 i IN BINARY_INTEGER);
280
281 PROCEDURE get_accounting_info(P_TRX_ID IN NUMBER,
282 P_TRX_LINE_ID IN NUMBER,
283 P_TAX_LINE_ID IN NUMBER,
284 P_EVENT_ID IN NUMBER,
285 P_AE_HEADER_ID IN NUMBER,
286 P_ACTG_SOURCE_ID IN NUMBER,
287 P_BALANCING_SEGMENT IN VARCHAR2,
288 P_ACCOUNTING_SEGMENT IN VARCHAR2,
289 P_SUMMARY_LEVEL IN VARCHAR2,
290 P_REPORT_NAME IN VARCHAR2,
291 P_TRX_CLASS IN VARCHAR2,
292 j IN binary_integer);
293
294 PROCEDURE get_accounting_amounts(P_TRX_ID IN NUMBER,
295 P_TRX_LINE_ID IN NUMBER,
296 P_TAX_LINE_ID IN NUMBER,
297 -- P_ENTITY_ID IN NUMBER,
298 P_EVENT_ID IN NUMBER,
299 P_AE_HEADER_ID IN NUMBER,
300 P_ACTG_SOURCE_ID IN NUMBER,
301 P_SUMMARY_LEVEL IN VARCHAR2,
302 P_TRX_CLASS IN VARCHAR2,
303 P_LEDGER_ID IN NUMBER,
304 j IN binary_integer);
305
306 PROCEDURE other_trx_segment_info(P_TRX_ID IN NUMBER,
307 P_TRX_LINE_ID IN NUMBER,
308 P_TAX_LINE_ID IN NUMBER,
309 -- P_ENTITY_ID IN NUMBER,
310 P_EVENT_ID IN NUMBER,
311 P_AE_HEADER_ID IN NUMBER,
312 P_ACTG_SOURCE_ID IN NUMBER,
313 P_BALANCING_SEGMENT IN VARCHAR2,
314 P_ACCOUNTING_SEGMENT IN VARCHAR2,
315 P_SUMMARY_LEVEL IN VARCHAR2,
316 P_TRX_CLASS IN VARCHAR2,
317 j IN binary_integer);
318 PROCEDURE other_trx_actg_amounts(P_TRX_ID IN NUMBER,
319 P_TRX_LINE_ID IN NUMBER,
320 P_TAX_LINE_ID IN NUMBER,
321 -- P_ENTITY_ID IN NUMBER,
322 P_EVENT_ID IN NUMBER,
323 P_AE_HEADER_ID IN NUMBER,
324 P_ACTG_SOURCE_ID IN NUMBER,
325 P_SUMMARY_LEVEL IN VARCHAR2,
326 P_TRX_CLASS IN VARCHAR2,
327 P_LEDGER_ID IN NUMBER,
328 j IN binary_integer);
329
330 PROCEDURE inv_segment_info (P_TRX_ID IN NUMBER,
331 P_TRX_LINE_ID IN NUMBER,
332 P_TAX_LINE_ID IN NUMBER,
333 -- P_ENTITY_ID IN NUMBER,
334 P_EVENT_ID IN NUMBER,
335 P_AE_HEADER_ID IN NUMBER,
336 P_ACTG_SOURCE_ID IN NUMBER,
337 P_BALANCING_SEGMENT IN VARCHAR2,
338 P_ACCOUNTING_SEGMENT IN VARCHAR2,
339 P_SUMMARY_LEVEL IN VARCHAR2,
340 P_REPORT_NAME IN VARCHAR2,
341 P_TRX_CLASS IN VARCHAR2,
342 j IN binary_integer);
343
344
345 PROCEDURE inv_actg_amounts(P_TRX_ID IN NUMBER,
346 P_TRX_LINE_ID IN NUMBER,
347 P_TAX_LINE_ID IN NUMBER,
348 -- P_ENTITY_ID IN NUMBER,
349 P_EVENT_ID IN NUMBER,
350 P_AE_HEADER_ID IN NUMBER,
351 P_ACTG_SOURCE_ID IN NUMBER,
352 P_SUMMARY_LEVEL IN VARCHAR2,
353 P_TRX_CLASS IN VARCHAR2,
354 P_LEDGER_ID IN NUMBER,
355 j IN binary_integer);
356
357
358 PROCEDURE insert_actg_info (
359 P_COUNT IN BINARY_INTEGER);
360
361 PROCEDURE EXTRACT_PARTY_INFO( i IN BINARY_INTEGER);
362
363 PROCEDURE initialize_variables (
364 p_count IN NUMBER);
365
366 PROCEDURE populate_meaning(
367 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE,
368 i BINARY_INTEGER);
369
370 PROCEDURE populate_tax_reg_num(
371 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE,
372 P_ORG_ID IN zx_lines.internal_organization_id%TYPE ,
373 P_TAX_DATE IN zx_lines.tax_date%TYPE,
374 i BINARY_INTEGER);
375
376 PROCEDURE UPDATE_REP_DETAIL_T(p_count IN NUMBER);
377
378
379 /*===========================================================================+
380 | PROCEDURE |
381 | UPDATE_ADDITIONAL_INFO |
382 | |
383 | DESCRIPTION |
384 | This procedure populates additional extract information |
385 | AR_TAX_EXTRACT_SUB_ITF |
386 | |
387 | Called from |
388 | |
389 | SCOPE - Public |
390 | |
391 | NOTES |
392 | |
393 | MODIFICATION HISTORY |
394 | |
395 +===========================================================================*/
396 PROCEDURE UPDATE_ADDITIONAL_INFO(
397 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE)
398 IS
399
400 /*CURSOR detail_t_cur(c_request_id IN NUMBER) IS
401 SELECT DETAIL_TAX_LINE_ID,
402 LEDGER_ID,
403 INTERNAL_ORGANIZATION_ID,
404 TRX_ID ,
405 TRX_TYPE_ID ,
406 TRX_LINE_CLASS,
407 TRX_BATCH_SOURCE_ID,
408 TAX_RATE_ID ,
409 TAX_RATE_VAT_TRX_TYPE_CODE,
410 TAX_RATE_REGISTER_TYPE_CODE,
411 TAX_EXEMPTION_ID ,
412 TAX_EXCEPTION_ID ,
413 TAX_LINE_ID ,
414 TAX_AMT ,
415 TAX_AMT_FUNCL_CURR ,
416 TAX_LINE_NUMBER ,
417 TAXABLE_AMT ,
418 TAXABLE_AMT_FUNCL_CURR ,
419 TRX_LINE_ID ,
420 TAX_EXCEPTION_REASON_CODE ,
421 EXEMPT_REASON_CODE,
422 RECONCILIATION_FLAG ,
423 INTERNAL_ORGANIZATION_ID,
424 BR_REF_CUSTOMER_TRX_ID,
425 REVERSE_FLAG,
426 AMOUNT_APPLIED,
427 TAX_RATE,
428 TAX_RATE_CODE,
429 TAX_TYPE_CODE,
430 TRX_DATE,
431 TRX_CURRENCY_CODE,
432 CURRENCY_CONVERSION_RATE,
433 APPLICATION_ID,
434 DOC_EVENT_STATUS,
435 EXTRACT_SOURCE_LEDGER ,
436 FUNCTIONAL_CURRENCY_CODE,
437 MINIMUM_ACCOUNTABLE_UNIT,
438 PRECISION,
439 RECEIPT_CLASS_ID ,
440 EXCEPTION_RATE,
441 SHIP_FROM_PARTY_TAX_PROF_ID,
442 SHIP_FROM_SITE_TAX_PROF_ID,
443 SHIP_TO_PARTY_TAX_PROF_ID ,
444 SHIP_TO_SITE_TAX_PROF_ID ,
445 BILL_TO_PARTY_TAX_PROF_ID,
446 BILL_TO_SITE_TAX_PROF_ID,
447 BILL_FROM_PARTY_TAX_PROF_ID,
448 BILL_FROM_SITE_TAX_PROF_ID,
449 BILLING_TRADING_PARTNER_ID,
450 BILLING_TP_SITE_ID,
451 BILLING_TP_ADDRESS_ID,
452 SHIPPING_TRADING_PARTNER_ID,
453 SHIPPING_TP_SITE_ID,
454 SHIPPING_TP_ADDRESS_ID,
455 BILL_TO_PARTY_ID,
456 BILL_TO_PARTY_SITE_ID,
457 SHIP_TO_PARTY_ID,
458 SHIP_TO_PARTY_SITE_ID,
459 HISTORICAL_FLAG
460 FROM zx_rep_trx_detail_t
461 WHERE EXTRACT_SOURCE_LEDGER = 'AR'
462 AND request_id = c_request_id;
463 */
464
465 CURSOR detail_t_cur(c_request_id IN NUMBER,c_ledger_id NUMBER ) IS --Bug 5509856
466 SELECT DISTINCT ZX_DTL.DETAIL_TAX_LINE_ID,
467 ZX_DTL.LEDGER_ID,
468 ZX_DTL.INTERNAL_ORGANIZATION_ID,
469 ZX_DTL.TAX_DATE,
470 ZX_DTL.HQ_ESTB_REG_NUMBER,
471 ZX_DTL.TRX_ID ,
472 ZX_DTL.TRX_TYPE_ID ,
473 ZX_DTL.DOC_SEQ_ID,
474 ZX_DTL.TRX_LINE_CLASS,
475 ZX_DTL.TRX_BATCH_SOURCE_ID,
476 ZX_DTL.TAX_RATE_ID ,
477 ZX_DTL.TAX_RATE_VAT_TRX_TYPE_CODE,
478 ZX_DTL.TAX_RATE_REGISTER_TYPE_CODE,
479 ZX_DTL.TAX_EXEMPTION_ID ,
480 ZX_DTL.TAX_EXCEPTION_ID ,
481 ZX_DTL.TAX_LINE_ID ,
482 ZX_DTL.TAX_AMT ,
483 nvl(ZX_DTL.TAX_AMT_FUNCL_CURR,ZX_DTL.TAX_AMT) ,
484 ZX_DTL.TAX_LINE_NUMBER ,
485 ZX_DTL.TAXABLE_AMT ,
486 nvl(ZX_DTL.TAXABLE_AMT_FUNCL_CURR,ZX_DTL.TAXABLE_AMT) ,
487 ZX_DTL.TRX_LINE_ID ,
488 ZX_DTL.TAX_EXCEPTION_REASON_CODE ,
489 ZX_DTL.EXEMPT_REASON_CODE,
490 ZX_DTL.RECONCILIATION_FLAG ,
491 ZX_DTL.INTERNAL_ORGANIZATION_ID,
492 ZX_DTL.BR_REF_CUSTOMER_TRX_ID,
493 ZX_DTL.REVERSE_FLAG,
494 ZX_DTL.AMOUNT_APPLIED,
495 ZX_DTL.TAX_RATE,
496 ZX_DTL.TAX_RATE_CODE,
497 ZX_DTL.TAX_TYPE_CODE,
498 ZX_DTL.TRX_DATE,
499 ZX_DTL.TRX_CURRENCY_CODE,
500 ZX_DTL.CURRENCY_CONVERSION_RATE,
501 ZX_DTL.APPLICATION_ID,
502 ZX_DTL.DOC_EVENT_STATUS,
503 ZX_DTL.EXTRACT_SOURCE_LEDGER ,
504 ZX_DTL.FUNCTIONAL_CURRENCY_CODE,
505 ZX_DTL.MINIMUM_ACCOUNTABLE_UNIT,
506 ZX_DTL.PRECISION,
507 ZX_DTL.RECEIPT_CLASS_ID ,
508 ZX_DTL.EXCEPTION_RATE,
509 ZX_DTL.SHIP_FROM_PARTY_TAX_PROF_ID,
510 ZX_DTL.SHIP_FROM_SITE_TAX_PROF_ID,
511 ZX_DTL.SHIP_TO_PARTY_TAX_PROF_ID ,
512 ZX_DTL.SHIP_TO_SITE_TAX_PROF_ID ,
513 ZX_DTL.BILL_TO_PARTY_TAX_PROF_ID,
514 ZX_DTL.BILL_TO_SITE_TAX_PROF_ID,
515 ZX_DTL.BILL_FROM_PARTY_TAX_PROF_ID,
516 ZX_DTL.BILL_FROM_SITE_TAX_PROF_ID,
517 ZX_DTL.BILLING_TRADING_PARTNER_ID,
518 ZX_DTL.BILLING_TP_SITE_ID,
519 ZX_DTL.BILLING_TP_ADDRESS_ID,
520 ZX_DTL.SHIPPING_TRADING_PARTNER_ID,
521 ZX_DTL.SHIPPING_TP_SITE_ID,
522 ZX_DTL.SHIPPING_TP_ADDRESS_ID,
523 ZX_DTL.BILL_TO_PARTY_ID,
524 ZX_DTL.BILL_TO_PARTY_SITE_ID,
525 ZX_DTL.SHIP_TO_PARTY_ID,
526 ZX_DTL.SHIP_TO_PARTY_SITE_ID,
527 ZX_DTL.HISTORICAL_FLAG,
528 ZX_DTL.POSTED_DATE,
529 xla_event.event_type_code, -- Accounting Columns
530 xla_event.event_number,
531 xla_event.event_status_code,
532 xla_head.je_category_name,
533 xla_head.accounting_date,
534 xla_head.gl_transfer_status_code,
535 xla_head.description,
536 xla_line.ae_line_num,
537 xla_line.accounting_class_code,
538 xla_line.description,
539 xla_line.statistical_amount,
540 xla_event.process_status_code,
541 xla_head.gl_transfer_status_code,
542 xla_head.doc_sequence_id,
543 xla_head.doc_sequence_value,
544 xla_line.party_id,
545 xla_line.party_site_id,
546 xla_line.party_type_code,
547 xla_event.event_id,
548 xla_head.ae_header_id,
549 xla_line.code_combination_id,
550 xla_head.period_name,
551 zx_dtl.actg_source_id,
552 zx_dtl.bank_account_id,
553 ZX_DTL.tax_determine_date--Bug 5622686
554 FROM zx_rep_trx_detail_t zx_dtl,
555 xla_transaction_entities xla_ent,
556 xla_events xla_event,
557 xla_ae_headers xla_head,
558 xla_ae_lines xla_line,
559 xla_acct_class_assgns acs,
560 xla_assignment_defns_b asd,
561 xla_distribution_links xla_dist
562 WHERE zx_dtl.request_id = c_request_id
563 AND zx_dtl.extract_source_ledger = 'AR'
564 AND zx_dtl.posted_date IS NOT NULL
565 AND zx_dtl.trx_id = xla_ent.source_id_int_1 -- Accounting Joins
566 AND xla_ent.entity_code = 'TRANSACTIONS' -- Check this condition
567 AND xla_ent.entity_id = xla_event.entity_id
568 AND xla_event.event_id = xla_head.event_id
569 AND xla_head.ae_header_id = xla_line.ae_header_id
570 AND xla_head.balance_type_code = 'A'
571 AND acs.program_code = 'TAX_REPORTING_LEDGER_SALES'
572 AND acs.program_code = asd.program_code
573 AND asd.assignment_code = acs.assignment_code
574 AND asd.enabled_flag = 'Y'
575 AND acs.accounting_class_code = xla_line.accounting_class_code
576 AND xla_ent.application_id = 222
577 AND xla_head.application_id = xla_ent.application_id
578 AND xla_head.application_id = xla_line.application_id
579 AND xla_head.ledger_id = c_ledger_id
580 -- AND xla_ent.ledger_id = zx_dtl.ledger_id
581 AND zx_dtl.account_class = 'TAX'
582 AND zx_dtl.tax_line_id = xla_dist.tax_line_ref_id
583 AND zx_dtl.actg_source_id = xla_dist.source_distribution_id_num_1
584 AND xla_head.ae_header_id = xla_dist.ae_header_id
585 AND xla_line.ae_header_id = xla_dist.ae_header_id
586 AND xla_line.ae_line_num = xla_dist.ae_line_num
587 -- can we get header_id as input parameter to the cursor? In that case we can add following join
588 -- AND xla_head.ae_header_id = :c_header_id
589 -- AND xla_dist.tax_line_ref_id IS NOT NULL
590 -- AND xla_dist.accounting_line_code = 'TAX'
591 UNION
592 SELECT ZX_DTL.DETAIL_TAX_LINE_ID,
593 ZX_DTL.LEDGER_ID,
594 ZX_DTL.INTERNAL_ORGANIZATION_ID,
595 ZX_DTL.TAX_DATE,
596 ZX_DTL.HQ_ESTB_REG_NUMBER,
597 ZX_DTL.TRX_ID ,
598 ZX_DTL.TRX_TYPE_ID ,
599 ZX_DTL.DOC_SEQ_ID,
600 ZX_DTL.TRX_LINE_CLASS,
601 ZX_DTL.TRX_BATCH_SOURCE_ID,
602 ZX_DTL.TAX_RATE_ID ,
603 ZX_DTL.TAX_RATE_VAT_TRX_TYPE_CODE,
604 ZX_DTL.TAX_RATE_REGISTER_TYPE_CODE,
605 ZX_DTL.TAX_EXEMPTION_ID ,
606 ZX_DTL.TAX_EXCEPTION_ID ,
607 ZX_DTL.TAX_LINE_ID ,
608 ZX_DTL.TAX_AMT ,
609 nvl(ZX_DTL.TAX_AMT_FUNCL_CURR,ZX_DTL.TAX_AMT) ,
610 ZX_DTL.TAX_LINE_NUMBER ,
611 ZX_DTL.TAXABLE_AMT ,
612 nvl(ZX_DTL.TAXABLE_AMT_FUNCL_CURR,ZX_DTL.TAXABLE_AMT) ,
613 ZX_DTL.TRX_LINE_ID ,
614 ZX_DTL.TAX_EXCEPTION_REASON_CODE ,
615 ZX_DTL.EXEMPT_REASON_CODE,
616 ZX_DTL.RECONCILIATION_FLAG ,
617 ZX_DTL.INTERNAL_ORGANIZATION_ID,
618 ZX_DTL.BR_REF_CUSTOMER_TRX_ID,
619 ZX_DTL.REVERSE_FLAG,
620 ZX_DTL.AMOUNT_APPLIED,
621 ZX_DTL.TAX_RATE,
622 ZX_DTL.TAX_RATE_CODE,
623 ZX_DTL.TAX_TYPE_CODE,
624 ZX_DTL.TRX_DATE,
625 ZX_DTL.TRX_CURRENCY_CODE,
626 ZX_DTL.CURRENCY_CONVERSION_RATE,
627 ZX_DTL.APPLICATION_ID,
628 ZX_DTL.DOC_EVENT_STATUS,
629 ZX_DTL.EXTRACT_SOURCE_LEDGER ,
630 ZX_DTL.FUNCTIONAL_CURRENCY_CODE,
631 ZX_DTL.MINIMUM_ACCOUNTABLE_UNIT,
632 ZX_DTL.PRECISION,
633 ZX_DTL.RECEIPT_CLASS_ID ,
634 ZX_DTL.EXCEPTION_RATE,
635 ZX_DTL.SHIP_FROM_PARTY_TAX_PROF_ID,
636 ZX_DTL.SHIP_FROM_SITE_TAX_PROF_ID,
637 ZX_DTL.SHIP_TO_PARTY_TAX_PROF_ID ,
638 ZX_DTL.SHIP_TO_SITE_TAX_PROF_ID ,
639 ZX_DTL.BILL_TO_PARTY_TAX_PROF_ID,
640 ZX_DTL.BILL_TO_SITE_TAX_PROF_ID,
641 ZX_DTL.BILL_FROM_PARTY_TAX_PROF_ID,
642 ZX_DTL.BILL_FROM_SITE_TAX_PROF_ID,
643 ZX_DTL.BILLING_TRADING_PARTNER_ID,
644 ZX_DTL.BILLING_TP_SITE_ID,
645 ZX_DTL.BILLING_TP_ADDRESS_ID,
646 ZX_DTL.SHIPPING_TRADING_PARTNER_ID,
647 ZX_DTL.SHIPPING_TP_SITE_ID,
648 ZX_DTL.SHIPPING_TP_ADDRESS_ID,
649 ZX_DTL.BILL_TO_PARTY_ID,
650 ZX_DTL.BILL_TO_PARTY_SITE_ID,
651 ZX_DTL.SHIP_TO_PARTY_ID,
652 ZX_DTL.SHIP_TO_PARTY_SITE_ID,
653 ZX_DTL.HISTORICAL_FLAG,
654 ZX_DTL.POSTED_DATE,
655 TO_CHAR(NULL), --xla_event.event_type_code, -- Accounting Columns
656 TO_NUMBER(NULL), --xla_event.event_number,
657 TO_CHAR(NULL), --xla_event.event_status_code,
658 TO_CHAR(NULL), --xla_head.je_category_name,
659 TO_DATE(NULL), --xla_head.accounting_date,
660 ZX_DTL.POSTED_FLAG, --xla_head.gl_transfer_status_code,
661 TO_CHAR(NULL), --xla_head.description,
662 TO_NUMBER(NULL), --xla_line.ae_line_num,
663 TO_CHAR(NULL), --xla_line.accounting_class_code,
664 TO_CHAR(NULL), --xla_line.description,
665 TO_NUMBER(NULL), --xla_line.statistical_amount,
666 TO_CHAR(NULL), --xla_event.process_status_code,
667 TO_CHAR(NULL), --xla_head.gl_transfer_status_code,
668 TO_NUMBER(NULL), --xla_head.doc_sequence_id,
669 TO_NUMBER(NULL), --xla_head.doc_sequence_value,
670 TO_NUMBER(NULL), --xla_line.party_id,
671 TO_NUMBER(NULL), --xla_line.party_site_id,
672 TO_CHAR(NULL), --xla_line.party_type_code,
673 TO_NUMBER(NULL), --xla_event.event_id,
674 TO_NUMBER(NULL), --xla_head.ae_header_id,
675 TO_NUMBER(NULL), --xla_line.code_combination_id,
676 TO_CHAR(NULL), --xla_head.period_name,
677 ZX_DTL.ACTG_SOURCE_ID,
678 zx_dtl.bank_account_id,
679 ZX_DTL.tax_determine_date --Bug 5622686
680 FROM zx_rep_trx_detail_t zx_dtl
681 WHERE zx_dtl.request_id = c_request_id
682 AND zx_dtl.extract_source_ledger = 'AR'
683 AND ((zx_dtl.posted_date IS NULL) OR
684 (zx_dtl.posted_date IS NOT NULL AND zx_dtl.tax_line_id is NULL));
685 /* OR
686 ( zx_dtl.posted_date IS NOT NULL
687 AND not exists(select 1 from xla_transaction_entities
688 where source_id_int_1 = zx_dtl.trx_id
689 and application_id = 2222))); */
690 /*Defined new cursor detail_t_cur_trx_line for bug7503539 bibeura */
691 CURSOR detail_t_cur_trx_line(c_request_id IN NUMBER,c_ledger_id NUMBER ) IS
692 SELECT DISTINCT ZX_DTL.DETAIL_TAX_LINE_ID,
693 ZX_DTL.LEDGER_ID,
694 ZX_DTL.INTERNAL_ORGANIZATION_ID,
695 ZX_DTL.TAX_DATE,
696 ZX_DTL.HQ_ESTB_REG_NUMBER,
697 ZX_DTL.TRX_ID ,
698 ZX_DTL.TRX_TYPE_ID ,
699 ZX_DTL.DOC_SEQ_ID,
700 ZX_DTL.TRX_LINE_CLASS,
701 ZX_DTL.TRX_BATCH_SOURCE_ID,
702 ZX_DTL.TAX_RATE_ID ,
703 ZX_DTL.TAX_RATE_VAT_TRX_TYPE_CODE,
704 ZX_DTL.TAX_RATE_REGISTER_TYPE_CODE,
705 ZX_DTL.TAX_EXEMPTION_ID ,
706 ZX_DTL.TAX_EXCEPTION_ID ,
707 ZX_DTL.TAX_LINE_ID ,
708 ZX_DTL.TAX_AMT ,
709 nvl(ZX_DTL.TAX_AMT_FUNCL_CURR,ZX_DTL.TAX_AMT) ,
710 ZX_DTL.TAX_LINE_NUMBER ,
711 ZX_DTL.TAXABLE_AMT ,
712 nvl(ZX_DTL.TAXABLE_AMT_FUNCL_CURR,ZX_DTL.TAXABLE_AMT) ,
713 ZX_DTL.TRX_LINE_ID ,
714 ZX_DTL.TAX_EXCEPTION_REASON_CODE ,
715 ZX_DTL.EXEMPT_REASON_CODE,
716 ZX_DTL.RECONCILIATION_FLAG ,
717 ZX_DTL.INTERNAL_ORGANIZATION_ID,
718 ZX_DTL.BR_REF_CUSTOMER_TRX_ID,
719 ZX_DTL.REVERSE_FLAG,
720 ZX_DTL.AMOUNT_APPLIED,
721 ZX_DTL.TAX_RATE,
722 ZX_DTL.TAX_RATE_CODE,
723 ZX_DTL.TAX_TYPE_CODE,
724 ZX_DTL.TRX_DATE,
725 ZX_DTL.TRX_CURRENCY_CODE,
726 ZX_DTL.CURRENCY_CONVERSION_RATE,
727 ZX_DTL.APPLICATION_ID,
728 ZX_DTL.DOC_EVENT_STATUS,
729 ZX_DTL.EXTRACT_SOURCE_LEDGER ,
730 ZX_DTL.FUNCTIONAL_CURRENCY_CODE,
731 ZX_DTL.MINIMUM_ACCOUNTABLE_UNIT,
732 ZX_DTL.PRECISION,
733 ZX_DTL.RECEIPT_CLASS_ID ,
734 ZX_DTL.EXCEPTION_RATE,
735 ZX_DTL.SHIP_FROM_PARTY_TAX_PROF_ID,
736 ZX_DTL.SHIP_FROM_SITE_TAX_PROF_ID,
737 ZX_DTL.SHIP_TO_PARTY_TAX_PROF_ID ,
738 ZX_DTL.SHIP_TO_SITE_TAX_PROF_ID ,
739 ZX_DTL.BILL_TO_PARTY_TAX_PROF_ID,
740 ZX_DTL.BILL_TO_SITE_TAX_PROF_ID,
741 ZX_DTL.BILL_FROM_PARTY_TAX_PROF_ID,
742 ZX_DTL.BILL_FROM_SITE_TAX_PROF_ID,
743 ZX_DTL.BILLING_TRADING_PARTNER_ID,
744 ZX_DTL.BILLING_TP_SITE_ID,
745 ZX_DTL.BILLING_TP_ADDRESS_ID,
746 ZX_DTL.SHIPPING_TRADING_PARTNER_ID,
747 ZX_DTL.SHIPPING_TP_SITE_ID,
748 ZX_DTL.SHIPPING_TP_ADDRESS_ID,
749 ZX_DTL.BILL_TO_PARTY_ID,
750 ZX_DTL.BILL_TO_PARTY_SITE_ID,
751 ZX_DTL.SHIP_TO_PARTY_ID,
752 ZX_DTL.SHIP_TO_PARTY_SITE_ID,
753 ZX_DTL.HISTORICAL_FLAG,
754 ZX_DTL.POSTED_DATE,
755 xla_event.event_type_code,
756 xla_event.event_number,
757 xla_event.event_status_code,
758 xla_head.je_category_name,
759 xla_head.accounting_date,
760 xla_head.gl_transfer_status_code,
761 xla_head.description,
762 xla_line.ae_line_num,
763 xla_line.accounting_class_code,
764 xla_line.description,
765 xla_line.statistical_amount,
766 xla_event.process_status_code,
767 xla_head.gl_transfer_status_code,
768 xla_head.doc_sequence_id,
769 xla_head.doc_sequence_value,
770 xla_line.party_id,
771 xla_line.party_site_id,
772 xla_line.party_type_code,
773 xla_event.event_id,
774 xla_head.ae_header_id,
775 xla_line.code_combination_id,
776 xla_head.period_name,
777 zx_dtl.actg_source_id,
778 zx_dtl.bank_account_id,
779 ZX_DTL.tax_determine_date
780 FROM zx_rep_trx_detail_t zx_dtl,
781 xla_transaction_entities xla_ent,
782 xla_events xla_event,
783 xla_ae_headers xla_head,
784 xla_ae_lines xla_line,
785 xla_acct_class_assgns acs,
786 xla_assignment_defns_b asd,
787 xla_distribution_links xla_dist
788 WHERE zx_dtl.request_id = c_request_id
789 AND zx_dtl.extract_source_ledger = 'AR'
790 AND zx_dtl.posted_date IS NOT NULL
791 AND zx_dtl.trx_id = xla_ent.source_id_int_1
792 AND xla_ent.entity_code = 'TRANSACTIONS'
793 AND xla_ent.entity_id = xla_event.entity_id
794 AND xla_event.event_id = xla_head.event_id
795 AND xla_head.ae_header_id = xla_line.ae_header_id
796 AND xla_head.balance_type_code = 'A'
797 AND acs.program_code = 'TAX_REPORTING_LEDGER_SALES'
798 AND acs.program_code = asd.program_code
799 AND asd.assignment_code = acs.assignment_code
800 AND asd.enabled_flag = 'Y'
801 AND acs.accounting_class_code = xla_line.accounting_class_code
802 AND xla_ent.application_id = 222
803 AND xla_head.application_id = xla_ent.application_id
804 AND xla_head.application_id = xla_line.application_id
805 AND xla_head.ledger_id = c_ledger_id
806 AND zx_dtl.account_class = 'TAX'
807 AND zx_dtl.tax_line_id = xla_dist.tax_line_ref_id
808 -- AND zx_dtl.actg_source_id = xla_dist.source_distribution_id_num_1
809 AND xla_head.ae_header_id = xla_dist.ae_header_id
810 AND xla_line.ae_header_id = xla_dist.ae_header_id
811 AND xla_line.ae_line_num = xla_dist.ae_line_num
812 UNION
813 SELECT ZX_DTL.DETAIL_TAX_LINE_ID,
814 ZX_DTL.LEDGER_ID,
815 ZX_DTL.INTERNAL_ORGANIZATION_ID,
816 ZX_DTL.TAX_DATE,
817 ZX_DTL.HQ_ESTB_REG_NUMBER,
818 ZX_DTL.TRX_ID ,
819 ZX_DTL.TRX_TYPE_ID ,
820 ZX_DTL.DOC_SEQ_ID,
821 ZX_DTL.TRX_LINE_CLASS,
822 ZX_DTL.TRX_BATCH_SOURCE_ID,
823 ZX_DTL.TAX_RATE_ID ,
824 ZX_DTL.TAX_RATE_VAT_TRX_TYPE_CODE,
825 ZX_DTL.TAX_RATE_REGISTER_TYPE_CODE,
826 ZX_DTL.TAX_EXEMPTION_ID ,
827 ZX_DTL.TAX_EXCEPTION_ID ,
828 ZX_DTL.TAX_LINE_ID ,
829 ZX_DTL.TAX_AMT ,
830 nvl(ZX_DTL.TAX_AMT_FUNCL_CURR,ZX_DTL.TAX_AMT) ,
831 ZX_DTL.TAX_LINE_NUMBER ,
832 ZX_DTL.TAXABLE_AMT ,
833 nvl(ZX_DTL.TAXABLE_AMT_FUNCL_CURR,ZX_DTL.TAXABLE_AMT) ,
834 ZX_DTL.TRX_LINE_ID ,
835 ZX_DTL.TAX_EXCEPTION_REASON_CODE ,
836 ZX_DTL.EXEMPT_REASON_CODE,
837 ZX_DTL.RECONCILIATION_FLAG ,
838 ZX_DTL.INTERNAL_ORGANIZATION_ID,
839 ZX_DTL.BR_REF_CUSTOMER_TRX_ID,
840 ZX_DTL.REVERSE_FLAG,
841 ZX_DTL.AMOUNT_APPLIED,
842 ZX_DTL.TAX_RATE,
843 ZX_DTL.TAX_RATE_CODE,
844 ZX_DTL.TAX_TYPE_CODE,
845 ZX_DTL.TRX_DATE,
846 ZX_DTL.TRX_CURRENCY_CODE,
847 ZX_DTL.CURRENCY_CONVERSION_RATE,
848 ZX_DTL.APPLICATION_ID,
849 ZX_DTL.DOC_EVENT_STATUS,
850 ZX_DTL.EXTRACT_SOURCE_LEDGER ,
851 ZX_DTL.FUNCTIONAL_CURRENCY_CODE,
852 ZX_DTL.MINIMUM_ACCOUNTABLE_UNIT,
853 ZX_DTL.PRECISION,
854 ZX_DTL.RECEIPT_CLASS_ID ,
855 ZX_DTL.EXCEPTION_RATE,
856 ZX_DTL.SHIP_FROM_PARTY_TAX_PROF_ID,
857 ZX_DTL.SHIP_FROM_SITE_TAX_PROF_ID,
858 ZX_DTL.SHIP_TO_PARTY_TAX_PROF_ID ,
859 ZX_DTL.SHIP_TO_SITE_TAX_PROF_ID ,
860 ZX_DTL.BILL_TO_PARTY_TAX_PROF_ID,
861 ZX_DTL.BILL_TO_SITE_TAX_PROF_ID,
862 ZX_DTL.BILL_FROM_PARTY_TAX_PROF_ID,
863 ZX_DTL.BILL_FROM_SITE_TAX_PROF_ID,
864 ZX_DTL.BILLING_TRADING_PARTNER_ID,
865 ZX_DTL.BILLING_TP_SITE_ID,
866 ZX_DTL.BILLING_TP_ADDRESS_ID,
867 ZX_DTL.SHIPPING_TRADING_PARTNER_ID,
868 ZX_DTL.SHIPPING_TP_SITE_ID,
869 ZX_DTL.SHIPPING_TP_ADDRESS_ID,
870 ZX_DTL.BILL_TO_PARTY_ID,
871 ZX_DTL.BILL_TO_PARTY_SITE_ID,
872 ZX_DTL.SHIP_TO_PARTY_ID,
873 ZX_DTL.SHIP_TO_PARTY_SITE_ID,
874 ZX_DTL.HISTORICAL_FLAG,
875 ZX_DTL.POSTED_DATE,
876 TO_CHAR(NULL), --xla_event.event_type_code, -- Accounting Columns
877 TO_NUMBER(NULL), --xla_event.event_number,
878 TO_CHAR(NULL), --xla_event.event_status_code,
879 TO_CHAR(NULL), --xla_head.je_category_name,
880 TO_DATE(NULL), --xla_head.accounting_date,
881 ZX_DTL.POSTED_FLAG, --xla_head.gl_transfer_status_code,
882 TO_CHAR(NULL), --xla_head.description,
883 TO_NUMBER(NULL), --xla_line.ae_line_num,
884 TO_CHAR(NULL), --xla_line.accounting_class_code,
885 TO_CHAR(NULL), --xla_line.description,
886 TO_NUMBER(NULL), --xla_line.statistical_amount,
887 TO_CHAR(NULL), --xla_event.process_status_code,
888 TO_CHAR(NULL), --xla_head.gl_transfer_status_code,
889 TO_NUMBER(NULL), --xla_head.doc_sequence_id,
890 TO_NUMBER(NULL), --xla_head.doc_sequence_value,
891 TO_NUMBER(NULL), --xla_line.party_id,
892 TO_NUMBER(NULL), --xla_line.party_site_id,
893 TO_CHAR(NULL), --xla_line.party_type_code,
894 TO_NUMBER(NULL), --xla_event.event_id,
895 TO_NUMBER(NULL), --xla_head.ae_header_id,
896 TO_NUMBER(NULL), --xla_line.code_combination_id,
897 TO_CHAR(NULL), --xla_head.period_name,
898 ZX_DTL.ACTG_SOURCE_ID,
899 zx_dtl.bank_account_id,
900 ZX_DTL.tax_determine_date
901 FROM zx_rep_trx_detail_t zx_dtl
902 WHERE zx_dtl.request_id = c_request_id
903 AND zx_dtl.extract_source_ledger = 'AR'
904 AND ((zx_dtl.posted_date IS NULL) OR
905 (zx_dtl.posted_date IS NOT NULL AND zx_dtl.tax_line_id is NULL));
906
907 L_TRX_CLASS VARCHAR2(30);
908 L_TAXABLE_AMOUNT NUMBER;
909 L_TAXABLE_ACCOUNTED_AMOUNT NUMBER;
910 l_balancing_segment VARCHAR2(25);
911 l_accounting_segment VARCHAR2(25);
912 l_ledger_id NUMBER(15);
913
914 -- L_BANKING_TP_NAME AR_TAX_EXTRACT_SUB_ITF.BANKING_TP_NAME%TYPE;
915 -- L_BANKING_TP_TAXPAYER_ID AR_TAX_EXTRACT_SUB_ITF.BANKING_TP_TAXPAYER_ID%type;
916 -- L_MATRIX_REPORT VARCHAR2(1);
917
918 -- L_TRX_APPLIED_TO_TRX_ID NUMBER; -- where it is used, AP
919 -- L_ACCOUNTING_DATE DATE; -- where is this being used AP
920 -- L_TRX_CURRENCY_CODE VARCHAR2(15);
921 -- RA_SUB_ITF_TABLE_REC AR_TAX_EXTRACT_SUB_ITF%ROWTYPE;
922 l_count NUMBER :=0;
923 l_act_nact ZX_EXTRACT_PKG.ACTG_LINE_TYPE_CODE_TBL;
924 j number;
925
926 BEGIN
927
928 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
929 g_request_id := P_TRL_GLOBAL_VARIABLES_REC.request_id;
930 l_ledger_id := P_TRL_GLOBAL_VARIABLES_REC.ledger_id;
931 g_coa_id := P_TRL_GLOBAL_VARIABLES_REC.chart_of_accounts_id;
932
933 IF (g_level_procedure >= g_current_runtime_level ) THEN
934 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.UPDATE_ADDITIONAL_INFO.BEGIN',
935 'ZX_AR_POPULATE_PKG: UPDATE_ADDITIONAL_INFO(+)');
936 END IF;
937
938 -- get functional currency code code --
939 gl_mc_info.get_ledger_currency(p_trl_global_variables_rec.ledger_id,
940 G_FUN_CURRENCY_CODE);
941
942 XLA_SECURITY_PKG.set_security_context(p_application_id => 602);
943
944 -- Accounting Flex Field Information --
945 -- Determine which segment is balancing segment for the given
946 -- chart of accounts (Set of books)
947
948 l_balancing_segment := fa_rx_flex_pkg.flex_sql(
949 p_application_id =>101,
950 p_id_flex_code => 'GL#',
951 p_id_flex_num => P_TRL_GLOBAL_VARIABLES_REC.chart_of_accounts_id,
952 p_table_alias => '',
953 p_mode => 'SELECT',
954 p_qualifier => 'GL_BALANCING');
955
956 l_accounting_segment := fa_rx_flex_pkg.flex_sql(
957 p_application_id =>101,
958 p_id_flex_code => 'GL#',
959 p_id_flex_num => P_TRL_GLOBAL_VARIABLES_REC.chart_of_accounts_id,
960 p_table_alias => '',
961 p_mode => 'SELECT',
962 p_qualifier => 'GL_ACCOUNT');
963
964 -- The above function will return balancing segment in the form CC.SEGMENT1
965 -- we need to drop CC. to get the actual balancing segment.
966
967 l_balancing_segment := substrb(l_balancing_segment,
968 instrb(l_balancing_segment,'.')+1);
969
970 IF P_TRL_GLOBAL_VARIABLES_REC.SUMMARY_LEVEL = 'TRANSACTION_DISTRIBUTION' THEN
971 OPEN detail_t_cur(P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID,
972 NVL(p_trl_global_variables_rec.reporting_ledger_id,
973 P_TRL_GLOBAL_VARIABLES_REC.ledger_id));
974 ELSE
975 OPEN detail_t_cur_trx_line(P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID,
976 NVL(p_trl_global_variables_rec.reporting_ledger_id,
977 P_TRL_GLOBAL_VARIABLES_REC.ledger_id));
978 END IF;
979 LOOP
980 IF P_TRL_GLOBAL_VARIABLES_REC.SUMMARY_LEVEL = 'TRANSACTION_DISTRIBUTION' THEN
981 FETCH detail_t_cur BULK COLLECT INTO
982 GT_DETAIL_TAX_LINE_ID,
983 GT_LEDGER_ID,
984 GT_INTERNAL_ORGANIZATION_ID,
985 GT_TAX_DATE,
986 GT_HQ_ESTB_REG_NUMBER,
987 GT_TRX_ID,
988 GT_TRX_TYPE_ID,
989 GT_DOC_SEQ_ID,
990 GT_TRX_CLASS,
991 GT_TRX_BATCH_SOURCE_ID,
992 GT_TAX_RATE_ID,
993 GT_TAX_RATE_VAT_TRX_TYPE_CODE,
994 GT_TAX_RATE_REG_TYPE_CODE,
995 GT_TAX_EXEMPTION_ID,
996 GT_TAX_EXCEPTION_ID,
997 GT_TAX_LINE_ID,
998 GT_TAX_AMT,
999 GT_TAX_AMT_FUNCL_CURR,
1000 GT_TAX_LINE_NUMBER,
1001 GT_TAXABLE_AMT,
1002 GT_TAXABLE_AMT_FUNCL_CURR,
1003 GT_TRX_LINE_ID,
1004 GT_TAX_EXCEPTION_REASON_CODE,
1005 GT_EXEMPT_REASON_CODE,
1006 GT_RECONCILIATION_FLAG,
1007 GT_INTERNAL_ORGANIZATION_ID,
1008 GT_BR_REF_CUSTOMER_TRX_ID,
1009 GT_REVERSE_FLAG,
1010 GT_AMOUNT_APPLIED,
1011 GT_TAX_RATE,
1012 GT_TAX_RATE_CODE,
1013 GT_TAX_TYPE_CODE,
1014 GT_TRX_DATE,
1015 GT_TRX_CURRENCY_CODE,
1016 GT_CURRENCY_CONVERSION_RATE,
1017 GT_APPLICATION_ID,
1018 GT_DOC_EVENT_STATUS,
1019 GT_EXTRACT_SOURCE_LEDGER,
1020 GT_FUNCTIONAL_CURRENCY_CODE,
1021 GT_MINIMUM_ACCOUNTABLE_UNIT,
1022 GT_PRECISION,
1023 GT_RECEIPT_CLASS_ID,
1024 GT_EXCEPTION_RATE,
1025 GT_SHIP_FROM_PARTY_TAX_PROF_ID,
1026 GT_SHIP_FROM_SITE_TAX_PROF_ID,
1027 GT_SHIP_TO_PARTY_TAX_PROF_ID,
1028 GT_SHIP_TO_SITE_TAX_PROF_ID,
1029 GT_BILL_TO_PARTY_TAX_PROF_ID,
1030 GT_BILL_TO_SITE_TAX_PROF_ID,
1031 GT_BILL_FROM_PARTY_TAX_PROF_ID,
1032 GT_BILL_FROM_SITE_TAX_PROF_ID,
1033 GT_BILLING_TP_ID, --bill_third_pty_acct_id
1034 GT_BILLING_TP_SITE_ID, --bill_to_cust_acct_site_use_id
1035 GT_BILLING_TP_ADDRESS_ID, --bill_third_pty_acct_site_id
1036 GT_SHIPPING_TP_ID, --ship_third_pty_acct_id
1037 GT_SHIPPING_TP_SITE_ID, --ship_to_cust_acct_site_use_id
1038 GT_SHIPPING_TP_ADDRESS_ID, --SHIP_THIRD_PTY_ACCT_SITE_ID
1039 GT_BILL_TO_PARTY_ID,
1040 GT_BILL_TO_PARTY_SITE_ID,
1041 GT_SHIP_TO_PARTY_ID,
1042 GT_SHIP_TO_PARTY_SITE_ID,
1043 GT_HISTORICAL_FLAG,
1044 GT_POSTED_DATE,
1045 gt_actg_event_type_code,
1046 gt_actg_event_number,
1047 gt_actg_event_status_flag,
1048 gt_actg_category_code,
1049 gt_accounting_date,
1050 gt_gl_transfer_flag,
1051 --gt_gl_transfer_run_id,
1052 gt_actg_header_description,
1053 gt_actg_line_num,
1054 gt_actg_line_type_code,
1055 gt_actg_line_description,
1056 gt_actg_stat_amt,
1057 gt_actg_error_code,
1058 gt_gl_transfer_code,
1059 gt_actg_doc_sequence_id,
1060 -- gt_actg_doc_sequence_name,
1061 gt_actg_doc_sequence_value,
1062 gt_actg_party_id,
1063 gt_actg_party_site_id,
1064 gt_actg_party_type,
1065 gt_actg_event_id,
1066 gt_actg_header_id,
1067 -- gt_actg_source_table,
1068 gt_actg_line_ccid,
1069 gt_period_name,
1070 gt_actg_source_id,
1071 gt_bank_account_id,
1072 gt_tax_determine_date
1073 LIMIT C_LINES_PER_COMMIT;
1074
1075 IF (g_level_procedure >= g_current_runtime_level ) THEN
1076 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.Dist cursor Opened',
1077 'detail_t_cur : ');
1078 END IF;
1079
1080 ELSE
1081 FETCH detail_t_cur_trx_line BULK COLLECT INTO
1082 GT_DETAIL_TAX_LINE_ID,
1083 GT_LEDGER_ID,
1084 GT_INTERNAL_ORGANIZATION_ID,
1085 GT_TAX_DATE,
1086 GT_HQ_ESTB_REG_NUMBER,
1087 GT_TRX_ID,
1088 GT_TRX_TYPE_ID,
1089 GT_DOC_SEQ_ID,
1090 GT_TRX_CLASS,
1091 GT_TRX_BATCH_SOURCE_ID,
1092 GT_TAX_RATE_ID,
1093 GT_TAX_RATE_VAT_TRX_TYPE_CODE,
1094 GT_TAX_RATE_REG_TYPE_CODE,
1095 GT_TAX_EXEMPTION_ID,
1096 GT_TAX_EXCEPTION_ID,
1097 GT_TAX_LINE_ID,
1098 GT_TAX_AMT,
1099 GT_TAX_AMT_FUNCL_CURR,
1100 GT_TAX_LINE_NUMBER,
1101 GT_TAXABLE_AMT,
1102 GT_TAXABLE_AMT_FUNCL_CURR,
1103 GT_TRX_LINE_ID,
1104 GT_TAX_EXCEPTION_REASON_CODE,
1105 GT_EXEMPT_REASON_CODE,
1106 GT_RECONCILIATION_FLAG,
1107 GT_INTERNAL_ORGANIZATION_ID,
1108 GT_BR_REF_CUSTOMER_TRX_ID,
1109 GT_REVERSE_FLAG,
1110 GT_AMOUNT_APPLIED,
1111 GT_TAX_RATE,
1112 GT_TAX_RATE_CODE,
1113 GT_TAX_TYPE_CODE,
1114 GT_TRX_DATE,
1115 GT_TRX_CURRENCY_CODE,
1116 GT_CURRENCY_CONVERSION_RATE,
1117 GT_APPLICATION_ID,
1118 GT_DOC_EVENT_STATUS,
1119 GT_EXTRACT_SOURCE_LEDGER,
1120 GT_FUNCTIONAL_CURRENCY_CODE,
1121 GT_MINIMUM_ACCOUNTABLE_UNIT,
1122 GT_PRECISION,
1123 GT_RECEIPT_CLASS_ID,
1124 GT_EXCEPTION_RATE,
1125 GT_SHIP_FROM_PARTY_TAX_PROF_ID,
1126 GT_SHIP_FROM_SITE_TAX_PROF_ID,
1127 GT_SHIP_TO_PARTY_TAX_PROF_ID,
1128 GT_SHIP_TO_SITE_TAX_PROF_ID,
1129 GT_BILL_TO_PARTY_TAX_PROF_ID,
1130 GT_BILL_TO_SITE_TAX_PROF_ID,
1131 GT_BILL_FROM_PARTY_TAX_PROF_ID,
1132 GT_BILL_FROM_SITE_TAX_PROF_ID,
1133 GT_BILLING_TP_ID, --bill_third_pty_acct_id
1134 GT_BILLING_TP_SITE_ID, --bill_to_cust_acct_site_use_id
1135 GT_BILLING_TP_ADDRESS_ID, --bill_third_pty_acct_site_id
1136 GT_SHIPPING_TP_ID, --ship_third_pty_acct_id
1137 GT_SHIPPING_TP_SITE_ID, --ship_to_cust_acct_site_use_id
1138 GT_SHIPPING_TP_ADDRESS_ID, --SHIP_THIRD_PTY_ACCT_SITE_ID
1139 GT_BILL_TO_PARTY_ID,
1140 GT_BILL_TO_PARTY_SITE_ID,
1141 GT_SHIP_TO_PARTY_ID,
1142 GT_SHIP_TO_PARTY_SITE_ID,
1143 GT_HISTORICAL_FLAG,
1144 GT_POSTED_DATE,
1145 gt_actg_event_type_code,
1146 gt_actg_event_number,
1147 gt_actg_event_status_flag,
1148 gt_actg_category_code,
1149 gt_accounting_date,
1150 gt_gl_transfer_flag,
1151 --gt_gl_transfer_run_id,
1152 gt_actg_header_description,
1153 gt_actg_line_num,
1154 gt_actg_line_type_code,
1155 gt_actg_line_description,
1156 gt_actg_stat_amt,
1157 gt_actg_error_code,
1158 gt_gl_transfer_code,
1159 gt_actg_doc_sequence_id,
1160 -- gt_actg_doc_sequence_name,
1161 gt_actg_doc_sequence_value,
1162 gt_actg_party_id,
1163 gt_actg_party_site_id,
1164 gt_actg_party_type,
1165 gt_actg_event_id,
1166 gt_actg_header_id,
1167 -- gt_actg_source_table,
1168 gt_actg_line_ccid,
1169 gt_period_name,
1170 gt_actg_source_id,
1171 gt_bank_account_id,
1172 gt_tax_determine_date
1173 LIMIT C_LINES_PER_COMMIT;
1174
1175 IF (g_level_procedure >= g_current_runtime_level ) THEN
1176 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.Trx / Line cursor Opened',
1177 'detail_t_cur_trx_line : ');
1178 END IF;
1179 END IF;
1180
1181
1182 l_count := nvl(GT_DETAIL_TAX_LINE_ID.COUNT,0);
1183
1184 -- Initialize j value for accounting records count --
1185 j:=0;
1186 IF (g_level_procedure >= g_current_runtime_level ) THEN
1187 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.get_accounting_info',
1188 'Row Count After fetch : ' ||to_char(l_count));
1189 END IF;
1190
1191 IF l_count >0 THEN
1192 initialize_variables(l_count);
1193
1194 G_REP_CONTEXT_ID := ZX_EXTRACT_PKG.GET_REP_CONTEXT_ID(P_TRL_GLOBAL_VARIABLES_REC.LEGAL_ENTITY_ID,
1195 P_TRL_GLOBAL_VARIABLES_REC.request_id);
1196
1197 FOR i IN 1..l_count
1198 LOOP
1199 L_TRX_CLASS := GT_TRX_CLASS(i);
1200
1201 IF (g_level_procedure >= g_current_runtime_level ) THEN
1202 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.UPDATE_ADDITIONAL_INFO',
1203 'Inside Loop : detail tax line id:'||to_char(gt_detail_tax_line_id(i)));
1204 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.UPDATE_ADDITIONAL_INFO',
1205 'i : '||to_char(i)||' L_TRX_CLASS : '||L_TRX_CLASS);
1206 END IF;
1207
1208 IF P_TRL_GLOBAL_VARIABLES_REC.SUMMARY_LEVEL = 'TRANSACTION_DISTRIBUTION'
1209 OR ( UPPER(L_TRX_CLASS) IN
1210 ('APP','EDISC','UNEDISC','ADJ','FINCHRG','MISC_CASH_RECEIPT','BR') )
1211 THEN
1212 -- Pass the taxable amount columns for rounding
1213 L_TAXABLE_AMOUNT := GT_TAXABLE_AMT(i);
1214 L_TAXABLE_ACCOUNTED_AMOUNT := GT_TAXABLE_AMT_FUNCL_CURR(i);
1215 END IF;
1216
1217 -- Replacement to populate_inv()
1218
1219 IF L_TRX_CLASS IN ('APP','EDISC','UNEDISC','ADJ','FINCHRG','MISC_CASH_RECEIPT','BR')
1220 THEN
1221 APP_FUNCTIONAL_AMOUNTS(
1222 GT_TRX_ID(i),
1223 GT_TAX_RATE_ID(i),
1224 GT_TRX_CURRENCY_CODE(i),
1225 GT_CURRENCY_CONVERSION_RATE(i),
1226 GT_PRECISION(i),
1227 GT_MINIMUM_ACCOUNTABLE_UNIT(i),
1228 GT_TAX_AMT(i),
1229 GT_TAXABLE_AMT(i),
1230 P_TRL_GLOBAL_VARIABLES_REC.SUMMARY_LEVEL,
1231 P_TRL_GLOBAL_VARIABLES_REC.REGISTER_TYPE,
1232 i);
1233 END IF;
1234
1235
1236 IF (gt_posted_date(i) IS NOT NULL AND
1237 P_TRL_GLOBAL_VARIABLES_REC.SUMMARY_LEVEL = 'TRANSACTION_DISTRIBUTION' ) THEN
1238
1239 IF p_trl_global_variables_rec.include_accounting_segments='Y' THEN
1240
1241 IF (g_level_procedure >= g_current_runtime_level ) THEN
1242 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.UPDATE_ADDITIONAL_INFO',
1243 'get_accounting_info Call:');
1244 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.UPDATE_ADDITIONAL_INFO',
1245 'detail tax line id:'||to_char(gt_detail_tax_line_id(i)));
1246 END IF;
1247
1248 j:=j+1;
1249 agt_detail_tax_line_id(j) := gt_detail_tax_line_id(i);
1250 agt_actg_event_type_code(j) := gt_actg_event_type_code(i);
1251 agt_actg_event_number(j) := gt_actg_event_number(i);
1252 agt_actg_event_status_flag(j) := gt_actg_event_status_flag(i);
1253 agt_actg_category_code(j) := gt_actg_category_code(i);
1254 agt_accounting_date(j) := gt_accounting_date(i);
1255 agt_gl_transfer_flag(j) := gt_gl_transfer_flag(i);
1256 -- agt_gl_transfer_run_id(j) := gt_gl_transfer_run_id(i);
1257 agt_actg_header_description(j) := gt_actg_header_description(i);
1258 agt_actg_line_num(j) := gt_actg_line_num(i);
1259 agt_actg_line_type_code(j) := gt_actg_line_type_code(i);
1260 agt_actg_line_description(j) := gt_actg_line_description(i);
1261 agt_actg_stat_amt(j) := gt_actg_stat_amt(i);
1262 agt_actg_error_code(j) := gt_actg_error_code(i);
1263 agt_gl_transfer_code(j) := gt_gl_transfer_code(i);
1264 agt_actg_doc_sequence_id(j) := gt_actg_doc_sequence_id(i);
1265 -- agt_actg_doc_sequence_name(j) := gt_actg_doc_sequence_name(i);
1266 agt_actg_doc_sequence_value(j) := gt_actg_doc_sequence_value(i);
1267 agt_actg_party_id(j) := gt_actg_party_id(i);
1268 agt_actg_party_site_id(j) := gt_actg_party_site_id(i);
1269 agt_actg_party_type(j) := gt_actg_party_type(i);
1270 agt_actg_event_id(j) := gt_actg_event_id(i);
1271 agt_actg_header_id(j) := gt_actg_header_id(i);
1272 agt_actg_source_id(j) := gt_actg_source_id(i);
1273 -- agt_actg_source_table(j) := gt_actg_source_table(i);
1274 agt_actg_line_ccid(j) := gt_actg_line_ccid(i);
1275 agt_period_name(j) := gt_period_name(i);
1276
1277 get_accounting_info(GT_TRX_ID(i),
1278 GT_TRX_LINE_ID(i),
1279 GT_TAX_LINE_ID(i),
1280 GT_ACTG_EVENT_ID(i),
1281 GT_ACTG_HEADER_ID(i),
1282 GT_ACTG_SOURCE_ID(i),
1283 l_balancing_segment,
1284 l_accounting_segment,
1285 P_TRL_GLOBAL_VARIABLES_REC.SUMMARY_LEVEL,
1286 P_TRL_GLOBAL_VARIABLES_REC.REPORT_NAME,
1287 L_TRX_CLASS,
1288 j) ;
1289
1290 END IF; -- Inlude account segments parameter check --
1291
1292 IF p_trl_global_variables_rec.reporting_ledger_id IS NOT NULL
1293 THEN
1294 get_accounting_amounts(GT_TRX_ID(i),
1295 GT_TRX_LINE_ID(i),
1296 GT_TAX_LINE_ID(i),
1297 -- GT_ENTITY_ID(i),
1298 GT_ACTG_EVENT_ID(i),
1299 GT_ACTG_HEADER_ID(i),
1300 GT_ACTG_SOURCE_ID(i),
1301 P_TRL_GLOBAL_VARIABLES_REC.SUMMARY_LEVEL,
1302 L_TRX_CLASS,
1303 p_trl_global_variables_rec.reporting_ledger_id, --l_ledger_id,
1304 i) ;
1305 END IF;
1306 IF (g_level_procedure >= g_current_runtime_level ) THEN
1307 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.inv_actg_amounts',
1308 'inv_actg_amounts call :GT_TAXABLE_AMT, GT_TAXABLE_AMT_FUNCL_CURR'||to_char(GT_TAXABLE_AMT(i))||
1309 'i='||to_char(i)||' j='||to_char(i)
1310 ||'-'||to_char(GT_TAXABLE_AMT_FUNCL_CURR(i)));
1311 END IF;
1312 --GT_TAXABLE_AMT_FUNCL_CURR(i) := null;
1313 --GT_TAX_AMT_FUNCL_CURR(i) := null;
1314
1315 END IF; -- Posted date check ---
1316
1317
1318 --Check This Code
1319 -- IF UPPER(L_TRX_CLASS) IN ('APP','EDISC','UNEDISC','ADJ','FINCHRG',
1320 -- 'MISC_CASH_RECEIPT','BR')
1321 -- AND GT_TAX_CODE_ID_TAB(i) IS NULL
1322 -- AND GT_TAX_OFFSET_TAX_CODE_ID_TAB(i) IS NOT NULL
1323 -- THEN
1324 --
1325 -- PG_TAX_CODE_ID_TAB(i) := PG_TAX_OFFSET_TAX_CODE_ID_TAB(i);
1326 --
1327 -- END If;
1328 -- Call to populate party information --
1329 EXTRACT_PARTY_INFO(i);
1330 populate_meaning(
1331 P_TRL_GLOBAL_VARIABLES_REC,
1332 i);
1333
1334 -- This api populates first party registration number if the HQ_ESTB_REG_NUMBER is null
1335 --
1336 IF GT_HQ_ESTB_REG_NUMBER(i) IS NULL AND
1337 P_TRL_GLOBAL_VARIABLES_REC.FIRST_PARTY_TAX_REG_NUM IS NULL THEN
1338 populate_tax_reg_num(
1339 P_TRL_GLOBAL_VARIABLES_REC,
1340 GT_INTERNAL_ORGANIZATION_ID(i),
1341 GT_TAX_DATE(i),
1342 i);
1343 ELSE
1344 GT_TAX_REG_NUM(i) := GT_HQ_ESTB_REG_NUMBER(i);
1345 END IF;
1346
1347
1348
1349 END LOOP; -- end loop of each extract line
1350
1351 END IF;
1352
1353 -- Call to update additional information in zx_rep_trx_detail_t table --
1354
1355 UPDATE_REP_DETAIL_T(l_count);
1356
1357 -- Call to insert accounting information in zx_rep_actg_ext_t table --
1358
1359 IF p_trl_global_variables_rec.include_accounting_segments='Y'
1360 AND NVL(gt_posted_date.count,0) <> 0 THEN
1361 insert_actg_info(j);
1362 END IF;
1363
1364 EXIT WHEN detail_t_cur%NOTFOUND
1365 OR detail_t_cur%NOTFOUND IS NULL;
1366
1367 END LOOP;
1368
1369 CLOSE detail_t_cur;
1370
1371 IF (g_level_procedure >= g_current_runtime_level ) THEN
1372 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.UPDATE_ADDITIONAL_INFO.END',
1373 'ZX_AR_POPULATE_PKG: UPDATE_ADDITIONAL_INFO(-)');
1374 END IF;
1375
1376 EXCEPTION
1377 WHEN OTHERS THEN
1378 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1379 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1380 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
1381 FND_MSG_PUB.Add;
1382 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1383 FND_LOG.STRING(g_level_unexpected,
1384 'ZX.TRL.ZX_AR_POPULATE_PKG.UPDATE_ADDITIONAL_INFO',
1385 g_error_buffer);
1386 END IF;
1387
1388 P_TRL_GLOBAL_VARIABLES_REC.RETCODE := G_RETCODE;
1389
1390 END UPDATE_ADDITIONAL_INFO;
1391
1392
1393 PROCEDURE APP_FUNCTIONAL_AMOUNTS(
1394 P_TRX_ID IN NUMBER,
1395 P_TAX_CODE_ID IN NUMBER,
1396 P_CURRENCY_CODE IN VARCHAR2,
1397 P_EXCHANGE_RATE IN NUMBER,
1398 P_PRECISION IN NUMBER,
1399 P_MIN_ACCT_UNIT IN NUMBER,
1400 P_INPUT_TAX_AMOUNT IN OUT NOCOPY NUMBER,
1401 P_INPUT_TAXABLE_AMOUNT IN OUT NOCOPY NUMBER,
1402 P_SUMMARY_LEVEL IN VARCHAR2,
1403 P_REGISTER_TYPE IN VARCHAR2,
1404 i IN binary_integer)
1405 IS
1406
1407
1408 CURSOR ROUNDING_AMTS_CURSOR (
1409 C_TRX_ID IN NUMBER,
1410 C_REGISTER_TYPE IN VARCHAR2,
1411 C_TAX_ID IN NUMBER ) IS
1412 SELECT SUM(NVL(ARDTAX.AMOUNT_CR,0) - NVL(ARDTAX.AMOUNT_DR,0)),
1413 SUM(NVL(ARDTAX.TAXABLE_ENTERED_CR,0) -
1414 NVL(ARDTAX.TAXABLE_ENTERED_DR,0))
1415 FROM AR_DISTRIBUTIONS_ALL ARDTAX,
1416 AR_RECEIVABLE_APPLICATIONS_ALL APP,
1417 RA_CUSTOMER_TRX_ALL TRXCM
1418 WHERE TRXCM.CUSTOMER_TRX_ID = C_TRX_ID
1419 AND APP.APPLIED_CUSTOMER_TRX_ID = TRXCM.CUSTOMER_TRX_ID
1420 AND APP.RECEIVABLE_APPLICATION_ID = ARDTAX.SOURCE_ID
1421 AND ARDTAX.SOURCE_TABLE = 'RA'
1422 AND ARDTAX.SOURCE_TYPE = DECODE(C_REGISTER_TYPE,'TAX','TAX',
1423 'INTERIM','DEFERRED_TAX',NULL)
1424 AND ARDTAX.TAX_CODE_ID = C_TAX_ID
1425 AND ARDTAX.SOURCE_TABLE_SECONDARY = 'CT'
1426 AND ARDTAX.SOURCE_TYPE_SECONDARY = 'RECONCILE'
1427 GROUP BY C_TRX_ID, C_TAX_ID ;
1428
1429
1430 L_CURRENCY_CODE VARCHAR2(15);
1431 L_EXCHANGE_RATE NUMBER;
1432 L_PRECISION NUMBER;
1433 L_MIN_ACCT_UNIT NUMBER;
1434 L_TAXABLE_AMOUNT NUMBER;
1435 L_TAX_AMOUNT NUMBER;
1436 L_EXEMPT_AMOUNT NUMBER;
1437 L_TAXABLE_ACCTD_AMT NUMBER;
1438 L_TAX_ACCTD_AMT NUMBER;
1439 L_ORG_ID NUMBER;
1440 L_MATRIX_STATEMENT VARCHAR2(5000);
1441 L_CONTROL_ACCOUNT_CCID NUMBER;
1442 L_SET_OF_BOOKS_ID NUMBER;
1443 L_AH_ACCOUNTING_DATE DATE;
1444 L_ROUNDING_TAXABLE_AMT NUMBER;
1445 L_ROUNDING_TAX_AMT NUMBER;
1446 l_al_third_party_id NUMBER;
1447 l_al_third_party_sub_id NUMBER;
1448 l_gl_posted_date DATE;
1449
1450 L_TRX_CLASS VARCHAR2(30);
1451 L_DIST_ID NUMBER;
1452 L_BAL_SEG_STATEMENT VARCHAR2(1000);
1453 L_BR_PARENT_TRX_ID NUMBER;
1454
1455 BEGIN
1456
1457 IF (g_level_procedure >= g_current_runtime_level ) THEN
1458 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.APP_FUNCTIONAL_AMOUNTS.BEGIN',
1459 'ZX_AR_POPULATE_PKG: APP_FUNCTIONAL_AMOUNTS(+)');
1460 END IF;
1461
1462 -- L_ORG_ID := P_RA_SUB_ITF_TABLE_REC.org_id;
1463 -- L_CONTROL_ACCOUNT_CCID := P_RA_SUB_ITF_TABLE_REC.al_account_ccid;
1464 -- L_SET_OF_BOOKS_ID := P_RA_SUB_ITF_TABLE_REC.set_of_books_id;
1465 -- L_AH_ACCOUNTING_DATE := P_RA_SUB_ITF_TABLE_REC.accounting_date;
1466 -- L_GL_POSTED_DATE := P_RA_SUB_ITF_TABLE_REC.gl_posted_date;
1467 -- L_CURRENCY_CODE := P_RA_SUB_ITF_TABLE_REC.currency_code;
1468 -- L_EXCHANGE_RATE := P_RA_SUB_ITF_TABLE_REC.exchange_rate;
1469 -- L_PRECISION := P_RA_SUB_ITF_TABLE_REC.precision;
1470 -- L_TAX_AMOUNT := P_RA_SUB_ITF_TABLE_REC.tax_entered_amount;
1471 -- L_TAXABLE_AMOUNT := P_RA_SUB_ITF_TABLE_REC.taxable_amount;
1472 -- L_EXEMPT_AMOUNT := P_RA_SUB_ITF_TABLE_REC.exempt_entered_amount;
1473
1474
1475 --L_TRX_CLASS := P_RA_SUB_ITF_TABLE_REC.TRX_CLASS_CODE;
1476 -- L_DIST_ID := P_RA_SUB_ITF_TABLE_REC.ACCTG_DIST_ID;
1477
1478 -- L_AL_THIRD_PARTY_ID := P_RA_SUB_ITF_TABLE_REC.BILLING_TRADING_PARTNER_ID;
1479 -- L_AL_THIRD_PARTY_SUB_ID := P_RA_SUB_ITF_TABLE_REC.BILLING_TP_SITE_ID;
1480
1481
1482 -- get ah_period_name
1483 -- get_acctg_period_name(L_SET_OF_BOOKS_ID,
1484 -- L_AH_ACCOUNTING_DATE,
1485 -- P_AH_PERIOD_NAME);
1486 --
1487 -- P_TAX_EXTRACT_DECLARER_ID := ARP_TAX_EXTRACT.GET_DECLARER_ID
1488 -- ( L_ORG_ID) ;
1489
1490 IF UPPER(P_SUMMARY_LEVEL) = 'TRANSACTION' THEN
1491
1492 -- IF P_RECONCILIATION_FLAG = 'Y' THEN --rm input parameter
1493 -- IF P_RA_SUB_ITF_TABLE_REC.RECONCILIATION_FLAG = 'Y' THEN
1494 -- Fetch the reconciliation amounts and add to the tax/taxable amounts
1495 OPEN ROUNDING_AMTS_CURSOR (
1496 P_TRX_ID,
1497 P_REGISTER_TYPE,
1498 P_TAX_CODE_ID );
1499
1500 FETCH ROUNDING_AMTS_CURSOR INTO
1501 L_ROUNDING_TAX_AMT, L_ROUNDING_TAXABLE_AMT ;
1502 CLOSE ROUNDING_AMTS_CURSOR;
1503
1504 P_INPUT_TAX_AMOUNT := P_INPUT_TAX_AMOUNT + nvl(L_ROUNDING_TAX_AMT,0);
1505 P_INPUT_TAXABLE_AMOUNT:= P_INPUT_TAXABLE_AMOUNT +
1506 nvl(L_ROUNDING_TAXABLE_AMT,0);
1507
1508 -- END IF;
1509
1510 L_TAXABLE_AMOUNT := L_TAXABLE_AMOUNT - nvl(L_EXEMPT_AMOUNT,0);
1511
1512 convert_amounts( P_CURRENCY_CODE,
1513 P_EXCHANGE_RATE,
1514 P_PRECISION,
1515 P_MIN_ACCT_UNIT,
1516 P_INPUT_TAX_AMOUNT,
1517 P_INPUT_TAXABLE_AMOUNT,
1518 0, i); --P_INPUT_EXEMPT_AMOUNT
1519
1520 ELSIF P_SUMMARY_LEVEL = 'TRANSACTION_LINE' THEN
1521
1522 L_TAXABLE_AMOUNT := L_TAXABLE_AMOUNT - nvl(L_EXEMPT_AMOUNT,0);
1523
1524 convert_amounts(
1525 P_CURRENCY_CODE,
1526 P_EXCHANGE_RATE,
1527 P_PRECISION,
1528 P_MIN_ACCT_UNIT,
1529 P_INPUT_TAX_AMOUNT,
1530 P_INPUT_TAXABLE_AMOUNT,
1531 0,i); --P_INPUT_EXEMPT_AMOUNT,
1532
1533
1534 ELSIF P_SUMMARY_LEVEL = 'TRANSACTION_DISTRIBUTION' THEN
1535 /*
1536 IF L_EXEMPT_AMOUNT IS NOT NULL THEN
1537 P_EXEMPT_ENTERED_AMOUNT :=
1538 arpcurr.CurrRound(L_EXEMPT_AMOUNT,L_CURRENCY_CODE);
1539
1540 IF P_TAXABLE_AMOUNT IS NOT NULL THEN
1541 P_TAXABLE_EXEMPT_ENTERED_AMT :=
1542 arpcurr.CurrRound((P_TAXABLE_AMOUNT + L_EXEMPT_AMOUNT),
1543 L_CURRENCY_CODE);
1544 END IF;
1545
1546 ELSE
1547 P_EXEMPT_ENTERED_AMOUNT := 0;
1548 P_TAXABLE_EXEMPT_ENTERED_AMT := P_TAXABLE_AMOUNT;
1549 END IF;
1550
1551 IF P_EXEMPT_ENTERED_AMOUNT IS NOT NULL THEN
1552 P_EXEMPT_ACCTD_AMOUNT :=
1553 arpcurr.FUNCTIONAL_AMOUNT(
1554 P_EXEMPT_ENTERED_AMOUNT,
1555 L_CURRENCY_CODE,
1556 L_EXCHANGE_RATE,
1557 L_PRECISION,
1558 L_MIN_ACCT_UNIT);
1559 END IF;
1560
1561 IF P_TAXABLE_EXEMPT_ENTERED_AMT IS NOT NULL THEN
1562 P_TAXABLE_EXEMPT_ACCTD_AMT :=
1563 arpcurr.FUNCTIONAL_AMOUNT(
1564 P_TAXABLE_EXEMPT_ENTERED_AMT,
1565 L_CURRENCY_CODE,
1566 L_EXCHANGE_RATE,
1567 L_PRECISION,
1568 L_MIN_ACCT_UNIT);
1569 END IF;
1570
1571 -- Round off the amounts to the precision for the functional currency.
1572 -- Modified the code such that taxable_accounted_amount is rounded
1573 -- before taxable amount is rounded (BUG3123264).
1574
1575 IF P_TAXABLE_AMOUNT IS NOT NULL THEN
1576 P_TAXABLE_ACCOUNTED_AMOUNT :=
1577 arpcurr.FUNCTIONAL_AMOUNT(P_TAXABLE_AMOUNT,
1578 L_CURRENCY_CODE,
1579 L_EXCHANGE_RATE,
1580 L_PRECISION,
1581 L_MIN_ACCT_UNIT);
1582 END IF;
1583
1584
1585 IF P_TAXABLE_AMOUNT IS NOT NULL THEN
1586 P_TAXABLE_AMOUNT :=
1587 arpcurr.CurrRound(P_TAXABLE_AMOUNT,L_CURRENCY_CODE);
1588 END IF;
1589 */
1590 NULL;
1591
1592 END IF; -- P_SUMMARY_LEVEL = 'TRANSACTION_DISTRIBUTION'
1593
1594 IF (g_level_procedure >= g_current_runtime_level ) THEN
1595 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.APP_FUNCTIONAL_AMOUNTS.END',
1596 'ZX_AR_POPULATE_PKG: APP_FUNCTIONAL_AMOUNTS(-)');
1597 END IF;
1598
1599 EXCEPTION
1600 WHEN OTHERS THEN
1601 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1602 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
1603 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
1604 FND_MSG_PUB.Add;
1605 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1606 FND_LOG.STRING(g_level_unexpected,
1607 'ZX.TRL.ZX_AR_POPULATE_PKG.APP_FUNCTIONAL_AMOUNTS',
1608 g_error_buffer);
1609 END IF;
1610
1611 G_RETCODE := 2;
1612
1613 END APP_FUNCTIONAL_AMOUNTS;
1614
1615
1616
1617 PROCEDURE get_accounting_info (P_TRX_ID IN NUMBER,
1618 P_TRX_LINE_ID IN NUMBER,
1619 P_TAX_LINE_ID IN NUMBER,
1620 -- P_ENTITY_ID IN NUMBER,
1621 P_EVENT_ID IN NUMBER,
1622 P_AE_HEADER_ID IN NUMBER,
1623 P_ACTG_SOURCE_ID IN NUMBER,
1624 P_BALANCING_SEGMENT IN VARCHAR2,
1625 P_ACCOUNTING_SEGMENT IN VARCHAR2,
1626 P_SUMMARY_LEVEL IN VARCHAR2,
1627 P_REPORT_NAME IN VARCHAR2,
1628 P_TRX_CLASS IN VARCHAR2,
1629 j IN binary_integer) IS
1630
1631 BEGIN
1632
1633 IF (g_level_procedure >= g_current_runtime_level ) THEN
1634 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.get_accounting_info.BEGIN',
1635 'ZX_AR_POPULATE_PKG: get_accounting_info(+)' );
1636 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.get_accounting_info',
1637 'j := '||to_char(j));
1638 END IF;
1639
1640 IF (g_level_procedure >= g_current_runtime_level ) THEN
1641 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.get_accounting_info',
1642 'TRANSACTION DIST LEVEL : p_trx_id - p_event_id - p_ae_header_id- p_trx_line_id'
1643 ||to_char(p_trx_id)||'-'||to_char(p_event_id)||'-'||to_char(p_ae_header_id)
1644 ||'-'||to_char(p_trx_line_id));
1645 END IF;
1646
1647 IF p_trx_class in ('INVOICE','CREDIT_MEMO','DEBIT_MEMO') THEN
1648 inv_segment_info (P_TRX_ID,
1649 P_TRX_LINE_ID,
1650 P_TAX_LINE_ID,
1651 -- P_ENTITY_ID,
1652 P_EVENT_ID,
1653 P_AE_HEADER_ID,
1654 P_ACTG_SOURCE_ID,
1655 P_BALANCING_SEGMENT,
1656 P_ACCOUNTING_SEGMENT,
1657 P_SUMMARY_LEVEL,
1658 P_REPORT_NAME,
1659 P_TRX_CLASS,
1660 j);
1661
1662 ELSIF p_trx_class IN ('APP','EDISC','UNEDISC','ADJ','FINCHRG',
1663 'MISC_CASH_RECEIPT') THEN
1664 other_trx_segment_info(P_TRX_ID,
1665 P_TRX_LINE_ID,
1666 P_TAX_LINE_ID,
1667 -- P_ENTITY_ID,
1668 P_EVENT_ID,
1669 P_AE_HEADER_ID,
1670 P_ACTG_SOURCE_ID,
1671 P_BALANCING_SEGMENT,
1672 P_ACCOUNTING_SEGMENT,
1673 P_SUMMARY_LEVEL,
1674 P_TRX_CLASS,
1675 j);
1676 END IF;
1677 IF (g_level_procedure >= g_current_runtime_level ) THEN
1678 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.get_accounting_info.END',
1679 'ZX_AR_POPULATE_PKG: get_accounting_info(-)');
1680 END IF;
1681
1682 END get_accounting_info;
1683
1684 PROCEDURE get_accounting_amounts(P_TRX_ID IN NUMBER,
1685 P_TRX_LINE_ID IN NUMBER,
1686 P_TAX_LINE_ID IN NUMBER,
1687 -- P_ENTITY_ID IN NUMBER,
1688 P_EVENT_ID IN NUMBER,
1689 P_AE_HEADER_ID IN NUMBER,
1690 P_ACTG_SOURCE_ID IN NUMBER,
1691 P_SUMMARY_LEVEL IN VARCHAR2,
1692 P_TRX_CLASS IN VARCHAR2,
1693 P_LEDGER_ID IN NUMBER,
1694 j IN binary_integer) IS
1695 BEGIN
1696
1697 IF (g_level_procedure >= g_current_runtime_level ) THEN
1698 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.get_accounting_amounts.BEGIN',
1699 'ZX_AR_POPULATE_PKG: get_accounting_amounts(+)');
1700 END IF;
1701
1702 IF p_trx_class in ('INVOICE','CREDIT_MEMO','DEBIT_MEMO') THEN
1703 inv_actg_amounts(P_TRX_ID,
1704 P_TRX_LINE_ID,
1705 P_TAX_LINE_ID,
1706 -- P_ENTITY_ID,
1707 P_EVENT_ID,
1708 P_AE_HEADER_ID,
1709 P_ACTG_SOURCE_ID,
1710 P_SUMMARY_LEVEL,
1711 P_TRX_CLASS,
1712 P_LEDGER_ID,
1713 j);
1714 IF (g_level_procedure >= g_current_runtime_level ) THEN
1715 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.inv_actg_amounts',
1716 'trx_id : '||p_trx_id||' tax_line_id : '||P_TAX_LINE_ID||' j : ' ||j);
1717 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.inv_actg_amounts',
1718 'inv_actg_amounts call :GT_TAXABLE_AMT, GT_TAXABLE_AMT_FUNCL_CURR ::: '||to_char(GT_TAXABLE_AMT(j))
1719 ||'-'||to_char(GT_TAXABLE_AMT_FUNCL_CURR(j)));
1720 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.inv_actg_amounts',
1721 'inv_actg_amounts call :GT_TAX_AMT, GT_TAX_AMT_FUNCL_CURR ::: '||to_char(GT_TAX_AMT(j))
1722 ||'-'||to_char(GT_TAX_AMT_FUNCL_CURR(j)));
1723 END IF;
1724
1725 ELSIF p_trx_class IN ('APP','EDISC','UNEDISC','ADJ','FINCHRG',
1726 'MISC_CASH_RECEIPT') THEN
1727 other_trx_actg_amounts(P_TRX_ID,
1728 P_TRX_LINE_ID,
1729 P_TAX_LINE_ID,
1730 -- P_ENTITY_ID,
1731 P_EVENT_ID,
1732 P_AE_HEADER_ID,
1733 P_ACTG_SOURCE_ID,
1734 P_SUMMARY_LEVEL,
1735 P_TRX_CLASS,
1736 P_LEDGER_ID,
1737 j);
1738 END IF;
1739
1740 IF (g_level_procedure >= g_current_runtime_level ) THEN
1741 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.get_accounting_amounts.END',
1742 'ZX_AR_POPULATE_PKG: get_accounting_amounts(-)');
1743 END IF;
1744
1745
1746 END get_accounting_amounts;
1747
1748 PROCEDURE inv_segment_info (P_TRX_ID IN NUMBER,
1749 P_TRX_LINE_ID IN NUMBER,
1750 P_TAX_LINE_ID IN NUMBER,
1751 -- P_ENTITY_ID IN NUMBER,
1752 P_EVENT_ID IN NUMBER,
1753 P_AE_HEADER_ID IN NUMBER,
1754 P_ACTG_SOURCE_ID IN NUMBER,
1755 P_BALANCING_SEGMENT IN VARCHAR2,
1756 P_ACCOUNTING_SEGMENT IN VARCHAR2,
1757 P_SUMMARY_LEVEL IN VARCHAR2,
1758 P_REPORT_NAME IN VARCHAR2,
1759 P_TRX_CLASS IN VARCHAR2,
1760 j IN binary_integer) IS
1761 CURSOR trx_ccid (c_trx_id number, c_event_id number, c_ae_header_id number) IS
1762 SELECT
1763 ael.code_combination_id
1764 FROM ra_cust_trx_line_gl_dist_all gl_dist,
1765 xla_distribution_links lnk,
1766 xla_ae_lines ael
1767 WHERE gl_dist.customer_trx_id = c_trx_id
1768 AND gl_dist.account_class = 'REV'
1769 AND lnk.application_id = 222
1770 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
1771 AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
1772 AND lnk.ae_header_id = ael.ae_header_id
1773 AND lnk.ae_line_num = ael.ae_line_num
1774 AND lnk.event_id = c_event_id
1775 AND lnk.ae_header_id = c_ae_header_id
1776 AND ael.application_id = lnk.application_id
1777 AND rownum =1;
1778
1779 CURSOR trx_line_ccid (c_trx_id number, c_trx_line_id number, c_event_id number, c_ae_header_id NUMBER) IS
1780 SELECT
1781 ael.code_combination_id
1782 FROM ra_cust_trx_line_gl_dist_all gl_dist,
1783 xla_distribution_links lnk,
1784 xla_ae_lines ael
1785 WHERE gl_dist.customer_trx_id = c_trx_id
1786 AND gl_dist.customer_trx_line_id = c_trx_line_id
1787 AND gl_dist.account_class = 'REV'
1788 AND lnk.application_id = 222
1789 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
1790 AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
1791 AND lnk.ae_header_id = ael.ae_header_id
1792 AND lnk.ae_line_num = ael.ae_line_num
1793 AND lnk.event_id = c_event_id
1794 AND lnk.ae_header_id = c_ae_header_id
1795 AND ael.application_id = lnk.application_id
1796 AND rownum =1;
1797
1798
1799 -- For transavtion distribution level code combination id select in the build SQL
1800 -- The following query can be removed ----
1801
1802 CURSOR trx_dist_ccid (c_trx_id NUMBER, c_trx_line_id NUMBER, c_event_id NUMBER, c_ae_header_id NUMBER) IS
1803 SELECT
1804 /*+ leading(gl_dist)*/ ael.code_combination_id
1805 FROM ra_cust_trx_line_gl_dist_all gl_dist,
1806 xla_distribution_links lnk,
1807 xla_ae_lines ael
1808 WHERE gl_dist.customer_trx_id = c_trx_id
1809 AND gl_dist.customer_trx_line_id = c_trx_line_id
1810 AND gl_dist.account_class = 'REV'
1811 AND lnk.application_id = 222
1812 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
1813 AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
1814 AND lnk.ae_header_id = ael.ae_header_id
1815 AND lnk.ae_line_num = ael.ae_line_num
1816 AND lnk.event_id = c_event_id
1817 AND lnk.ae_header_id = c_ae_header_id
1818 AND ael.application_id = lnk.application_id
1819 AND rownum =1;
1820
1821
1822 CURSOR tax_ccid (c_trx_id number, c_event_id number, c_ae_header_id number) IS
1823 SELECT
1824 ael.code_combination_id
1825 FROM ra_cust_trx_line_gl_dist_all gl_dist,
1826 xla_distribution_links lnk,
1827 xla_ae_lines ael
1828 WHERE gl_dist.customer_trx_id = c_trx_id
1829 AND gl_dist.account_class = 'TAX'
1830 AND lnk.application_id = 222
1831 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
1832 AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
1833 AND lnk.ae_header_id = ael.ae_header_id
1834 AND lnk.ae_line_num = ael.ae_line_num
1835 AND lnk.event_id = c_event_id
1836 AND lnk.ae_header_id = c_ae_header_id
1837 AND ael.application_id = lnk.application_id
1838 AND rownum =1;
1839
1840 CURSOR tax_line_ccid (c_trx_id number, c_tax_line_id NUMBER, c_event_id number, c_ae_header_id number) IS
1841 SELECT
1842 ael.code_combination_id
1843 FROM ra_cust_trx_line_gl_dist_all gl_dist,
1844 xla_distribution_links lnk,
1845 xla_ae_lines ael
1846 WHERE gl_dist.customer_trx_id = c_trx_id
1847 AND gl_dist.customer_trx_line_id = c_tax_line_id
1848 AND gl_dist.account_class = 'TAX'
1849 AND lnk.application_id = 222
1850 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
1851 AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
1852 AND lnk.ae_header_id = ael.ae_header_id
1853 AND lnk.ae_line_num = ael.ae_line_num
1854 AND lnk.event_id = c_event_id
1855 AND lnk.ae_header_id = c_ae_header_id
1856 AND ael.application_id = lnk.application_id
1857 AND rownum =1;
1858
1859
1860 -- For transavtion distribution level code combination id select in the build SQL
1861 -- The following query can be removed ----
1862
1863 --CURSOR tax_dist_ccid (c_trx_id NUMBER, c_tax_line_id NUMBER, c_tax_line_dist_id NUMBER,
1864 -- c_event_id number, c_ae_header_id number) IS
1865 CURSOR tax_dist_ccid (c_trx_id NUMBER, c_tax_line_dist_id NUMBER,
1866 c_event_id number, c_ae_header_id number) IS
1867 SELECT
1868 ael.code_combination_id
1869 FROM ra_cust_trx_line_gl_dist_all gl_dist,
1870 xla_distribution_links lnk,
1871 xla_ae_lines ael
1872 WHERE gl_dist.customer_trx_id = c_trx_id
1873 -- AND gl_dist.customer_trx_line_id = c_tax_line_id
1874 AND gl_dist.cust_trx_line_gl_dist_id = c_tax_line_dist_id
1875 AND gl_dist.account_class = 'TAX'
1876 AND lnk.application_id = 222
1877 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
1878 AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
1879 AND lnk.ae_header_id = ael.ae_header_id
1880 AND lnk.ae_line_num = ael.ae_line_num
1881 AND lnk.event_id = c_event_id
1882 AND lnk.ae_header_id = c_ae_header_id
1883 AND ael.application_id = lnk.application_id
1884 AND rownum =1;
1885
1886 L_BAL_SEG_VAL VARCHAR2(240);
1887 L_BAL_SEG_DESC VARCHAR2(240);
1888
1889 L_ACCT_SEG_VAL VARCHAR2(240);
1890 L_ACCT_SEG_DESC VARCHAR2(240);
1891
1892 L_SQL_STATEMENT1 VARCHAR2(1000);
1893 L_SQL_STATEMENT2 VARCHAR2(1000);
1894 l_ccid number;
1895 l_tax_dist_ccid number;
1896 L_TRX_DIST_CCID NUMBER ;
1897 BEGIN
1898 IF (g_level_procedure >= g_current_runtime_level ) THEN
1899 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.inv_segment_info.BEGIN',
1900 'ZX_AR_POPULATE_PKG: inv_segment_info(+)');
1901 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.inv_segment_info',
1902 'j := '||to_char(j));
1903
1904 END IF;
1905
1906 GT_TRX_ARAP_BALANCING_SEGMENT(j) := NULL;
1907 GT_TRX_ARAP_NATURAL_ACCOUNT(j) := NULL;
1908 GT_TRX_TAXABLE_BAL_SEG(j) := NULL;
1909 GT_TRX_TAXABLE_BALSEG_DESC(j) := NULL;
1910 GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) := NULL;
1911 GT_TRX_TAX_BALANCING_SEGMENT(j) := NULL;
1912 GT_TRX_TAX_NATURAL_ACCOUNT(j) := NULL;
1913 GT_ACCOUNT_FLEXFIELD(j) := NULL;
1914 GT_ACCOUNT_DESCRIPTION(j) := NULL;
1915
1916 GT_TRX_TAXABLE_ACCOUNT_DESC(j) := NULL ;
1917 GT_TRX_TAXABLE_NATACCT_DESC(j) := NULL ;
1918
1919
1920 L_BAL_SEG_VAL := '';
1921 L_ACCT_SEG_VAL := '';
1922 L_BAL_SEG_DESC := '';
1923 L_ACCT_SEG_DESC := '';
1924
1925 L_SQL_STATEMENT1 := ' SELECT '||P_BALANCING_SEGMENT ||
1926 ' FROM GL_CODE_COMBINATIONS '||
1927 ' WHERE CODE_COMBINATION_ID = :L_CCID ';
1928
1929 L_SQL_STATEMENT2 := ' SELECT '||P_ACCOUNTING_SEGMENT ||
1930 ' FROM GL_CODE_COMBINATIONS '||
1931 ' WHERE CODE_COMBINATION_ID = :L_CCID ';
1932
1933
1934 IF P_SUMMARY_LEVEL = 'TRANSACTION' THEN
1935 OPEN trx_ccid (p_trx_id, p_event_id, p_ae_header_id);
1936 LOOP
1937 FETCH trx_ccid INTO l_ccid;
1938
1939 IF (g_level_procedure >= g_current_runtime_level ) THEN
1940 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.inv_segment_info.BEGIN',
1941 'ZX_AR_POPULATE_PKG: inv_segment_info(+)');
1942 END IF;
1943
1944 IF (g_level_procedure >= g_current_runtime_level ) THEN
1945 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.inv_segment_info',
1946 'TRANSACTION LEVEL : p_trx_id - p_event_id - p_ae_header_id'||to_char(p_trx_id)
1947 ||'-'||to_char(p_event_id)||'-'||to_char(p_ae_header_id)||'-'||to_char(l_ccid));
1948 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.inv_segment_info',
1949 'L_SQL_STATEMENT1: ' ||L_SQL_STATEMENT1);
1950 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.inv_segment_info',
1951 'L_SQL_STATEMENT1: ' ||L_SQL_STATEMENT2);
1952 END IF;
1953
1954
1955 EXIT WHEN trx_ccid%NOTFOUND;
1956
1957 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
1958 USING l_ccid;
1959
1960 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
1961 USING l_ccid;
1962
1963 IF GT_TRX_TAXABLE_BAL_SEG(j) IS NULL then
1964 GT_TRX_TAXABLE_BAL_SEG(j) := L_BAL_SEG_VAL;
1965 ELSE
1966 IF INSTRB(GT_TRX_TAXABLE_BAL_SEG(j),L_BAL_SEG_VAL) > 0 THEN
1967 NULL;
1968 ELSE
1969 GT_TRX_TAXABLE_BAL_SEG(j) := GT_TRX_TAXABLE_BAL_SEG(j)
1970 ||','||L_BAL_SEG_VAL;
1971 END IF;
1972 END IF;
1973
1974
1975 IF GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) IS NULL then
1976 GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) := L_ACCT_SEG_VAL;
1977 ELSE
1978 IF INSTRB(GT_TRX_TAXABLE_NATURAL_ACCOUNT(j),L_BAL_SEG_VAL) > 0 THEN
1979 NULL;
1980 ELSE
1981 GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(j)
1982 ||','||L_ACCT_SEG_VAL;
1983 END IF;
1984 END IF;
1985
1986 GT_TRX_ARAP_BALANCING_SEGMENT(j) := GT_TRX_TAXABLE_BAL_SEG(j);
1987 GT_TRX_ARAP_NATURAL_ACCOUNT(j) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(j);
1988 END LOOP;
1989
1990
1991 OPEN tax_ccid (p_trx_id, p_event_id, p_ae_header_id);
1992 LOOP
1993 FETCH tax_ccid INTO l_ccid;
1994 EXIT WHEN tax_ccid%NOTFOUND;
1995
1996 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
1997 USING l_ccid;
1998
1999 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
2000 USING l_ccid;
2001
2002 IF GT_TRX_TAX_BALANCING_SEGMENT(j) IS NULL then
2003 GT_TRX_TAX_BALANCING_SEGMENT(j) := L_BAL_SEG_VAL;
2004 ELSE
2005 IF INSTRB(GT_TRX_TAX_BALANCING_SEGMENT(j),L_BAL_SEG_VAL) > 0 THEN
2006 NULL;
2007 ELSE
2008 GT_TRX_TAX_BALANCING_SEGMENT(j) := GT_TRX_TAX_BALANCING_SEGMENT(j)
2009 ||','||L_BAL_SEG_VAL;
2010 END IF;
2011 END IF;
2012
2013
2014 IF GT_TRX_TAX_NATURAL_ACCOUNT(j) IS NULL then
2015 GT_TRX_TAX_NATURAL_ACCOUNT(j) := L_ACCT_SEG_VAL;
2016 ELSE
2017 IF INSTRB(GT_TRX_TAX_NATURAL_ACCOUNT(j),L_BAL_SEG_VAL) > 0 THEN
2018 NULL;
2019 ELSE
2020 GT_TRX_TAX_NATURAL_ACCOUNT(j) := GT_TRX_TAX_NATURAL_ACCOUNT(j)
2021 ||','||L_ACCT_SEG_VAL;
2022 END IF;
2023 END IF;
2024
2025 END LOOP;
2026
2027 ELSIF P_SUMMARY_LEVEL = 'TRANSACTION_LINE' THEN
2028 OPEN trx_line_ccid (p_trx_id, p_trx_line_id, p_event_id, p_ae_header_id);
2029 LOOP
2030 FETCH trx_line_ccid INTO l_ccid;
2031 EXIT WHEN trx_line_ccid%NOTFOUND;
2032
2033 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
2034 USING l_ccid;
2035
2036 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
2037 USING l_ccid;
2038
2039 IF GT_TRX_TAXABLE_BAL_SEG(j) IS NULL then
2040 GT_TRX_TAXABLE_BAL_SEG(j) := L_BAL_SEG_VAL;
2041 ELSE
2042 IF INSTRB(GT_TRX_TAXABLE_BAL_SEG(j),L_BAL_SEG_VAL) > 0 THEN
2043 NULL;
2044 ELSE
2045 GT_TRX_TAXABLE_BAL_SEG(j) := GT_TRX_TAXABLE_BAL_SEG(j)
2046 ||','||L_BAL_SEG_VAL;
2047 END IF;
2048 END IF;
2049
2050
2051 IF GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) IS NULL then
2052 GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) := L_ACCT_SEG_VAL;
2053 ELSE
2054 IF INSTRB(GT_TRX_TAXABLE_NATURAL_ACCOUNT(j),L_BAL_SEG_VAL) > 0 THEN
2055 NULL;
2056 ELSE
2057 GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(j)
2058 ||','||L_ACCT_SEG_VAL;
2059 END IF;
2060 END IF;
2061
2062 GT_TRX_ARAP_BALANCING_SEGMENT(j) := GT_TRX_TAXABLE_BAL_SEG(j);
2063 GT_TRX_ARAP_NATURAL_ACCOUNT(j) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(j);
2064 END LOOP;
2065
2066
2067 OPEN tax_line_ccid (p_trx_id, p_trx_line_id, p_event_id, p_ae_header_id);
2068 LOOP
2069 FETCH tax_line_ccid INTO l_ccid;
2070 EXIT WHEN tax_line_ccid%NOTFOUND;
2071
2072 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
2073 USING l_ccid;
2074
2075 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
2076 USING l_ccid;
2077
2078 IF GT_TRX_TAX_BALANCING_SEGMENT(j) IS NULL then
2079 GT_TRX_TAX_BALANCING_SEGMENT(j) := L_BAL_SEG_VAL;
2080 ELSE
2081 IF INSTRB(GT_TRX_TAX_BALANCING_SEGMENT(j),L_BAL_SEG_VAL) > 0 THEN
2082 NULL;
2083 ELSE
2084 GT_TRX_TAX_BALANCING_SEGMENT(j) := GT_TRX_TAX_BALANCING_SEGMENT(j)
2085 ||','||L_BAL_SEG_VAL;
2086 END IF;
2087 END IF;
2088
2089
2090 IF GT_TRX_TAX_NATURAL_ACCOUNT(j) IS NULL then
2091 GT_TRX_TAX_NATURAL_ACCOUNT(j) := L_ACCT_SEG_VAL;
2092 ELSE
2093 IF INSTRB(GT_TRX_TAX_NATURAL_ACCOUNT(j),L_BAL_SEG_VAL) > 0 THEN
2094 NULL;
2095 ELSE
2096 GT_TRX_TAX_NATURAL_ACCOUNT(j) := GT_TRX_TAX_NATURAL_ACCOUNT(j)
2097 ||','||L_ACCT_SEG_VAL;
2098 END IF;
2099 END IF;
2100
2101 END LOOP;
2102
2103
2104 ELSIF P_SUMMARY_LEVEL = 'TRANSACTION_DISTRIBUTION' THEN
2105 IF (g_level_procedure >= g_current_runtime_level ) THEN
2106 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.inv_segment_info',
2107 'TRANSACTION DIST LEVEL : p_trx_id - p_event_id - p_ae_header_id- p_trx_line_id'
2108 ||to_char(p_trx_id)||'-'||to_char(p_event_id)||'-'||to_char(p_ae_header_id)
2109 ||'-'||to_char(p_trx_line_id)||'-'||to_char(l_ccid));
2110 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.inv_segment_info',
2111 'L_SQL_STATEMENT1: ' ||L_SQL_STATEMENT1);
2112 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.inv_segment_info',
2113 'L_SQL_STATEMENT1: ' ||L_SQL_STATEMENT2);
2114 END IF;
2115
2116 OPEN trx_dist_ccid (p_trx_id, p_trx_line_id, p_event_id, p_ae_header_id);
2117 LOOP
2118 FETCH trx_dist_ccid INTO l_ccid;
2119 EXIT WHEN trx_dist_ccid%NOTFOUND;
2120 l_trx_dist_ccid := l_ccid; --Bug 5510907
2121 IF P_REPORT_NAME = 'ZXJGTAX' THEN
2122 agt_actg_line_ccid(j) := l_ccid ;
2123 END IF;
2124
2125 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
2126 USING l_ccid;
2127
2128 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
2129 USING l_ccid;
2130
2131 IF L_BAL_SEG_VAL IS NOT NULL THEN
2132 L_BAL_SEG_DESC := FA_RX_FLEX_PKG.GET_DESCRIPTION(
2133 P_APPLICATION_ID => 101,
2134 P_ID_FLEX_CODE => 'GL#',
2135 P_ID_FLEX_NUM => g_coa_id,
2136 P_QUALIFIER => 'GL_BALANCING',
2137 P_DATA => L_BAL_SEG_VAL);
2138 END IF;
2139
2140 IF GT_TRX_TAXABLE_BAL_SEG(j) IS NULL then
2141 GT_TRX_TAXABLE_BAL_SEG(j) := L_BAL_SEG_VAL;
2142 GT_TRX_TAXABLE_BALSEG_DESC(j) := L_BAL_SEG_DESC;
2143 ELSE
2144 IF INSTRB(GT_TRX_TAXABLE_BAL_SEG(j),L_BAL_SEG_VAL) > 0 THEN
2145 NULL;
2146 ELSE
2147 GT_TRX_TAXABLE_BAL_SEG(j) := GT_TRX_TAXABLE_BAL_SEG(j)
2148 ||','||L_BAL_SEG_VAL;
2149 GT_TRX_TAXABLE_BALSEG_DESC(j) := GT_TRX_TAXABLE_BALSEG_DESC(j) || ',' ||L_BAL_SEG_DESC;
2150 END IF;
2151 END IF;
2152
2153 --Bug 5650415 : Get the description for the natural Segnemt of the taxable line ccid
2154 IF L_ACCT_SEG_VAL IS NOT NULL THEN
2155 L_ACCT_SEG_DESC := FA_RX_FLEX_PKG.GET_DESCRIPTION(
2156 P_APPLICATION_ID => 101,
2157 P_ID_FLEX_CODE => 'GL#',
2158 P_ID_FLEX_NUM => g_coa_id,
2159 P_QUALIFIER => 'GL_ACCOUNT',
2160 P_DATA => L_ACCT_SEG_VAL);
2161 END IF;
2162
2163 IF GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) IS NULL then
2164 GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) := L_ACCT_SEG_VAL;
2165 GT_TRX_TAXABLE_NATACCT_DESC(j) := L_ACCT_SEG_DESC;
2166 ELSE
2167 IF INSTRB(GT_TRX_TAXABLE_NATURAL_ACCOUNT(j),L_BAL_SEG_VAL) > 0 THEN
2168 NULL;
2169 ELSE
2170 GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(j)
2171 ||','||L_ACCT_SEG_VAL;
2172 GT_TRX_TAXABLE_NATACCT_DESC(j) := GT_TRX_TAXABLE_NATACCT_DESC(j)||','||L_ACCT_SEG_DESC;
2173 END IF;
2174 END IF;
2175
2176 GT_TRX_ARAP_BALANCING_SEGMENT(j) := GT_TRX_TAXABLE_BAL_SEG(j);
2177 GT_TRX_ARAP_NATURAL_ACCOUNT(j) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(j);
2178 END LOOP;
2179
2180 OPEN tax_dist_ccid (p_trx_id, P_ACTG_SOURCE_ID, p_event_id, p_ae_header_id);
2181 LOOP
2182 IF (g_level_procedure >= g_current_runtime_level ) THEN
2183 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.inv_segment_info',
2184 'TRANSACTION DIST LEVEL - tax_dist_ccid : p_trx_id - p_event_id - p_ae_header_id- p_tax_line_id'
2185 ||to_char(p_trx_id)||'-'||to_char(p_event_id)||'-'||to_char(p_ae_header_id)
2186 ||'-'||to_char(p_tax_line_id)||'-'||to_char(P_ACTG_SOURCE_ID)||'-'||to_char(l_ccid));
2187 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.inv_segment_info',
2188 'L_SQL_STATEMENT1: ' ||L_SQL_STATEMENT1);
2189 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.inv_segment_info',
2190 'L_SQL_STATEMENT1: ' ||L_SQL_STATEMENT2);
2191 END IF;
2192
2193 FETCH tax_dist_ccid INTO l_ccid;
2194 EXIT WHEN tax_dist_ccid%NOTFOUND;
2195 l_tax_dist_ccid := l_ccid;
2196 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
2197 USING l_ccid;
2198
2199 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
2200 USING l_ccid;
2201
2202 IF GT_TRX_TAX_BALANCING_SEGMENT(j) IS NULL then
2203 GT_TRX_TAX_BALANCING_SEGMENT(j) := L_BAL_SEG_VAL;
2204 ELSE
2205 IF INSTRB(GT_TRX_TAX_BALANCING_SEGMENT(j),L_BAL_SEG_VAL) > 0 THEN
2206 NULL;
2207 ELSE
2208 GT_TRX_TAX_BALANCING_SEGMENT(j) := GT_TRX_TAX_BALANCING_SEGMENT(j)
2209 ||','||L_BAL_SEG_VAL;
2210 END IF;
2211 END IF;
2212
2213
2214 IF GT_TRX_TAX_NATURAL_ACCOUNT(j) IS NULL then
2215 GT_TRX_TAX_NATURAL_ACCOUNT(j) := L_ACCT_SEG_VAL;
2216 ELSE
2217 IF INSTRB(GT_TRX_TAX_NATURAL_ACCOUNT(j),L_BAL_SEG_VAL) > 0 THEN
2218 NULL;
2219 ELSE
2220 GT_TRX_TAX_NATURAL_ACCOUNT(j) := GT_TRX_TAX_NATURAL_ACCOUNT(j)
2221 ||','||L_ACCT_SEG_VAL;
2222 END IF;
2223 END IF;
2224
2225 END LOOP;
2226
2227 -- populare accounting_flexfield and accounting_description column ---
2228 ----------------------------------------------------------------------
2229
2230 IF l_tax_dist_ccid IS NOT NULL THEN
2231
2232 GT_ACCOUNT_FLEXFIELD(j) := FA_RX_FLEX_PKG.GET_VALUE(
2233 P_APPLICATION_ID => 101,
2234 P_ID_FLEX_CODE => 'GL#',
2235 P_ID_FLEX_NUM => g_coa_id,
2236 P_QUALIFIER => 'ALL',
2237 P_CCID => l_tax_dist_ccid);
2238
2239 IF (g_level_procedure >= g_current_runtime_level ) THEN
2240 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.INV_SEGMENT_INFO',
2241 'Account Flexfield = '||GT_ACCOUNT_FLEXFIELD(j));
2242 END IF;
2243
2244 GT_ACCOUNT_DESCRIPTION(j) := FA_RX_FLEX_PKG.GET_DESCRIPTION(
2245 P_APPLICATION_ID => 101,
2246 P_ID_FLEX_CODE => 'GL#',
2247 P_ID_FLEX_NUM => g_coa_id,
2248 P_QUALIFIER => 'ALL',
2249 P_DATA => GT_ACCOUNT_FLEXFIELD(j));
2250
2251 IF (g_level_procedure >= g_current_runtime_level ) THEN
2252 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.INV_SEGMENT_INFO',
2253 'Account Description = '||GT_ACCOUNT_DESCRIPTION(j));
2254 END IF;
2255
2256 END IF;
2257
2258 --Bug 5510907 : To get the accounting Flexfield for the Taxable Line
2259
2260 IF l_trx_dist_ccid IS NOT NULL THEN
2261
2262 GT_TRX_CONTROL_ACCFLEXFIELD(j) := FA_RX_FLEX_PKG.GET_VALUE(
2263 P_APPLICATION_ID => 101,
2264 P_ID_FLEX_CODE => 'GL#',
2265 P_ID_FLEX_NUM => g_coa_id,
2266 P_QUALIFIER => 'ALL',
2267 P_CCID => l_trx_dist_ccid);
2268
2269 IF (g_level_procedure >= g_current_runtime_level ) THEN
2270 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.GET_ACCOUNTING_INFO',
2271 'inv_segment_info : Taxable Line Account Flexfield = '||GT_TRX_CONTROL_ACCFLEXFIELD(j));
2272 END IF;
2273
2274 --Bug 5650415
2275 GT_TRX_TAXABLE_ACCOUNT_DESC(j) := FA_RX_FLEX_PKG.GET_DESCRIPTION(
2276 P_APPLICATION_ID => 101,
2277 P_ID_FLEX_CODE => 'GL#',
2278 P_ID_FLEX_NUM => g_coa_id,
2279 P_QUALIFIER => 'ALL',
2280 P_DATA => GT_TRX_CONTROL_ACCFLEXFIELD(j));
2281
2282 IF (g_level_procedure >= g_current_runtime_level ) THEN
2283 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.GET_ACCOUNTING_INFO',
2284 'Account Description for Taxable Line CCID = '||GT_TRX_TAXABLE_ACCOUNT_DESC(j));
2285 END IF;
2286
2287 END IF ;
2288 ---- End of accounting flexfield population -----------------------
2289
2290 END IF; -- Summary Level
2291 IF (g_level_procedure >= g_current_runtime_level ) THEN
2292 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.inv_segment_info.END',
2293 'ZX_AR_POPULATE_PKG: inv_segment_info(-)');
2294 END IF;
2295
2296 END inv_segment_info;
2297
2298
2299 PROCEDURE inv_actg_amounts(P_TRX_ID IN NUMBER,
2300 P_TRX_LINE_ID IN NUMBER,
2301 P_TAX_LINE_ID IN NUMBER,
2302 -- P_ENTITY_ID IN NUMBER,
2303 P_EVENT_ID IN NUMBER,
2304 P_AE_HEADER_ID IN NUMBER,
2305 P_ACTG_SOURCE_ID IN NUMBER,
2306 P_SUMMARY_LEVEL IN VARCHAR2,
2307 P_TRX_CLASS IN VARCHAR2,
2308 P_LEDGER_ID IN NUMBER,
2309 j IN binary_integer) IS
2310 -- Transaction Header Level
2311
2312 CURSOR taxable_amount_hdr (c_trx_id NUMBER, c_ae_header_id NUMBER, c_event_id NUMBER, c_ledger_id NUMBER) IS
2313 SELECT sum(nvl(lnk.UNROUNDED_ENTERED_DR,0)) - sum(nvl(lnk.UNROUNDED_ENTERED_CR,0)),
2314 sum(nvl(lnk.UNROUNDED_ACCOUNTED_DR,0)) - SUM(nvl(lnk.UNROUNDED_ACCOUNTED_CR,0))
2315 FROM ra_cust_trx_line_gl_dist_all gl_dist,
2316 xla_distribution_links lnk,
2317 xla_ae_headers aeh,
2318 xla_ae_lines ael
2319 WHERE gl_dist.customer_trx_id = c_trx_id
2320 AND lnk.application_id = 222
2321 AND gl_dist.account_class = 'REV'
2322 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
2323 AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
2324 AND lnk.ae_header_id = c_ae_header_id
2325 AND lnk.event_id = c_event_id
2326 AND lnk.ae_line_num = ael.ae_line_num
2327 AND aeh.ae_header_id = lnk.ae_header_id
2328 AND aeh.ae_header_id = ael.ae_header_id
2329 AND aeh.ledger_id = c_ledger_id
2330 AND aeh.application_id = lnk.application_id
2331 and ael.application_id = aeh.application_id;
2332
2333
2334
2335 CURSOR tax_amount_hdr (c_trx_id NUMBER, c_ae_header_id NUMBER, c_event_id NUMBER,c_ledger_id NUMBER) IS
2336 SELECT sum(nvl(lnk.UNROUNDED_ENTERED_DR,0)) - sum(nvl(lnk.UNROUNDED_ENTERED_CR,0)),
2337 sum(nvl(lnk.UNROUNDED_ACCOUNTED_DR,0)) - SUM(nvl(lnk.UNROUNDED_ACCOUNTED_CR,0))
2338 FROM ra_cust_trx_line_gl_dist_all gl_dist,
2339 xla_distribution_links lnk,
2340 xla_ae_headers aeh,
2341 xla_ae_lines ael
2342 WHERE gl_dist.customer_trx_id = c_trx_id
2343 AND gl_dist.account_class = 'TAX'
2344 AND lnk.application_id = 222
2345 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
2346 AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
2347 AND lnk.ae_header_id = c_ae_header_id
2348 AND lnk.event_id = c_event_id
2349 AND lnk.ae_line_num = ael.ae_line_num
2350 AND aeh.ae_header_id = ael.ae_header_id
2351 AND aeh.ledger_id = c_ledger_id
2352 AND aeh.ae_header_id = lnk.ae_header_id
2353 AND aeh.application_id = lnk.application_id
2354 AND ael.application_id = aeh.application_id;
2355
2356
2357
2358 -- Transaction Line Level
2359
2360 CURSOR taxable_amount_line (c_trx_id NUMBER,c_trx_line_id NUMBER, c_ae_header_id NUMBER,
2361 c_event_id NUMBER, c_ledger_id NUMBER) IS
2362 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
2363 FROM ra_cust_trx_line_gl_dist_all gl_dist,
2364 xla_distribution_links lnk,
2365 xla_ae_headers aeh,
2366 xla_ae_lines ael
2367 WHERE gl_dist.customer_trx_id = c_trx_id
2368 AND gl_dist.customer_trx_line_id = c_trx_line_id
2369 AND gl_dist.account_class = 'REV'
2370 AND lnk.application_id = 222
2371 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
2372 AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
2373 AND lnk.ae_header_id = c_ae_header_id
2374 AND lnk.event_id = c_event_id
2375 AND lnk.ae_line_num = ael.ae_line_num
2376 AND aeh.ae_header_id = ael.ae_header_id
2377 AND aeh.ledger_id = c_ledger_id
2378 AND aeh.ae_header_id = lnk.ae_header_id
2379 AND aeh.application_id = lnk.application_id
2380 AND ael.application_id = aeh.application_id;
2381
2382
2383
2384 CURSOR tax_amount_line (c_trx_id NUMBER,c_tax_line_id NUMBER, c_ae_header_id NUMBER, c_event_id NUMBER, c_ledger_id NUMBER) IS
2385 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
2386 FROM ra_cust_trx_line_gl_dist_all gl_dist,
2387 xla_distribution_links lnk,
2388 xla_ae_headers aeh,
2389 xla_ae_lines ael
2390 WHERE gl_dist.customer_trx_id = c_trx_id
2391 AND gl_dist.customer_trx_line_id = c_tax_line_id
2392 AND gl_dist.account_class = 'TAX'
2393 AND lnk.application_id = 222
2394 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
2395 AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
2396 AND lnk.event_id = c_event_id
2397 AND lnk.ae_header_id = c_ae_header_id
2398 AND lnk.ae_line_num = ael.ae_line_num
2399 AND aeh.ae_header_id = ael.ae_header_id
2400 AND aeh.ledger_id = c_ledger_id
2401 AND aeh.ae_header_id = lnk.ae_header_id
2402 AND aeh.application_id = lnk.application_id
2403 AND ael.application_id = aeh.application_id;
2404
2405
2406 -- Transaction Distribution Level
2407
2408
2409
2410 --CURSOR tax_amount_dist ( c_trx_id NUMBER,c_tax_line_id NUMBER, c_tax_dist_id NUMBER, c_ae_header_id NUMBER,
2411 -- c_event_id NUMBER, c_ledger_id NUMBER) IS
2412 CURSOR tax_amount_dist ( c_trx_id NUMBER, c_tax_dist_id NUMBER, c_ae_header_id NUMBER,
2413 c_event_id NUMBER, c_ledger_id NUMBER) IS
2414 SELECT sum(nvl(lnk.UNROUNDED_ENTERED_CR,0)) - sum(nvl(lnk.UNROUNDED_ENTERED_DR,0)),
2415 sum(nvl(lnk.UNROUNDED_ACCOUNTED_CR,0)) - SUM(nvl(lnk.UNROUNDED_ACCOUNTED_DR,0))
2416 --sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
2417 FROM ra_cust_trx_line_gl_dist_all gl_dist,
2418 xla_distribution_links lnk,
2419 xla_ae_headers aeh,
2420 xla_ae_lines ael
2421 WHERE gl_dist.customer_trx_id = c_trx_id
2422 -- AND gl_dist.customer_trx_line_id = c_tax_line_id
2423 AND gl_dist.cust_trx_line_gl_dist_id = c_tax_dist_id
2424 AND gl_dist.account_class = 'TAX'
2425 AND lnk.application_id = 222
2426 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
2427 AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
2428 AND ael.accounting_class_code = 'TAX'
2429 AND lnk.ae_header_id = ael.ae_header_id
2430 AND lnk.ae_line_num = ael.ae_line_num
2431 AND lnk.event_id = c_event_id
2432 AND lnk.ae_header_id = c_ae_header_id
2433 AND aeh.ae_header_id = ael.ae_header_id
2434 AND aeh.ledger_id = c_ledger_id
2435 AND aeh.ae_header_id = lnk.ae_header_id
2436 AND aeh.application_id = lnk.application_id
2437 AND ael.application_id = aeh.application_id;
2438
2439
2440
2441 CURSOR taxable_amount_dist (c_trx_id NUMBER,c_trx_line_id NUMBER, c_ae_header_id NUMBER,
2442 c_event_id NUMBER, c_ledger_id NUMBER) IS
2443 SELECT sum(nvl(lnk.UNROUNDED_ENTERED_CR,0)) - sum(nvl(lnk.UNROUNDED_ENTERED_DR,0)),
2444 sum(nvl(lnk.UNROUNDED_ACCOUNTED_CR,0)) - SUM(nvl(lnk.UNROUNDED_ACCOUNTED_DR,0))
2445 --SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
2446 FROM ra_cust_trx_line_gl_dist_all gl_dist,
2447 xla_distribution_links lnk,
2448 xla_ae_headers aeh,
2449 xla_ae_lines ael
2450 WHERE gl_dist.customer_trx_id = c_trx_id
2451 AND gl_dist.customer_trx_line_id = c_trx_line_id
2452 -- AND gl_dist.account_class = 'REV'
2453 AND lnk.application_id = 222
2454 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
2455 AND ael.accounting_class_code = 'REVENUE'
2456 AND lnk.source_distribution_id_num_1 = gl_dist.cust_trx_line_gl_dist_id
2457 AND lnk.ae_header_id = c_ae_header_id
2458 AND lnk.event_id = c_event_id
2459 AND lnk.ae_line_num = ael.ae_line_num
2460 AND aeh.ae_header_id = ael.ae_header_id
2461 AND aeh.ledger_id = c_ledger_id
2462 AND aeh.ae_header_id = lnk.ae_header_id
2463 AND aeh.application_id = lnk.application_id
2464 AND ael.application_id = aeh.application_id;
2465
2466
2467 BEGIN
2468
2469 IF (g_level_procedure >= g_current_runtime_level ) THEN
2470 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.inv_actg_amounts.BEGIN',
2471 'ZX_AR_POPULATE_PKG: inv_actg_amounts(+)');
2472 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.inv_actg_amounts',
2473 'ZX_AR_POPULATE_PKG: inv_actg_amounts :'|| to_char(p_ledger_id));
2474 END IF;
2475
2476 IF p_summary_level = 'TRANSACTION' THEN
2477 OPEN taxable_amount_hdr(p_trx_id , p_ae_header_id , p_event_id,p_ledger_id );
2478 FETCH taxable_amount_hdr INTO GT_TAXABLE_AMT(j),GT_TAXABLE_AMT_FUNCL_CURR(j);
2479 -- EXIT WHEN taxable_amount_hdr%NOTFOUND;
2480 CLOSE taxable_amount_hdr;
2481 IF (g_level_procedure >= g_current_runtime_level ) THEN
2482 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.inv_actg_amounts',
2483 'GT_TAXABLE_AMT, GT_TAXABLE_AMT_FUNCL_CURR'||to_char(GT_TAXABLE_AMT(j))
2484 ||'-'||to_char(GT_TAXABLE_AMT_FUNCL_CURR(j)));
2485 END IF;
2486
2487
2488 OPEN tax_amount_hdr(p_trx_id , p_ae_header_id , p_event_id,p_ledger_id);
2489 FETCH tax_amount_hdr INTO GT_TAX_AMT(j),GT_TAX_AMT_FUNCL_CURR(j);
2490 -- EXIT WHEN tax_amount_hdr%NOTFOUND;
2491 CLOSE tax_amount_hdr;
2492 IF (g_level_procedure >= g_current_runtime_level ) THEN
2493 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.inv_actg_amounts',
2494 'GT_TAX_AMT, GT_TAX_AMT_FUNCL_CURR'||to_char(GT_TAX_AMT(j))
2495 ||'-'||to_char(GT_TAX_AMT_FUNCL_CURR(j)));
2496 END IF;
2497
2498 ELSIF p_summary_level = 'TRANSACTION_LINE' THEN
2499 OPEN taxable_amount_line(p_trx_id ,p_trx_line_id, p_ae_header_id , p_event_id,p_ledger_id);
2500 FETCH taxable_amount_line INTO GT_TAXABLE_AMT(j),GT_TAXABLE_AMT_FUNCL_CURR(j);
2501 -- EXIT WHEN taxable_amount_line%NOTFOUND;
2502 CLOSE taxable_amount_line;
2503
2504 OPEN tax_amount_line(p_trx_id , p_trx_line_id, p_ae_header_id , p_event_id,p_ledger_id);
2505 FETCH tax_amount_line INTO GT_TAX_AMT(j),GT_TAX_AMT_FUNCL_CURR(j);
2506 -- EXIT WHEN tax_amount_line%NOTFOUND;
2507 CLOSE tax_amount_line;
2508
2509 ELSIF p_summary_level = 'TRANSACTION_DISTRIBUTION' THEN
2510
2511 IF (g_level_procedure >= g_current_runtime_level ) THEN
2512 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.inv_segment_info',
2513 'TRANSACTION DIST LEVEL : p_trx_id - p_event_id - p_ae_header_id- p_trx_line_id'
2514 ||to_char(p_trx_id)||'-'||to_char(p_event_id)||'-'||to_char(p_ae_header_id)
2515 ||'-'||to_char(p_trx_line_id));
2516 END IF;
2517
2518 OPEN taxable_amount_dist(P_TRX_ID ,p_trx_line_id,p_ae_header_id , p_event_id,p_ledger_id);
2519 FETCH taxable_amount_dist INTO GT_TAXABLE_AMT(j),GT_TAXABLE_AMT_FUNCL_CURR(j);
2520 -- EXIT WHEN taxable_amount_dist%NOTFOUND;
2521 CLOSE taxable_amount_dist;
2522
2523 IF (g_level_procedure >= g_current_runtime_level ) THEN
2524 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.inv_actg_amounts',
2525 'GT_TAXABLE_AMT, GT_TAXABLE_AMT_FUNCL_CURR'||to_char(GT_TAXABLE_AMT(j))
2526 ||'-'||to_char(GT_TAXABLE_AMT_FUNCL_CURR(j)));
2527 END IF;
2528
2529 --OPEN tax_amount_dist(p_trx_id ,p_tax_line_id,P_ACTG_SOURCE_ID, p_ae_header_id , p_event_id,p_ledger_id);
2530 OPEN tax_amount_dist(p_trx_id ,P_ACTG_SOURCE_ID, p_ae_header_id , p_event_id,p_ledger_id);
2531 FETCH tax_amount_dist INTO GT_TAX_AMT(j),GT_TAX_AMT_FUNCL_CURR(j);
2532 -- EXIT WHEN tax_amount_dist%NOTFOUND;
2533 CLOSE tax_amount_dist;
2534
2535 IF (g_level_procedure >= g_current_runtime_level ) THEN
2536 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.inv_actg_amounts',
2537 'GT_TAX_AMT, GT_TAX_AMT_FUNCL_CURR'||to_char(GT_TAX_AMT(j))
2538 ||'-'||to_char(GT_TAX_AMT_FUNCL_CURR(j)));
2539 END IF;
2540
2541 END IF;
2542
2543 IF (g_level_procedure >= g_current_runtime_level ) THEN
2544 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.inv_actg_amounts.END',
2545 'ZX_AR_POPULATE_PKG: inv_actg_amounts(-)');
2546 END IF;
2547
2548 END inv_actg_amounts;
2549
2550
2551 PROCEDURE other_trx_segment_info(P_TRX_ID IN NUMBER,
2552 P_TRX_LINE_ID IN NUMBER,
2553 P_TAX_LINE_ID IN NUMBER,
2554 -- P_ENTITY_ID IN NUMBER,
2555 P_EVENT_ID IN NUMBER,
2556 P_AE_HEADER_ID IN NUMBER,
2557 P_ACTG_SOURCE_ID IN NUMBER,
2558 P_BALANCING_SEGMENT IN VARCHAR2,
2559 P_ACCOUNTING_SEGMENT IN VARCHAR2,
2560 P_SUMMARY_LEVEL IN VARCHAR2,
2561 P_TRX_CLASS IN VARCHAR2,
2562 j IN binary_integer) IS
2563
2564 CURSOR trx_ccid (c_actg_source_id number, c_event_id number, c_ae_header_id number) IS
2565 SELECT
2566 ael.code_combination_id
2567 FROM ar_distributions_all dist,
2568 xla_distribution_links lnk,
2569 xla_ae_lines ael
2570 WHERE dist.line_id = p_actg_source_id
2571 AND lnk.application_id = 222
2572 AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
2573 AND lnk.source_distribution_id_num_1 = dist.line_id
2574 AND lnk.ae_header_id = ael.ae_header_id
2575 AND lnk.ae_line_num = ael.ae_line_num
2576 AND lnk.event_id = c_event_id
2577 AND lnk.ae_header_id = c_ae_header_id
2578 AND lnk.application_id = ael.application_id
2579 AND rownum =1;
2580
2581 CURSOR trx_dist_ccid (c_actg_source_id number, c_event_id number, c_ae_header_id number) IS
2582 SELECT ael.code_combination_id
2583 FROM ar_distributions_all dist,
2584 ar_distributions_all taxdist,
2585 xla_distribution_links lnk,
2586 xla_ae_lines ael
2587 WHERE taxdist.line_id = p_actg_source_id
2588 AND NVL(dist.source_table,'X') = NVL(taxdist.source_table_secondary,'X')
2589 AND dist.tax_link_id = taxdist.tax_link_id
2590 AND dist.source_id = taxdist.source_id
2591 AND lnk.source_distribution_id_num_1 = dist.line_id
2592 AND lnk.application_id = 222
2593 AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
2594 AND lnk.ae_header_id = ael.ae_header_id
2595 AND lnk.ae_line_num = ael.ae_line_num
2596 AND lnk.event_id = c_event_id
2597 AND lnk.ae_header_id = c_ae_header_id
2598 AND lnk.application_id = ael.application_id
2599 AND rownum =1;
2600
2601 CURSOR tax_ccid (c_actg_source_id number, c_event_id number, c_ae_header_id number) IS
2602 SELECT ael.code_combination_id
2603 FROM ar_distributions_all dist,
2604 ar_distributions_all taxdist,
2605 xla_distribution_links lnk,
2606 xla_ae_lines ael
2607 WHERE dist.line_id = p_actg_source_id
2608 AND NVL(dist.source_table,'X') = NVL(taxdist.source_table_secondary,'X')
2609 AND dist.tax_link_id = taxdist.tax_link_id
2610 AND dist.source_id = taxdist.source_id
2611 AND lnk.source_distribution_id_num_1 = taxdist.line_id
2612 AND lnk.application_id = 222
2613 AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
2614 AND lnk.ae_header_id = ael.ae_header_id
2615 AND lnk.ae_line_num = ael.ae_line_num
2616 AND lnk.event_id = c_event_id
2617 AND lnk.ae_header_id = c_ae_header_id
2618 AND lnk.application_id = ael.application_id
2619 AND rownum =1;
2620
2621 CURSOR tax_dist_ccid (c_actg_source_id number, c_event_id number, c_ae_header_id number) IS
2622 SELECT
2623 ael.code_combination_id
2624 FROM ar_distributions_all taxdist,
2625 xla_distribution_links lnk,
2626 xla_ae_lines ael
2627 WHERE taxdist.line_id = p_actg_source_id
2628 AND lnk.application_id = 222
2629 AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
2630 AND lnk.source_distribution_id_num_1 = taxdist.line_id
2631 AND lnk.ae_header_id = ael.ae_header_id
2632 AND lnk.ae_line_num = ael.ae_line_num
2633 AND lnk.event_id = c_event_id
2634 AND lnk.ae_header_id = c_ae_header_id
2635 AND lnk.application_id = ael.application_id
2636 AND rownum =1;
2637
2638
2639
2640 L_BAL_SEG_VAL VARCHAR2(240);
2641 L_BAL_SEG_DESC VARCHAR2(240);
2642 L_ACCT_SEG_VAL VARCHAR2(240);
2643 L_SQL_STATEMENT1 VARCHAR2(1000);
2644 L_SQL_STATEMENT2 VARCHAR2(1000);
2645 l_ccid number;
2646 l_tax_dist_ccid number;
2647 L_TRX_DIST_CCID NUMBER ;
2648 BEGIN
2649
2650 IF (g_level_procedure >= g_current_runtime_level ) THEN
2651 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.other_trx_segment_info.BEGIN',
2652 'ZX_AR_POPULATE_PKG: other_trx_segment_info(+)');
2653 END IF;
2654
2655 GT_TRX_ARAP_BALANCING_SEGMENT(j) := NULL;
2656 GT_TRX_ARAP_NATURAL_ACCOUNT(j) := NULL;
2657 GT_TRX_TAXABLE_BAL_SEG(j) := NULL;
2658 GT_TRX_TAXABLE_BALSEG_DESC(j) := NULL;
2659 GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) := NULL;
2660 GT_TRX_TAX_BALANCING_SEGMENT(j) := NULL;
2661 GT_TRX_TAX_NATURAL_ACCOUNT(j) := NULL;
2662
2663
2664 L_BAL_SEG_VAL := '';
2665 L_BAL_SEG_DESC := '';
2666 L_ACCT_SEG_VAL := '';
2667
2668 L_SQL_STATEMENT1 := ' SELECT '||P_BALANCING_SEGMENT ||
2669 ' FROM GL_CODE_COMBINATIONS '||
2670 ' WHERE CODE_COMBINATION_ID = :L_CCID ';
2671
2672 L_SQL_STATEMENT2 := ' SELECT '||P_ACCOUNTING_SEGMENT ||
2673 ' FROM GL_CODE_COMBINATIONS '||
2674 ' WHERE CODE_COMBINATION_ID = :L_CCID ';
2675
2676
2677 IF P_SUMMARY_LEVEL = 'TRANSACTION' OR P_SUMMARY_LEVEL = 'TRANSACTION_LINE' THEN
2678
2679 OPEN trx_ccid (p_actg_source_id, p_event_id, p_ae_header_id);
2680 LOOP
2681 FETCH trx_ccid INTO l_ccid;
2682 EXIT WHEN trx_ccid%NOTFOUND;
2683
2684 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
2685 USING l_ccid;
2686
2687 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
2688 USING l_ccid;
2689
2690 IF GT_TRX_TAXABLE_BAL_SEG(j) IS NULL then
2691 GT_TRX_TAXABLE_BAL_SEG(j) := L_BAL_SEG_VAL;
2692 ELSE
2693 IF INSTRB(GT_TRX_TAXABLE_BAL_SEG(j),L_BAL_SEG_VAL) > 0 THEN
2694 NULL;
2695 ELSE
2696 GT_TRX_TAXABLE_BAL_SEG(j) := GT_TRX_TAXABLE_BAL_SEG(j)
2697 ||','||L_BAL_SEG_VAL;
2698 END IF;
2699 END IF;
2700
2701
2702 IF GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) IS NULL then
2703 GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) := L_ACCT_SEG_VAL;
2704 ELSE
2705 IF INSTRB(GT_TRX_TAXABLE_NATURAL_ACCOUNT(j),L_BAL_SEG_VAL) > 0 THEN
2706 NULL;
2707 ELSE
2708 GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(j)
2709 ||','||L_ACCT_SEG_VAL;
2710 END IF;
2711 END IF;
2712
2713 GT_TRX_ARAP_BALANCING_SEGMENT(j) := GT_TRX_TAXABLE_BAL_SEG(j);
2714 GT_TRX_ARAP_NATURAL_ACCOUNT(j) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(j);
2715 END LOOP;
2716
2717
2718 OPEN tax_ccid (p_actg_source_id, p_event_id, p_ae_header_id);
2719 LOOP
2720 FETCH tax_ccid INTO l_ccid;
2721 EXIT WHEN tax_ccid%NOTFOUND;
2722
2723 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
2724 USING l_ccid;
2725
2726 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
2727 USING l_ccid;
2728
2729 IF GT_TRX_TAX_BALANCING_SEGMENT(j) IS NULL then
2730 GT_TRX_TAX_BALANCING_SEGMENT(j) := L_BAL_SEG_VAL;
2731 ELSE
2732 IF INSTRB(GT_TRX_TAX_BALANCING_SEGMENT(j),L_BAL_SEG_VAL) > 0 THEN
2733 NULL;
2734 ELSE
2735 GT_TRX_TAX_BALANCING_SEGMENT(j) := GT_TRX_TAX_BALANCING_SEGMENT(j)
2736 ||','||L_BAL_SEG_VAL;
2737 END IF;
2738 END IF;
2739
2740
2741 IF GT_TRX_TAX_NATURAL_ACCOUNT(j) IS NULL then
2742 GT_TRX_TAX_NATURAL_ACCOUNT(j) := L_ACCT_SEG_VAL;
2743 ELSE
2744 IF INSTRB(GT_TRX_TAX_NATURAL_ACCOUNT(j),L_BAL_SEG_VAL) > 0 THEN
2745 NULL;
2746 ELSE
2747 GT_TRX_TAX_NATURAL_ACCOUNT(j) := GT_TRX_TAX_NATURAL_ACCOUNT(j)
2748 ||','||L_ACCT_SEG_VAL;
2749 END IF;
2750 END IF;
2751
2752 END LOOP;
2753 /*
2754 ELSIF P_SUMMARY_LEVEL = 'TRANSACTION_LINE' THEN
2755 OPEN trx_line_ccid (p_trx_id, p_trx_line_id, p_event_id, p_ae_header_id);
2756 LOOP
2757 FETCH trx_line_ccid INTO l_ccid;
2758 EXIT WHEN trx_line_ccid%NOTFOUND;
2759
2760 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
2761 USING l_ccid;
2762
2763 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
2764 USING l_ccid;
2765
2766 IF GT_TRX_TAXABLE_BAL_SEG(j) IS NULL then
2767 GT_TRX_TAXABLE_BAL_SEG(j) := L_BAL_SEG_VAL;
2768 ELSE
2769 IF INSTRB(GT_TRX_TAXABLE_BAL_SEG(j),L_BAL_SEG_VAL) > 0 THEN
2770 NULL;
2771 ELSE
2772 GT_TRX_TAXABLE_BAL_SEG(j) := GT_TRX_TAXABLE_BAL_SEG(j)
2773 ||','||L_BAL_SEG_VAL;
2774 END IF;
2775 END IF;
2776
2777
2778 IF GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) IS NULL then
2779 GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) := L_ACCT_SEG_VAL;
2780 ELSE
2781 IF INSTRB(GT_TRX_TAXABLE_NATURAL_ACCOUNT(j),L_BAL_SEG_VAL) > 0 THEN
2782 NULL;
2783 ELSE
2784 GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(j)
2785 ||','||L_ACCT_SEG_VAL;
2786 END IF;
2787 END IF;
2788
2789 GT_TRX_ARAP_BALANCING_SEGMENT(j) := GT_TRX_TAXABLE_BAL_SEG(j);
2790 GT_TRX_ARAP_NATURAL_ACCOUNT(j) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(j);
2791 END LOOP;
2792
2793
2794 OPEN tax_line_ccid (p_trx_id, p_trx_line_id, p_event_id, p_ae_header_id);
2795 LOOP
2796 FETCH tax_line_ccid INTO l_ccid;
2797 EXIT WHEN tax_line_ccid%NOTFOUND;
2798
2799 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
2800 USING l_ccid;
2801
2802 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
2803 USING l_ccid;
2804
2805 IF GT_TRX_TAX_BALANCING_SEGMENT(j) IS NULL then
2806 GT_TRX_TAX_BALANCING_SEGMENT(j) := L_BAL_SEG_VAL;
2807 ELSE
2808 IF INSTRB(GT_TRX_TAX_BALANCING_SEGMENT(j),L_BAL_SEG_VAL) > 0 THEN
2809 NULL;
2810 ELSE
2811 GT_TRX_TAX_BALANCING_SEGMENT(j) := GT_TRX_TAX_BALANCING_SEGMENT(j)
2812 ||','||L_BAL_SEG_VAL;
2813 END IF;
2814 END IF;
2815
2816
2817 IF GT_TRX_TAX_NATURAL_ACCOUNT(j) IS NULL then
2818 GT_TRX_TAX_NATURAL_ACCOUNT(j) := L_ACCT_SEG_VAL;
2819 ELSE
2820 IF INSTRB(GT_TRX_TAX_NATURAL_ACCOUNT(j),L_BAL_SEG_VAL) > 0 THEN
2821 NULL;
2822 ELSE
2823 GT_TRX_TAX_NATURAL_ACCOUNT(j) := GT_TRX_TAX_NATURAL_ACCOUNT(j)
2824 ||','||L_ACCT_SEG_VAL;
2825 END IF;
2826 END IF;
2827
2828 END LOOP;
2829 */
2830
2831 ELSIF P_SUMMARY_LEVEL = 'TRANSACTION_DISTRIBUTION' THEN
2832
2833 IF p_event_id IS NOT NULL AND p_ae_header_id IS NOT NULL THEN
2834
2835 OPEN trx_dist_ccid (p_trx_line_id, p_event_id, p_ae_header_id);
2836 LOOP
2837 FETCH trx_dist_ccid INTO l_ccid;
2838 EXIT WHEN trx_dist_ccid%NOTFOUND;
2839
2840 l_trx_dist_ccid := l_ccid; --Bug 5510907
2841
2842 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
2843 USING l_ccid;
2844
2845 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
2846 USING l_ccid;
2847
2848 IF L_BAL_SEG_VAL IS NOT NULL THEN
2849 L_BAL_SEG_DESC := FA_RX_FLEX_PKG.GET_DESCRIPTION(
2850 P_APPLICATION_ID => 101,
2851 P_ID_FLEX_CODE => 'GL#',
2852 P_ID_FLEX_NUM => g_coa_id,
2853 P_QUALIFIER => 'GL_BALANCING',
2854 P_DATA => L_BAL_SEG_VAL);
2855 END IF;
2856
2857 IF GT_TRX_TAXABLE_BAL_SEG(j) IS NULL then
2858 GT_TRX_TAXABLE_BAL_SEG(j) := L_BAL_SEG_VAL;
2859 GT_TRX_TAXABLE_BALSEG_DESC(j) := L_BAL_SEG_DESC;
2860 ELSE
2861 IF INSTRB(GT_TRX_TAXABLE_BAL_SEG(j),L_BAL_SEG_VAL) > 0 THEN
2862 NULL;
2863 ELSE
2864 GT_TRX_TAXABLE_BAL_SEG(j) := GT_TRX_TAXABLE_BAL_SEG(j)
2865 ||','||L_BAL_SEG_VAL;
2866 GT_TRX_TAXABLE_BALSEG_DESC(j) := GT_TRX_TAXABLE_BALSEG_DESC(j) || ',' || L_BAL_SEG_DESC;
2867 END IF;
2868 END IF;
2869
2870
2871 IF GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) IS NULL then
2872 GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) := L_ACCT_SEG_VAL;
2873 ELSE
2874 IF INSTRB(GT_TRX_TAXABLE_NATURAL_ACCOUNT(j),L_BAL_SEG_VAL) > 0 THEN
2875 NULL;
2876 ELSE
2877 GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(j)
2878 ||','||L_ACCT_SEG_VAL;
2879 END IF;
2880 END IF;
2881
2882 GT_TRX_ARAP_BALANCING_SEGMENT(j) := GT_TRX_TAXABLE_BAL_SEG(j);
2883 GT_TRX_ARAP_NATURAL_ACCOUNT(j) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(j);
2884 END LOOP;
2885
2886
2887 OPEN tax_dist_ccid (p_actg_source_id, p_event_id, p_ae_header_id);
2888 LOOP
2889 FETCH tax_dist_ccid INTO l_ccid;
2890 EXIT WHEN tax_dist_ccid%NOTFOUND;
2891 l_tax_dist_ccid := l_ccid;
2892 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
2893 USING l_ccid;
2894
2895 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
2896 USING l_ccid;
2897
2898 IF GT_TRX_TAX_BALANCING_SEGMENT(j) IS NULL then
2899 GT_TRX_TAX_BALANCING_SEGMENT(j) := L_BAL_SEG_VAL;
2900 ELSE
2901 IF INSTRB(GT_TRX_TAX_BALANCING_SEGMENT(j),L_BAL_SEG_VAL) > 0 THEN
2902 NULL;
2903 ELSE
2904 GT_TRX_TAX_BALANCING_SEGMENT(j) := GT_TRX_TAX_BALANCING_SEGMENT(j)
2905 ||','||L_BAL_SEG_VAL;
2906 END IF;
2907 END IF;
2908
2909
2910 IF GT_TRX_TAX_NATURAL_ACCOUNT(j) IS NULL then
2911 GT_TRX_TAX_NATURAL_ACCOUNT(j) := L_ACCT_SEG_VAL;
2912 ELSE
2913 IF INSTRB(GT_TRX_TAX_NATURAL_ACCOUNT(j),L_BAL_SEG_VAL) > 0 THEN
2914 NULL;
2915 ELSE
2916 GT_TRX_TAX_NATURAL_ACCOUNT(j) := GT_TRX_TAX_NATURAL_ACCOUNT(j)
2917 ||','||L_ACCT_SEG_VAL;
2918 END IF;
2919 END IF;
2920
2921 END LOOP;
2922 ELSE -- Adjustments for Tax Reconciliation Report ---
2923
2924 BEGIN
2925 SELECT CODE_COMBINATION_ID INTO l_ccid
2926 FROM AR_DISTRIBUTIONS_ALL
2927 WHERE LINE_ID = P_ACTG_SOURCE_ID;
2928 --SOURCE_ID = P_TRX_LINE_ID
2929
2930 agt_actg_line_ccid(j) := l_ccid;
2931
2932 l_tax_dist_ccid := l_ccid;
2933 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
2934 USING l_ccid;
2935
2936 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
2937 USING l_ccid;
2938
2939 IF (g_level_procedure >= g_current_runtime_level ) THEN
2940 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.other_trx_segment_info',
2941 'Tax Line : trx id - P_ACTG_SOURCE_ID- ccid :' ||to_char(p_trx_id)
2942 ||'-'||to_char(P_ACTG_SOURCE_ID)||'-'||to_char(l_ccid)||'-'||L_BAL_SEG_VAL);
2943 END IF;
2944
2945
2946 IF L_BAL_SEG_VAL IS NOT NULL THEN
2947 L_BAL_SEG_DESC := FA_RX_FLEX_PKG.GET_DESCRIPTION(
2948 P_APPLICATION_ID => 101,
2949 P_ID_FLEX_CODE => 'GL#',
2950 P_ID_FLEX_NUM => g_coa_id,
2951 P_QUALIFIER => 'GL_BALANCING',
2952 P_DATA => L_BAL_SEG_VAL);
2953 END IF;
2954
2955
2956 IF GT_TRX_TAX_BALANCING_SEGMENT(j) IS NULL then
2957 GT_TRX_TAX_BALANCING_SEGMENT(j) := L_BAL_SEG_VAL;
2958 GT_TRX_TAXABLE_BALSEG_DESC(j) := L_BAL_SEG_DESC;
2959 ELSE
2960 IF INSTRB(GT_TRX_TAX_BALANCING_SEGMENT(j),L_BAL_SEG_VAL) > 0 THEN
2961 NULL;
2962 ELSE
2963 GT_TRX_TAX_BALANCING_SEGMENT(j) := GT_TRX_TAX_BALANCING_SEGMENT(j)
2964 ||','||L_BAL_SEG_VAL;
2965 END IF;
2966 END IF;
2967
2968
2969 IF GT_TRX_TAX_NATURAL_ACCOUNT(j) IS NULL then
2970 GT_TRX_TAX_NATURAL_ACCOUNT(j) := L_ACCT_SEG_VAL;
2971 ELSE
2972 IF INSTRB(GT_TRX_TAX_NATURAL_ACCOUNT(j),L_BAL_SEG_VAL) > 0 THEN
2973 NULL;
2974 ELSE
2975 GT_TRX_TAX_NATURAL_ACCOUNT(j) := GT_TRX_TAX_NATURAL_ACCOUNT(j)
2976 ||','||L_ACCT_SEG_VAL;
2977 END IF;
2978 END IF;
2979 exception
2980 when no_data_found then
2981 NULL;
2982 END;
2983
2984 /* IF P_TRX_LINE_ID IS NOT NULL THEN
2985 BEGIN
2986 SELECT CODE_COMBINATION_ID INTO l_ccid
2987 FROM AR_DISTRIBUTIONS_ALL
2988 WHERE LINE_ID = P_TRX_LINE_ID;
2989 --SOURCE_ID = P_TRX_ID
2990
2991
2992 l_trx_dist_ccid := l_ccid; --Bug 5510907
2993
2994 EXECUTE IMMEDIATE L_SQL_STATEMENT1 INTO L_BAL_SEG_VAL
2995 USING l_ccid;
2996
2997 EXECUTE IMMEDIATE L_SQL_STATEMENT2 INTO L_ACCT_SEG_VAL
2998 USING l_ccid;
2999
3000 IF (g_level_procedure >= g_current_runtime_level ) THEN
3001 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.other_trx_segment_info',
3002 'Taxable line : - trx id - P_ACTG_SOURCE_ID- ccid :' ||to_char(p_trx_id)
3003 ||'-'||to_char(P_TRX_LINE_ID)||'-'||to_char(l_ccid)||'-'||L_BAL_SEG_VAL);
3004 END IF;
3005
3006 IF L_BAL_SEG_VAL IS NOT NULL THEN
3007 L_BAL_SEG_DESC := FA_RX_FLEX_PKG.GET_DESCRIPTION(
3008 P_APPLICATION_ID => 101,
3009 P_ID_FLEX_CODE => 'GL#',
3010 P_ID_FLEX_NUM => g_coa_id,
3011 P_QUALIFIER => 'GL_BALANCING',
3012 P_DATA => L_BAL_SEG_VAL);
3013 END IF;
3014
3015 IF GT_TRX_TAXABLE_BAL_SEG(j) IS NULL then
3016 GT_TRX_TAXABLE_BAL_SEG(j) := L_BAL_SEG_VAL;
3017 GT_TRX_TAXABLE_BALSEG_DESC(j) := L_BAL_SEG_DESC;
3018 ELSE
3019 IF INSTRB(GT_TRX_TAXABLE_BAL_SEG(j),L_BAL_SEG_VAL) > 0 THEN
3020 NULL;
3021 ELSE
3022 GT_TRX_TAXABLE_BAL_SEG(j) := GT_TRX_TAXABLE_BAL_SEG(j)
3023 ||','||L_BAL_SEG_VAL;
3024 GT_TRX_TAXABLE_BALSEG_DESC(j) := GT_TRX_TAXABLE_BALSEG_DESC(j) || ',' || L_BAL_SEG_DESC;
3025 END IF;
3026 END IF;
3027
3028
3029 IF GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) IS NULL then
3030 GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) := L_ACCT_SEG_VAL;
3031 ELSE
3032 IF INSTRB(GT_TRX_TAXABLE_NATURAL_ACCOUNT(j),L_BAL_SEG_VAL) > 0 THEN
3033 NULL;
3034 ELSE
3035 GT_TRX_TAXABLE_NATURAL_ACCOUNT(j) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(j)
3036 ||','||L_ACCT_SEG_VAL;
3037 END IF;
3038 END IF;
3039
3040 GT_TRX_ARAP_BALANCING_SEGMENT(j) := GT_TRX_TAXABLE_BAL_SEG(j);
3041 GT_TRX_ARAP_NATURAL_ACCOUNT(j) := GT_TRX_TAXABLE_NATURAL_ACCOUNT(j);
3042 exception
3043 when no_data_found then
3044 NULL;
3045 END;
3046 END IF; */
3047 END IF;
3048
3049 -- populare accounting_flexfield and accounting_description column ---
3050 ----------------------------------------------------------------------
3051
3052 IF l_tax_dist_ccid IS NOT NULL THEN
3053
3054 GT_ACCOUNT_FLEXFIELD(j) := FA_RX_FLEX_PKG.GET_VALUE(
3055 P_APPLICATION_ID => 101,
3056 P_ID_FLEX_CODE => 'GL#',
3057 P_ID_FLEX_NUM => g_coa_id,
3058 P_QUALIFIER => 'ALL',
3059 P_CCID => l_tax_dist_ccid);
3060
3061 IF (g_level_procedure >= g_current_runtime_level ) THEN
3062 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.OTHER_TRX_SEGMENT_INFO',
3063 'Account Flexfield = '||GT_ACCOUNT_FLEXFIELD(j));
3064 END IF;
3065
3066 GT_ACCOUNT_DESCRIPTION(j) := FA_RX_FLEX_PKG.GET_DESCRIPTION(
3067 P_APPLICATION_ID => 101,
3068 P_ID_FLEX_CODE => 'GL#',
3069 P_ID_FLEX_NUM => g_coa_id,
3070 P_QUALIFIER => 'ALL',
3071 P_DATA => GT_ACCOUNT_FLEXFIELD(j));
3072
3073 IF (g_level_procedure >= g_current_runtime_level ) THEN
3074 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.OTHER_TRX_SEGMENT_INFO',
3075 'Account Description = '||GT_ACCOUNT_DESCRIPTION(j));
3076 END IF;
3077
3078 END IF;
3079
3080 --Bug 5510907 : To get the accounting Flexfield for the Taxable Line
3081
3082 IF l_trx_dist_ccid IS NOT NULL THEN
3083
3084 GT_TRX_CONTROL_ACCFLEXFIELD(j) := FA_RX_FLEX_PKG.GET_VALUE(
3085 P_APPLICATION_ID => 101,
3086 P_ID_FLEX_CODE => 'GL#',
3087 P_ID_FLEX_NUM => g_coa_id,
3088 P_QUALIFIER => 'ALL',
3089 P_CCID => l_trx_dist_ccid);
3090
3091 IF (g_level_procedure >= g_current_runtime_level ) THEN
3092 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.GET_ACCOUNTING_INFO',
3093 'other_trx_segment_info : Taxable Line Account Flexfield = '||GT_TRX_CONTROL_ACCFLEXFIELD(j));
3094 END IF;
3095 END IF ;
3096 ---- End of accounting flexfield population -----------------------
3097
3098 END IF; -- Summary Level
3099 IF (g_level_procedure >= g_current_runtime_level ) THEN
3100 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.other_trx_segment_info.END',
3101 'ZX_AR_POPULATE_PKG: other_trx_segment_info(-)');
3102 END IF;
3103
3104 END other_trx_segment_info;
3105
3106
3107
3108 PROCEDURE other_trx_actg_amounts(P_TRX_ID IN NUMBER,
3109 P_TRX_LINE_ID IN NUMBER,
3110 P_TAX_LINE_ID IN NUMBER,
3111 -- P_ENTITY_ID IN NUMBER,
3112 P_EVENT_ID IN NUMBER,
3113 P_AE_HEADER_ID IN NUMBER,
3114 P_ACTG_SOURCE_ID IN NUMBER,
3115 P_SUMMARY_LEVEL IN VARCHAR2,
3116 P_TRX_CLASS IN VARCHAR2,
3117 P_LEDGER_ID IN NUMBER,
3118 j IN binary_integer) IS
3119 -- Transaction Header Level
3120 CURSOR taxable_amount_hdr (c_actg_source_id NUMBER, c_ae_header_id NUMBER,
3121 c_event_id NUMBER,c_ledger_id NUMBER) IS
3122 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
3123 FROM ar_distributions_all dist,
3124 xla_distribution_links lnk,
3125 xla_ae_headers aeh,
3126 xla_ae_lines ael
3127 WHERE dist.line_id = c_actg_source_id
3128 AND lnk.application_id = 222
3129 AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
3130 AND lnk.source_distribution_id_num_1 = dist.line_id
3131 AND lnk.ae_header_id = c_ae_header_id
3132 AND lnk.event_id = c_event_id
3133 AND lnk.ae_line_num = ael.ae_line_num
3134 AND aeh.ae_header_id = ael.ae_header_id
3135 AND aeh.ledger_id = c_ledger_id
3136 AND aeh.ae_header_id = lnk.ae_header_id
3137 AND aeh.application_id = lnk.application_id
3138 AND ael.application_id = aeh.application_id;
3139
3140
3141 CURSOR tax_amount_hdr (c_actg_source_id NUMBER, c_ae_header_id NUMBER,
3142 c_event_id NUMBER,c_ledger_id NUMBER) IS
3143 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
3144 FROM AR_DISTRIBUTIONS_ALL dist,
3145 AR_DISTRIBUTIONS_ALL taxdist,
3146 xla_distribution_links lnk,
3147 xla_ae_headers aeh,
3148 xla_ae_lines ael
3149 WHERE dist.line_id = c_actg_source_id
3150 AND taxdist.tax_link_id = dist.tax_link_id
3151 AND NVL(taxdist.source_type_secondary,'X') = NVL(dist.source_type,'X')
3152 AND taxdist.source_id = dist.source_id
3153 AND lnk.source_distribution_id_num_1 = taxdist.line_id
3154 AND lnk.application_id = 222
3155 AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
3156 AND lnk.ae_header_id = c_ae_header_id
3157 AND lnk.event_id = c_event_id
3158 AND lnk.ae_line_num = ael.ae_line_num
3159 AND aeh.ae_header_id = ael.ae_header_id
3160 AND aeh.ledger_id = c_ledger_id
3161 AND aeh.ae_header_id = lnk.ae_header_id
3162 AND aeh.application_id = lnk.application_id
3163 AND ael.application_id = aeh.application_id;
3164
3165
3166 -- Transaction Distribution Level
3167
3168 CURSOR taxable_amount_dist (c_actg_source_id NUMBER, c_ae_header_id NUMBER, c_event_id NUMBER,c_ledger_id NUMBER) IS
3169 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
3170 FROM AR_DISTRIBUTIONS_ALL dist,
3171 AR_DISTRIBUTIONS_ALL taxdist,
3172 xla_distribution_links lnk,
3173 xla_ae_headers aeh,
3174 xla_ae_lines ael
3175 WHERE taxdist.line_id = c_actg_source_id
3176 AND taxdist.tax_link_id = dist.tax_link_id
3177 AND NVL(taxdist.source_type_secondary,'X') = NVL(dist.source_type,'X')
3178 AND taxdist.source_id = dist.source_id
3179 AND lnk.source_distribution_id_num_1 = dist.line_id
3180 AND lnk.application_id = 222
3181 AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
3182 AND lnk.ae_header_id = c_ae_header_id
3183 AND lnk.event_id = c_event_id
3184 AND lnk.ae_line_num = ael.ae_line_num
3185 AND aeh.ae_header_id = ael.ae_header_id
3186 AND aeh.ledger_id = c_ledger_id
3187 AND aeh.ae_header_id = lnk.ae_header_id
3188 AND aeh.application_id = lnk.application_id
3189 AND ael.application_id = aeh.application_id;
3190
3191 CURSOR tax_amount_dist (c_actg_source_id NUMBER, c_ae_header_id NUMBER,
3192 c_event_id NUMBER,c_ledger_id NUMBER) IS
3193 SELECT sum(lnk.DOC_ROUNDING_ENTERED_AMT), sum(lnk.DOC_ROUNDING_ACCTD_AMT)
3194 FROM ar_distributions_all taxdist,
3195 xla_distribution_links lnk,
3196 xla_ae_headers aeh,
3197 xla_ae_lines ael
3198 WHERE taxdist.line_id = c_actg_source_id
3199 AND lnk.application_id = 222
3200 AND lnk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
3201 AND lnk.source_distribution_id_num_1 = taxdist.line_id
3202 AND lnk.ae_header_id = c_ae_header_id
3203 AND lnk.event_id = c_event_id
3204 AND lnk.ae_line_num = ael.ae_line_num
3205 AND aeh.ae_header_id = ael.ae_header_id
3206 AND aeh.ledger_id = c_ledger_id
3207 AND aeh.ae_header_id = lnk.ae_header_id
3208 AND aeh.application_id = lnk.application_id
3209 AND ael.application_id = aeh.application_id;
3210
3211 BEGIN
3212 IF (g_level_procedure >= g_current_runtime_level ) THEN
3213 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.other_trx_actg_amounts.BEGIN',
3214 'ZX_AR_POPULATE_PKG: other_trx_actg_amounts(+)');
3215 END IF;
3216
3217 IF p_summary_level = 'TRANSACTION' THEN
3218 OPEN taxable_amount_hdr(p_actg_source_id , p_ae_header_id , p_event_id,p_ledger_id );
3219 FETCH taxable_amount_hdr INTO GT_TAXABLE_AMT(j),GT_TAXABLE_AMT_FUNCL_CURR(j);
3220 -- EXIT WHEN taxable_amount_hdr%NOTFOUND;
3221 CLOSE taxable_amount_hdr;
3222
3223 OPEN tax_amount_hdr(p_actg_source_id , p_ae_header_id , p_event_id,p_ledger_id);
3224 FETCH tax_amount_hdr INTO GT_TAX_AMT(j),GT_TAX_AMT_FUNCL_CURR(j);
3225 -- EXIT WHEN tax_amount_hdr%NOTFOUND;
3226 CLOSE tax_amount_hdr;
3227 /* ELSIF p_summary_level = 'TRANSACTION_LINE' THEN
3228 OPEN taxable_amount_line(p_trx_id ,p_trx_line_id, p_ae_header_id , p_event_id);
3229 FETCH taxable_amount_line INTO GT_TAXABLE_AMT(j),GT_TAXABLE_AMT_FUNCL_CURR(j);
3230 -- EXIT WHEN taxable_amount_line%NOTFOUND;
3231 CLOSE taxable_amount_line;
3232
3233 OPEN tax_amount_line(p_trx_id , p_trx_line_id, p_ae_header_id , p_event_id);
3234 FETCH tax_amount_line INTO GT_TAX_AMT(j),GT_TAX_AMT_FUNCL_CURR(j);
3235 -- EXIT WHEN tax_amount_line%NOTFOUND;
3236 CLOSE tax_amount_line;
3237 */
3238 ELSIF p_summary_level = 'TRANSACTION_DISTRIBUTION' THEN
3239 OPEN taxable_amount_dist(p_actg_source_id ,p_ae_header_id , p_event_id,p_ledger_id);
3240 FETCH taxable_amount_dist INTO GT_TAXABLE_AMT(j),GT_TAXABLE_AMT_FUNCL_CURR(j);
3241 -- EXIT WHEN taxable_amount_dist%NOTFOUND;
3242 CLOSE taxable_amount_dist;
3243
3244 OPEN tax_amount_dist(p_actg_source_id, p_ae_header_id , p_event_id,p_ledger_id);
3245 FETCH tax_amount_dist INTO GT_TAX_AMT(j),GT_TAX_AMT_FUNCL_CURR(j);
3246 -- EXIT WHEN tax_amount_dist%NOTFOUND;
3247 CLOSE tax_amount_dist;
3248 END IF;
3249
3250 IF (g_level_procedure >= g_current_runtime_level ) THEN
3251 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.other_trx_actg_amounts.END',
3252 'ZX_AR_POPULATE_PKG: other_trx_actg_amounts(-)');
3253 END IF;
3254
3255 END other_trx_actg_amounts;
3256
3257 /*===========================================================================+
3258 | PROCEDURE |
3259 | convert_amounts |
3260 | |
3261 | DESCRIPTION |
3262 | This procedure converts tax and taxable amounts into functional amounts|
3263 | |
3264 | |
3265 | SCOPE - Private |
3266 | |
3267 | NOTES |
3268 | |
3269 | MODIFICATION HISTORY |
3270 | |
3271 +===========================================================================*/
3272
3273
3274 PROCEDURE convert_amounts(P_CURRENCY_CODE IN VARCHAR2,
3275 P_EXCHANGE_RATE IN NUMBER,
3276 P_PRECISION IN NUMBER,
3277 P_MIN_ACCT_UNIT IN NUMBER,
3278 P_INPUT_TAX_AMOUNT IN NUMBER,
3279 P_INPUT_TAXABLE_AMOUNT IN NUMBER,
3280 P_INPUT_EXEMPT_AMOUNT IN NUMBER,
3281 i IN BINARY_INTEGER) IS
3282
3283 l_taxable_amount NUMBER;
3284 l_TAXABLE_ACCOUNTED_AMOUNT number;
3285 l_TAX_ACCOUNTED_AMOUNT number;
3286 BEGIN
3287
3288 IF (g_level_procedure >= g_current_runtime_level ) THEN
3289 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.convert_amounts.BEGIN',
3290 'ZX_AR_POPULATE_PKG: convert_amounts(+)');
3291 END IF;
3292 /*
3293 IF P_INPUT_EXEMPT_AMOUNT IS NOT NULL THEN
3294 P_EXEMPT_ENTERED_AMOUNT := P_INPUT_EXEMPT_AMOUNT;
3295 P_TAXABLE_EXEMPT_ENTERED_AMT :=
3296 P_INPUT_TAXABLE_AMOUNT + P_INPUT_EXEMPT_AMOUNT;
3297 l_taxable_amount := P_INPUT_TAXABLE_AMOUNT ;
3298
3299 ELSE
3300 P_EXEMPT_ENTERED_AMOUNT := 0;
3301 P_TAXABLE_EXEMPT_ENTERED_AMT := P_INPUT_TAXABLE_AMOUNT;
3302 l_taxable_amount := P_INPUT_TAXABLE_AMOUNT;
3303 END IF;
3304
3305 IF P_EXEMPT_ENTERED_AMOUNT IS NOT NULL THEN
3306 P_EXEMPT_ACCTD_AMOUNT := arpcurr.FUNCTIONAL_AMOUNT(
3307 P_EXEMPT_ENTERED_AMOUNT,
3308 P_CURRENCY_CODE,
3309 P_EXCHANGE_RATE,
3310 P_PRECISION,
3311 P_MIN_ACCT_UNIT);
3312 END IF;
3313
3314 IF P_TAXABLE_EXEMPT_ENTERED_AMT IS NOT NULL THEN
3315 P_TAXABLE_EXEMPT_ACCTD_AMT := arpcurr.FUNCTIONAL_AMOUNT(
3316 P_TAXABLE_EXEMPT_ENTERED_AMT,
3317 P_CURRENCY_CODE,
3318 P_EXCHANGE_RATE,
3319 P_PRECISION,
3320 P_MIN_ACCT_UNIT);
3321 END IF;
3322
3323 P_TAX_ENTERED_AMOUNT := P_INPUT_TAX_AMOUNT;
3324
3325
3326 P_TAXABLE_AMOUNT := l_taxable_amount;
3327 */
3328 IF P_INPUT_TAX_AMOUNT IS NOT NULL THEN
3329 l_TAX_ACCOUNTED_AMOUNT := arpcurr.FUNCTIONAL_AMOUNT(
3330 P_INPUT_TAX_AMOUNT,
3331 P_CURRENCY_CODE,
3332 P_EXCHANGE_RATE,
3333 P_PRECISION,
3334 P_MIN_ACCT_UNIT);
3335 END IF;
3336
3337 IF p_input_taxable_amount IS NOT NULL THEN
3338 l_TAXABLE_ACCOUNTED_AMOUNT := arpcurr.FUNCTIONAL_AMOUNT(
3339 p_input_taxable_amount,
3340 P_CURRENCY_CODE,
3341 P_EXCHANGE_RATE,
3342 P_PRECISION,
3343 P_MIN_ACCT_UNIT);
3344 END IF;
3345 GT_TAX_AMT_FUNCL_CURR(i) := l_TAX_ACCOUNTED_AMOUNT;
3346 GT_TAXABLE_AMT_FUNCL_CURR(i) := l_TAXABLE_ACCOUNTED_AMOUNT;
3347
3348
3349 IF (g_level_procedure >= g_current_runtime_level ) THEN
3350 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.convert_amounts.END',
3351 'ZX_AR_POPULATE_PKG: convert_amounts(-)');
3352 END IF;
3353
3354 EXCEPTION
3355 WHEN OTHERS THEN
3356 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
3357 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
3358 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
3359 FND_MSG_PUB.Add;
3360 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3361 FND_LOG.STRING(g_level_unexpected,
3362 'ZX.TRL.ZX_AR_POPULATE_PKG.convert_amounts',
3363 g_error_buffer);
3364 END IF;
3365
3366 G_RETCODE := 2;
3367 END convert_amounts;
3368
3369 PROCEDURE EXTRACT_PARTY_INFO( i IN BINARY_INTEGER) IS
3370
3371 l_bill_to_party_id zx_rep_trx_detail_t.BILL_TO_PARTY_ID%TYPE;
3372 l_bill_to_pty_site_id zx_rep_trx_detail_t.BILL_TO_PARTY_SITE_ID%TYPE;
3373 l_bill_to_ptp_id zx_rep_trx_detail_t.BILL_FROM_PARTY_TAX_PROF_ID%TYPE;
3374 l_bill_to_stp_id zx_rep_trx_detail_t.BILL_FROM_SITE_TAX_PROF_ID%TYPE;
3375
3376 l_ship_to_party_id zx_rep_trx_detail_t.SHIP_TO_PARTY_ID%TYPE;
3377 l_ship_to_pty_site_id zx_rep_trx_detail_t.SHIP_TO_PARTY_SITE_ID%TYPE;
3378 l_ship_to_ptp_id zx_rep_trx_detail_t.SHIP_FROM_PARTY_TAX_PROF_ID%TYPE;
3379 l_ship_to_stp_id zx_rep_trx_detail_t.SHIP_FROM_SITE_TAX_PROF_ID%TYPE;
3380
3381 l_bill_to_acct_id zx_rep_trx_detail_t.BILL_TO_PARTY_ID%TYPE;
3382 l_bill_to_acct_site_id zx_rep_trx_detail_t.BILL_TO_PARTY_SITE_ID%TYPE;
3383
3384 l_ship_to_acct_id zx_rep_trx_detail_t.SHIP_TO_PARTY_ID%TYPE;
3385 l_ship_to_acct_site_id zx_rep_trx_detail_t.SHIP_TO_PARTY_SITE_ID%TYPE;
3386
3387 l_bill_ship varchar2(30);
3388
3389 l_tbl_index_party BINARY_INTEGER;
3390 l_tbl_index_bill_site BINARY_INTEGER;
3391 l_tbl_index_ship_site VARCHAR2(50);
3392 l_tbl_index_cust BINARY_INTEGER;
3393 --Bug 5622686
3394 p_parent_ptp_id zx_party_tax_profile.party_tax_profile_id%TYPE;
3395 p_site_ptp_id zx_party_tax_profile.party_tax_profile_id%TYPE;
3396 p_account_Type_Code zx_registrations.account_type_code%TYPE;
3397 p_tax_determine_date ZX_LINES.TAX_DETERMINE_DATE%TYPE;
3398 p_tax ZX_TAXES_B.TAX%TYPE;
3399 p_tax_regime_code ZX_REGIMES_B.TAX_REGIME_CODE%TYPE;
3400 p_jurisdiction_code ZX_JURISDICTIONS_B.TAX_JURISDICTION_CODE%TYPE;
3401 p_account_id ZX_REGISTRATIONS.ACCOUNT_ID%TYPE;
3402 p_account_site_id ZX_REGISTRATIONS.ACCOUNT_SITE_ID%TYPE;
3403 p_site_use_id HZ_CUST_SITE_USES_ALL.SITE_USE_ID%TYPE;
3404 p_zx_registration_rec ZX_TCM_CONTROL_PKG.ZX_REGISTRATION_INFO_REC;
3405 p_ret_record_level VARCHAR2(100);
3406 p_return_status VARCHAR2(100);
3407
3408
3409 CURSOR ledger_cur (c_ledger_id ZX_REP_TRX_DETAIL_T.ledger_id%TYPE) IS
3410 SELECT name
3411 FROM gl_ledgers
3412 WHERE ledger_id = c_ledger_id
3413 AND rownum = 1;
3414
3415 -- If party_id is NOT NULL and Historical flag 'Y' then get the party tax profile ID from zx_party_tax_profile
3416
3417 CURSOR party_reg_num_cur
3418 (c_party_id ZX_REP_TRX_DETAIL_T.BILL_TO_PARTY_ID%TYPE) IS
3419 SELECT rep_registration_number
3420 FROM zx_party_tax_profile
3421 WHERE party_id = c_party_id
3422 AND party_type_code = 'THIRD_PARTY';
3423
3424 CURSOR party_base_reg_num_cur
3425 (c_party_id ZX_REP_TRX_DETAIL_T.BILL_TO_PARTY_ID%TYPE) IS
3426 SELECT registration_number
3427 FROM zx_party_tax_profile ptp,
3428 zx_registrations reg
3429 WHERE ptp.party_id = c_party_id
3430 AND ptp.party_type_code = 'THIRD_PARTY'
3431 AND reg.party_tax_profile_id = ptp.party_tax_profile_id
3432 ORDER BY default_registration_flag DESC;
3433
3434 CURSOR party_site_reg_cur
3435 (c_party_site_id ZX_REP_TRX_DETAIL_T.BILL_TO_PARTY_SITE_ID%TYPE) IS
3436 SELECT rep_registration_number
3437 FROM zx_party_tax_profile
3438 WHERE party_id = c_party_site_id
3439 AND party_type_code = 'THIRD_PARTY_SITE';
3440
3441 CURSOR party_site_base_reg_cur
3442 (c_party_site_id ZX_REP_TRX_DETAIL_T.BILL_TO_PARTY_SITE_ID%TYPE) IS
3443 SELECT registration_number
3444 FROM zx_party_tax_profile ptp,
3445 zx_registrations reg
3446 WHERE ptp.party_id = c_party_site_id
3447 AND ptp.party_type_code = 'THIRD_PARTY_SITE'
3448 AND reg.party_tax_profile_id = ptp.party_tax_profile_id
3449 ORDER BY default_registration_flag DESC;
3450
3451 CURSOR party_cur (c_party_id ZX_REP_TRX_DETAIL_T.BILL_TO_PARTY_ID%TYPE) IS
3452 select SUBSTRB(PARTY.PARTY_NAME,1,240) ,
3453 DECODE(PARTY.PARTY_TYPE,
3454 'ORGANIZATION',
3455 PARTY.ORGANIZATION_NAME_PHONETIC,
3456 NULL) ,
3457 DECODE(PARTY.PARTY_TYPE,
3458 'ORGANIZATION',
3459 PARTY.SIC_CODE,
3460 NULL) ,
3461 PARTY.PARTY_NUMBER,
3462 PARTY.JGZZ_FISCAL_CODE
3463 FROM HZ_PARTIES PARTY
3464 WHERE PARTY.PARTY_ID = c_party_id;
3465
3466 CURSOR party_site_cur ( c_party_site_id ZX_REP_TRX_DETAIL_T.BILL_TO_PARTY_SITE_ID%TYPE) IS
3467 select LOC.CITY,
3468 LOC.COUNTY,
3469 LOC.STATE,
3470 LOC.PROVINCE,
3471 LOC.ADDRESS1,
3472 LOC.ADDRESS2,
3473 LOC.ADDRESS3,
3474 LOC.ADDRESS_LINES_PHONETIC,
3475 LOC.COUNTRY,
3476 LOC.POSTAL_CODE
3477 FROM HZ_PARTY_SITES PARTY_SITE,
3478 HZ_LOCATIONS LOC
3479 WHERE party_site.party_site_id = c_party_site_id
3480 AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID;
3481
3482 CURSOR cust_acct_cur (c_site_use_id ZX_REP_TRX_DETAIL_T.BILL_TO_PARTY_SITE_ID%TYPE,
3483 c_cust_account_id ZX_REP_TRX_DETAIL_T.BILL_TO_PARTY_ID%TYPE,
3484 c_ship_bill varchar2) IS
3485 SELECT acct.account_number,
3486 acct.global_attribute10,
3487 acct.global_attribute12,
3488 acct_site.global_attribute8,
3489 acct_site.global_attribute9,
3490 site_use.location,
3491 -- site_use.tax_reference
3492 acct.party_id,
3493 acct_site.party_site_id
3494 FROM hz_cust_accounts acct,
3495 hz_cust_site_uses_all site_use ,
3496 hz_cust_acct_sites_all acct_site
3497 WHERE acct.CUST_ACCOUNT_ID = acct_site.CUST_ACCOUNT_ID
3498 and acct_site.CUST_ACCT_SITE_ID = site_use.CUST_ACCT_SITE_ID
3499 and site_use.site_use_id = c_site_use_id
3500 and ACCT.CUST_ACCOUNT_ID = c_cust_account_id
3501 and site_use.site_use_code = c_ship_bill;
3502
3503
3504 CURSOR bank_tp_taxpayer_cur (c_bank_account_id ZX_REP_TRX_DETAIL_T.BANK_ACCOUNT_ID%TYPE) IS
3505 SELECT NVL(br_party.jgzz_fiscal_code, ba_party.jgzz_fiscal_code)
3506 FROM hz_parties br_party,
3507 hz_parties ba_party,
3508 ce_bank_branches_v ce_branch,
3509 ce_bank_accounts ce_accts
3510 WHERE ce_accts.bank_account_id = c_bank_account_id
3511 AND ce_accts.bank_branch_id = ce_branch.branch_party_id
3512 AND ce_branch.branch_party_id = br_party.party_id
3513 AND ce_branch.bank_party_id = ba_party.party_id;
3514
3515 CURSOR doc_seq_name_cur (c_doc_seq_id ZX_REP_TRX_DETAIL_T.doc_seq_id%TYPE) IS
3516 SELECT name
3517 FROM fnd_document_sequences
3518 WHERE doc_sequence_id = c_doc_seq_id;
3519
3520 BEGIN
3521
3522
3523 IF (g_level_procedure >= g_current_runtime_level ) THEN
3524 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO.BEGIN',
3525 'ZX_AR_POPULATE_PKG: EXTRACT_PARTY_INFO(+)');
3526 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3527 'gt_historical_flag :' ||gt_historical_flag(i));
3528 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3529 'GT_BILL_TO_PARTY_TAX_PROF_ID :' ||to_char(GT_BILL_TO_PARTY_TAX_PROF_ID(i)));
3530 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3531 'GT_BILLING_TP_SITE_ID :' ||to_char(GT_BILLING_TP_SITE_ID(i)));
3532 END IF;
3533
3534
3535 OPEN ledger_cur(GT_LEDGER_ID(i));
3536 FETCH ledger_cur into GT_LEDGER_NAME(i);
3537 CLOSE ledger_cur;
3538
3539
3540 l_bill_to_acct_site_id := GT_BILLING_TP_SITE_ID(i);
3541 l_bill_to_acct_id := GT_BILLING_TP_ID(i);
3542
3543 IF (g_level_procedure >= g_current_runtime_level ) THEN
3544 FND_LOG.STRING(g_level_procedure,
3545 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3546 'party_site_id_cur : l_bill_to_site_id '||to_char(l_bill_to_acct_site_id));
3547 END IF;
3548
3549 l_bill_ship := 'BILL_TO';
3550
3551
3552 -- IF GT_BILLING_TP_ID(i) IS NOT NULL AND GT_BILLING_TP_ADDRESS_ID(i) IS NOT NULL THEN
3553
3554 IF l_bill_to_acct_site_id is not null and l_bill_to_acct_id is not null THEN
3555 --l_tbl_index_cust := dbms_utility.get_hash_value(to_char(l_bill_to_acct_site_id)||
3556 -- to_char(l_bill_to_acct_id)|| l_bill_ship, 1,8192);
3557
3558 l_tbl_index_cust := to_char(l_bill_to_acct_site_id);
3559 --|| to_char(l_bill_to_acct_id);
3560 IF (g_level_procedure >= g_current_runtime_level ) THEN
3561 FND_LOG.STRING(g_level_procedure,
3562 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3563 'Before Open cust_acct_cur :'||to_char(l_bill_to_acct_site_id)||'-'||to_char(l_bill_to_acct_id));
3564 END IF;
3565
3566 IF g_cust_bill_ar_tbl.EXISTS(l_tbl_index_cust) THEN
3567 GT_BILLING_TP_NUMBER(i) := g_cust_bill_ar_tbl(l_tbl_index_cust).BILLING_TP_NUMBER ;
3568 GT_GDF_RA_CUST_BILL_ATT10(i) := g_cust_bill_ar_tbl(l_tbl_index_cust).GDF_RA_CUST_BILL_ATT10;
3569 GT_GDF_RA_CUST_BILL_ATT12(i) := g_cust_bill_ar_tbl(l_tbl_index_cust).GDF_RA_CUST_BILL_ATT12;
3570 GT_GDF_RA_ADDRESSES_BILL_ATT8(i) :=g_cust_bill_ar_tbl(l_tbl_index_cust).GDF_RA_ADDRESSES_BILL_ATT8;
3571 GT_GDF_RA_ADDRESSES_BILL_ATT9(i) :=g_cust_bill_ar_tbl(l_tbl_index_cust).GDF_RA_ADDRESSES_BILL_ATT9;
3572 GT_BILLING_TP_SITE_NAME(i) := g_cust_bill_ar_tbl(l_tbl_index_cust).BILLING_TP_SITE_NAME;
3573 GT_BILL_TO_PARTY_ID(i) := g_cust_bill_ar_tbl(l_tbl_index_cust).BILL_TO_PARTY_ID;
3574 GT_BILL_TO_PARTY_SITE_ID(i) := g_cust_bill_ar_tbl(l_tbl_index_cust).BILL_TO_PARTY_SITE_ID;
3575
3576 IF (g_level_procedure >= g_current_runtime_level ) THEN
3577 FND_LOG.STRING(g_level_procedure,
3578 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3579 'If g_cust_bill_ar_tbl.EXISTS :'||GT_BILLING_TP_NUMBER(i));
3580 END IF;
3581
3582 ELSE
3583 OPEN cust_acct_cur (l_bill_to_acct_site_id,
3584 l_bill_to_acct_id,
3585 l_bill_ship);
3586 FETCH cust_acct_cur INTO GT_BILLING_TP_NUMBER(i),
3587 GT_GDF_RA_CUST_BILL_ATT10(i),
3588 GT_GDF_RA_CUST_BILL_ATT12(i),
3589 GT_GDF_RA_ADDRESSES_BILL_ATT8(i),
3590 GT_GDF_RA_ADDRESSES_BILL_ATT9(i),
3591 GT_BILLING_TP_SITE_NAME(i),
3592 -- GT_BILLING_SITE_TAX_REG_NUM(i),
3593 GT_BILL_TO_PARTY_ID(i),
3594 GT_BILL_TO_PARTY_SITE_ID(i);
3595
3596 IF (g_level_procedure >= g_current_runtime_level ) THEN
3597 FND_LOG.STRING(g_level_procedure,
3598 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3599 'After fetch of cust_acct_cur'||GT_BILLING_TP_NUMBER(i)||'-'||GT_BILLING_TP_TAX_REG_NUM(i));
3600 END IF;
3601
3602 g_cust_bill_ar_tbl(l_tbl_index_cust).BILLING_TP_NUMBER := GT_BILLING_TP_NUMBER(i);
3603 g_cust_bill_ar_tbl(l_tbl_index_cust).GDF_RA_CUST_BILL_ATT10 := GT_GDF_RA_CUST_BILL_ATT10(i);
3604 g_cust_bill_ar_tbl(l_tbl_index_cust).GDF_RA_CUST_BILL_ATT12 := GT_GDF_RA_CUST_BILL_ATT12(i);
3605 g_cust_bill_ar_tbl(l_tbl_index_cust).GDF_RA_ADDRESSES_BILL_ATT8 := GT_GDF_RA_ADDRESSES_BILL_ATT8(i);
3606 g_cust_bill_ar_tbl(l_tbl_index_cust).GDF_RA_ADDRESSES_BILL_ATT9 := GT_GDF_RA_ADDRESSES_BILL_ATT9(i);
3607 g_cust_bill_ar_tbl(l_tbl_index_cust).BILLING_TP_SITE_NAME := GT_BILLING_TP_SITE_NAME(i);
3608 -- g_cust_bill_ar_tbl(l_tbl_index_cust).BILLING_SITE_TAX_REG_NUM := GT_BILLING_SITE_TAX_REG_NUM(i);
3609 g_cust_bill_ar_tbl(l_tbl_index_cust).BILL_TO_PARTY_ID := GT_BILL_TO_PARTY_ID(i);
3610 g_cust_bill_ar_tbl(l_tbl_index_cust).BILL_TO_PARTY_SITE_ID := GT_BILL_TO_PARTY_SITE_ID(i);
3611
3612 CLOSE cust_acct_cur;
3613 END IF;
3614 l_bill_to_pty_site_id := GT_BILL_TO_PARTY_SITE_ID(i);
3615 l_bill_to_party_id := GT_BILL_TO_PARTY_ID(i);
3616
3617 IF (g_level_procedure >= g_current_runtime_level ) THEN
3618 FND_LOG.STRING(g_level_procedure,
3619 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3620 'After assign to g_cust_bill_ar_tbl ');
3621 END IF;
3622
3623 --l_tbl_index_party := dbms_utility.get_hash_value(to_char(l_bill_to_party_id)||
3624 -- l_bill_ship, 1,8192);
3625 l_tbl_index_party := to_char(l_bill_to_party_id);
3626
3627 IF (g_level_procedure >= g_current_runtime_level ) THEN
3628 FND_LOG.STRING(g_level_procedure,
3629 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO', 'Party : l_tbl_index_party : '
3630 ||to_char(l_tbl_index_party));
3631 END IF;
3632
3633 IF g_party_bill_ar_tbl.EXISTS(l_tbl_index_party) THEN
3634 IF (g_level_procedure >= g_current_runtime_level ) THEN
3635 FND_LOG.STRING(g_level_procedure,
3636 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO', 'Party : exist : ');
3637 END IF;
3638
3639 GT_BILLING_TP_NAME_ALT(i) := g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_NAME_ALT;
3640 GT_BILLING_TP_NAME(i) := g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_NAME;
3641 GT_BILLING_TP_SIC_CODE(i) := g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_SIC_CODE;
3642 GT_BILLING_TP_NUMBER(i) := g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_NUMBER;
3643 GT_BILLING_TP_TAXPAYER_ID(i) := g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_TAXPAYER_ID;
3644 -- GT_BILLING_TP_TAX_REG_NUM(i) := g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_TAX_REG_NUM;
3645
3646 ELSE
3647 IF (g_level_procedure >= g_current_runtime_level ) THEN
3648 FND_LOG.STRING(g_level_procedure,
3649 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO', 'Party : not exist : '||to_char(l_bill_to_party_id));
3650 END IF;
3651 OPEN party_cur (l_bill_to_party_id);
3652 FETCH party_cur INTO GT_BILLING_TP_NAME(i),
3653 GT_BILLING_TP_NAME_ALT(i),
3654 GT_BILLING_TP_SIC_CODE(i),
3655 GT_BILLING_TP_NUMBER(i),
3656 GT_BILLING_TP_TAXPAYER_ID(i);
3657 -- GT_BILLING_TP_TAX_REG_NUM(i);
3658
3659 g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_NAME_ALT := GT_BILLING_TP_NAME_ALT(i);
3660 g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_NAME := GT_BILLING_TP_NAME(i);
3661 g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_SIC_CODE := GT_BILLING_TP_SIC_CODE(i);
3662 g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_NUMBER := GT_BILLING_TP_NUMBER(i);
3663 g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_TAXPAYER_ID := GT_BILLING_TP_TAXPAYER_ID(i);
3664 -- g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_TAX_REG_NUM := GT_BILLING_TP_TAX_REG_NUM(i);
3665
3666 CLOSE party_cur;
3667 END IF;
3668 OPEN party_reg_num_cur (l_bill_to_party_id);
3669 FETCH party_reg_num_cur into GT_BILLING_TP_TAX_REG_NUM(i);
3670 CLOSE party_reg_num_cur;
3671
3672 IF GT_BILLING_TP_TAX_REG_NUM(i) IS NULL THEN
3673 OPEN party_base_reg_num_cur (l_bill_to_party_id);
3674 FETCH party_base_reg_num_cur into GT_BILLING_TP_TAX_REG_NUM(i);
3675 CLOSE party_base_reg_num_cur;
3676 END IF;
3677
3678 -- Uncommented as Part of 7226438. //Commented as part of Bug 5622686 to include the api call for getting the registratio number
3679 OPEN party_site_reg_cur(l_bill_to_pty_site_id);
3680 FETCH party_site_reg_cur INTO GT_BILLING_SITE_TAX_REG_NUM(i);
3681 CLOSE party_site_reg_cur;
3682
3683 IF GT_BILLING_SITE_TAX_REG_NUM(i) IS NULL THEN
3684 OPEN party_site_base_reg_cur(l_bill_to_pty_site_id);
3685 FETCH party_site_base_reg_cur INTO GT_BILLING_SITE_TAX_REG_NUM(i);
3686 CLOSE party_site_base_reg_cur;
3687 END IF;
3688
3689 IF GT_BILLING_SITE_TAX_REG_NUM(i) IS NULL THEN -- Bug 7226438
3690 --Bug 5622686
3691 Begin
3692
3693 select decode(GT_APPLICATION_ID(i),222,'CUSTOMER',200,'SUPPLIER')
3694 into p_account_Type_Code
3695 from dual ;
3696
3697 p_parent_ptp_id := GT_BILL_TO_PARTY_TAX_PROF_ID(i);
3698 p_site_ptp_id := GT_BILL_TO_SITE_TAX_PROF_ID(i);
3699 p_tax_determine_date := gt_tax_determine_date(i);
3700 p_account_id:= GT_BILLING_TP_ID(i);
3701 p_account_site_id := GT_BILLING_TP_ADDRESS_ID(i);
3702 p_site_use_id := GT_BILLING_TP_SITE_ID(i);
3703
3704 IF (g_level_procedure >= g_current_runtime_level ) THEN
3705 FND_LOG.STRING(g_level_procedure,
3706 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3707 'Before call to ZX_TCM_CONTROL_PKG.Get_Tax_Registration api to get the registration number ');
3708 FND_LOG.STRING(g_level_procedure,
3709 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3710 'trx_id : '||GT_TRX_ID(i));
3711 FND_LOG.STRING(g_level_procedure,
3712 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3713 'p_parent_ptp_id : '||p_parent_ptp_id);
3714 FND_LOG.STRING(g_level_procedure,
3715 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3716 'p_site_ptp_id : '|| p_site_ptp_id);
3717 FND_LOG.STRING(g_level_procedure,
3718 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3719 'p_tax_determine_date : '|| p_tax_determine_date);
3720 FND_LOG.STRING(g_level_procedure,
3721 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3722 'p_account_id : '|| p_account_id);
3723 FND_LOG.STRING(g_level_procedure,
3724 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3725 'p_account_site_id : '|| p_account_site_id);
3726 FND_LOG.STRING(g_level_procedure,
3727 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3728 'p_site_use_id : '|| p_site_use_id);
3729 FND_LOG.STRING(g_level_procedure,
3730 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3731 'p_account_Type_Code : '|| p_account_Type_Code);
3732 END IF;
3733
3734 ZX_TCM_CONTROL_PKG.Get_Tax_Registration(
3735 p_parent_ptp_id
3736 , p_site_ptp_id
3737 , p_account_Type_Code
3738 , p_tax_determine_date
3739 , p_tax
3740 , p_tax_regime_code
3741 , p_jurisdiction_code
3742 , p_account_id
3743 , p_account_site_id
3744 , p_site_use_id
3745 , p_zx_registration_rec
3746 , p_ret_record_level
3747 , p_return_status );
3748
3749 if ZX_GLOBAL_STRUCTURES_PKG.g_cust_site_use_info_tbl.exists(p_site_use_id) then
3750 GT_BILLING_SITE_TAX_REG_NUM(i) := ZX_GLOBAL_STRUCTURES_PKG.g_cust_site_use_info_tbl(p_site_use_id).tax_reference;
3751 else
3752 GT_BILLING_SITE_TAX_REG_NUM(i) := null ;
3753 IF (g_level_procedure >= g_current_runtime_level ) THEN
3754 FND_LOG.STRING(g_level_procedure,'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3755 'Could not fetch a value for registration number ');
3756 end if ;
3757 end if;
3758
3759 IF (g_level_procedure >= g_current_runtime_level ) THEN
3760 FND_LOG.STRING(g_level_procedure,
3761 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3762 'Stauts: '|| p_return_status);
3763 FND_LOG.STRING(g_level_procedure,
3764 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3765 'Registration: '||p_zx_registration_rec.registration_number);
3766 FND_LOG.STRING(g_level_procedure,
3767 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3768 'Registration from structure : '||GT_BILLING_SITE_TAX_REG_NUM(i));
3769 END IF ;
3770 EXCEPTION
3771 WHEN OTHERS THEN
3772 NULL ;
3773 End;
3774 END IF; -- GT_BILLING_SITE_TAX_REG_NUM IS NULL
3775
3776 IF (g_level_procedure >= g_current_runtime_level ) THEN
3777 FND_LOG.STRING(g_level_procedure,
3778 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3779 'After assign to g_party_bill_ar_tbl '||g_party_bill_ar_tbl(l_tbl_index_party).BILLING_TP_NUMBER);
3780 END IF;
3781 -- l_tbl_index_site := dbms_utility.get_hash_value(to_char(l_bill_to_pty_site_id)||
3782 -- l_bill_ship, 1,8192);
3783
3784 l_tbl_index_bill_site := to_char(l_bill_to_pty_site_id);
3785
3786 IF (g_level_procedure >= g_current_runtime_level ) THEN
3787 FND_LOG.STRING(g_level_procedure,
3788 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO', 'Bill : l_tbl_index_bill_site : '
3789 ||to_char(l_tbl_index_bill_site));
3790 END IF;
3791
3792 IF g_site_bill_ar_tbl.EXISTS(l_tbl_index_bill_site) THEN
3793
3794 IF (g_level_procedure >= g_current_runtime_level ) THEN
3795 FND_LOG.STRING(g_level_procedure,
3796 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO', 'Bill : exist : ');
3797 END IF;
3798 GT_BILLING_TP_CITY(i) := g_site_bill_ar_tbl(l_tbl_index_bill_site).BILLING_TP_CITY;
3799 GT_BILLING_TP_COUNTY(i) := g_site_bill_ar_tbl(l_tbl_index_bill_site).BILLING_TP_COUNTY;
3800 GT_BILLING_TP_STATE(i) := g_site_bill_ar_tbl(l_tbl_index_bill_site).BILLING_TP_STATE;
3801 GT_BILLING_TP_PROVINCE(i) := g_site_bill_ar_tbl(l_tbl_index_bill_site).BILLING_TP_PROVINCE;
3802 GT_BILLING_TP_ADDRESS1(i) := g_site_bill_ar_tbl(l_tbl_index_bill_site).BILLING_TP_ADDRESS1;
3803 GT_BILLING_TP_ADDRESS2(i) := g_site_bill_ar_tbl(l_tbl_index_bill_site).BILLING_TP_ADDRESS2;
3804 GT_BILLING_TP_ADDRESS3(i) := g_site_bill_ar_tbl(l_tbl_index_bill_site).BILLING_TP_ADDRESS3;
3805 GT_BILLING_TP_ADDR_LINES_ALT(i) := g_site_bill_ar_tbl(l_tbl_index_bill_site).BILLING_TP_ADDR_LINES_ALT;
3806 GT_BILLING_TP_COUNTRY(i) := g_site_bill_ar_tbl(l_tbl_index_bill_site).BILLING_TP_COUNTRY;
3807 GT_BILLING_TP_POSTAL_CODE(i) := g_site_bill_ar_tbl(l_tbl_index_bill_site).BILLING_TP_POSTAL_CODE;
3808 ELSE
3809 IF (g_level_procedure >= g_current_runtime_level ) THEN
3810 FND_LOG.STRING(g_level_procedure,
3811 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO', 'Bill : not exist : '||to_char(l_bill_to_pty_site_id));
3812 END IF;
3813 OPEN party_site_cur (l_bill_to_pty_site_id);
3814 FETCH party_site_cur INTO GT_BILLING_TP_CITY(i),
3815 GT_BILLING_TP_COUNTY(i),
3816 GT_BILLING_TP_STATE(i),
3817 GT_BILLING_TP_PROVINCE(i),
3818 GT_BILLING_TP_ADDRESS1(i),
3819 GT_BILLING_TP_ADDRESS2(i),
3820 GT_BILLING_TP_ADDRESS3(i),
3821 GT_BILLING_TP_ADDR_LINES_ALT(i),
3822 GT_BILLING_TP_COUNTRY(i),
3823 GT_BILLING_TP_POSTAL_CODE(i);
3824
3825 g_site_bill_ar_tbl(l_tbl_index_bill_site).BILLING_TP_CITY := GT_BILLING_TP_CITY(i);
3826 g_site_bill_ar_tbl(l_tbl_index_bill_site).BILLING_TP_COUNTY := GT_BILLING_TP_COUNTY(i);
3827 g_site_bill_ar_tbl(l_tbl_index_bill_site).BILLING_TP_STATE := GT_BILLING_TP_STATE(i);
3828 g_site_bill_ar_tbl(l_tbl_index_bill_site).BILLING_TP_PROVINCE := GT_BILLING_TP_PROVINCE(i);
3829 g_site_bill_ar_tbl(l_tbl_index_bill_site).BILLING_TP_ADDRESS1 := GT_BILLING_TP_ADDRESS1(i);
3830 g_site_bill_ar_tbl(l_tbl_index_bill_site).BILLING_TP_ADDRESS2 := GT_BILLING_TP_ADDRESS2(i);
3831 g_site_bill_ar_tbl(l_tbl_index_bill_site).BILLING_TP_ADDRESS3 := GT_BILLING_TP_ADDRESS3(i);
3832 g_site_bill_ar_tbl(l_tbl_index_bill_site).BILLING_TP_ADDR_LINES_ALT := GT_BILLING_TP_ADDR_LINES_ALT(i);
3833 g_site_bill_ar_tbl(l_tbl_index_bill_site).BILLING_TP_COUNTRY := GT_BILLING_TP_COUNTRY(i);
3834 g_site_bill_ar_tbl(l_tbl_index_bill_site).BILLING_TP_POSTAL_CODE := GT_BILLING_TP_POSTAL_CODE(i);
3835 CLOSE party_site_cur;
3836 END IF;
3837
3838
3839
3840 IF (g_level_procedure >= g_current_runtime_level ) THEN
3841 FND_LOG.STRING(g_level_procedure,
3842 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3843 'After assign to g_site_bill_ar_tbl '||g_site_bill_ar_tbl(l_tbl_index_bill_site).BILLING_TP_CITY);
3844 END IF;
3845 END IF;
3846
3847 -- Ship to party information ----
3848
3849 l_ship_to_acct_site_id := GT_SHIPPING_TP_SITE_ID(i);
3850 l_ship_to_acct_id := GT_SHIPPING_TP_ID(i);
3851
3852 IF (g_level_procedure >= g_current_runtime_level ) THEN
3853 FND_LOG.STRING(g_level_procedure,
3854 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3855 'party_site_id_cur : l_ship_to_site_id '||to_char(l_ship_to_acct_site_id));
3856 END IF;
3857
3858 l_bill_ship := 'SHIP_TO';
3859
3860
3861 -- IF GT_SHIPPING_TP_ID(i) IS NOT NULL AND GT_SHIPPING_TP_ADDRESS_ID(i) IS NOT NULL THEN
3862
3863
3864 IF l_ship_to_acct_site_id is not null and l_ship_to_acct_id is not null THEN
3865 --l_tbl_index_cust := dbms_utility.get_hash_value(to_char(l_ship_to_acct_site_id)||(l_ship_to_acct_id)||
3866 -- l_bill_ship, 1,8192);
3867
3868 l_tbl_index_cust := to_char(l_ship_to_acct_site_id);
3869 --||(l_ship_to_acct_id);
3870
3871 IF g_cust_ship_ar_tbl.EXISTS(l_tbl_index_cust) THEN
3872 GT_SHIPPING_TP_NUMBER(i) := g_cust_ship_ar_tbl(l_tbl_index_cust).SHIPPING_TP_NUMBER ;
3873 GT_GDF_RA_CUST_SHIP_ATT10(i) := g_cust_ship_ar_tbl(l_tbl_index_cust).GDF_RA_CUST_SHIP_ATT10;
3874 GT_GDF_RA_CUST_SHIP_ATT12(i) := g_cust_ship_ar_tbl(l_tbl_index_cust).GDF_RA_CUST_SHIP_ATT12;
3875 GT_GDF_RA_ADDRESSES_SHIP_ATT8(i) :=g_cust_ship_ar_tbl(l_tbl_index_cust).GDF_RA_ADDRESSES_SHIP_ATT8;
3876 GT_GDF_RA_ADDRESSES_SHIP_ATT9(i) :=g_cust_ship_ar_tbl(l_tbl_index_cust).GDF_RA_ADDRESSES_SHIP_ATT9;
3877 GT_SHIPPING_TP_SITE_NAME(i) := g_cust_ship_ar_tbl(l_tbl_index_cust).SHIPPING_TP_SITE_NAME;
3878 GT_SHIP_TO_PARTY_ID(i) := g_cust_ship_ar_tbl(l_tbl_index_cust).SHIP_TO_PARTY_ID;
3879 GT_SHIP_TO_PARTY_SITE_ID(i) := g_cust_ship_ar_tbl(l_tbl_index_cust).SHIP_TO_PARTY_SITE_ID;
3880
3881 -- GT_SHIPPING_SITE_TAX_REG_NUM(i) := g_cust_ship_ar_tbl(l_tbl_index_cust).SHIPPING_SITE_TAX_REG_NUM;
3882
3883 IF (g_level_procedure >= g_current_runtime_level ) THEN
3884 FND_LOG.STRING(g_level_procedure,
3885 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3886 'Exists in the cache g_cust_ship_ar_tbl '||g_cust_ship_ar_tbl(l_tbl_index_cust).SHIPPING_TP_NUMBER);
3887 END IF;
3888
3889 ELSE
3890 OPEN cust_acct_cur (l_ship_to_acct_site_id,
3891 l_ship_to_acct_id,
3892 l_bill_ship);
3893 FETCH cust_acct_cur INTO GT_SHIPPING_TP_NUMBER(i),
3894 GT_GDF_RA_CUST_SHIP_ATT10(i),
3895 GT_GDF_RA_CUST_SHIP_ATT12(i),
3896 GT_GDF_RA_ADDRESSES_SHIP_ATT8(i),
3897 GT_GDF_RA_ADDRESSES_SHIP_ATT9(i),
3898 GT_SHIPPING_TP_SITE_NAME(i),
3899 GT_SHIP_TO_PARTY_ID(i),
3900 GT_SHIP_TO_PARTY_SITE_ID(i);
3901
3902 g_cust_ship_ar_tbl(l_tbl_index_cust).SHIPPING_TP_NUMBER := GT_SHIPPING_TP_NUMBER(i);
3903 g_cust_ship_ar_tbl(l_tbl_index_cust).GDF_RA_CUST_SHIP_ATT10 := GT_GDF_RA_CUST_SHIP_ATT10(i);
3904 g_cust_ship_ar_tbl(l_tbl_index_cust).GDF_RA_CUST_SHIP_ATT12 := GT_GDF_RA_CUST_SHIP_ATT12(i);
3905 g_cust_ship_ar_tbl(l_tbl_index_cust).GDF_RA_ADDRESSES_SHIP_ATT8 := GT_GDF_RA_ADDRESSES_SHIP_ATT8(i);
3906 g_cust_ship_ar_tbl(l_tbl_index_cust).GDF_RA_ADDRESSES_SHIP_ATT9 := GT_GDF_RA_ADDRESSES_SHIP_ATT9(i);
3907 g_cust_ship_ar_tbl(l_tbl_index_cust).SHIPPING_TP_SITE_NAME := GT_SHIPPING_TP_SITE_NAME(i);
3908 g_cust_ship_ar_tbl(l_tbl_index_cust).SHIP_TO_PARTY_ID := GT_SHIP_TO_PARTY_ID(i);
3909 g_cust_ship_ar_tbl(l_tbl_index_cust).SHIP_TO_PARTY_SITE_ID := GT_SHIP_TO_PARTY_SITE_ID(i);
3910
3911 CLOSE cust_acct_cur;
3912 END IF;
3913 IF (g_level_procedure >= g_current_runtime_level ) THEN
3914 FND_LOG.STRING(g_level_procedure,
3915 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
3916 'After assign to g_cust_ship_ar_tbl '||g_cust_ship_ar_tbl(l_tbl_index_cust).SHIPPING_TP_NUMBER);
3917 END IF;
3918
3919 l_ship_to_pty_site_id := GT_SHIP_TO_PARTY_SITE_ID(i);
3920 l_ship_to_party_id := GT_SHIP_TO_PARTY_ID(i);
3921
3922
3923 -- l_tbl_index_party := dbms_utility.get_hash_value(to_char(l_ship_to_party_id)||
3924 -- l_bill_ship, 1,8192);
3925 l_tbl_index_party := to_char(l_ship_to_party_id);
3926
3927 IF (g_level_procedure >= g_current_runtime_level ) THEN
3928 FND_LOG.STRING(g_level_procedure,
3929 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO', 'Party : l_tbl_index_party : '
3930 ||to_char(l_tbl_index_party));
3931 END IF;
3932
3933 IF g_party_ship_ar_tbl.EXISTS(l_tbl_index_party) THEN
3934 IF (g_level_procedure >= g_current_runtime_level ) THEN
3935 FND_LOG.STRING(g_level_procedure,
3936 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO', 'Party : exist : ');
3937 END IF;
3938
3939 GT_SHIPPING_TP_NAME_ALT(i) := g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_NAME_ALT;
3940 GT_SHIPPING_TP_NAME(i) := g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_NAME;
3941 GT_SHIPPING_TP_SIC_CODE(i) := g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_SIC_CODE;
3942 GT_SHIPPING_TP_NUMBER(i) := g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_NUMBER;
3943 GT_SHIPPING_TP_TAXPAYER_ID(i) := g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_TAXPAYER_ID;
3944 -- GT_SHIPPING_TP_TAX_REG_NUM(i) := g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_TAX_REG_NUM;
3945 ELSE
3946 IF (g_level_procedure >= g_current_runtime_level ) THEN
3947 FND_LOG.STRING(g_level_procedure,
3948 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO', 'Party : not exist : '||to_char(l_ship_to_party_id));
3949 END IF;
3950 OPEN party_cur (l_ship_to_party_id);
3951 FETCH party_cur INTO GT_SHIPPING_TP_NAME(i),
3952 GT_SHIPPING_TP_NAME_ALT(i),
3953 GT_SHIPPING_TP_SIC_CODE(i),
3954 GT_SHIPPING_TP_NUMBER(i),
3955 GT_SHIPPING_TP_TAXPAYER_ID(i);
3956
3957 g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_NAME_ALT := GT_SHIPPING_TP_NAME_ALT(i);
3958 g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_NAME := GT_SHIPPING_TP_NAME(i);
3959 g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_SIC_CODE := GT_SHIPPING_TP_SIC_CODE(i);
3960 g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_NUMBER := GT_SHIPPING_TP_NUMBER(i);
3961 g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_TAXPAYER_ID := GT_SHIPPING_TP_TAXPAYER_ID(i);
3962
3963 CLOSE party_cur;
3964 END IF;
3965
3966 OPEN party_reg_num_cur (l_ship_to_party_id);
3967 FETCH party_reg_num_cur into GT_SHIPPING_TP_TAX_REG_NUM(i);
3968 CLOSE party_reg_num_cur;
3969
3970 IF GT_SHIPPING_TP_TAX_REG_NUM(i) IS NULL THEN
3971 OPEN party_base_reg_num_cur (l_ship_to_party_id);
3972 FETCH party_base_reg_num_cur into GT_SHIPPING_TP_TAX_REG_NUM(i);
3973 CLOSE party_base_reg_num_cur;
3974 END IF;
3975
3976 OPEN party_site_reg_cur(l_ship_to_pty_site_id);
3977 FETCH party_site_reg_cur INTO GT_SHIPPING_SITE_TAX_REG_NUM(i);
3978 CLOSE party_site_reg_cur;
3979
3980 IF GT_SHIPPING_SITE_TAX_REG_NUM(i) IS NULL THEN
3981 OPEN party_site_base_reg_cur(l_ship_to_pty_site_id);
3982 FETCH party_site_base_reg_cur INTO GT_SHIPPING_SITE_TAX_REG_NUM(i);
3983 CLOSE party_site_base_reg_cur;
3984 END IF;
3985
3986 IF GT_SHIPPING_SITE_TAX_REG_NUM(i) IS NULL THEN -- Bug 7226438
3987 --Bug 5622686
3988 Begin
3989
3990 select decode(GT_APPLICATION_ID(i),222,'CUSTOMER',200,'SUPPLIER')
3991 into p_account_Type_Code
3992 from dual ;
3993
3994 p_parent_ptp_id := GT_SHIP_TO_PARTY_TAX_PROF_ID(i);
3995 p_site_ptp_id := GT_SHIP_TO_SITE_TAX_PROF_ID(i);
3996 p_tax_determine_date := gt_tax_determine_date(i);
3997 p_account_id:= GT_SHIPPING_TP_ID(i);
3998 p_account_site_id := GT_SHIPPING_TP_ADDRESS_ID(i);
3999 p_site_use_id := GT_SHIPPING_TP_SITE_ID(i);
4000
4001 IF (g_level_procedure >= g_current_runtime_level ) THEN
4002 FND_LOG.STRING(g_level_procedure,
4003 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
4004 'Before call to ZX_TCM_CONTROL_PKG.Get_Tax_Registration api to get the registration number : SHIP_TO');
4005 FND_LOG.STRING(g_level_procedure,
4006 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
4007 'trx_id : '||GT_TRX_ID(i));
4008 FND_LOG.STRING(g_level_procedure,
4009 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
4010 'p_parent_ptp_id : '||p_parent_ptp_id);
4011 FND_LOG.STRING(g_level_procedure,
4012 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
4013 'p_site_ptp_id : '|| p_site_ptp_id);
4014 FND_LOG.STRING(g_level_procedure,
4015 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
4016 'p_tax_determine_date : '|| p_tax_determine_date);
4017 FND_LOG.STRING(g_level_procedure,
4018 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
4019 'p_account_id : '|| p_account_id);
4020 FND_LOG.STRING(g_level_procedure,
4021 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
4022 'p_account_site_id : '|| p_account_site_id);
4023 FND_LOG.STRING(g_level_procedure,
4024 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
4025 'p_site_use_id : '|| p_site_use_id);
4026 FND_LOG.STRING(g_level_procedure,
4027 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
4028 'p_account_Type_Code : '|| p_account_Type_Code);
4029 END IF;
4030
4031 ZX_TCM_CONTROL_PKG.Get_Tax_Registration(
4032 p_parent_ptp_id
4033 , p_site_ptp_id
4034 , p_account_Type_Code
4035 , p_tax_determine_date
4036 , p_tax
4037 , p_tax_regime_code
4038 , p_jurisdiction_code
4039 , p_account_id
4040 , p_account_site_id
4041 , p_site_use_id
4042 , p_zx_registration_rec
4043 , p_ret_record_level
4044 , p_return_status );
4045
4046 IF ZX_GLOBAL_STRUCTURES_PKG.g_cust_site_use_info_tbl.exists(p_site_use_id) then
4047 GT_SHIPPING_SITE_TAX_REG_NUM(i) := ZX_GLOBAL_STRUCTURES_PKG.g_cust_site_use_info_tbl(p_site_use_id).tax_reference;
4048 ELSE
4049 GT_SHIPPING_SITE_TAX_REG_NUM(i) := null ;
4050 IF (g_level_procedure >= g_current_runtime_level ) THEN
4051 FND_LOG.STRING(g_level_procedure,'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
4052 'Could not fetch a value for registration number ');
4053 END IF;
4054 END IF;
4055
4056 IF (g_level_procedure >= g_current_runtime_level ) THEN
4057 FND_LOG.STRING(g_level_procedure,
4058 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
4059 'Stauts: '|| p_return_status);
4060 FND_LOG.STRING(g_level_procedure,
4061 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
4062 'Registration: '||p_zx_registration_rec.registration_number);
4063 FND_LOG.STRING(g_level_procedure,
4064 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
4065 'Registration from structure : '||GT_SHIPPING_SITE_TAX_REG_NUM(i));
4066 END IF ;
4067 EXCEPTION
4068 WHEN OTHERS THEN
4069 NULL ;
4070 END;
4071 END IF; -- GT_SHIPPING_SITE_TAX_REG_NUM IS NULL
4072
4073 IF (g_level_procedure >= g_current_runtime_level ) THEN
4074 FND_LOG.STRING(g_level_procedure,
4075 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
4076 'After assign to g_party_ship_ar_tbl '||g_party_ship_ar_tbl(l_tbl_index_party).SHIPPING_TP_NAME);
4077 END IF;
4078
4079 --l_tbl_index_site := dbms_utility.get_hash_value(to_char(l_ship_to_pty_site_id)||
4080 -- l_bill_ship, 1,8192);
4081 l_tbl_index_ship_site := to_char(l_ship_to_pty_site_id);
4082
4083 IF (g_level_procedure >= g_current_runtime_level ) THEN
4084 FND_LOG.STRING(g_level_procedure,
4085 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO', 'Ship : l_tbl_index_ship_site : party site id : acct_site_id : acct_id '
4086 ||l_tbl_index_ship_site ||'-'||to_char(l_ship_to_pty_site_id)||'-'||to_char(l_ship_to_acct_site_id)||'-'||to_char(l_ship_to_acct_id));
4087 END IF;
4088 IF g_site_ship_ar_tbl.EXISTS(l_tbl_index_ship_site) THEN
4089 IF (g_level_procedure >= g_current_runtime_level ) THEN
4090 FND_LOG.STRING(g_level_procedure,
4091 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO', 'Ship : exist : ');
4092 END IF;
4093 GT_SHIPPING_TP_CITY(i) := g_site_ship_ar_tbl(l_tbl_index_ship_site).SHIPPING_TP_CITY;
4094 GT_SHIPPING_TP_COUNTY(i) := g_site_ship_ar_tbl(l_tbl_index_ship_site).SHIPPING_TP_COUNTY;
4095 GT_SHIPPING_TP_STATE(i) := g_site_ship_ar_tbl(l_tbl_index_ship_site).SHIPPING_TP_STATE;
4096 GT_SHIPPING_TP_PROVINCE(i) := g_site_ship_ar_tbl(l_tbl_index_ship_site).SHIPPING_TP_PROVINCE;
4097 GT_SHIPPING_TP_ADDRESS1(i) := g_site_ship_ar_tbl(l_tbl_index_ship_site).SHIPPING_TP_ADDRESS1;
4098 GT_SHIPPING_TP_ADDRESS2(i) := g_site_ship_ar_tbl(l_tbl_index_ship_site).SHIPPING_TP_ADDRESS2;
4099 GT_SHIPPING_TP_ADDRESS3(i) := g_site_ship_ar_tbl(l_tbl_index_ship_site).SHIPPING_TP_ADDRESS3;
4100 GT_SHIPPING_TP_ADDR_LINES_ALT(i) := g_site_ship_ar_tbl(l_tbl_index_ship_site).SHIPPING_TP_ADDR_LINES_ALT;
4101 GT_SHIPPING_TP_COUNTRY(i) := g_site_ship_ar_tbl(l_tbl_index_ship_site).SHIPPING_TP_COUNTRY;
4102 GT_SHIPPING_TP_POSTAL_CODE(i) := g_site_ship_ar_tbl(l_tbl_index_ship_site).SHIPPING_TP_POSTAL_CODE;
4103 ELSE
4104 IF (g_level_procedure >= g_current_runtime_level ) THEN
4105 FND_LOG.STRING(g_level_procedure,
4106 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO', 'Ship : not exist in Cache : '||to_char(l_ship_to_pty_site_id));
4107 END IF;
4108 OPEN party_site_cur (l_ship_to_pty_site_id);
4109 FETCH party_site_cur INTO GT_SHIPPING_TP_CITY(i),
4110 GT_SHIPPING_TP_COUNTY(i),
4111 GT_SHIPPING_TP_STATE(i),
4112 GT_SHIPPING_TP_PROVINCE(i),
4113 GT_SHIPPING_TP_ADDRESS1(i),
4114 GT_SHIPPING_TP_ADDRESS2(i),
4115 GT_SHIPPING_TP_ADDRESS3(i),
4116 GT_SHIPPING_TP_ADDR_LINES_ALT(i),
4117 GT_SHIPPING_TP_COUNTRY(i),
4118 GT_SHIPPING_TP_POSTAL_CODE(i);
4119 g_site_ship_ar_tbl(l_tbl_index_ship_site).SHIPPING_TP_CITY := GT_SHIPPING_TP_CITY(i);
4120 g_site_ship_ar_tbl(l_tbl_index_ship_site).SHIPPING_TP_COUNTY := GT_SHIPPING_TP_COUNTY(i);
4121 g_site_ship_ar_tbl(l_tbl_index_ship_site).SHIPPING_TP_STATE := GT_SHIPPING_TP_STATE(i);
4122 g_site_ship_ar_tbl(l_tbl_index_ship_site).SHIPPING_TP_PROVINCE := GT_SHIPPING_TP_PROVINCE(i);
4123 g_site_ship_ar_tbl(l_tbl_index_ship_site).SHIPPING_TP_ADDRESS1 := GT_SHIPPING_TP_ADDRESS1(i);
4124 g_site_ship_ar_tbl(l_tbl_index_ship_site).SHIPPING_TP_ADDRESS2 := GT_SHIPPING_TP_ADDRESS2(i);
4125 g_site_ship_ar_tbl(l_tbl_index_ship_site).SHIPPING_TP_ADDRESS3 := GT_SHIPPING_TP_ADDRESS3(i);
4126 g_site_ship_ar_tbl(l_tbl_index_ship_site).SHIPPING_TP_ADDR_LINES_ALT := GT_SHIPPING_TP_ADDR_LINES_ALT(i);
4127 g_site_ship_ar_tbl(l_tbl_index_ship_site).SHIPPING_TP_COUNTRY := GT_SHIPPING_TP_COUNTRY(i);
4128 g_site_ship_ar_tbl(l_tbl_index_ship_site).SHIPPING_TP_POSTAL_CODE := GT_SHIPPING_TP_POSTAL_CODE(i);
4129 CLOSE party_site_cur;
4130 END IF;
4131 IF (g_level_procedure >= g_current_runtime_level ) THEN
4132 FND_LOG.STRING(g_level_procedure,
4133 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
4134 'After assign to g_site_ship_ar_tbl '||g_site_ship_ar_tbl(l_tbl_index_ship_site).SHIPPING_TP_CITY);
4135 END IF;
4136
4137 END IF;
4138
4139 IF GT_TRX_CLASS(i) = 'MISC_CASH_RECEIPT' THEN
4140 OPEN bank_tp_taxpayer_cur (GT_BANK_ACCOUNT_ID(i));
4141 FETCH bank_tp_taxpayer_cur INTO GT_BANKING_TP_TAXPAYER_ID(i);
4142 CLOSE bank_tp_taxpayer_cur;
4143 END IF;
4144
4145 IF GT_DOC_SEQ_ID(i) IS NOT NULL THEN
4146 OPEN doc_seq_name_cur (GT_DOC_SEQ_ID(i));
4147 FETCH doc_seq_name_cur INTO GT_DOC_SEQ_NAME(i);
4148 CLOSE doc_seq_name_cur;
4149 ELSE
4150 GT_DOC_SEQ_NAME(i) := NULL;
4151 END IF;
4152
4153 IF (g_level_procedure >= g_current_runtime_level ) THEN
4154 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO.END',
4155 'ZX_AR_POPULATE_PKG: EXTRACT_PARTY_INFO(-)');
4156 END IF;
4157
4158 EXCEPTION
4159 WHEN OTHERS THEN
4160 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
4161 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
4162 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
4163 FND_MSG_PUB.Add;
4164 IF (g_level_unexpected >= g_current_runtime_level ) THEN
4165 FND_LOG.STRING(g_level_unexpected,
4166 'ZX.TRL.ZX_AR_POPULATE_PKG.EXTRACT_PARTY_INFO',
4167 g_error_buffer);
4168 END IF;
4169
4170 G_RETCODE := 2;
4171
4172
4173 END EXTRACT_PARTY_INFO;
4174
4175 PROCEDURE populate_tax_reg_num(
4176 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE,
4177 P_ORG_ID IN zx_lines.internal_organization_id%TYPE ,
4178 P_TAX_DATE IN zx_lines.tax_date%TYPE,
4179 i BINARY_INTEGER) IS
4180
4181 CURSOR trn_ptp_id_cur (c_org_id zx_lines.internal_organization_id%TYPE,
4182 c_le_id NUMBER,
4183 c_tax_date zx_lines.tax_date%TYPE
4184 ) IS
4185 SELECT ptp.rep_registration_number
4186 FROM xle_tax_associations rel
4187 ,zx_party_tax_profile ptp
4188 ,xle_etb_profiles etb
4189 WHERE rel.legal_construct_id = etb.establishment_id
4190 AND etb.party_id = ptp.party_id
4191 AND ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
4192 AND rel.entity_id = c_org_id
4193 AND rel.legal_parent_id = c_le_id
4194 --P_TRL_GLOBAL_VARIABLES_REC.legal_entity_id
4195 AND rel.LEGAL_CONSTRUCT = 'ESTABLISHMENT'
4196 AND rel.entity_type = 'OPERATING_UNIT'
4197 AND rel.context = 'TAX_CALCULATION'
4198 AND c_tax_date between rel.effective_from and nvl(rel.effective_to,c_tax_date);
4199
4200 BEGIN
4201 IF (g_level_procedure >= g_current_runtime_level ) THEN
4202 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.populate_tax_reg_num.BEGIN',
4203 'populate_tax_reg_num(+)');
4204 END IF;
4205 OPEN trn_ptp_id_cur (p_org_id,
4206 P_TRL_GLOBAL_VARIABLES_REC.legal_entity_id,
4207 p_tax_date);
4208 FETCH trn_ptp_id_cur into GT_TAX_REG_NUM(i);
4209 CLOSE trn_ptp_id_cur;
4210
4211 IF (g_level_procedure >= g_current_runtime_level ) THEN
4212 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AP_POPULATE_PKG.populate_tax_reg_num.END',
4213 'populate_tax_reg_num(-)');
4214 END IF;
4215 END populate_tax_reg_num;
4216
4217 PROCEDURE populate_meaning(
4218 P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE,
4219 i BINARY_INTEGER)
4220 IS
4221 l_description VARCHAR2(240);
4222 l_meaning VARCHAR2(80);
4223 BEGIN
4224
4225 IF (g_level_unexpected >= g_current_runtime_level ) THEN
4226 FND_LOG.STRING(g_level_unexpected,
4227 'ZX.TRL.ZX_AR_POPULATE_PKG.populate_meaning.BEGIN',
4228 'populate_meaning(+) ');
4229 FND_LOG.STRING(g_level_unexpected, 'ZX.TRL.ZX_AR_POPULATE_PKG.populate_meaning.BEGIN',
4230 'Value of i : '||i);
4231 FND_LOG.STRING(g_level_unexpected, 'ZX.TRL.ZX_AR_POPULATE_PKG.populate_meaning.BEGIN',
4232 'GT_TRX_ID(i) : '||GT_TRX_ID(i));
4233 FND_LOG.STRING(g_level_unexpected,
4234 'ZX.TRL.ZX_AR_POPULATE_PKG.populate_meaning.BEGIN',
4235 'REGISTER_TYPE : '||GT_TAX_RATE_REG_TYPE_CODE(i));
4236 END IF;
4237
4238
4239 GT_TRX_CLASS_MNG(i) := NULL ;
4240 GT_TAX_RATE_CODE_REG_TYPE_MNG(i) := NULL ;
4241 GT_TAX_EXEMPT_REASON_MNG(i) := NULL ;
4242 GT_TAX_RATE_VAT_TRX_TYPE_DESC(i) := NULL ;
4243 GT_TAX_EXCEPTION_REASON_MNG(i) := NULL ;
4244 GT_TAX_TYPE_MNG(i) := NULL ;
4245
4246 IF GT_TRX_CLASS(i) IS NOT NULL THEN
4247 ZX_AP_POPULATE_PKG.lookup_desc_meaning('ZX_TRL_TAXABLE_TRX_TYPE',
4248 GT_TRX_CLASS(i),
4249 l_meaning,
4250 l_description);
4251 GT_TRX_CLASS_MNG(i) := l_meaning;
4252 END IF;
4253
4254 IF (g_level_unexpected >= g_current_runtime_level ) THEN
4255 FND_LOG.STRING(g_level_unexpected,
4256 'ZX.TRL.ZX_AR_POPULATE_PKG.populate_meaning.BEGIN',
4257 'GT_TRX_CLASS : '||GT_TRX_CLASS(i));
4258 FND_LOG.STRING(g_level_unexpected,
4259 'ZX.TRL.ZX_AR_POPULATE_PKG.populate_meaning.BEGIN',
4260 'GT_TRX_CLASS_MNG(i) : '||GT_TRX_CLASS_MNG(i));
4261 END IF;
4262
4263 IF P_TRL_GLOBAL_VARIABLES_REC.REGISTER_TYPE IS NOT NULL THEN
4264 ZX_AP_POPULATE_PKG.lookup_desc_meaning('ZX_TRL_REGISTER_TYPE',
4265 -- P_TRL_GLOBAL_VARIABLES_REC.REGISTER_TYPE,
4266 GT_TAX_RATE_REG_TYPE_CODE(i),
4267 l_meaning,
4268 l_description);
4269
4270 GT_TAX_RATE_CODE_REG_TYPE_MNG(i) := l_meaning;
4271 END IF;
4272
4273 IF GT_TAX_RATE_VAT_TRX_TYPE_CODE(i) IS NOT NULL THEN
4274 ZX_AP_POPULATE_PKG.lookup_desc_meaning('ZX_JEBE_VAT_TRANS_TYPE',
4275 GT_TAX_RATE_VAT_TRX_TYPE_CODE(i),
4276 l_meaning,
4277 l_description);
4278 GT_TAX_RATE_VAT_TRX_TYPE_DESC(i) := l_description;
4279 END IF;
4280
4281 IF (g_level_unexpected >= g_current_runtime_level ) THEN
4282 FND_LOG.STRING(g_level_unexpected,
4283 'ZX.TRL.ZX_AR_POPULATE_PKG.populate_meaning.BEGIN',
4284 'GT_TAX_RATE_CODE_REG_TYPE_MNG(i) : '||GT_TAX_RATE_CODE_REG_TYPE_MNG(i));
4285 FND_LOG.STRING(g_level_unexpected,
4286 'ZX.TRL.ZX_AR_POPULATE_PKG.populate_meaning.BEGIN',
4287 'GT_TAX_RATE_VAT_TRX_TYPE_CODE(i) : '||GT_TAX_RATE_VAT_TRX_TYPE_CODE(i));
4288 FND_LOG.STRING(g_level_unexpected,
4289 'ZX.TRL.ZX_AR_POPULATE_PKG.populate_meaning.BEGIN',
4290 'GT_TAX_RATE_VAT_TRX_TYPE_DESC(i) : '||GT_TAX_RATE_VAT_TRX_TYPE_DESC(i));
4291 END IF;
4292
4293 IF GT_TAX_EXCEPTION_REASON_CODE(i) IS NOT NULL THEN
4294 ZX_AP_POPULATE_PKG.lookup_desc_meaning('ZX_EXCEPTION_REASON',
4295 GT_TAX_EXCEPTION_REASON_CODE(i),
4296 l_meaning,
4297 l_description);
4298
4299 GT_TAX_EXCEPTION_REASON_MNG(i) := l_meaning;
4300 END IF;
4301
4302
4303 IF (g_level_unexpected >= g_current_runtime_level ) THEN
4304 FND_LOG.STRING(g_level_unexpected,
4305 'ZX.TRL.ZX_AR_POPULATE_PKG.populate_meaning.BEGIN',
4306 'GT_TAX_EXCEPTION_REASON_CODE(i) : '||GT_TAX_EXCEPTION_REASON_CODE(i));
4307 FND_LOG.STRING(g_level_unexpected,
4308 'ZX.TRL.ZX_AR_POPULATE_PKG.populate_meaning.BEGIN',
4309 'GT_TAX_EXCEPTION_REASON_MNG(i) : '||GT_TAX_EXCEPTION_REASON_MNG(i));
4310 END IF;
4311
4312 IF GT_EXEMPT_REASON_CODE(i) IS NOT NULL THEN
4313 ZX_AP_POPULATE_PKG.lookup_desc_meaning('ZX_EXEMPTION_REASON',
4314 GT_EXEMPT_REASON_CODE(i),
4315 l_meaning,
4316 l_description);
4317 GT_TAX_EXEMPT_REASON_MNG(i) := l_meaning;
4318 END IF;
4319
4320 IF (g_level_unexpected >= g_current_runtime_level ) THEN
4321 FND_LOG.STRING(g_level_unexpected,
4322 'ZX.TRL.ZX_AR_POPULATE_PKG.populate_meaning.BEGIN',
4323 'GT_EXEMPT_REASON_CODE : '||GT_EXEMPT_REASON_CODE(i));
4324 FND_LOG.STRING(g_level_unexpected,
4325 'ZX.TRL.ZX_AR_POPULATE_PKG.populate_meaning.BEGIN',
4326 'GT_TAX_EXEMPT_REASON_MNG(i) : '||GT_TAX_EXEMPT_REASON_MNG(i));
4327 END IF;
4328
4329 --Bug 5671767 :Code added to populate tax_type_mng
4330 IF GT_TAX_TYPE_CODE(i) IS NOT NULL THEN
4331 BEGIN
4332 SELECT meaning , description
4333 INTO l_meaning, l_description
4334 FROM ar_lookups
4335 WHERE lookup_code = GT_TAX_TYPE_CODE(i)
4336 AND lookup_type = 'TAX_TYPE' ;
4337
4338 EXCEPTION
4339 WHEN NO_DATA_FOUND THEN
4340 ZX_AP_POPULATE_PKG.lookup_desc_meaning('ZX_TAX_TYPE_CATEGORY',
4341 GT_TAX_TYPE_CODE(i),
4342 l_meaning,
4343 l_description);
4344 END ;
4345 GT_TAX_TYPE_MNG(i) := l_meaning;
4346 END IF;
4347
4348 IF (g_level_unexpected >= g_current_runtime_level ) THEN
4349 FND_LOG.STRING(g_level_unexpected,
4350 'ZX.TRL.ZX_AR_POPULATE_PKG.populate_meaning.BEGIN',
4351 'GT_TAX_TYPE_CODE(i) : '||GT_TAX_TYPE_CODE(i));
4352 FND_LOG.STRING(g_level_unexpected,
4353 'ZX.TRL.ZX_AR_POPULATE_PKG.populate_meaning.BEGIN',
4354 'GT_TAX_TYPE_MNG(i) : '||GT_TAX_TYPE_MNG(i));
4355 END IF;
4356
4357 IF (g_level_unexpected >= g_current_runtime_level ) THEN
4358 FND_LOG.STRING(g_level_unexpected,
4359 'ZX.TRL.ZX_AR_POPULATE_PKG.populate_meaning.END',
4360 'populate_meaning(-) ');
4361 END IF;
4362
4363 END populate_meaning;
4364
4365
4366
4367
4368
4369 PROCEDURE UPDATE_REP_DETAIL_T(p_count IN NUMBER) IS
4370 i number;
4371 BEGIN
4372
4373 IF (g_level_procedure >= g_current_runtime_level ) THEN
4374 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.UPDATE_REP_DETAIL_T.BEGIN',
4375 'ZX_AR_POPULATE_PKG: UPDATE_REP_DETAIL_T(+)');
4376 END IF;
4377
4378 FORALL i in 1..p_count
4379 UPDATE ZX_REP_TRX_DETAIL_T SET
4380 REP_CONTEXT_ID = G_REP_CONTEXT_ID,
4381 BILLING_TP_NUMBER = GT_BILLING_TP_NUMBER(i),
4382 BILLING_TP_TAX_REG_NUM = GT_BILLING_TP_TAX_REG_NUM(i),
4383 BILLING_TP_SITE_TAX_REG_NUM = GT_BILLING_SITE_TAX_REG_NUM(i),
4384 BILLING_TP_TAXPAYER_ID = GT_BILLING_TP_TAXPAYER_ID(i),
4385 BILLING_TP_SITE_NAME_ALT = GT_BILLING_TP_SITE_NAME_ALT(i),
4386 BILLING_TP_NAME = GT_BILLING_TP_NAME(i),
4387 BILLING_TP_NAME_ALT = GT_BILLING_TP_NAME_ALT(i),
4388 BILLING_TP_SIC_CODE = GT_BILLING_TP_SIC_CODE(i),
4389 HQ_ESTB_REG_NUMBER = GT_TAX_REG_NUM(i),
4390 BILLING_TP_CITY = GT_BILLING_TP_CITY(i),
4391 BILLING_TP_COUNTY = GT_BILLING_TP_COUNTY(i),
4392 BILLING_TP_STATE = GT_BILLING_TP_STATE(i),
4393 BILLING_TP_PROVINCE = GT_BILLING_TP_PROVINCE(i),
4394 BILLING_TP_ADDRESS1 = GT_BILLING_TP_ADDRESS1(i),
4395 BILLING_TP_ADDRESS2 = GT_BILLING_TP_ADDRESS2(i),
4396 BILLING_TP_ADDRESS3 = GT_BILLING_TP_ADDRESS3(i),
4397 BILLING_TP_ADDRESS_LINES_ALT = GT_BILLING_TP_ADDR_LINES_ALT(i),
4398 BILLING_TP_COUNTRY = GT_BILLING_TP_COUNTRY(i),
4399 BILLING_TP_POSTAL_CODE = GT_BILLING_TP_POSTAL_CODE(i),
4400 BILLING_TP_PARTY_NUMBER = GT_BILLING_TP_PARTY_NUMBER(i),
4401 BILLING_TRADING_PARTNER_ID = GT_BILLING_TP_ID(i),
4402 BILLING_TP_SITE_ID = GT_BILLING_TP_SITE_ID(i),
4403 BILLING_TP_ADDRESS_ID = GT_BILLING_TP_ADDRESS_ID(i),
4404 -- BILLING_TP_TAX_REP_FLAG = GT_BILLING_TP_TAX_REP_FLAG(i),
4405 BILLING_TP_SITE_NAME = GT_BILLING_TP_SITE_NAME(i),
4406 GDF_RA_ADDRESSES_BILL_ATT9 = GT_GDF_RA_ADDRESSES_BILL_ATT9(i),
4407 GDF_PARTY_SITES_BILL_ATT8 = GT_GDF_PARTY_SITES_BILL_ATT8(i),
4408 GDF_RA_CUST_BILL_ATT10 = GT_GDF_RA_CUST_BILL_ATT10(i),
4409 GDF_RA_CUST_BILL_ATT12 = GT_GDF_RA_CUST_BILL_ATT12(i),
4410 GDF_RA_ADDRESSES_BILL_ATT8 = GT_GDF_RA_ADDRESSES_BILL_ATT8(i),
4411 SHIPPING_TP_NUMBER = GT_SHIPPING_TP_NUMBER(i),
4412 DOC_SEQ_NAME = GT_DOC_SEQ_NAME(i),
4413 SHIPPING_TP_TAX_REG_NUM = GT_SHIPPING_TP_TAX_REG_NUM(i),
4414 SHIPPING_TP_SITE_TAX_REG_NUM = GT_SHIPPING_SITE_TAX_REG_NUM(i),
4415 SHIPPING_TP_TAXPAYER_ID = GT_SHIPPING_TP_TAXPAYER_ID(i),
4416 -- SHIPPING_TP_SITE_NAME_ALT = GT_SHIPPING_TP_SITE_NAME_ALT(i),
4417 SHIPPING_TP_NAME = GT_SHIPPING_TP_NAME(i),
4418 SHIPPING_TP_NAME_ALT = GT_SHIPPING_TP_NAME_ALT(i),
4419 SHIPPING_TP_SIC_CODE = GT_SHIPPING_TP_SIC_CODE(i),
4420 SHIPPING_TP_CITY = GT_SHIPPING_TP_CITY(i),
4421 SHIPPING_TP_COUNTY = GT_SHIPPING_TP_COUNTY(i),
4422 SHIPPING_TP_STATE = GT_SHIPPING_TP_STATE(i),
4423 SHIPPING_TP_PROVINCE = GT_SHIPPING_TP_PROVINCE(i),
4424 SHIPPING_TP_ADDRESS1 = GT_SHIPPING_TP_ADDRESS1(i),
4425 SHIPPING_TP_ADDRESS2 = GT_SHIPPING_TP_ADDRESS2(i),
4426 SHIPPING_TP_ADDRESS3 = GT_SHIPPING_TP_ADDRESS3(i),
4427 -- SHIPPING_TP_ADDR_LINES_ALT = GT_SHIPPING_TP_ADDR_LINES_ALT(i),
4428 SHIPPING_TP_COUNTRY = GT_SHIPPING_TP_COUNTRY(i),
4429 SHIPPING_TP_POSTAL_CODE = GT_SHIPPING_TP_POSTAL_CODE(i),
4430 -- SHIPPING_TP_PARTY_NUMBER = GT_SHIPPING_TP_PARTY_NUMBER(i),
4431 -- SHIPPING_TRADING_PARTNER_ID = GT_SHIPPING_TRADING_PARTNER_ID(i),
4432 SHIPPING_TP_SITE_ID = GT_SHIPPING_TP_SITE_ID(i),
4433 SHIPPING_TP_ADDRESS_ID = GT_SHIPPING_TP_ADDRESS_ID(i),
4434 -- SHIPPING_TP_TAX_REP_FLAG = GT_SHIPPING_TP_TAX_REP_FLAG(i),
4435 SHIPPING_TP_SITE_NAME = GT_SHIPPING_TP_SITE_NAME(i),
4436 GDF_RA_ADDRESSES_SHIP_ATT9 = GT_GDF_RA_ADDRESSES_SHIP_ATT9(i),
4437 GDF_PARTY_SITES_SHIP_ATT8 = GT_GDF_PARTY_SITES_SHIP_ATT8(i),
4438 GDF_RA_CUST_SHIP_ATT10 = GT_GDF_RA_CUST_SHIP_ATT10(i),
4439 GDF_RA_CUST_SHIP_ATT12 = GT_GDF_RA_CUST_SHIP_ATT12(i),
4440 GDF_RA_ADDRESSES_SHIP_ATT8 = GT_GDF_RA_ADDRESSES_SHIP_ATT8(i),
4441 TRX_CLASS_MNG = GT_TRX_CLASS_MNG(i),
4442 TAX_RATE_CODE_REG_TYPE_MNG = GT_TAX_RATE_CODE_REG_TYPE_MNG(i),
4443 TAX_RATE_VAT_TRX_TYPE_DESC = GT_TAX_RATE_VAT_TRX_TYPE_DESC(i),
4444 FUNCTIONAL_CURRENCY_CODE = G_FUN_CURRENCY_CODE,
4445 LEDGER_NAME = GT_LEDGER_NAME(i),
4446 BANKING_TP_TAXPAYER_ID = GT_BANKING_TP_TAXPAYER_ID(i),
4447 TAX_AMT = GT_TAX_AMT(i),
4448 TAX_AMT_FUNCL_CURR = GT_TAX_AMT_FUNCL_CURR(i),
4449 TAXABLE_AMT = GT_TAXABLE_AMT(i),
4450 TAXABLE_AMT_FUNCL_CURR = GT_TAXABLE_AMT_FUNCL_CURR(i),
4451 TAX_TYPE_MNG = GT_TAX_TYPE_MNG(i)
4452 WHERE DETAIL_TAX_LINE_ID = GT_DETAIL_TAX_LINE_ID(i);
4453
4454 IF (g_level_procedure >= g_current_runtime_level ) THEN
4455 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.UPDATE_REP_DETAIL_T.END',
4456 'ZX_AR_POPULATE_PKG: UPDATE_REP_DETAIL_T(-)');
4457 END IF;
4458
4459
4460 EXCEPTION
4461 WHEN OTHERS THEN
4462 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
4463 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
4464 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
4465 FND_MSG_PUB.Add;
4466 IF (g_level_unexpected >= g_current_runtime_level ) THEN
4467 FND_LOG.STRING(g_level_unexpected,
4468 'ZX.TRL.ZX_AR_POPULATE_PKG.UPDATE_REP_DETAIL_T',
4469 g_error_buffer);
4470 END IF;
4471
4472 G_RETCODE := 2;
4473
4474 END UPDATE_REP_DETAIL_T;
4475
4476
4477
4478 /*===========================================================================+
4479 | PROCEDURE |
4480 | insert_actg_info |
4481 | DESCRIPTION |
4482 | This procedure inserts payables tax data into ZX_REP_TRX_DETAIL_T table|
4483 | |
4484 | SCOPE - Private |
4485 | |
4486 | NOTES |
4487 | |
4488 | MODIFICATION HISTORY |
4489 | 11-Jan-2005 Srinivasa Rao Korrapati Created |
4490 | |
4491 +===========================================================================*/
4492
4493
4494 PROCEDURE insert_actg_info(
4495 P_COUNT IN BINARY_INTEGER)
4496 IS
4497 l_count NUMBER;
4498
4499 BEGIN
4500
4501 IF (g_level_procedure >= g_current_runtime_level ) THEN
4502 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_EXTRACT_PKG.insert_actg_info.BEGIN',
4503 'ZX_AR_ACTG_EXTRACT_PKG: insert_actg_info(+)');
4504 END IF;
4505
4506 l_count := P_COUNT;
4507
4508 IF (g_level_procedure >= g_current_runtime_level ) THEN
4509 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_EXTRACT_PKG.insert_actg_info',
4510 ' Record Count = ' ||to_char(P_COUNT));
4511 END IF;
4512
4513
4514 FORALL i IN 1 .. l_count
4515 INSERT INTO ZX_REP_ACTG_EXT_T(
4516 actg_ext_line_id,
4517 detail_tax_line_id,
4518 actg_event_type_code,
4519 actg_event_number,
4520 actg_event_status_flag,
4521 actg_category_code,
4522 accounting_date,
4523 gl_transfer_flag,
4524 -- gl_transfer_run_id,
4525 actg_header_description,
4526 actg_line_num,
4527 actg_line_type_code,
4528 actg_line_description,
4529 actg_stat_amt,
4530 actg_error_code,
4531 gl_transfer_code,
4532 actg_doc_sequence_id,
4533 --actg_doc_sequence_name,
4534 actg_doc_sequence_value,
4535 actg_party_id,
4536 actg_party_site_id,
4537 actg_party_type,
4538 actg_event_id,
4539 actg_header_id,
4540 actg_source_id,
4541 --actg_source_table,
4542 actg_line_ccid,
4543 period_name,
4544 TRX_ARAP_BALANCING_SEGMENT,
4545 TRX_ARAP_NATURAL_ACCOUNT,
4546 TRX_TAXABLE_BALANCING_SEGMENT,
4547 TRX_TAXABLE_BALSEG_DESC,
4548 TRX_TAXABLE_NATURAL_ACCOUNT,
4549 TRX_TAX_BALANCING_SEGMENT,
4550 TRX_TAX_NATURAL_ACCOUNT,
4551 ACCOUNT_FLEXFIELD,
4552 ACCOUNT_DESCRIPTION,
4553 created_by,
4554 creation_date,
4555 last_updated_by,
4556 last_update_date,
4557 last_update_login,
4558 program_application_id,
4559 program_id,
4560 program_login_id,
4561 request_id,
4562 TRX_CONTROL_ACCOUNT_FLEXFIELD) --Bug 5510907
4563 VALUES (zx_rep_actg_ext_t_s.nextval,
4564 agt_detail_tax_line_id(i),
4565 agt_actg_event_type_code(i),
4566 agt_actg_event_number(i),
4567 agt_actg_event_status_flag(i),
4568 agt_actg_category_code(i),
4569 agt_accounting_date(i),
4570 agt_gl_transfer_flag(i),
4571 -- agt_gl_transfer_run_id(i),
4572 agt_actg_header_description(i),
4573 agt_actg_line_num(i),
4574 agt_actg_line_type_code(i),
4575 agt_actg_line_description(i),
4576 agt_actg_stat_amt(i),
4577 agt_actg_error_code(i),
4578 agt_gl_transfer_code(i),
4579 agt_actg_doc_sequence_id(i),
4580 -- agt_actg_doc_sequence_name(i),
4581 agt_actg_doc_sequence_value(i),
4582 agt_actg_party_id(i),
4583 agt_actg_party_site_id(i),
4584 agt_actg_party_type(i),
4585 agt_actg_event_id(i),
4586 agt_actg_header_id(i),
4587 agt_actg_source_id(i),
4588 -- agt_actg_source_table(i),
4589 agt_actg_line_ccid(i),
4590 agt_period_name(i),
4591 GT_TRX_ARAP_BALANCING_SEGMENT(i),
4592 GT_TRX_ARAP_NATURAL_ACCOUNT(i),
4593 GT_TRX_TAXABLE_BAL_SEG(i),
4594 GT_TRX_TAXABLE_BALSEG_DESC(i),
4595 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i),
4596 GT_TRX_TAX_BALANCING_SEGMENT(i),
4597 GT_TRX_TAX_NATURAL_ACCOUNT(i),
4598 GT_ACCOUNT_FLEXFIELD(i),
4599 GT_ACCOUNT_DESCRIPTION(i),
4600 g_created_by,
4601 g_creation_date,
4602 g_last_updated_by,
4603 g_last_update_date,
4604 g_last_update_login,
4605 g_program_application_id,
4606 g_program_id,
4607 g_program_login_id,
4608 g_request_id,
4609 GT_TRX_CONTROL_ACCFLEXFIELD(i)); --Bug 5510907
4610
4611 IF (g_level_procedure >= g_current_runtime_level ) THEN
4612 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_EXTRACT_PKG.insert_actg_info',
4613 'Number of Tax Lines successfully inserted = '||TO_CHAR(l_count));
4614
4615 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_EXTRACT_PKG.insert_actg_info.END',
4616 'ZX_AR_ACTG_EXTRACT_PKG: INIT_GT_VARIABLES(-)');
4617 END IF;
4618
4619 EXCEPTION
4620 WHEN OTHERS THEN
4621 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
4622 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
4623 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
4624 FND_MSG_PUB.Add;
4625 IF (g_level_unexpected >= g_current_runtime_level ) THEN
4626 FND_LOG.STRING(g_level_unexpected,
4627 'ZX.TRL.ZX_AR_ACTG_EXTRACT_PKG.insert_actg_info',
4628 g_error_buffer);
4629 END IF;
4630
4631 g_retcode := 2;
4632
4633 END insert_actg_info;
4634
4635 PROCEDURE initialize_variables (
4636 p_count IN NUMBER) IS
4637 i number;
4638
4639 BEGIN
4640
4641 IF (g_level_procedure >= g_current_runtime_level ) THEN
4642 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.initialize_variables.BEGIN',
4643 'ZX_AR_POPULATE_PKG: initialize_variables(+)');
4644 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.initialize_variables',
4645 'p_count : '||to_char(p_count));
4646 END IF;
4647
4648 FOR i IN 1.. p_count LOOP
4649 GT_BILLING_TP_NUMBER(i) := NULL;
4650 GT_BILLING_TP_TAX_REG_NUM(i) := NULL;
4651 GT_BILLING_TP_TAXPAYER_ID(i) := NULL;
4652 GT_BILLING_TP_SITE_NAME_ALT(i) := NULL;
4653 GT_BILLING_TP_NAME(i) := NULL;
4654 GT_BILLING_TP_NAME_ALT(i) := NULL;
4655 GT_BILLING_TP_SIC_CODE(i) := NULL;
4656 GT_TAX_REG_NUM(i) := NULL;
4657 GT_BILLING_TP_CITY(i) := NULL;
4658 GT_BILLING_TP_COUNTY(i) := NULL;
4659 GT_BILLING_TP_STATE(i) := NULL;
4660 GT_BILLING_TP_PROVINCE(i) := NULL;
4661 GT_BILLING_TP_ADDRESS1(i) := NULL;
4662 GT_BILLING_TP_ADDRESS2(i) := NULL;
4663 GT_BILLING_TP_ADDRESS3(i) := NULL;
4664 GT_BILLING_TP_ADDR_LINES_ALT(i) := NULL;
4665 GT_BILLING_TP_COUNTRY(i) := NULL;
4666 GT_BILLING_TP_POSTAL_CODE(i) := NULL;
4667 GT_BILLING_TP_PARTY_NUMBER(i) := NULL;
4668 -- GT_BILLING_TP_ID(i) := NULL;
4669 -- GT_BILLING_TP_SITE_ID(i) := NULL;
4670 -- GT_BILLING_TP_ADDRESS_ID(i) := NULL;
4671 -- GT_BILLING_TP_TAX_REP_FLAG(i) := NULL;
4672 GT_BILLING_TP_SITE_NAME(i) := NULL;
4673 GT_GDF_RA_ADDRESSES_BILL_ATT9(i) := NULL;
4674 GT_GDF_PARTY_SITES_BILL_ATT8(i) := NULL;
4675 GT_GDF_RA_CUST_BILL_ATT10(i) := NULL;
4676 GT_GDF_RA_CUST_BILL_ATT12(i) := NULL;
4677 GT_GDF_RA_ADDRESSES_BILL_ATT8(i) := NULL;
4678 GT_SHIPPING_TP_NUMBER(i) := NULL;
4679 GT_DOC_SEQ_NAME(i) := NULL;
4680 GT_SHIPPING_TP_TAX_REG_NUM(i) := NULL;
4681 GT_SHIPPING_TP_TAXPAYER_ID(i) := NULL;
4682 -- GT_SHIPPING_TP_SITE_NAME_ALT(i) := NULL;
4683 GT_SHIPPING_TP_NAME(i) := NULL;
4684 GT_SHIPPING_TP_NAME_ALT(i) := NULL;
4685 GT_SHIPPING_TP_SIC_CODE(i) := NULL;
4686 GT_SHIPPING_TP_CITY(i) := NULL;
4687 GT_SHIPPING_TP_COUNTY(i) := NULL;
4688 GT_SHIPPING_TP_STATE(i) := NULL;
4689 GT_SHIPPING_TP_PROVINCE(i) := NULL;
4690 GT_SHIPPING_TP_ADDRESS1(i) := NULL;
4691 GT_SHIPPING_TP_ADDRESS2(i) := NULL;
4692 GT_SHIPPING_TP_ADDRESS3(i) := NULL;
4693 -- GT_SHIPPING_TP_ADDR_LINES_ALT(i) := NULL;
4694 GT_SHIPPING_TP_COUNTRY(i) := NULL;
4695 GT_SHIPPING_TP_POSTAL_CODE(i) := NULL;
4696 -- GT_SHIPPING_TP_PARTY_NUMBER(i) := NULL;
4697 -- GT_SHIPPING_TRADING_PARTNER_ID(i):= NULL;
4698 -- GT_SHIPPING_TP_SITE_ID(i) := NULL;
4699 -- GT_SHIPPING_TP_ADDRESS_ID(i) := NULL;
4700 -- GT_SHIPPING_TP_TAX_REP_FLAG(i) := NULL;
4701 GT_SHIPPING_TP_SITE_NAME(i) := NULL;
4702 GT_GDF_RA_ADDRESSES_SHIP_ATT9(i) := NULL;
4703 GT_GDF_PARTY_SITES_SHIP_ATT8(i) := NULL;
4704 GT_GDF_RA_CUST_SHIP_ATT10(i) := NULL;
4705 GT_GDF_RA_CUST_SHIP_ATT12(i) := NULL;
4706 GT_GDF_RA_ADDRESSES_SHIP_ATT8(i) := NULL;
4707 GT_TRX_CLASS_MNG(i) := NULL;
4708 GT_LEDGER_NAME(i) := NULL;
4709 GT_BANKING_TP_TAXPAYER_ID(i) := NULL;
4710 GT_TAX_RATE_CODE_REG_TYPE_MNG(i) := NULL;
4711 GT_TAX_RATE_VAT_TRX_TYPE_DESC(i) := NULL;
4712 -- New --
4713 GT_BILLING_TP_NUMBER(i) := NULL;
4714 GT_GDF_RA_CUST_BILL_ATT10(i) := NULL;
4715 GT_GDF_RA_CUST_BILL_ATT12(i) := NULL;
4716 GT_GDF_RA_ADDRESSES_BILL_ATT8(i) := NULL;
4717 GT_GDF_RA_ADDRESSES_BILL_ATT9(i) := NULL;
4718 GT_BILLING_TP_SITE_NAME(i) := NULL;
4719 GT_BILLING_TP_TAX_REG_NUM(i) := NULL;
4720 GT_BILLING_SITE_TAX_REG_NUM(i) := NULL;
4721 GT_BILLING_TP_NAME(i) := NULL;
4722 GT_BILLING_TP_NAME_ALT(i) := NULL;
4723 GT_BILLING_TP_SIC_CODE(i) := NULL;
4724 GT_BILLING_TP_NUMBER(i) := NULL;
4725 GT_BILLING_TP_CITY(i) := NULL;
4726 GT_BILLING_TP_COUNTY(i) := NULL;
4727 GT_BILLING_TP_STATE(i) := NULL;
4728 GT_BILLING_TP_PROVINCE(i) := NULL;
4729 GT_BILLING_TP_ADDRESS1(i) := NULL;
4730 GT_BILLING_TP_ADDRESS2(i) := NULL;
4731 GT_BILLING_TP_ADDRESS3(i) := NULL;
4732 GT_BILLING_TP_ADDR_LINES_ALT(i) := NULL;
4733 GT_BILLING_TP_COUNTRY(i) := NULL;
4734 GT_BILLING_TP_POSTAL_CODE(i) := NULL;
4735 GT_SHIPPING_TP_NUMBER(i) := NULL;
4736 GT_GDF_RA_CUST_SHIP_ATT10(i) := NULL;
4737 GT_GDF_RA_CUST_SHIP_ATT12(i) := NULL;
4738 GT_GDF_RA_ADDRESSES_SHIP_ATT8(i) := NULL;
4739 GT_GDF_RA_ADDRESSES_SHIP_ATT9(i) := NULL;
4740 GT_SHIPPING_TP_SITE_NAME(i) := NULL;
4741 GT_SHIPPING_TP_TAX_REG_NUM(i) := NULL;
4742 GT_SHIPPING_SITE_TAX_REG_NUM(i) := NULL;
4743 GT_SHIPPING_TP_NAME(i) := NULL;
4744 GT_SHIPPING_TP_NAME_ALT(i) := NULL;
4745 GT_SHIPPING_TP_SIC_CODE(i) := NULL;
4746 GT_SHIPPING_TP_NUMBER(i) := NULL;
4747 GT_SHIPPING_TP_CITY(i) := NULL;
4748 GT_SHIPPING_TP_COUNTY(i) := NULL;
4749 GT_SHIPPING_TP_STATE(i) := NULL;
4750 GT_SHIPPING_TP_PROVINCE(i) := NULL;
4751 GT_SHIPPING_TP_ADDRESS1(i) := NULL;
4752 GT_SHIPPING_TP_ADDRESS2(i) := NULL;
4753 GT_SHIPPING_TP_ADDRESS3(i) := NULL;
4754 GT_SHIPPING_TP_ADDR_LINES_ALT(i) := NULL;
4755 GT_SHIPPING_TP_COUNTRY(i) := NULL;
4756 GT_SHIPPING_TP_POSTAL_CODE(i) := NULL;
4757 gt_actg_ext_line_id(i) := NULL;
4758 -- gt_detail_tax_line_id(i) := NULL;
4759 /* gt_actg_event_type_code(i) := NULL;
4760 gt_actg_event_number(i) := NULL;
4761 gt_actg_event_status_flag(i) := NULL;
4762 gt_actg_category_code(i) := NULL;
4763 gt_accounting_date(i) := NULL;
4764 gt_gl_transfer_flag(i) := NULL;
4765 gt_gl_transfer_run_id(i) := NULL;
4766 gt_actg_header_description(i) := NULL;
4767 gt_actg_line_num(i) := NULL;
4768 gt_actg_line_type_code(i) := NULL;
4769 gt_actg_line_description(i) := NULL;
4770 gt_actg_stat_amt(i) := NULL;
4771 gt_actg_error_code(i) := NULL;
4772 gt_gl_transfer_code(i) := NULL;
4773 gt_actg_doc_sequence_id(i) := NULL;
4774 gt_actg_doc_sequence_name(i) := NULL;
4775 gt_actg_doc_sequence_value(i) := NULL;
4776 gt_actg_party_id(i) := NULL;
4777 gt_actg_party_site_id(i) := NULL;
4778 gt_actg_party_type(i) := NULL;
4779 gt_actg_event_id(i) := NULL;
4780 gt_actg_header_id(i) := NULL;
4781 gt_actg_source_id(i) := NULL;
4782 gt_actg_source_table(i) := NULL;
4783 gt_actg_line_ccid(i) := NULL;
4784 gt_period_name(i) := NULL;
4785 */
4786 GT_TRX_ARAP_BALANCING_SEGMENT(i) := NULL;
4787 GT_TRX_ARAP_NATURAL_ACCOUNT(i) := NULL;
4788 GT_TRX_TAXABLE_BAL_SEG(i) := NULL;
4789 GT_TRX_TAXABLE_BALSEG_DESC(i):= NULL;
4790 GT_TRX_TAXABLE_NATURAL_ACCOUNT(i) := NULL;
4791 GT_TRX_TAX_BALANCING_SEGMENT(i) := NULL;
4792 GT_TRX_TAX_NATURAL_ACCOUNT(i) := NULL;
4793 GT_ACCOUNT_FLEXFIELD(i) := NULL;
4794 GT_ACCOUNT_DESCRIPTION(i) := NULL;
4795 GT_TRX_CONTROL_ACCFLEXFIELD(i) := NULL;
4796 -- Populate WHO columns --
4797
4798 g_created_by := fnd_global.user_id;
4799 g_creation_date := sysdate;
4800 g_last_updated_by := fnd_global.user_id;
4801 g_last_update_login := fnd_global.login_id;
4802 g_last_update_date := sysdate;
4803
4804 g_program_application_id := fnd_global.prog_appl_id ; --program_application_id
4805 g_program_id := fnd_global.conc_program_id ; --program_id
4806 g_program_login_id := fnd_global.conc_login_id ; --program_login_id
4807
4808
4809 END LOOP;
4810
4811 IF (g_level_procedure >= g_current_runtime_level ) THEN
4812 FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_POPULATE_PKG.initialize_variables.END',
4813 'ZX_AR_POPULATE_PKG: initialize_variables(-)');
4814 END IF;
4815
4816 EXCEPTION
4817 WHEN OTHERS THEN
4818 g_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
4819 FND_MESSAGE.SET_NAME('ZX','GENERIC_MESSAGE');
4820 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','populate_tax_data- '|| g_error_buffer);
4821 FND_MSG_PUB.Add;
4822 IF (g_level_unexpected >= g_current_runtime_level ) THEN
4823 FND_LOG.STRING(g_level_unexpected,
4824 'ZX.TRL.ZX_AR_POPULATE_PKG.initialize_variables',
4825 g_error_buffer);
4826 END IF;
4827
4828 END initialize_variables ;
4829
4830 END ZX_AR_POPULATE_PKG;