[Home] [Help]
PACKAGE BODY: APPS.ZX_TCM_GET_DEF_EXEMPTION
Source
1 PACKAGE BODY ZX_TCM_GET_DEF_EXEMPTION AS
2 /* $Header: zxcgetdefexemptb.pls 120.3 2006/07/28 00:28:28 dbetanco ship $ */
3
4 -- Logging Infra
5 G_CURRENT_RUNTIME_LEVEL NUMBER;
6 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
7 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
8 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
10 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
12 G_MODULE_NAME CONSTANT VARCHAR2(40) := 'ZX.PLSQL.ZX_TCM_GET_DEF_EXEMPTION';
13
14 PROCEDURE get_default_exemptions(
15 p_bill_to_cust_acct_id IN NUMBER,
16 p_ship_to_cust_acct_id IN NUMBER,
17 p_ship_to_site_use_id IN NUMBER,
18 p_bill_to_site_use_id IN NUMBER,
19 p_bill_to_party_id IN NUMBER,
20 p_bill_to_party_site_id IN NUMBER,
21 p_ship_to_party_site_id IN NUMBER,
22 p_legal_entity_id IN NUMBER,
23 p_org_id IN NUMBER,
24 p_trx_date IN DATE,
25 p_exempt_certificate_number IN VARCHAR2,
26 p_reason_code IN VARCHAR2,
27 p_exempt_control_flag IN VARCHAR2,
28 p_inventory_org_id IN NUMBER,
29 p_inventory_item_id IN NUMBER,
30 x_return_status OUT NOCOPY VARCHAR2,
31 x_exemption_rec_tbl OUT NOCOPY exemption_rec_tbl_type) IS
32
33 CURSOR get_default_exemptions(p_country_code IN VARCHAR2, p_ptp_id IN NUMBER) IS
34 SELECT
35 v.tax_exemption_id,
36 v.exemption_type_code,
37 v.exempt_certificate_number,
38 v.exempt_reason_code,
39 v.meaning,
40 v.Exemption_Status_code,
41 v.tax_regime_code,
42 v.Tax_status_code,
43 v.Tax,
44 v.Tax_Rate_Code,
45 v.cust_account_id,
46 v.site_use_id,
47 v.party_id,
48 v.party_site_id,
49 v.effective_from,
50 v.effective_to,
51 v.content_owner_id,
52 v.PRODUCT_ID,
53 v.INVENTORY_ORG_ID,
54 v.RATE_MODIFIER,
55 v.TAX_JURISDICTION_ID,
56 v.PARTY_TAX_PROFILE_ID,
57 decode(zxr.country_code, p_country_code, 1, 2) select_order
58 FROM
59 (SELECT
60 ex.tax_exemption_id,
61 ex.exemption_type_code,
62 ex.exempt_certificate_number,
63 ex.exempt_reason_code,
64 lkp.Meaning,
65 Ex.Exemption_Status_code,
66 Ex.tax_regime_code,
67 Ex.Tax_status_code,
68 Ex.Tax,
69 Ex.Tax_Rate_Code,
70 Ex.Cust_account_id,
71 Ex.Site_use_id,
72 Ptp_party.party_id party_id,
73 null party_site_id,
74 Ex.effective_from,
75 Ex.effective_to,
76 Ex.content_owner_id,
77 Ex.PRODUCT_ID,
78 Ex.INVENTORY_ORG_ID,
79 Ex.RATE_MODIFIER,
80 Ex.TAX_JURISDICTION_ID,
81 Ex.PARTY_TAX_PROFILE_ID
82 FROM
83 zx_exemptions ex, fnd_lookups lkp,
84 zx_party_tax_profile ptp_party
85 WHERE
86 Ex.exempt_reason_code = lkp.lookup_code and
87 Lkp.lookup_type = 'ZX_EXEMPTION_REASON_CODE' and
88 ex.exemption_status_code in ( 'PRIMARY', 'MANUAL', 'UNAPPROVED' ) and
89 ex.party_tax_profile_id = ptp_party.party_tax_profile_id and
90 ptp_party.party_type_code = 'THIRD_PARTY'
91 UNION
92 SELECT
93 ex.tax_exemption_id,
94 ex.exemption_type_code,
95 ex.exempt_certificate_number,
96 ex.exempt_reason_code,
97 lkp.Meaning,
98 Ex.Exemption_Status_code,
99 Ex.tax_regime_code,
100 Ex.Tax_status_code,
101 Ex.Tax,
102 Ex.Tax_Rate_Code,
103 Ex.Cust_account_id,
104 Ex.Site_use_id,
105 ps.party_id party_id,
106 Ptp_party_site.party_id party_site_id,
107 Ex.effective_from,
108 Ex.effective_to,
109 Ex.content_owner_id,
110 Ex.PRODUCT_ID,
111 Ex.INVENTORY_ORG_ID,
112 Ex.RATE_MODIFIER,
113 Ex.TAX_JURISDICTION_ID,
114 Ex.PARTY_TAX_PROFILE_ID
115 FROM
116 zx_exemptions ex, fnd_lookups lkp,
117 zx_party_tax_profile ptp_party_site, hz_party_sites ps
118 WHERE
119 Ex.exempt_reason_code = lkp.lookup_code and
120 Lkp.lookup_type = 'ZX_EXEMPTION_REASON_CODE' and
121 ex.exemption_status_code in ( 'PRIMARY', 'MANUAL', 'UNAPPROVED' ) and
122 ex.party_tax_profile_id = ptp_party_site.party_tax_profile_id and
123 ptp_party_site.party_type_code = 'THIRD_PARTY_SITE' and
124 ptp_party_site.party_id = ps.party_site_id) v, zx_regimes_b zxr
125 WHERE
126 v.content_owner_id = p_ptp_id
127 AND v.tax_regime_code = zxr.tax_regime_code
128 AND nvl(v.EXEMPT_CERTIFICATE_NUMBER, -99) = nvl(p_exempt_certificate_number, -99)
129 AND nvl(v.EXEMPT_REASON_CODE, -99) = nvl(p_reason_code,-99)
130 AND nvl(v.site_use_id,nvl(p_ship_to_site_use_id,p_bill_to_site_use_id)) =
131 nvl(p_ship_to_site_use_id, p_bill_to_site_use_id)
132 AND nvl(v.cust_account_id, p_bill_to_cust_acct_id) = p_bill_to_cust_acct_id
133 AND nvl(v.PARTY_SITE_ID,nvl(p_ship_to_party_site_id, p_bill_to_party_site_id))=
134 nvl(p_ship_to_party_site_id, p_bill_to_party_site_id)
135 and v.party_id = p_bill_to_party_id
136 AND v.EXEMPTION_STATUS_CODE = 'PRIMARY'
137 AND TRUNC(NVL(p_trx_date,sysdate))
138 BETWEEN TRUNC(v.EFFECTIVE_FROM)
139 AND TRUNC(NVL(v.EFFECTIVE_TO,NVL(p_trx_date,sysdate)))
140 ORDER BY select_order;
141
142 TYPE l_exemption_rec_type IS RECORD
143 ( TAX_EXEMPTION_ID NUMBER(15),
144 EXEMPTION_TYPE_CODE VARCHAR2(30),
145 EXEMPT_CERTIFICATE_NUMBER VARCHAR2(80),
146 EXEMPT_REASON_CODE VARCHAR2(30),
147 MEANING VARCHAR2(80),
148 EXEMPTION_STATUS_CODE VARCHAR2(30),
149 TAX_REGIME_CODE VARCHAR2(30),
150 TAX_STATUS_CODE VARCHAR2(30),
151 TAX VARCHAR2(30),
152 TAX_RATE_CODE VARCHAR2(50),
153 CUST_ACCT_ID NUMBER(15),
154 SITE_USE_ID NUMBER(15),
155 PARTY_ID NUMBER(15),
156 PARTY_SITE_ID NUMBER(15),
157 EFFECTIVE_FROM DATE,
158 EFFECTIVE_TO DATE,
159 CONTENT_OWNER_ID NUMBER(15),
160 PRODUCT_ID NUMBER,
161 INVENTORY_ORG_ID NUMBER,
162 RATE_MODIFIER NUMBER,
163 TAX_JURISDICTION_ID NUMBER(15),
164 PARTY_TAX_PROFILE_ID NUMBER(15),
165 SELECT_ORDER NUMBER);
166
167 TYPE l_exemption_rec_tbl_type IS TABLE of l_exemption_rec_type INDEX BY BINARY_INTEGER;
168 l_exemption_rec_tbl l_exemption_rec_tbl_type;
169
170
171 l_country_code VARCHAR2(60);
172 l_ptp_id NUMBER;
173 -- Logging Infra
174 l_procedure_name CONSTANT VARCHAR2(30) := 'get_default_exemptions';
175 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
176 BEGIN
177
178 -- Logging Infra: Setting up runtime level
179 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
180 -- Logging Infra: Procedure level
181 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
182 l_log_msg := l_procedure_name||'(+)';
183 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
184 END IF;
185
186 -- Initialize API return status to success.
187
188 x_return_status := FND_API.G_RET_STS_SUCCESS;
189
190 -- Logging Infra: Statement level: "B" means "B"reak point
191 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
192 l_log_msg := 'B: SEL hz_locations: in: p_ship_to_party_site_id='||p_ship_to_party_site_id;
193 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
194 END IF;
195
196 SELECT hzl.COUNTRY
197 INTO l_country_code
198 FROM hz_locations hzl,
199 hz_party_sites hzp
200 WHERE hzl.location_id = hzp.location_id
201 AND hzp.PARTY_SITE_ID = p_ship_to_party_site_id;
202
203 -- Logging Infra: Statement level: "R" means "R"eturned value to a caller
204 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
205 l_log_msg := 'R: SEL hz_locations:: out: l_country_code='||l_country_code;
206 l_log_msg := l_log_msg ||' B: get_tax_subscriber: in: p_legal_entity_id, p_org_id='||p_legal_entity_id||p_org_id;
207 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
208 END IF;
209
210 zx_tcm_ptp_pkg.get_tax_subscriber( p_legal_entity_id,
211 p_org_id,
212 l_ptp_id,
213 x_return_status);
214
215 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
216 RAISE FND_API.G_EXC_ERROR;
217 END IF;
218
219 OPEN get_default_exemptions(l_country_code,l_ptp_id);
220 FETCH get_default_exemptions BULK COLLECT INTO l_exemption_rec_tbl;
221 IF l_exemption_rec_tbl.count <> 0 THEN
222 FOR i IN l_exemption_rec_tbl.first..l_exemption_rec_tbl.last LOOP
223 x_exemption_rec_tbl(i).tax_exemption_id := l_exemption_rec_tbl(i).tax_exemption_id;
224 x_exemption_rec_tbl(i).exemption_type_code := l_exemption_rec_tbl(i).exemption_type_code;
225 x_exemption_rec_tbl(i).exemption_status_code:= l_exemption_rec_tbl(i).exemption_status_code;
226 x_exemption_rec_tbl(i).exempt_certificate_number:= l_exemption_rec_tbl(i).exempt_certificate_number;
227 x_exemption_rec_tbl(i).exempt_reason_code:= l_exemption_rec_tbl(i).exempt_reason_code;
228 x_exemption_rec_tbl(i).tax_regime_code:= l_exemption_rec_tbl(i).tax_regime_code;
229 x_exemption_rec_tbl(i).tax_status_code := l_exemption_rec_tbl(i).tax_status_code;
230 x_exemption_rec_tbl(i).tax := l_exemption_rec_tbl(i).tax;
231 x_exemption_rec_tbl(i).tax_rate_code := l_exemption_rec_tbl(i).tax_rate_code;
232 x_exemption_rec_tbl(i).effective_from := l_exemption_rec_tbl(i).effective_from;
233 x_exemption_rec_tbl(i).effective_to := l_exemption_rec_tbl(i).effective_to;
234 x_exemption_rec_tbl(i).content_owner_id:= l_exemption_rec_tbl(i).content_owner_id;
235 x_exemption_rec_tbl(i).product_id := l_exemption_rec_tbl(i).product_id;
236 x_exemption_rec_tbl(i).inventory_org_id := l_exemption_rec_tbl(i).inventory_org_id;
237 x_exemption_rec_tbl(i).rate_modifier:= l_exemption_rec_tbl(i).rate_modifier;
238 x_exemption_rec_tbl(i).tax_jurisdiction_id := l_exemption_rec_tbl(i).tax_jurisdiction_id;
239 x_exemption_rec_tbl(i).party_tax_profile_id := l_exemption_rec_tbl(i).party_tax_profile_id;
240 END LOOP;
241 l_exemption_rec_tbl.delete;
242 END IF;
243 close get_default_exemptions;
244
245 EXCEPTION
246 WHEN OTHERS THEN
247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
248 -- Logging Infra: Statement level
249 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
250 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, 'E: EXC: OTHERS: '
251 || SQLCODE||': '||SQLERRM);
252
253 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
254 END IF;
255 END;
256 END;
257