1 PACKAGE BODY arh_cprof1_pkg as
2 /* $Header: ARHCPR1B.pls 120.3 2005/09/21 12:00:39 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(50);
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 end if;
104 --
105 END check_credit_hold;
106 --
107 --
108 PROCEDURE update_send_dunning_letters ( p_send_dunning_letters IN varchar2,
109 p_customer_id IN number,
110 p_site_use_id IN number
111 ) is
112 begin
113
114 if p_site_use_id is not null then
115
116 --
117 -- UPDATE THE SITE LEVEL PROFILE
118 --
119
120 update hz_customer_profiles
121 set dunning_letters = p_send_dunning_letters
122 where cust_account_id = p_customer_id
123 and site_use_id = p_site_use_id;
124
125 if SQL%NOTFOUND then
126 update hz_customer_profiles
127 set dunning_letters = p_send_dunning_letters
128 where cust_account_id = p_customer_id
129 and site_use_id is null;
130 end if;
131
132 else
133
134 --
135 -- UPDATE THE CUSTOMER LEVEL PROFILE
136 --
137
138 update hz_customer_profiles
139 set dunning_letters = p_send_dunning_letters
140 where cust_account_id = p_customer_id
141 and site_use_id is null;
142
143 if SQL%NOTFOUND then
144 app_exception.invalid_argument('arp_cprof_pkg.update_send_dunning_letters',
145 'p_customer_id', p_customer_id);
146
147 end if;
148
149 end if;
150
151 end update_send_dunning_letters;
152 --
153 -- The following function is added so that the credit_hold can be updated by
154 -- OE product. The p_customer_id and p_credit_hold parameters are MANDATORY.
155 -- The function returns 0 or 1 and does not return any error messages.
156 --
157 FUNCTION update_credit_hold(p_customer_id IN number,
158 p_site_use_id IN number,
159 p_credit_hold IN varchar2) RETURN BOOLEAN AS
160
161 BEGIN
162 if (p_site_use_id is null) then
163 update hz_customer_profiles
164 set credit_hold = p_credit_hold
165 where cust_account_id = p_customer_id
166 and site_use_id is null;
167 else
168 update hz_customer_profiles
169 set credit_hold = p_credit_hold
170 where cust_account_id = p_customer_id
171 and site_use_id = p_site_use_id;
172 end if;
173
174 return(TRUE);
175
176 EXCEPTION
177 when no_data_found then
178 return(FALSE);
179
180 END update_credit_hold;
181 --
182 END arh_cprof1_pkg;
[Home] [Help]