1 PACKAGE OE_CUSTOMER_INFO_PVT AUTHID CURRENT_USER AS
2 /* $Header: OEXVCUSS.pls 120.1 2009/02/27 08:29:32 smanian noship $ */
3
4 G_SOLD_TO_CUSTOMER_ID NUMBER;
5 G_SOLD_TO_CONTACT_ID NUMBER;
6
7 Procedure get_customer_info_ids
8 (
9 p_customer_info_tbl IN OUT NOCOPY OE_ORDER_PUB.CUSTOMER_INFO_TABLE_TYPE,
10 p_operation_code IN VARCHAR2,
11 p_sold_to_customer_ref IN VARCHAR2,
12 p_ship_to_customer_ref IN VARCHAR2,
13 p_bill_to_customer_ref IN VARCHAR2,
14 p_deliver_to_customer_ref IN VARCHAR2,
15
16 p_ship_to_address_ref IN VARCHAR2,
17 p_bill_to_address_ref IN VARCHAR2,
18 p_deliver_to_address_ref IN VARCHAR2,
19 p_sold_to_address_ref IN VARCHAR2,
20
21 p_sold_to_contact_ref IN VARCHAR2,
22 p_ship_to_contact_ref IN VARCHAR2,
23 p_bill_to_contact_ref IN VARCHAR2,
24 p_deliver_to_contact_ref IN VARCHAR2,
25
26 p_sold_to_customer_id IN NUMBER,
27 p_ship_to_customer_id IN NUMBER,
28 p_bill_to_customer_id IN NUMBER,
29 p_deliver_to_customer_id IN NUMBER,
30
31 p_ship_to_org_id IN NUMBER,
32 p_invoice_to_org_id IN NUMBER,
33 p_deliver_to_org_id IN NUMBER,
34 p_sold_to_site_use_id IN NUMBER,
35
36 p_sold_to_contact_id IN NUMBER,
37 p_ship_to_contact_id IN NUMBER,
38 p_invoice_to_contact_id IN NUMBER,
39 p_deliver_to_contact_id IN NUMBER,
40
41 x_sold_to_customer_id OUT NOCOPY NUMBER,
42 x_ship_to_customer_id OUT NOCOPY NUMBER,
43 x_bill_to_customer_id OUT NOCOPY NUMBER,
44 x_deliver_to_customer_id OUT NOCOPY NUMBER,
45
46 x_ship_to_org_id OUT NOCOPY NUMBER,
47 x_invoice_to_org_id OUT NOCOPY NUMBER,
48 x_deliver_to_org_id OUT NOCOPY NUMBER,
49 x_sold_to_site_use_id OUT NOCOPY NUMBER,
50
51 x_sold_to_contact_id OUT NOCOPY NUMBER,
52 x_ship_to_contact_id OUT NOCOPY NUMBER,
53 x_invoice_to_contact_id OUT NOCOPY NUMBER,
54 x_deliver_to_contact_id OUT NOCOPY NUMBER,
55
56
57 x_return_status OUT NOCOPY VARCHAR2,
58 x_msg_count OUT NOCOPY NUMBER,
59 x_msg_data OUT NOCOPY VARCHAR2
60 );
61
62
63 Procedure Create_account (p_header_customer_info_rec IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE,
64 x_party_id OUT NOCOPY NUMBER,
65 x_cust_account_id OUT NOCOPY NUMBER,
66 x_return_status OUT NOCOPY VARCHAR2,
67 x_msg_count OUT NOCOPY NUMBER,
68 x_msg_data OUT NOCOPY VARCHAR2
69 );
70
71 Procedure Create_relationships (p_sold_to_customer_id IN NUMBER,
72 p_ship_to_customer_id NUMBER DEFAULT NULL,
73 p_bill_to_customer_id NUMBER DEFAULT NULL,
74 p_deliver_to_cust_id NUMBER DEFAULT NULL,
75 x_return_status OUT NOCOPY VARCHAR2,
76 x_msg_count OUT NOCOPY NUMBER,
77 x_msg_data OUT NOCOPY VARCHAR2
78 );
79
80
81
82 Procedure Create_Addresses (
83 p_customer_info_tbl IN OUT NOCOPY OE_ORDER_PUB.CUSTOMER_INFO_TABLE_TYPE
84 , p_operation_code IN VARCHAR2
85 , p_sold_to_customer_id IN NUMBER
86 , p_ship_to_customer_id IN NUMBER
87 , p_bill_to_customer_id IN NUMBER
88 , p_deliver_to_customer_id IN NUMBER
89 , p_customer_id IN NUMBER
90 , p_address_rec_index IN NUMBER
91 , p_address_usage IN VARCHAR2
92 , x_ship_to_org_id IN OUT NOCOPY NUMBER
93 , x_invoice_to_org_id IN OUT NOCOPY NUMBER
94 , x_deliver_to_org_id IN OUT NOCOPY NUMBER
95 , x_sold_to_site_use_id IN OUT NOCOPY NUMBER
96 , x_return_status OUT NOCOPY VARCHAR2
97 , x_msg_count OUT NOCOPY NUMBER
98 , x_msg_data OUT NOCOPY VARCHAR2
99 );
100
101 Procedure Create_Party_Site
102 ( p_party_id IN NUMBER,
103 p_address_rec IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE,
104 x_location_id OUT NOCOPY NUMBER,
105 x_party_site_id OUT NOCOPY NUMBER,
106 x_return_status OUT NOCOPY VARCHAR2,
107 x_msg_count OUT NOCOPY NUMBER,
108 x_msg_data OUT NOCOPY VARCHAR2 );
109
110
111
112 PROCEDURE Create_Cust_Account_Site
113 ( p_party_site_id IN NUMBER,
114 p_cust_account_id IN NUMBER,
115 p_address_rec IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE,
116 x_cust_account_site_id OUT NOCOPY NUMBER,
117 x_return_status OUT NOCOPY VARCHAR2,
118 x_msg_count OUT NOCOPY NUMBER,
119 x_msg_data OUT NOCOPY VARCHAR2 );
120
121 PROCEDURE Create_Acct_Site_Uses
122 (
123 p_cust_acct_site_id IN NUMBER,
124 p_location_number IN VARCHAR2,
125 p_site_use_code IN VARCHAR2,
126 x_site_use_id OUT NOCOPY NUMBER,
127 x_return_status OUT NOCOPY VARCHAR2,
128 x_msg_count OUT NOCOPY NUMBER,
129 x_msg_data OUT NOCOPY VARCHAR2
130 );
131
132
133 Procedure Create_Contact ( p_customer_info_tbl IN OE_ORDER_PUB.CUSTOMER_INFO_TABLE_TYPE
134 , p_operation_code IN VARCHAR2
135 , p_customer_id IN NUMBER
136 , p_customer_rec_index IN NUMBER
137 , p_usage_code IN VARCHAR2
138 , x_sold_to_contact_id IN OUT NOCOPY NUMBER
139 , x_ship_to_contact_id IN OUT NOCOPY NUMBER
140 , x_invoice_to_contact_id IN OUT NOCOPY NUMBER
141 , x_deliver_to_contact_id IN OUT NOCOPY NUMBER
142 , x_return_status OUT NOCOPY VARCHAR2
143 , x_msg_count OUT NOCOPY NUMBER
144 , x_msg_data OUT NOCOPY VARCHAR2
145 );
146
147 Function check_relation_exists ( p_customer_id IN NUMBER
148 ,p_rel_customer_id NUMBER ) RETURN BOOLEAN;
149
150 Procedure Check_Party_Site_Exists( p_party_id IN NUMBER,
151 p_address_rec IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE,
152 x_party_site_id OUT NOCOPY NUMBER
153 );
154
155
156 Procedure Check_Cust_Site_Exists ( p_party_site_id IN NUMBER,
157 p_cust_account_id IN NUMBER,
158 x_cust_acct_site_id OUT NOCOPY NUMBER
159 );
160
161
162
163 Procedure Check_Customer_Fields (p_customer_record IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
164 ,x_return_status OUT NOCOPY VARCHAR2 );
165 Procedure Validate_Customer_Fields (p_customer_record IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
166 ,x_return_status OUT NOCOPY VARCHAR2 );
167
168 Procedure Check_Duplicate_Customer ( p_customer_record IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
169 , p_type IN VARCHAR2
170 , x_customer_id OUT NOCOPY VARCHAR2
171 );
172
173
174 Procedure Check_Address_Fields (p_address_record IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
175 ,x_return_status OUT NOCOPY VARCHAR2 );
176 Procedure Validate_Address_Fields (p_address_record IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
177 ,x_return_status OUT NOCOPY VARCHAR2 );
178 Procedure Check_Duplicate_Address ( p_address_record IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
179 , p_sold_to_org_id IN NUMBER
180 , p_site_customer_id IN NUMBER
181 , p_site_usage IN VARCHAR2
182 , x_site_usage_id OUT NOCOPY NUMBER
183 );
184
185 Procedure Value_To_Id_Address ( p_address_record IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
186 , p_sold_to_org_id IN NUMBER
187 , p_site_customer_id IN NUMBER
188 , p_site_usage IN VARCHAR2
189 , x_site_usage_id OUT NOCOPY NUMBER
190 );
191 Procedure Value_To_Id_contact ( p_contact_record IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
192 , p_customer_id IN NUMBER
193 , p_site_usage_code IN VARCHAR2
194 , p_site_usage_id OUT NOCOPY NUMBER
195 , x_contact_id OUT NOCOPY NUMBER
196 );
197
198 Procedure Check_Contact_Fields (p_contact_record IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
199 ,x_return_status OUT NOCOPY VARCHAR2 );
200 Procedure Validate_Contact_Fields (p_contact_record IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
201 ,x_return_status OUT NOCOPY VARCHAR2 );
202
203
204
205
206 Function IS_BOTH_ID_VAL_PASSED ( p_customer_rec IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE)
207 RETURN BOOLEAN;
208
209 Function IS_BOTH_ID_VAL_PASSED ( p_address_rec IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE)
210 RETURN BOOLEAN;
211
212 Function IS_BOTH_ID_VAL_PASSED ( p_contact_rec IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE)
213 RETURN BOOLEAN;
214
215
216 FUNCTION Get_obj_version_number( p_location_id IN NUMBER DEFAULT NULL
217 , p_cust_account_id IN NUMBER DEFAULT NULL
218 , p_party_id IN NUMBER DEFAULT NULL ) RETURN NUMBER;
219
220 Procedure Update_Location ( p_address_rec IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
221 , p_site_use_id IN NUMBER
222 , p_site_usage_code IN VARCHAR2
223 , x_return_status OUT NOCOPY VARCHAR2
224 , x_msg_count OUT NOCOPY NUMBER
225 , x_msg_data OUT NOCOPY VARCHAR2
226 );
227
228
229 Procedure Update_Customer ( p_customer_rec IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
230 , x_return_status OUT NOCOPY VARCHAR2 );
231
232 PROCEDURE Update_Contact ( p_contact_rec IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
233 , x_return_status OUT NOCOPY VARCHAR2
234 , x_msg_count OUT NOCOPY NUMBER
235 , x_msg_data OUT NOCOPY VARCHAR2
236 );
237
238
239
240 FUNCTION IS_VALID_ID ( p_party_id IN NUMBER DEFAULT NULL ,
241 p_customer_id IN NUMBER DEFAULT NULL ) RETURN BOOLEAN ;
242
243 Function Get_Party_ID ( p_cust_acct_id IN NUMBER) RETURN NUMBER;
244
245 PROCEDURE Check_site_usage_exists ( p_cust_acct_site_id IN NUMBER
246 ,p_site_usage IN VARCHAR2
247 ,x_site_use_id OUT NOCOPY NUMBER);
248
249 FUNCTION Get_Party_Type ( p_party_id IN NUMBER )RETURN VARCHAR2;
250
251 Function Get_Location_id( p_cust_acct_site_id IN NUMBER DEFAULT NULL)
252 RETURN NUMBER;
253
254
255 Function Get_Location_id ( p_site_usage_code IN VARCHAR2 DEFAULT NULL
256 , p_site_use_id IN NUMBER DEFAULT NULL)RETURN NUMBER;
257
258 END OE_CUSTOMER_INFO_PVT;
[Home] [Help]