[Home] [Help]
PACKAGE BODY: APPS.ARP_CINT
Source
1 PACKAGE BODY ARP_CINT AS
2 /* $Header: ARPLCINB.pls 120.2 2005/10/30 04:24:22 appldev ship $ */
3
4 /*-------------------------------------------------------------------------+
5 | |
6 | PRIVATE EXCEPTIONS |
7 | |
8 +-------------------------------------------------------------------------*/
9
10 ccid_error1 EXCEPTION;
11 PRAGMA EXCEPTION_INIT( ccid_error1, -20000 );
12
13
14 /*-------------------------------------------------------------------------+
15 | |
16 | PRIVATE VARIABLES |
17 | |
18 +-------------------------------------------------------------------------*/
19
20 loc_ccid NUMBER(15) := NULL;
21 msg_text VARCHAR(240) := NULL;
22 int_stat VARCHAR(3) := NULL;
23
24 /*-------------------------------------------------------------------------+
25 | |
26 | PRIVATE CURSORS |
27 | |
28 | |
29 +-------------------------------------------------------------------------*/
30
31 CURSOR val_address_segs( request_id in number ) IS
32 SELECT rci.country country,
33 rci.city city,
34 rci.state state,
35 rci.county county,
36 rci.province province,
37 rci.postal_code postal_code,
38 rci.address_attribute1 attribute1,
39 rci.address_attribute2 attribute2,
40 rci.address_attribute3 attribute3,
41 rci.address_attribute4 attribute4,
42 rci.address_attribute5 attribute5,
43 rci.address_attribute6 attribute6,
44 rci.address_attribute7 attribute7,
45 rci.address_attribute8 attribute8,
46 rci.address_attribute9 attribute9,
47 rci.address_attribute10 attribute10,
48 rci.rowid row_id
49 FROM ra_customers_interface rci ,
50 ar_system_parameters sp
51 WHERE rci.interface_status is null
52 AND rci.orig_system_address_ref is not null
53 AND rci.request_id = request_id
54 AND rci.country = sp.default_country
55 AND rci.rowid in (SELECT min(rci2.rowid)
56 FROM ra_customers_interface rci2
57 WHERE rci2.interface_status is null
58 AND rci2.orig_system_address_ref =
59 rci.orig_system_address_ref
60 /* Added the site_use_code clause for Bug 235747 which causes all of the *
61 * relevant rows to be considered for error checking. Leaving this where *
62 * clause causes only 1 of the row from ra_customers_interface to be *
63 * selected. In order to do proper error checking we would like to have *
64 * all those rows which have a distinct combination of address ref and *
65 * site use code. */
66 AND NVL(rci2.site_use_code,'X') = NVL(rci.site_use_code,'X')
67 AND rci2.request_id = request_id
68 );
69
70
71 /*-------------------------------------------------------------------------+
72 | |
73 | PROCEDURE up_cust_int |
74 | |
75 +-------------------------------------------------------------------------*/
76 PROCEDURE up_cust_int( warning_text_in in varchar2,
77 location_ccid_in in number,
78 message_text_in in varchar2,
79 interface_status_in in varchar2,
80 rowid_in in rowid
81 ) IS
82 BEGIN
83
84 UPDATE ra_customers_interface
85 SET warning_text = warning_text_in,
86 location_ccid = location_ccid_in,
87 message_text = message_text_in,
88 interface_status = interface_status_in
89 WHERE rowid = rowid_in;
90 COMMIT;
91
92 END;
93
94
95 /*-------------------------------------------------------------------------+
96 | |
97 | PROCEDURE cint_gen_loc_ccid |
98 | |
99 +-------------------------------------------------------------------------*/
100 PROCEDURE cint_gen_loc_ccid (h_request_id in number,
101 h_user_id in number,
102 h_prog_appl_id in number,
103 h_program_id in number,
104 h_last_update_login in number,
105 h_application_id in number,
106 h_language_id in number ) IS
107
108 BEGIN
109
110 arp_standard.set_who_information( h_user_id,
111 h_request_id,
112 h_prog_appl_id,
113 h_program_id,
114 h_last_update_login ) ;
115
116 arp_standard.set_application_information( h_application_id,
117 h_language_id );
118
119 /*
120 * Loop through the address records within the interface
121 * table and validate the address segments.
122 */
123 FOR add_rec IN val_address_segs(h_request_id) LOOP
124 BEGIN
125 BEGIN
126 loc_ccid:=NULL;
127 arp_adds.Set_Location_CCID(add_rec.country,
128 add_rec.city,
129 add_rec.state,
130 add_rec.county,
131 add_rec.province,
132 add_rec.postal_code,
133 add_rec.attribute1,
134 add_rec.attribute2,
135 add_rec.attribute3,
136 add_rec.attribute4,
137 add_rec.attribute5,
138 add_rec.attribute6,
139 add_rec.attribute7,
140 add_rec.attribute8,
141 add_rec.attribute9,
142 add_rec.attribute10,
143 loc_ccid
144 );
145 IF ( arp_standard.sysparm.address_validation = 'WARN') AND
146 ( arp_adds.location_segment_inserted = TRUE )
147 THEN
148 arp_cint.up_cust_int( 'Q1,', loc_ccid, null,
149 null, add_rec.row_id );
150 ELSE
151 arp_cint.up_cust_int( '', loc_ccid, '',
152 '', add_rec.row_id );
153 END IF;
154
155 EXCEPTION
156
157 WHEN ccid_error1 THEN
158 BEGIN
159 msg_text :=
160 arp_standard.fnd_message(ARP_STANDARD.MD_MSG_NUMBER + ARP_STANDARD.MD_MSG_TEXT );
161 arp_cint.up_cust_int( '', null, msg_text, 'Q2,', add_rec.row_id );
162 END;
163
164 END;
165
166 END;
167
168 END LOOP;
169
170 END; /* end of procedure gen_address_loc_ccid */
171
172
173 END ARP_CINT;