[Home] [Help]
PACKAGE: APPS.OKS_TAX_UTIL_PVT
Source
1 PACKAGE OKS_TAX_UTIL_PVT AUTHID CURRENT_USER AS
2 /* $Header: OKSTAXUS.pls 120.11.12000000.1 2007/01/16 22:15:12 appldev ship $*/
3
4 --------------------------------------------------------------------------
5 -- GLOBAL MESSAGE CONSTANTS
6 ---------------------------------------------------------------------------
7 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
8 G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_LOGICALLY_DELETED;
9 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
13 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
10 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
11 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
12 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_PARENT_TABLE_TOKEN;
14 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKS_TAX_UTIL_UNEXPECTED_ERROR';
15 G_EXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKS_TAX_UTIL_PVT';
16 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLCODE';
17 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLERRM';
18 G_NO_TRX_TYPE CONSTANT VARCHAR2(200) := 'OKS_NO_TRX_TYPE';
19
20 ---------------------------------------------------------------------------
21 -- GLOBAL EXCEPTIONS
22 ---------------------------------------------------------------------------
23 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
24 G_EXCEPTION_ROLLBACK EXCEPTION;
25 G_ERROR EXCEPTION;
26
27 ---------------------------------------------------------------------------
28 -- GLOBAL VARIABLES
29 ---------------------------------------------------------------------------
30 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKS_TAX_UTIL_PVT';
31 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
32 G_OKS_APP_NAME CONSTANT VARCHAR2(3) := 'OKS'; --all new nessages should use this
33
34 TYPE RA_REC_TYPE IS RECORD
35 (
36 HEADER_ID NUMBER,
37 LINE_ID NUMBER,
38 TAX_EXEMPT_FLAG VARCHAR2(1),
39 TAX_EXEMPT_REASON VARCHAR2(30),
40 TAX_EXEMPT_NUMBER VARCHAR2(80),
41 SALES_TAX_ID NUMBER(15),
42 ORG_ID NUMBER(15),
43 ORIG_SYSTEM_SHIP_ADDRESS_ID NUMBER(15),
44 ORIG_SYSTEM_SHIP_CONTACT_ID NUMBER(15),
45 ORIG_SYSTEM_SHIP_CUSTOMER_ID NUMBER(15),
46 SHIP_TO_SITE_USE_ID NUMBER(15),
47 SHIP_TO_POSTAL_CODE VARCHAR2(60),
48 SHIP_TO_LOCATION_ID NUMBER(15),
49 SHIP_TO_CUSTOMER_NUMBER VARCHAR2(50),
50 SHIP_TO_CUSTOMER_NAME VARCHAR2(200),
51 SHIP_TO_ORG_ID NUMBER(15),
52 WAREHOUSE_ID NUMBER,
53 ORIG_SYSTEM_SOLD_CUSTOMER_ID NUMBER(15),
54 ORIG_SYSTEM_BILL_CUSTOMER_ID NUMBER(15),
55 ORIG_SYSTEM_BILL_ADDRESS_ID NUMBER(15),
56 ORIG_SYSTEM_BILL_CONTACT_ID NUMBER(15),
57 BILL_TO_SITE_USE_ID NUMBER(15),
58 BILL_TO_POSTAL_CODE VARCHAR2(60),
59 BILL_TO_LOCATION_ID NUMBER(15),
60 BILL_TO_CUSTOMER_NUMBER VARCHAR2(50),
61 BILL_TO_CUSTOMER_NAME VARCHAR2(200),
62 BILL_TO_ORG_ID NUMBER(15),
63 RECEIPT_METHOD_NAME VARCHAR2(30),
64 RECEIPT_METHOD_ID NUMBER(15),
65 CONVERSION_TYPE VARCHAR2(30),
66 CONVERSION_DATE DATE,
67 CONVERSION_RATE NUMBER,
68 CUSTOMER_TRX_ID NUMBER(15),
69 TRX_DATE DATE,
70 GL_DATE DATE,
71 DOCUMENT_NUMBER NUMBER(15),
72 TRX_NUMBER VARCHAR2(20),
73 LINE_NUMBER NUMBER(15),
74 QUANTITY NUMBER,
75 QUANTITY_ORDERED NUMBER,
76 UNIT_SELLING_PRICE NUMBER,
77 UNIT_STANDARD_PRICE NUMBER,
78 PRINTING_OPTION VARCHAR2(20),
79 INVENTORY_ITEM_ID NUMBER(15),
80 INVENTORY_ORG_ID NUMBER(15),
81 UOM_CODE VARCHAR2(3),
82 UOM_NAME VARCHAR2(25),
83 RELATED_TRX_NUMBER VARCHAR2(20),
84 RELATED_CUSTOMER_TRX_ID NUMBER(15),
85 PREVIOUS_CUSTOMER_TRX_ID NUMBER(15),
86 REASON_CODE VARCHAR2(30),
87 TAX_RATE NUMBER,
88 TAX_CODE VARCHAR2(50),
89 TAX_PRECEDENCE NUMBER,
90 INVOICING_RULE_NAME VARCHAR2(30),
91 INVOICING_RULE_ID NUMBER(15),
92 PURCHASE_ORDER VARCHAR2(50),
93 SET_OF_BOOKS_ID NUMBER(15),
94 LINE_TYPE VARCHAR2(20),
95 DESCRIPTION VARCHAR2(240),
96 CURRENCY_CODE VARCHAR2(15),
97 AMOUNT NUMBER,
98 CUST_TRX_TYPE_NAME VARCHAR2(20),
99 CUST_TRX_TYPE_ID NUMBER(15),
100 PAYMENT_TERM_ID NUMBER(15),
101 AMOUNT_INCLUDES_TAX_FLAG VARCHAR2(1),
102 TAX_CAL_PLSQL_BLOCK VARCHAR2(3000),
103 MINIMUM_ACCOUNTABLE_UNIT NUMBER,
104 PRECISION NUMBER,
105 FOB_POINT VARCHAR2(30),
106 TAXABLE_BASIS VARCHAR2(30),
107 VAT_TAX_ID NUMBER,
108 TAX_VALUE NUMBER,
109 TOTAL_PLUS_TAX NUMBER,
110 --Added in R12 by rsu
111 TAX_CLASSIFICATION_CODE VARCHAR2(30),
112 EXEMPT_CERTIFICATE_NUMBER VARCHAR2(80),
113 EXEMPT_REASON_CODE VARCHAR2(30),
114 SHIP_TO_PARTY_ID HZ_CUST_ACCOUNTS.PARTY_ID%TYPE,
115 SHIP_TO_PARTY_SITE_ID HZ_PARTY_SITES.PARTY_SITE_ID%TYPE,
116 SHIP_TO_CUST_ACCT_ID HZ_CUST_ACCT_SITES_ALL.CUST_ACCOUNT_ID%TYPE,
117 SHIP_TO_CUST_ACCT_SITE_ID HZ_CUST_SITE_USES_ALL.CUST_ACCT_SITE_ID%TYPE,
118 SHIP_TO_CUST_ACCT_SITE_USE_ID OKC_K_LINES_B.SHIP_TO_SITE_USE_ID%TYPE,
119 BILL_TO_PARTY_ID HZ_CUST_ACCOUNTS.PARTY_ID%TYPE,
120 BILL_TO_PARTY_SITE_ID HZ_PARTY_SITES.PARTY_SITE_ID%TYPE,
121 BILL_TO_CUST_ACCT_ID OKC_K_LINES_B.CUST_ACCT_ID%TYPE,
122 BILL_TO_CUST_ACCT_SITE_ID HZ_CUST_SITE_USES_ALL.CUST_ACCT_SITE_ID%TYPE,
123 BILL_TO_CUST_ACCT_SITE_USE_ID OKC_K_LINES_B.BILL_TO_SITE_USE_ID%TYPE,
124 PRICE_NEGOTIATED OKC_K_LINES_B.price_negotiated%TYPE,
125 PRODUCT_TYPE ZX_PRODUCT_TYPES_DEF_V.CLASSIFICATION_CODE%TYPE
126 );
127 TYPE RA_REC_TBL IS TABLE OF ra_rec_type INDEX BY BINARY_INTEGER;
128
129 ---------------------------------------------------------------------------
130 -- Procedures and Functions
131 ---------------------------------------------------------------------------
132 /*
133 Procedure that calciulates tax for a given contract line and populates the
134 results in px_rail_rec
135
136 Parameters
137 p_chr_id : contract id
138 p_cle_id : top line or subline id
139 px_rail_rec : tax record structure
140
141 */
142 PROCEDURE GET_TAX
143 (
144 p_api_version IN NUMBER,
145 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
146 p_chr_id IN NUMBER,
147 p_cle_id IN NUMBER,
148 px_rail_rec IN OUT NOCOPY ra_rec_type,
149 x_msg_count OUT NOCOPY NUMBER,
150 x_msg_data OUT NOCOPY VARCHAR2,
151 x_return_status OUT NOCOPY VARCHAR2
152 );
153
154
155 /*
156 This is a concurrent program to migrate the pre-R12 tax data
157 to R12 eBTax. We do not remove the old values.
158 OKS_K_HEADERS_B:
159 TAX_EXEMPTION_ID --> EXEMPT_CERTIFICATE_NUMBER and EXEMPT_REASON_CODE
160
161 OKS_K_LINES_B:
162 TAX_EXEMPTION_ID --> EXEMPT_CERTIFICATE_NUMBER and EXEMPT_REASON_CODE
163 TAX_CODE --> TAX_CLASSIFICATION_CODE
164 */
165 PROCEDURE TAX_MIGRATION
166 (
167 ERRBUF OUT NOCOPY VARCHAR2,
168 RETCODE OUT NOCOPY NUMBER
169 );
170
171 --npalepu added on 19-jun-2006 for bug # 4908543
172 PROCEDURE Update_Tax_BMGR(X_errbuf out NOCOPY varchar2,
173 X_retcode out NOCOPY varchar2,
174 P_batch_size in number,
175 P_Num_Workers in number);
176
177 PROCEDURE Update_Tax_HMGR(X_errbuf out NOCOPY varchar2,
178 X_retcode out NOCOPY varchar2,
179 P_batch_size in number,
180 P_Num_Workers in number);
181
182 /*
183 This is a concurrent program to migrate the pre-R12 tax data
184 to R12 eBTax. We do not remove the old values.
185 OKS_K_LINES_B:
186 TAX_EXEMPTION_ID --> EXEMPT_CERTIFICATE_NUMBER and EXEMPT_REASON_CODE
187 TAX_CODE --> TAX_CLASSIFICATION_CODE
188 */
189 PROCEDURE Update_Tax_BWKR(X_errbuf out NOCOPY varchar2,
190 X_retcode out NOCOPY varchar2,
191 P_batch_size in number,
192 P_Worker_Id in number,
193 P_Num_Workers in number);
194
195 /*
196 This is a concurrent program to migrate the pre-R12 tax data
197 to R12 eBTax. We do not remove the old values.
198 OKS_K_LINES_BH:
199 TAX_EXEMPTION_ID --> EXEMPT_CERTIFICATE_NUMBER and EXEMPT_REASON_CODE
200 TAX_CODE --> TAX_CLASSIFICATION_CODE
201 */
202 PROCEDURE Update_Tax_HWKR(X_errbuf out NOCOPY varchar2,
203 X_retcode out NOCOPY varchar2,
204 P_batch_size in number,
205 P_Worker_Id in number,
206 P_Num_Workers in number);
207 --end npalepu
208
209
210 --Added in R12 by rsu
211 FUNCTION GET_CUST_TRX_TYPE_ID
212 (
213 p_org_id IN NUMBER,
214 p_inv_trx_type IN VARCHAR2
215 )
216 RETURN NUMBER;
217
218
219 --Added in R12 by rsu
220 FUNCTION GET_LEGAL_ENTITY_ID
221 (
222 p_bill_to_cust_acct_id IN NUMBER,
223 p_cust_trx_type_id IN NUMBER,
224 p_org_id IN NUMBER
225 )
226 RETURN NUMBER;
227
228
229 --Added in R12 by rsu
230 --Overloaded Get_Legal_Entity_Id
231 --For UI use
232 FUNCTION GET_LEGAL_ENTITY_ID
233 (
234 p_chr_id IN NUMBER
235 )
236 RETURN NUMBER;
237
238 END OKS_TAX_UTIL_PVT;