[Home] [Help]
PACKAGE BODY: APPS.PO_ASL_THS
Source
1 PACKAGE BODY PO_ASL_THS as
2 /* $Header: POXA2LSB.pls 120.4 2006/01/18 10:54:15 pthapliy noship $ */
3
4 -- <INBOUND LOGISTICS FPJ START>
5 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_ASL_THS';
6 c_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'|| g_pkg_name || '.';
7 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
8 -- <INBOUND LOGISTICS FPJ END>
9
10 /*=============================================================================
11
12 PROCEDURE NAME: insert_row()
13
14 ===============================================================================*/
15 procedure insert_row(
16 x_row_id IN OUT NOCOPY VARCHAR2,
17 x_asl_id IN OUT NOCOPY NUMBER,
18 x_using_organization_id NUMBER,
19 x_owning_organization_id NUMBER,
20 x_vendor_business_type VARCHAR2,
21 x_asl_status_id NUMBER,
22 x_last_update_date DATE,
23 x_last_updated_by NUMBER,
24 x_creation_date DATE,
25 x_created_by NUMBER,
26 x_manufacturer_id NUMBER,
27 x_vendor_id NUMBER,
28 x_item_id NUMBER,
29 x_category_id NUMBER,
30 x_vendor_site_id NUMBER,
31 x_primary_vendor_item VARCHAR2,
32 x_manufacturer_asl_id NUMBER,
33 x_comments VARCHAR2,
34 x_review_by_date DATE,
35 x_attribute_category VARCHAR2,
36 x_attribute1 VARCHAR2,
37 x_attribute2 VARCHAR2,
38 x_attribute3 VARCHAR2,
39 x_attribute4 VARCHAR2,
40 x_attribute5 VARCHAR2,
41 x_attribute6 VARCHAR2,
42 x_attribute7 VARCHAR2,
43 x_attribute8 VARCHAR2,
44 x_attribute9 VARCHAR2,
45 x_attribute10 VARCHAR2,
46 x_attribute11 VARCHAR2,
47 x_attribute12 VARCHAR2,
48 x_attribute13 VARCHAR2,
49 x_attribute14 VARCHAR2,
50 x_attribute15 VARCHAR2,
51 x_last_update_login NUMBER,
52 x_disable_flag VARCHAR2) is
53
54 cursor row_id is SELECT rowid
55 FROM PO_APPROVED_SUPPLIER_LIST
56 WHERE x_asl_id = asl_id;
57
58 x_record_unique BOOLEAN;
59
60 -- <INBOUND LOGISTICS FPJ START>
61 l_api_version CONSTANT NUMBER := 1.0;
62 l_return_status VARCHAR2(1);
63 l_msg_count NUMBER;
64 l_msg_data FND_NEW_MESSAGES.message_text%TYPE;
65 l_msg_buf VARCHAR2(2000);
66 l_api_name CONSTANT VARCHAR2(40) := 'insert_row';
67 l_progress VARCHAR2(3) := '001';
68 -- <INBOUND LOGISTICS FPJ END>
69
70 begin
71
72 -- Check that the record is unique (i.e., no other
73 -- record contains the same supplier/manufactuerer, using_org
74 -- and item/commodity).
75
76 x_record_unique := po_asl_sv.check_record_unique(
77 x_manufacturer_id,
78 x_vendor_id,
79 x_vendor_site_id,
80 x_item_id,
81 x_category_id,
82 x_using_organization_id);
83
84 if not x_record_unique then
85
86 fnd_message.set_name('FND','PO_ASL_SUPPLIER_ITEM_DUP');
87 app_exception.raise_exception;
88
89 end if;
90
91 if (x_asl_id is null) then
92
93 SELECT po_approved_supplier_list_s.nextval
94 INTO x_asl_id
95 FROM sys.dual;
96
97 end if;
98
99 INSERT INTO PO_APPROVED_SUPPLIER_LIST(
100 asl_id ,
101 using_organization_id ,
102 owning_organization_id ,
103 vendor_business_type ,
104 asl_status_id ,
105 last_update_date ,
106 last_updated_by ,
107 creation_date ,
108 created_by ,
109 manufacturer_id ,
110 vendor_id ,
111 item_id ,
112 category_id ,
113 vendor_site_id ,
114 primary_vendor_item ,
115 manufacturer_asl_id ,
116 comments ,
117 review_by_date ,
118 attribute_category ,
119 attribute1 ,
120 attribute2 ,
121 attribute3 ,
122 attribute4 ,
123 attribute5 ,
124 attribute6 ,
125 attribute7 ,
126 attribute8 ,
127 attribute9 ,
128 attribute10 ,
129 attribute11 ,
130 attribute12 ,
131 attribute13 ,
132 attribute14 ,
133 attribute15 ,
134 last_update_login ,
135 disable_flag
136 ) VALUES (
137 x_asl_id ,
138 x_using_organization_id ,
139 x_owning_organization_id ,
140 x_vendor_business_type ,
141 x_asl_status_id ,
142 x_last_update_date ,
143 x_last_updated_by ,
144 x_creation_date ,
145 x_created_by ,
146 x_manufacturer_id ,
147 x_vendor_id ,
148 x_item_id ,
149 x_category_id ,
150 x_vendor_site_id ,
151 x_primary_vendor_item ,
152 x_manufacturer_asl_id ,
153 x_comments ,
154 x_review_by_date ,
155 x_attribute_category ,
156 x_attribute1 ,
157 x_attribute2 ,
158 x_attribute3 ,
159 x_attribute4 ,
160 x_attribute5 ,
161 x_attribute6 ,
162 x_attribute7 ,
163 x_attribute8 ,
164 x_attribute9 ,
165 x_attribute10 ,
166 x_attribute11 ,
167 x_attribute12 ,
168 x_attribute13 ,
169 x_attribute14 ,
170 x_attribute15 ,
171 x_last_update_login ,
172 x_disable_flag
173 );
174
175 OPEN row_id;
176 FETCH row_id INTO x_row_id;
177 if (row_id%notfound) then
178 CLOSE row_id;
179 raise no_data_found;
180 end if;
181 CLOSE row_id;
182
183 -- <INBOUND LOGISTICS FPJ START>
184 l_progress := '020';
185 l_return_status := FND_API.G_RET_STS_SUCCESS;
186 IF (g_fnd_debug = 'Y') THEN
187 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
188 FND_LOG.string( LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
189 MODULE => c_log_head || '.'||l_api_name||'.' || l_progress,
190 MESSAGE => 'Call PO_BUSINESSEVENT_PVT.raise_event'
191 );
192 END IF;
193 END IF;
194
195 PO_BUSINESSEVENT_PVT.raise_event
196 (
197 p_api_version => l_api_version,
198 x_return_status => l_return_status,
199 x_msg_count => l_msg_count,
200 x_msg_data => l_msg_data,
201 p_event_name => 'oracle.apps.po.event.create_asl',
202 p_entity_name => 'ASL',
203 p_entity_id => x_asl_id
204 );
205
206 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
207 IF (g_fnd_debug = 'Y') THEN
208 l_msg_buf := NULL;
209 l_msg_buf := FND_MSG_PUB.Get( p_msg_index => 1,
210 p_encoded => 'F');
211 l_msg_buf := SUBSTR('ASL' || x_asl_id || 'errors out at' || l_progress || l_msg_buf, 1, 2000);
212 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
213 FND_LOG.string( LOG_LEVEL => FND_LOG.level_unexpected,
214 MODULE => c_log_head || '.'||l_api_name||'.error_exception',
215 MESSAGE => l_msg_buf
216 );
217 END IF;
218 END IF;
219 ELSE
220 IF (g_fnd_debug = 'Y') THEN
221 l_msg_buf := NULL;
222 l_msg_buf := SUBSTR('ASL' || x_asl_id||'raised business event successfully', 1, 2000);
223 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
224 FND_LOG.string( LOG_LEVEL => FND_LOG.level_statement,
225 MODULE => c_log_head || '.'||l_api_name,
226 MESSAGE => l_msg_buf
227 );
228 END IF;
229 END IF;
230 END IF; -- IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
231
232 EXCEPTION
233 WHEN OTHERS THEN
234 IF (g_fnd_debug = 'Y') THEN
235 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
236 FND_LOG.string( LOG_LEVEL => FND_LOG.LEVEL_EXCEPTION,
237 MODULE => c_log_head || '.'||l_api_name,
238 MESSAGE => SQLERRM(SQLCODE)
239 );
240 END IF;
241 END IF;
242
243 APP_EXCEPTION.raise_exception; -- <ASL ERECORD FPJ>
244 -- <INBOUND LOGISTICS FPJ END>
245
246 end insert_row;
247
248 END PO_ASL_THS;