[Home] [Help]
PACKAGE BODY: APPS.ONT_SOLD_TO_SU_DEF_UTIL
Source
1 PACKAGE BODY ONT_SOLD_TO_SU_Def_Util AS
2 /* $Header: OEXDFWKB.pls 115.0 30-MAR-09 23:23:31 appldev ship $ */
3
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- ONT_SOLD_TO_SU_Def_Util
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package ONT_SOLD_TO_SU_Def_Util
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 30-MAR-09 Created
21 --
22
23 -- Global constant holding the package name
24 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ONT_SOLD_TO_SU_Def_Util';
25
26
27 g_database_object_name varchar2(30) :='OE_AK_SOLD_TO_SITE_USES_V';
28
29
30 FUNCTION Get_Attr_Val_Varchar2
31 ( p_attr_code IN VARCHAR2
32 , p_record IN OE_AK_SOLD_TO_SITE_USES_V%ROWTYPE
33 ) RETURN VARCHAR2
34 IS
35 BEGIN
36
37 IF p_attr_code =('CONTACT_ID') THEN
38 IF NVL(p_record.CONTACT_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
39 RETURN p_record.CONTACT_ID;
40 ELSE
41 RETURN NULL;
42 END IF;
43 ELSIF p_attr_code =('FOB_POINT_CODE') THEN
44 IF NVL(p_record.FOB_POINT_CODE, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR THEN
45 RETURN p_record.FOB_POINT_CODE;
46 ELSE
47 RETURN NULL;
48 END IF;
49 ELSIF p_attr_code =('FREIGHT_TERMS_CODE') THEN
50 IF NVL(p_record.FREIGHT_TERMS_CODE, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR THEN
51 RETURN p_record.FREIGHT_TERMS_CODE;
52 ELSE
53 RETURN NULL;
54 END IF;
55 ELSIF p_attr_code =('ORDER_TYPE_ID') THEN
56 IF NVL(p_record.ORDER_TYPE_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
57 RETURN p_record.ORDER_TYPE_ID;
58 ELSE
59 RETURN NULL;
60 END IF;
61 ELSIF p_attr_code =('OVER_SHIPMENT_TOLERANCE') THEN
62 IF NVL(p_record.OVER_SHIPMENT_TOLERANCE, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
63 RETURN p_record.OVER_SHIPMENT_TOLERANCE;
64 ELSE
65 RETURN NULL;
66 END IF;
67 ELSIF p_attr_code =('PAYMENT_TERM_ID') THEN
68 IF NVL(p_record.PAYMENT_TERM_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
69 RETURN p_record.PAYMENT_TERM_ID;
70 ELSE
71 RETURN NULL;
72 END IF;
73 ELSIF p_attr_code =('PRICE_LIST_ID') THEN
74 IF NVL(p_record.PRICE_LIST_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
75 RETURN p_record.PRICE_LIST_ID;
76 ELSE
77 RETURN NULL;
78 END IF;
79 ELSIF p_attr_code =('SALESREP_ID') THEN
80 IF NVL(p_record.SALESREP_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
81 RETURN p_record.SALESREP_ID;
82 ELSE
83 RETURN NULL;
84 END IF;
85 ELSIF p_attr_code =('SHIPPING_METHOD_CODE') THEN
86 IF NVL(p_record.SHIPPING_METHOD_CODE, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR THEN
87 RETURN p_record.SHIPPING_METHOD_CODE;
88 ELSE
89 RETURN NULL;
90 END IF;
91 ELSIF p_attr_code =('SOLD_TO_SITE_USE_ID') THEN
92 IF NVL(p_record.SOLD_TO_SITE_USE_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
93 RETURN p_record.SOLD_TO_SITE_USE_ID;
94 ELSE
95 RETURN NULL;
96 END IF;
97 ELSIF p_attr_code =('UNDER_SHIPMENT_TOLERANCE') THEN
98 IF NVL(p_record.UNDER_SHIPMENT_TOLERANCE, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
99 RETURN p_record.UNDER_SHIPMENT_TOLERANCE;
100 ELSE
101 RETURN NULL;
102 END IF;
103 ELSE
104 RETURN NULL;
105 END IF;
106 END Get_Attr_Val_Varchar2;
107
108
109 FUNCTION Get_Attr_Val_Date
110 ( p_attr_code IN VARCHAR2
111 , p_record IN OE_AK_SOLD_TO_SITE_USES_V%ROWTYPE
112 ) RETURN DATE
113 IS
114 BEGIN
115
116 IF p_attr_code =('CONTACT_ID') THEN
117 IF NVL(p_record.CONTACT_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
118 RETURN to_date(p_record.CONTACT_ID,'RRRR/MM/DD HH24:MI:SS');
119 ELSE
120 RETURN NULL;
121 END IF;
122 ELSIF p_attr_code =('FOB_POINT_CODE') THEN
123 IF NVL(p_record.FOB_POINT_CODE, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR THEN
124 RETURN to_date(p_record.FOB_POINT_CODE,'RRRR/MM/DD HH24:MI:SS');
125 ELSE
126 RETURN NULL;
127 END IF;
128 ELSIF p_attr_code =('FREIGHT_TERMS_CODE') THEN
129 IF NVL(p_record.FREIGHT_TERMS_CODE, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR THEN
130 RETURN to_date(p_record.FREIGHT_TERMS_CODE,'RRRR/MM/DD HH24:MI:SS');
131 ELSE
132 RETURN NULL;
133 END IF;
134 ELSIF p_attr_code =('ORDER_TYPE_ID') THEN
135 IF NVL(p_record.ORDER_TYPE_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
136 RETURN to_date(p_record.ORDER_TYPE_ID,'RRRR/MM/DD HH24:MI:SS');
137 ELSE
138 RETURN NULL;
139 END IF;
140 ELSIF p_attr_code =('OVER_SHIPMENT_TOLERANCE') THEN
141 IF NVL(p_record.OVER_SHIPMENT_TOLERANCE, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
142 RETURN to_date(p_record.OVER_SHIPMENT_TOLERANCE,'RRRR/MM/DD HH24:MI:SS');
143 ELSE
144 RETURN NULL;
145 END IF;
146 ELSIF p_attr_code =('PAYMENT_TERM_ID') THEN
147 IF NVL(p_record.PAYMENT_TERM_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
148 RETURN to_date(p_record.PAYMENT_TERM_ID,'RRRR/MM/DD HH24:MI:SS');
149 ELSE
150 RETURN NULL;
151 END IF;
152 ELSIF p_attr_code =('PRICE_LIST_ID') THEN
153 IF NVL(p_record.PRICE_LIST_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
154 RETURN to_date(p_record.PRICE_LIST_ID,'RRRR/MM/DD HH24:MI:SS');
155 ELSE
156 RETURN NULL;
157 END IF;
158 ELSIF p_attr_code =('SALESREP_ID') THEN
159 IF NVL(p_record.SALESREP_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
160 RETURN to_date(p_record.SALESREP_ID,'RRRR/MM/DD HH24:MI:SS');
161 ELSE
162 RETURN NULL;
163 END IF;
164 ELSIF p_attr_code =('SHIPPING_METHOD_CODE') THEN
165 IF NVL(p_record.SHIPPING_METHOD_CODE, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR THEN
166 RETURN to_date(p_record.SHIPPING_METHOD_CODE,'RRRR/MM/DD HH24:MI:SS');
167 ELSE
168 RETURN NULL;
169 END IF;
170 ELSIF p_attr_code =('SOLD_TO_SITE_USE_ID') THEN
171 IF NVL(p_record.SOLD_TO_SITE_USE_ID, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
172 RETURN to_date(p_record.SOLD_TO_SITE_USE_ID,'RRRR/MM/DD HH24:MI:SS');
173 ELSE
174 RETURN NULL;
175 END IF;
176 ELSIF p_attr_code =('UNDER_SHIPMENT_TOLERANCE') THEN
177 IF NVL(p_record.UNDER_SHIPMENT_TOLERANCE, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
178 RETURN to_date(p_record.UNDER_SHIPMENT_TOLERANCE,'RRRR/MM/DD HH24:MI:SS');
179 ELSE
180 RETURN NULL;
181 END IF;
182 ELSE
183 RETURN NULL;
184 END IF;
185
186 END Get_Attr_Val_Date;
187
188
189 PROCEDURE Clear_SOLD_TO_SU_Cache
190 IS
191 BEGIN
192 g_cached_record.SOLD_TO_SITE_USE_ID := null;
193 END Clear_SOLD_TO_SU_Cache;
194
195
196 FUNCTION Sync_SOLD_TO_SU_Cache
197 ( p_SOLD_TO_SITE_USE_ID IN NUMBER
198
199
200 ) RETURN NUMBER
201 IS
202 CURSOR cache IS
203 SELECT * FROM OE_AK_SOLD_TO_SITE_USES_V
204 WHERE SOLD_TO_SITE_USE_ID = p_SOLD_TO_SITE_USE_ID
205 ;
206 BEGIN
207
208 IF (NVL(p_SOLD_TO_SITE_USE_ID,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
209 THEN
210 RETURN 0 ;
211 ELSIF (NVL(g_cached_record.SOLD_TO_SITE_USE_ID,FND_API.G_MISS_NUM) <> p_SOLD_TO_SITE_USE_ID)
212 THEN
213 Clear_SOLD_TO_SU_Cache;
214 Open cache;
215 FETCH cache into g_cached_record;
216 IF cache%NOTFOUND THEN
217 RETURN 0;
218 END IF;
219 Close cache;
220 RETURN 1 ;
221 END IF;
222
223 RETURN 1 ;
224 EXCEPTION
225 WHEN OTHERS THEN
226 RETURN 0 ;
227 END Sync_SOLD_TO_SU_Cache;
228
229
230 END ONT_SOLD_TO_SU_Def_Util;