[Home] [Help]
PACKAGE BODY: APPS.ZX_TCM_VALIDATE_EXEMPT_PKG
Source
1 PACKAGE BODY ZX_TCM_VALIDATE_EXEMPT_PKG AS
2 /* $Header: zxcvalexemptb.pls 120.2 2005/12/21 02:59:09 sachandr ship $ */
3 -- Logging Infra
4 G_CURRENT_RUNTIME_LEVEL NUMBER;
5 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
6 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
7 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
8 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
9 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
10 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
11 G_MODULE_NAME CONSTANT VARCHAR2(30) := 'ZX.ZX_TCM_VAL_EXEMPT_PKG';
12
13 PROCEDURE VALIDATE_TAX_EXEMPTIONS
14 (p_tax_exempt_number IN VARCHAR2,
15 p_tax_exempt_reason_code IN VARCHAR2,
16 p_ship_to_org_id IN NUMBER,
17 p_invoice_to_org_id IN NUMBER,
18 p_bill_to_cust_account_id IN NUMBER,
19 p_ship_to_party_site_id IN NUMBER,
20 p_bill_to_party_site_id IN NUMBER,
21 p_org_id IN NUMBER,
22 p_bill_to_party_id IN NUMBER,
23 p_legal_entity_id IN NUMBER,
24 p_trx_type_id IN NUMBER,
25 p_batch_source_id IN NUMBER,
26 p_trx_date IN DATE,
27 p_exemption_status IN VARCHAR2 default 'P',
28 x_valid_flag OUT NOCOPY VARCHAR2,
29 x_return_status OUT NOCOPY VARCHAR2,
30 x_msg_count OUT NOCOPY NUMBER ,
31 x_msg_data OUT NOCOPY VARCHAR2) IS
32 l_legal_entity_id NUMBER;
33 l_effective_date DATE;
34 l_return_status VARCHAR2(1);
35 l_msg_count NUMBER;
36 l_msg_data VARCHAR2(2000);
37 -- Logging Infra
38 l_procedure_name CONSTANT VARCHAR2(30) := 'validate_tax_exemptions';
39 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
40 BEGIN
41 -- Logging Infra: Setting up runtime message level
42 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
43 x_return_status := FND_API.G_RET_STS_SUCCESS;
44 x_valid_flag := 'N';
45 IF p_legal_entity_id IS NULL THEN
46 IF p_trx_type_id IS NOT NULL AND p_batch_source_id IS NOT NULL AND p_org_id IS NOT NULL THEN
47 BEGIN
48 l_legal_entity_id := XLE_BUSINESSINFO_GRP.Get_OrdertoCash_Info(
49 p_customer_type => null, -- IN
50 p_customer_id => null, -- IN
51 p_transaction_type_id => p_trx_type_id, -- IN
52 p_batch_source_id => p_batch_source_id, -- IN
53 p_operating_unit_id => p_org_id -- IN
54 );
55 EXCEPTION WHEN OTHERS THEN
56 x_return_status := FND_API.G_RET_STS_ERROR;
57 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
58 l_log_msg := 'Function XLE_BUSINESSINFO_GRP.Get_OrdertoCash_Info returned errors';
59 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
60 END IF;
61
62 END;
63 ELSE
64 x_return_status := FND_API.G_RET_STS_ERROR;
65 ----- Unable to derive legal entity
66 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
67 l_log_msg := 'Transaction Type Id or Batch source Id or Org Id is null.';
68 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
69 END IF;
70 END IF;
71 ELSE
72 l_legal_entity_id := p_legal_entity_id;
73
74 END IF;
75 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
76 RAISE FND_API.G_EXC_ERROR;
77
78 END IF;
79 IF l_legal_entity_id IS NOT NULL THEN
80
81 zx_api_pub.set_tax_security_context(
82 p_api_version => 1.0 ,
83 p_init_msg_list =>NULL,
84 p_commit => 'N',
85 p_validation_level => 1,
86 x_msg_count =>x_msg_count,
87 x_msg_data =>x_msg_data,
88 p_internal_org_id => p_org_id,
89 p_legal_entity_id => l_legal_entity_id,
90 p_transaction_date => p_trx_date,
91 p_related_doc_date => NULL,
92 p_adjusted_doc_date =>NULL,
93 x_effective_date =>l_effective_date,
94 x_return_status => x_return_status);
95 IF p_exemption_status = 'P' THEN
96 BEGIN
97 SELECT 'Y'
98 INTO x_valid_flag
99 FROM ZX_EXEMPTIONS_V
100 WHERE EXEMPT_CERTIFICATE_NUMBER = p_tax_exempt_number
101 AND EXEMPT_REASON_CODE = p_tax_exempt_reason_code
102 AND nvl(site_use_id,nvl(p_ship_to_org_id, p_invoice_to_org_id)) = nvl(p_ship_to_org_id, p_invoice_to_org_id)
103 AND nvl(cust_account_id, p_bill_to_cust_account_id) = p_bill_to_cust_account_id
104 AND nvl(PARTY_SITE_ID,nvl(p_ship_to_party_site_id, p_bill_to_party_site_id))= nvl(p_ship_to_party_site_id,
105 p_bill_to_party_site_id)
106 AND party_id = p_bill_to_party_id
107 AND EXEMPTION_STATUS_CODE = 'PRIMARY'
108 AND TRUNC(NVL(p_trx_date,sysdate)) BETWEEN TRUNC(EFFECTIVE_FROM)
109 AND TRUNC(NVL(EFFECTIVE_TO,NVL(p_trx_date,sysdate)))
110 AND ROWNUM = 1;
111 EXCEPTION WHEN NO_DATA_FOUND THEN
112 x_valid_flag := 'N';
113
114 END;
115 ELSIF p_exemption_status = 'PM' THEN
116 BEGIN
117 SELECT 'Y'
118 INTO x_valid_flag
119 FROM ZX_EXEMPTIONS_V
120 WHERE EXEMPT_CERTIFICATE_NUMBER = p_tax_exempt_number
121 AND EXEMPT_REASON_CODE = p_tax_exempt_reason_code
122 AND nvl(site_use_id,nvl(p_ship_to_org_id, p_invoice_to_org_id)) = nvl(p_ship_to_org_id, p_invoice_to_org_id)
123 AND nvl(cust_account_id, p_bill_to_cust_account_id) = p_bill_to_cust_account_id
124 AND nvl(PARTY_SITE_ID,nvl(p_ship_to_party_site_id, p_bill_to_party_site_id))= nvl(p_ship_to_party_site_id,
125 p_bill_to_party_site_id)
126 AND party_id = p_bill_to_party_id
127 AND EXEMPTION_STATUS_CODE IN ('PRIMARY', 'MANUAL')
128 AND TRUNC(NVL(p_trx_date,sysdate)) BETWEEN TRUNC(EFFECTIVE_FROM)
129 AND TRUNC(NVL(EFFECTIVE_TO,NVL(p_trx_date,sysdate)))
130 AND ROWNUM = 1;
131 EXCEPTION WHEN NO_DATA_FOUND THEN
132 x_valid_flag := 'N';
133
134 END;
135 ELSIF p_exemption_status = 'PMU' THEN
136 BEGIN
137 SELECT 'Y'
138 INTO x_valid_flag
139 FROM ZX_EXEMPTIONS_V
140 WHERE EXEMPT_CERTIFICATE_NUMBER = p_tax_exempt_number
141 AND EXEMPT_REASON_CODE = p_tax_exempt_reason_code
142 AND nvl(site_use_id,nvl(p_ship_to_org_id, p_invoice_to_org_id)) = nvl(p_ship_to_org_id, p_invoice_to_org_id)
143 AND nvl(cust_account_id, p_bill_to_cust_account_id) = p_bill_to_cust_account_id
144 AND nvl(PARTY_SITE_ID,nvl(p_ship_to_party_site_id, p_bill_to_party_site_id))= nvl(p_ship_to_party_site_id,
145 p_bill_to_party_site_id)
146 AND party_id = p_bill_to_party_id
147 AND EXEMPTION_STATUS_CODE IN ('PRIMARY', 'MANUAL','UNAPPROVED')
148 AND TRUNC(NVL(p_trx_date,sysdate)) BETWEEN TRUNC(EFFECTIVE_FROM)
149 AND TRUNC(NVL(EFFECTIVE_TO,NVL(p_trx_date,sysdate)))
150 AND ROWNUM = 1;
151 EXCEPTION WHEN NO_DATA_FOUND THEN
152 x_valid_flag := 'N';
153
154 END;
155 ELSE
156 x_valid_flag := 'N';
157 END IF;
158
159
160
161 ELSE
162 x_valid_flag := 'N';
163
164 END IF;
165
166 EXCEPTION WHEN FND_API.G_EXC_ERROR THEN
167 x_return_status := FND_API.G_RET_STS_ERROR;
168 -- Logging Infra: Statement level
169 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
170 l_log_msg := 'E: EXC: FND_API.G_EXC_ERROR';
171 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
172 END IF;
173
174 WHEN OTHERS THEN
175 x_return_status := FND_API.G_RET_STS_ERROR;
176 -- Logging Infra: Statement level
177 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
178 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, 'E: EXC: OTHERS: '||SQLCODE||': '||SQLERRM);
179 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
180 END IF;
181
182
183
184 END;
185
186 END;