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