[Home] [Help]
PACKAGE BODY: APPS.AR_AI_INTEG_PKG
Source
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AR_AI_INTEG_PKG';
1 PACKAGE BODY AR_AI_INTEG_PKG AS
2 /*$Header: ARXINTEGB.pls 120.5.12020000.2 2013/02/21 18:56:26 hvaladip ship $ */
3
5 procedure DEFAULT_ATTRIBUTES ( p_org_id IN NUMBER,
6 p_bill_to_customer_account_id IN NUMBER,
7 p_ship_to_customer_account_id IN NUMBER,
8 p_currency_code IN VARCHAR2,
9 x_bill_to_address_id OUT NOCOPY VARCHAR2,
10 x_ship_to_address_id OUT NOCOPY VARCHAR2,
11 x_payment_term_id OUT NOCOPY NUMBER,
12 x_conversion_type OUT NOCOPY VARCHAR2,
13 x_conversion_date OUT NOCOPY DATE,
14 x_conversion_rate OUT NOCOPY NUMBER,
15 x_return_status OUT NOCOPY VARCHAR2,
16 x_msg_data OUT NOCOPY VARCHAR2)
17 IS
18 l_currency_code GL_LEDGERS.currency_code%TYPE;
19 l_procedure_name VARCHAR2(30);
20 l_site_use_id NUMBER;
21 BEGIN
22
23
24 l_procedure_name := '.DEFAULT_ATTRIBUTES';
25
26 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
27 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'AR_AI_INTEG_PKG.DEFAULT_ATTRIBUTES (+)');
28 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'org_id : '||p_org_id);
29 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'bill_to_customer_account_id : '||p_bill_to_customer_account_id);
30 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'ship_to_customer_account_id : '||p_ship_to_customer_account_id);
31 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'currency_code : '||p_currency_code);
32 END IF;
33
34 x_return_status := FND_API.G_RET_STS_SUCCESS;
35
36 IF p_org_id is NULL THEN
37 FND_MESSAGE.SET_NAME('AR','AR_MAND_PARAMETER_NULL');
38 FND_MESSAGE.SET_TOKEN('PARAM','p_org_id');
39 x_msg_data := FND_MESSAGE.GET;
40 x_return_status := FND_API.G_RET_STS_ERROR;
41 RETURN;
42 END IF;
43 IF p_bill_to_customer_account_id IS NULL THEN
44 FND_MESSAGE.SET_NAME('AR','AR_MAND_PARAMETER_NULL');
45 FND_MESSAGE.SET_TOKEN('PARAM','p_bill_to_customer_account_id');
46 x_msg_data := FND_MESSAGE.GET;
47 x_return_status := FND_API.G_RET_STS_ERROR;
48 RETURN;
49 END IF;
50
51 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
52 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Retrieving the Functional Currency');
53 END IF;
54 select gl.currency_code into l_currency_code
55 from ar_system_parameters_all asp,
56 gl_ledgers gl
57 where org_id = p_org_id
61 IF p_currency_code = l_currency_code THEN
58 and gl.ledger_id = asp.set_of_books_id;
59
60 /*If the currency is in functional currency then only defaulting . */
62 x_conversion_type := 'User';
63 x_conversion_rate := 1;
64 x_conversion_date := trunc(sysdate);
65
66 END IF;
67 BEGIN
68 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
69 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Get the primary bill to site and payment_term_id');
70 END IF;
71 /* Get the primary bill to site */
72 select site_use.cust_acct_site_id, site_use.PAYMENT_TERM_ID,
73 site_use.site_use_id
74 into x_bill_to_address_id,x_payment_term_id, l_site_use_id
75 from hz_cust_site_uses_all site_use,
76 hz_cust_acct_sites_all sites
77 where sites.cust_account_id = p_bill_to_customer_account_id
78 and sites.status ='A'
79 and sites.cust_acct_site_id = site_use.cust_acct_site_id
80 and site_use.site_use_code = 'BILL_TO'
81 and site_use.primary_flag = 'Y'
82 and site_use.org_id = p_org_id;
83 EXCEPTION
84 WHEN NO_DATA_FOUND THEN
85 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
86 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'No Primary Bill to Site');
87 END IF;
88
89
90 END;
91
92 BEGIN
93 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
94 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Get the primary ship to site');
95 END IF;
96 /* Get the primary ship to site */
97 IF p_ship_to_customer_account_id IS NOT NULL THEN
98 select site_use.cust_acct_site_id
99 into x_ship_to_address_id
100 from hz_cust_site_uses_all site_use,
101 hz_cust_acct_sites_all sites
102 where sites.cust_account_id = p_ship_to_customer_account_id
103 and sites.status ='A'
104 and sites.cust_acct_site_id = site_use.cust_acct_site_id
105 and site_use.site_use_code = 'SHIP_TO'
106 and site_use.primary_flag = 'Y'
107 and site_use.org_id = p_org_id;
108 END IF;
109 EXCEPTION
110 WHEN NO_DATA_FOUND THEN
111 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
112 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'No Primary Ship to Site');
113 END IF;
114 END;
115
116 BEGIN
117 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
118 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Get the payment term id');
119 END IF;
120
121 IF x_payment_term_id IS NULL THEN
122 SELECT tl.term_id into x_payment_term_id
123 FROM ra_terms t_su,
124 ra_terms t_cp1,
125 ra_terms t_cp2,
126 ra_terms tl,
127 hz_customer_profiles cp1,
128 hz_customer_profiles cp2,
129 hz_cust_site_uses_all su
130 WHERE p_bill_to_customer_account_id = cp1.cust_account_id(+)
131 AND cp2.cust_account_id = p_bill_to_customer_account_id
132 AND su.site_use_id = cp1.site_use_id(+)
133 AND cp2.site_use_id IS NULL
134 AND su.payment_term_id = t_su.term_id(+)
135 AND cp1.standard_terms = t_cp1.term_id(+)
136 AND cp2.standard_terms = t_cp2.term_id(+)
137 AND NVL( t_su.term_id, NVL( t_cp1.term_id, t_cp2.term_id)) = tl.term_id
138 AND su.site_use_id = l_site_use_id
139 AND su.org_id = p_org_id;
140 END IF;
141
142 /*
143 Hari commented as part of bug# 16004835
144
145 select payment_term_id into x_payment_term_id
146 from hz_cust_accounts
147 where CUST_ACCOUNT_ID = p_bill_to_customer_account_id;
148 END IF;
149 */
150
151 EXCEPTION
152 WHEN NO_DATA_FOUND THEN
153 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
154 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'No Payment term id corresponding to bill_to_customer_account_id');
155 END IF;
156 END;
157
158 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
159 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'bill_to_address_id : '||x_bill_to_address_id);
160 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'ship_to_address_id : '||x_ship_to_address_id);
161 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'payment_term_id : '||x_payment_term_id);
162 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'conversion_type : '||x_conversion_type);
163 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'conversion_date : '||x_conversion_date);
164 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'conversion_rate : '||x_conversion_rate);
165 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'AR_AI_INTEG_PKG.DEFAULT_ATTRIBUTES (-)');
166 END IF;
167
168 EXCEPTION
169 WHEN OTHERS THEN
170 x_return_status := fnd_api.g_ret_sts_unexp_error;
174
171 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
172 fnd_message.set_token('ERROR' ,SQLERRM);
173 x_msg_data := fnd_message.get;
175 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
176 fnd_log.string(fnd_log.LEVEL_EXCEPTION,G_PKG_NAME||l_procedure_name,'SQLERRM :'||SQLERRM);
177 END IF;
178
179 END DEFAULT_ATTRIBUTES;
180
181 END AR_AI_INTEG_PKG;