1 PACKAGE BODY AR_CMGT_REASSIGN_CONC AS
2 /* $Header: ARCMRACB.pls 115.3 2003/10/10 14:23:33 mraymond noship $ */
3
4 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
5
6 PROCEDURE reassign_credit_analyst(
7 errbuf IN OUT NOCOPY VARCHAR2,
8 retcode IN OUT NOCOPY VARCHAR2,
9 p_credit_analyst_id_from IN VARCHAR2,
10 p_credit_analyst_id_to IN VARCHAR2,
11 p_assign_status IN VARCHAR2,
12 p_start_date IN VARCHAR2,
13 p_end_date IN VARCHAR2
14 ) IS
15
16 --Declare Local variables
17 l_credit_analyst_id_from NUMBER;
18 l_credit_analyst_id_to NUMBER;
19 l_assign_status VARCHAR2(30);
20 l_start_date DATE;
21 l_end_date DATE;
22
23 l_cf_return_status VARCHAR2(1);
24 l_req_return_status VARCHAR2(1);
25 l_prof_return_status VARCHAR2(1);
26
27 BEGIN
28
29 IF PG_DEBUG in ('Y', 'C') THEN
30 arp_util.debug('ar_cmgt_reassign_conc.reassign_credit_analyst (+) ');
31 END IF;
32
33 --Print all input variables
34 IF PG_DEBUG in ('Y', 'C') THEN
35 arp_util.debug('reassign_credit_analyst: ' || 'p_credit_analyst_id_from :'||p_credit_analyst_id_from);
36 arp_util.debug('reassign_credit_analyst: ' || 'p_credit_analyst_id_to :'||p_credit_analyst_id_to);
37 arp_util.debug('reassign_credit_analyst: ' || 'p_assign_status :'||p_assign_status);
38 arp_util.debug('reassign_credit_analyst: ' || 'p_start_date :'||p_start_date);
39 arp_util.debug('reassign_credit_analyst: ' || 'p_end_date :'||p_end_date);
40 END IF;
41
42 --Convert the IN variables
43 l_credit_analyst_id_from := FND_NUMBER.CANONICAL_TO_NUMBER(p_credit_analyst_id_from);
44 l_credit_analyst_id_to := FND_NUMBER.CANONICAL_TO_NUMBER(p_credit_analyst_id_to);
45 l_assign_status := p_assign_status;
46 l_start_date := FND_DATE.CANONICAL_TO_DATE(p_start_date);
47 l_end_date := FND_DATE.CANONICAL_TO_DATE(p_end_date);
48
49 --Intialize the out NOCOPY variable
50 l_cf_return_status := FND_API.G_RET_STS_SUCCESS;
51 l_req_return_status := FND_API.G_RET_STS_SUCCESS;
52 l_prof_return_status := FND_API.G_RET_STS_SUCCESS;
53
54 --Issue Save point
55 SAVEPOINT assign_analyst_pvt;
56
57 IF (NVL(l_assign_status,'NONE') = 'PERMANENT')
58 THEN
59
60 --Update pending case folders
61 BEGIN
62 UPDATE ar_cmgt_case_folders
63 SET credit_analyst_id = l_credit_analyst_id_to
64 WHERE credit_analyst_id = l_credit_analyst_id_from
65 AND status in ('CREATED','SAVED');
66 EXCEPTION
67 WHEN others THEN
68 l_cf_return_status := FND_API.G_RET_STS_ERROR;
69 END;
70
71 --Update pending credit requests
72 BEGIN
73 UPDATE ar_cmgt_credit_requests
74 SET credit_analyst_id = l_credit_analyst_id_to
75 WHERE credit_analyst_id = l_credit_analyst_id_from
76 AND status in ('IN_PROCESS','SAVED','SUBMIT');
77 EXCEPTION
78 WHEN others THEN
79 l_req_return_status := FND_API.G_RET_STS_ERROR;
80 END;
81
82 ELSIF (NVL(l_assign_status,'NONE') = 'TEMPORARY') THEN
83
84 IF l_end_date is null THEN
85
86 IF PG_DEBUG in ('Y', 'C') THEN
87 arp_util.debug('reassign_credit_analyst: ' || 'End should be enterted, if it is temporary assignment ');
88 END IF;
89 FND_MESSAGE.SET_NAME('AR','AR_DEPOSIT_UI_ED_DATE_INV');
90 FND_MSG_PUB.Add;
91 ROLLBACK TO assign_analyst_pvt;
92 app_exception.raise_exception;
93
94 END IF;
95
96 --Update pending case folders
97 BEGIN
98 UPDATE ar_cmgt_case_folders
99 SET credit_analyst_id = l_credit_analyst_id_to
100 WHERE credit_analyst_id = l_credit_analyst_id_from
101 AND creation_date_time between NVL(l_start_date,creation_date_time)
102 AND NVL(l_end_date,creation_date_time)
103 AND status in ('CREATED','SAVED');
104 EXCEPTION
105 WHEN others THEN
106 l_cf_return_status := FND_API.G_RET_STS_ERROR;
107 END;
108
109 --Update pending credit requests
110 BEGIN
111 UPDATE ar_cmgt_credit_requests
112 SET credit_analyst_id = l_credit_analyst_id_to
113 WHERE credit_analyst_id = l_credit_analyst_id_from
114 AND creation_date between NVL(l_start_date,creation_date)
115 AND NVL(l_end_date,creation_date)
116 AND status in ('IN_PROCESS','SAVED','SUBMIT');
117 EXCEPTION
118 WHEN others THEN
119 l_req_return_status := FND_API.G_RET_STS_ERROR;
120 END;
121
122 END IF;
123
124 --Update customer profile
125 BEGIN
126 UPDATE hz_customer_profiles
127 SET credit_analyst_id = l_credit_analyst_id_to
128 WHERE credit_analyst_id = l_credit_analyst_id_from;
129 EXCEPTION
130 WHEN others THEN
131 l_prof_return_status := FND_API.G_RET_STS_ERROR;
132 END;
133
134 IF l_cf_return_status <> FND_API.G_RET_STS_SUCCESS OR
135 l_req_return_status <> FND_API.G_RET_STS_SUCCESS OR
136 l_prof_return_status <> FND_API.G_RET_STS_SUCCESS
137 THEN
138 IF PG_DEBUG in ('Y', 'C') THEN
139 arp_util.debug('EXCEPTION: ' ||'ar_cmgt_reassign_conc.reassign_credit_analyst :'||SQLERRM);
140 END IF;
141 ROLLBACK TO assign_analyst_pvt;
142 app_exception.raise_exception;
143 END IF;
144
145 IF PG_DEBUG in ('Y', 'C') THEN
146 arp_util.debug('ar_cmgt_reassign_conc.reassign_credit_analyst (-) ');
147 END IF;
148
149 END reassign_credit_analyst;
150
151 END AR_CMGT_REASSIGN_CONC;
[Home] [Help]