1 PACKAGE BODY arp_cprof1_pkg as
2 /* $Header: AROCPR1B.pls 120.3 2005/09/21 07:16:08 mantani ship $ */
3 --
4 --
5 --
6 -- THIS PROCEDURE IS ADDED TO ENSURE THAT OE, AR AND PA ARE IN SYNC WITH
7 -- EACH OTHER AS FAR AS PLACING AND RELEASING CUSTOMERS FROM HOLD.
8 -- CALL TO API OE_HOLDS.HOLDS_API IS MADE
9 --
10 PROCEDURE check_credit_hold (
11 p_customer_id in number,
12 p_site_use_id in number,
13 p_credit_hold in varchar2
14 ) is
15 --
16 old_credit_hold varchar2(1);
17 v_action varchar2(10);
18 v_entity_code varchar2(1);
19 v_entity_id number(15);
20 dummy_message varchar2(2000);/*Bug 2806125, increased the maximum width to 2000*/
21 --
22 begin
23 --
24 if ( p_site_use_id is null ) then
25 --
26 -- TRANSLATING VALUES TO BE PASSED AS PARAMETERS TO OE_HOLDS.HOLDS_API
27 -- WHEN CALLED FROM CUSTOMER PROFILE ZONE
28 --
29 v_entity_code := 'C';
30 v_entity_id := p_customer_id;
31 --
32 -- CHECK THE EXISTING VALUE OF CREDIT_HOLD IN THE DATABASE
33 --
34 declare
35 cursor cust_hold is
36 select credit_hold
37 from hz_customer_profiles
38 where CUST_ACCOUNT_ID = p_customer_id
39 and site_use_id is null;
40 begin
41 for c in cust_hold
42 loop
43 old_credit_hold := c.credit_hold;
44 end loop;
45 end;
46 --
47 else /* IF NOT NULL */
48 --
49 -- TRANSLATING VALUES TO BE PASSED AS PARAMETERS TO OE_HOLDS.HOLDS_API
50 -- WHEN CALLED FROM ADDRESS PROFILE ZONE
51 --
52 v_entity_code := 'S';
53 v_entity_id := p_site_use_id;
54 --
55 -- CHECK THE EXISTING VALUE OF CREDIT_HOLD IN THE DATABASE
56 --
57 declare
58 cursor site_hold is
59 select credit_hold
60 from hz_customer_profiles
61 where CUST_ACCOUNT_ID = p_customer_id
62 and site_use_id = p_site_use_id;
63 begin
64 for s in site_hold
65 loop
66 old_credit_hold := s.credit_hold;
67 end loop;
68 end;
69 --
70 end if;
71 --
72 -- IF THE VALUE OF THE DATABASE FIELD IS DIFFERENT THEN THE FORM FIELD THEN
73 -- SET THE VALUES FOR THE PARAMETERS TO BE PASSED TO OE_HOLDS.HOLDS_API
74 --
75 if old_credit_hold is null or
76 old_credit_hold <> p_credit_hold then
77 --
78 -- TRANSLATING VALUES TO BE PASSED AS PARAMETERS TO OE_HOLDS.HOLDS_API
79 --
80 if p_credit_hold = 'Y' then
81 v_action := 'APPLY' ;
82 else
83 v_action := 'RELEASE';
84 end if;
85 --
86 -- CALL OE API
87 --
88 /* This is obsolete in R12, need to replace with th new signature later
89 if OE_HOLDS.HOLDS_API (
90 v_action, 1, v_entity_code,
91 v_entity_id, 'AR_AUTOMATIC', null, dummy_message
92 ) > 0 then
93 --
94 -- RAISE FATAL EXCEPTION WHEN FUNCTION RETURNS ERROR_CODE
95 --
96 fnd_message.set_name('AR','AR_CUST_OE_ERROR');
97 fnd_message.set_token ('PROCEDURE', 'OE_HOLDS.HOLDS_API');
98 fnd_message.set_token ('ERROR_MSG', dummy_message);
99 app_exception.raise_exception;
100
101 end if;
102 */
103
104 end if;
105 --
106 END check_credit_hold;
107 --
108 --
109 PROCEDURE update_send_dunning_letters ( p_send_dunning_letters IN varchar2,
110 p_customer_id IN number,
111 p_site_use_id IN number
112 ) is
113 begin
114
115 if p_site_use_id is not null then
116
117 --
118 -- UPDATE THE SITE LEVEL PROFILE
119 --
120
121 update hz_customer_profiles
122 set dunning_letters = p_send_dunning_letters
123 where CUST_ACCOUNT_ID = p_customer_id
124 and site_use_id = p_site_use_id;
125
126 if SQL%NOTFOUND then
127 update hz_customer_profiles
128 set dunning_letters = p_send_dunning_letters
129 where CUST_ACCOUNT_ID = p_customer_id
130 and site_use_id is null;
131 end if;
132
133 else
134
135 --
136 -- UPDATE THE CUSTOMER LEVEL PROFILE
137 --
138
139 update hz_customer_profiles
140 set dunning_letters = p_send_dunning_letters
141 where CUST_ACCOUNT_ID = p_customer_id
142 and site_use_id is null;
143
144 if SQL%NOTFOUND then
145 app_exception.invalid_argument('arp_cprof_pkg.update_send_dunning_letters',
146 'p_customer_id', p_customer_id);
147
148 end if;
149
150 end if;
151
152 end update_send_dunning_letters;
153 --
154 -- The following function is added so that the credit_hold can be updated by
155 -- OE product. The p_customer_id and p_credit_hold parameters are MANDATORY.
156 -- The function returns 0 or 1 and does not return any error messages.
157 --
158 FUNCTION update_credit_hold(p_customer_id IN number,
159 p_site_use_id IN number,
160 p_credit_hold IN varchar2) RETURN BOOLEAN AS
161
162 BEGIN
163 if (p_site_use_id is null) then
164 update hz_customer_profiles
165 set credit_hold = p_credit_hold
166 where CUST_ACCOUNT_ID= p_customer_id
167 and site_use_id is null;
168 else
169 update hz_customer_profiles
170 set credit_hold = p_credit_hold
171 where CUST_ACCOUNT_ID = p_customer_id
172 and site_use_id = p_site_use_id;
173 end if;
174
175 return(TRUE);
176
177 EXCEPTION
178 when no_data_found then
179 return(FALSE);
180
181 END update_credit_hold;
182 --
183 END arp_cprof1_pkg;
[Home] [Help]