[Home] [Help]
PACKAGE BODY: APPS.CSP_REPAIR_PO_GRP
Source
1 PACKAGE BODY CSP_REPAIR_PO_GRP AS
2 /* $Header: cspgrpob.pls 120.19 2007/09/19 18:39:26 ajosephg ship $ */
3 -- Purpose: To create Repair execution
4 -- Start of Comments
5 -- Package name : CSP_REPAIR_PO_GRP
6 -- Purpose : This package creates Repair Purchase Order Requisition and Reservation of defective parts.
7 -- History : 09-June-2005, Arul Joseph.
8 -- NOTE :
9 -- End of Comments
10
11 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSP_REPAIR_PO_GRP';
12 G_FILE_NAME CONSTANT VARCHAR2(30) := 'cspvprqb.pls';
13
14 PROCEDURE CREATE_REPAIR_PO
15 (p_api_version IN NUMBER
16 ,p_Init_Msg_List IN VARCHAR2 DEFAULT FND_API.G_FALSE
17 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
18 ,P_repair_supplier_id IN NUMBER
19 ,P_repair_supplier_org_id IN NUMBER
20 ,P_repair_program IN VARCHAR2
21 ,P_dest_organization_id IN NUMBER
22 ,P_source_organization_id IN NUMBER
23 ,P_repair_to_item_id IN NUMBER
24 ,P_quantity IN NUMBER
25 ,P_need_by_date IN DATE
26 ,P_defective_parts_tbl IN CSP_REPAIR_PO_GRP.defective_parts_tbl_Type
27 ,x_requisition_header_id OUT NOCOPY NUMBER
28 ,x_return_status OUT NOCOPY VARCHAR2
29 ,x_msg_count OUT NOCOPY NUMBER
30 ,x_msg_data OUT NOCOPY VARCHAR2
31 )
32 IS
33 l_api_version_number CONSTANT NUMBER := 1.0;
34 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_REPAIR_PO';
35 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
36 l_msg_count NUMBER;
37 l_msg_data VARCHAR2(2000);
38 l_commit VARCHAR2(1) := FND_API.G_FALSE;
39 l_user_id NUMBER;
40 l_login_id NUMBER;
41 l_today DATE;
42 EXCP_USER_DEFINED EXCEPTION;
43 l_check_existence NUMBER;
44
45 l_defective_parts_rec CSP_REPAIR_PO_GRP.defective_parts_rec_type;
46 l_defective_parts_tbl CSP_REPAIR_PO_GRP.defective_parts_tbl_Type;
47
48 l_reservation_rec CSP_SCH_INT_PVT.RESERVATION_REC_TYP;
49 l_out_reservation_rec CSP_REPAIR_PO_GRP.out_reserve_rec_type;
50 l_out_reservation_tbl CSP_REPAIR_PO_GRP.out_reserve_tbl_type;
51 x_reservation_id NUMBER;
52
53 l_header_rec CSP_PARTS_REQUIREMENT.header_rec_type;
54 l_line_rec CSP_PARTS_REQUIREMENT.Line_rec_type;
55 l_lines_tbl CSP_PARTS_REQUIREMENT.Line_Tbl_type;
56 l_repair_po_header_id NUMBER ;
57
58 L_repair_supplier_org_id NUMBER;
59 L_VENDOR_NAME Varchar2(240);
60 L_SOURCE_ORGANIZATION_NAME Varchar2(240);
61 L_REPAIR_ORGANIZATION_NAME Varchar2(240);
62 L_DEST_ORGANIZATION_NAME Varchar2(240);
63 ln_count NUMBER;
64 X_item_number VARCHAR2(40);
65
66 X_PRIMARY_UOM_CODE VARCHAR2(3);
67 l_REVISION_QTY_CONTROL_CODE NUMBER;
68 l_REVISION VARCHAR2(3);
69 X_ITEM_DESCRIPTION VARCHAR2(240);
70 l_ship_to_location_id NUMBER;
71 l_shipping_method_code VARCHAR2(30);
72 l_supplier_org_location_id NUMBER;
73
74 l_MEANING VARCHAR2(80);
75 l_LOOKUP_CODE NUMBER;
76 P_NOTE_ID NUMBER;
77 P_NOTE LONG;
78 X_Rowid VARCHAR2(2000);
79 X_document_id NUMBER;
80 X_media_id NUMBER;
81
82 x_repair_po_header_id NUMBER;
83 x_order_header_id NUMBER;
84 x_requisition_number VARCHAR2(20);
85
86 x_requisition_line_id NUMBER;
87 x_order_line_id NUMBER;
88 x_line_num NUMBER;
89 x_poreq_line_reservation_id NUMBER;
90 x_repair_po_line_id NUMBER;
91 l_repair_po_line_id NUMBER;
92
93 BEGIN
94
95 SAVEPOINT CREATE_REPAIR_PO_PUB;
96
97 /*----------- Initialize Message List ------------ */
98 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
99 FND_MSG_PUB.initialize;
100 END IF;
101
102 /*----------- Standard call to check for call compatibility --------- */
103 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
104 p_api_version,
105 l_api_name,
106 G_PKG_NAME) THEN
107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
108 END IF;
109
110 /* ------- Initialize Return Status --------- */
111 x_return_status := FND_API.G_RET_STS_SUCCESS;
112
113 l_defective_parts_tbl := p_defective_parts_tbl;
114
115 /* -------- User and Login Information -------- */
116 SELECT Sysdate INTO l_today FROM dual;
117 l_user_id := fnd_global.user_id;
118 l_login_id := fnd_global.login_id;
119
120 /* --------- Start Create Reservations for Defective Parts passed ----------- */
121 FOR I IN 1..l_defective_parts_tbl.COUNT
122 LOOP
123 l_defective_parts_rec := l_defective_parts_tbl(I);
124
125 l_reservation_rec.need_by_date := P_need_by_date;
126 l_reservation_rec.organization_id := P_source_organization_id; -- Defective Warehouse
127 l_reservation_rec.item_id := l_defective_parts_rec.defective_item_id;
128 l_reservation_rec.quantity_needed := l_defective_parts_rec.defective_quantity;
129 l_reservation_rec.sub_inventory_code := Null;
130 l_reservation_rec.line_id := Null;
131
132 If l_defective_parts_rec.defective_quantity <= 0 then
133 FND_MESSAGE.SET_NAME ('CSP','CSP_INVALID_DEFECTIVE_QUANTITY');
134 FND_MSG_PUB.ADD;
135 RAISE EXCP_USER_DEFINED;
136 End if;
137
138 CSP_REPAIR_PO_GRP.GET_ITEM_DETAILS
139 (P_source_organization_id
140 ,l_defective_parts_rec.defective_item_id
141 ,x_item_number
142 ,x_item_description
143 ,x_primary_uom_code
144 ,x_return_status
145 ,x_msg_data
146 ,x_msg_count
147 );
148 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
149 L_SOURCE_ORGANIZATION_NAME := GET_ORGANIZATION_NAME(P_source_organization_id);
150 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_DEFECTITEM_AT_DEFECTORG');
151 FND_MESSAGE.SET_TOKEN ('DEFECTIVE_ORG_NAME', L_SOURCE_ORGANIZATION_NAME,TRUE);
152 FND_MSG_PUB.ADD;
153 RAISE EXCP_USER_DEFINED;
154 END IF;
155
156 CSP_REPAIR_PO_GRP.GET_ITEM_DETAILS
157 (P_repair_supplier_org_id
158 ,l_defective_parts_rec.defective_item_id
159 ,x_item_number
160 ,x_item_description
161 ,x_primary_uom_code
162 ,x_return_status
163 ,x_msg_data
164 ,x_msg_count
165 );
166 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
167 L_REPAIR_ORGANIZATION_NAME := GET_ORGANIZATION_NAME(P_repair_supplier_org_id);
168 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_DEFECTITEM_AT_REPAIRORG');
169 FND_MESSAGE.SET_TOKEN ('REPAIR_SUPPLIER_ORG_NAME', L_REPAIR_ORGANIZATION_NAME,TRUE);
170 FND_MSG_PUB.ADD;
171 RAISE EXCP_USER_DEFINED;
172 END IF;
173
174 l_reservation_rec.item_uom_code := X_PRIMARY_UOM_CODE;
175
176 x_reservation_id := csp_sch_int_pvt.create_reservation(
177 p_reservation_parts => l_reservation_rec,
178 x_return_status => l_return_status,
179 x_msg_data => l_msg_data
180 );
181
182 IF x_reservation_id = 0 OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
183 RAISE EXCP_USER_DEFINED;
184 Elsif l_return_status = FND_API.G_RET_STS_SUCCESS THEN
185
186 /* This is for Insert the Reservation_id into CSP_REPAIR_PO_LINES table
187 all the reservation made for defective parts
188 */
189
190 l_out_reservation_rec.need_by_date := l_reservation_rec.need_by_date;
191 l_out_reservation_rec.organization_id := l_reservation_rec.organization_id; ---- Defective Warehouse
192 l_out_reservation_rec.item_id := l_reservation_rec.item_id; ---- Defective Item
193 l_out_reservation_rec.quantity_needed := l_reservation_rec.quantity_needed;
194 l_out_reservation_rec.sub_inventory_code := Null;
195 l_out_reservation_rec.line_id := Null;
196 l_out_reservation_rec.item_uom_code := l_reservation_rec.item_uom_code;
197
198 l_out_reservation_rec.reservation_id := x_reservation_id;
199
200 l_out_reservation_tbl(I) := l_out_reservation_rec;
201
202 If L_SOURCE_ORGANIZATION_NAME is null then
203 L_SOURCE_ORGANIZATION_NAME := GET_ORGANIZATION_NAME(P_source_organization_id);
204 End if;
205
206 If p_note_id is null then
207 P_NOTE:= 'ITEM '||
208 'DESCRIPTION '||
209 'QUANTITY '||
210 'ORGANIZATION '||
211 fnd_global.newline||
212 x_item_number||' '||
213 x_item_description||' '||
214 l_reservation_rec.quantity_needed||' '||
215 L_SOURCE_ORGANIZATION_NAME;
216 Else
217 P_NOTE:= P_NOTE||
218 fnd_global.newline||
219 x_item_number||' '||
220 x_item_description||' '||
221 l_reservation_rec.quantity_needed||' '||
222 L_SOURCE_ORGANIZATION_NAME;
223 End if;
224
225 End if;
226 END LOOP;
227
228 ----- End Create Reservations for Defective Parts passed -----------
229
230
231 ----- Start Create PO_REQUISITION for the Repair to Item -----------
232 --------------------------------------------
233 /*Validate the Repair supplier*/
234 --------------------------------------------
235 Begin
236 Select VENDOR_NAME
237 into l_VENDOR_NAME
238 from po_vendors
239 where vendor_id = P_repair_supplier_id
240 --and nvl(start_date_active,sysdate) <= nvl(end_date_active,sysdate);
241 and NVL(START_DATE_ACTIVE,SYSDATE) <= SYSDATE
242 and NVL(END_DATE_ACTIVE,SYSDATE) >= SYSDATE;
243 Exception
244 When No_Data_Found then
245 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_VALID_REPAIR_SUPPLIER');
246 FND_MESSAGE.SET_TOKEN ('REPAIR_SUPPLIER_ID',to_char(P_repair_supplier_id), TRUE);
247 FND_MSG_PUB.ADD;
248 RAISE EXCP_USER_DEFINED;
249 End;
250
251 ---------------------------------------------------------------
252 /* Get Repair supplier Organization Id based on Vendor Id */
253 ---------------------------------------------------------------
254 Begin
255 Select organization_id
256 into L_repair_supplier_org_id ----> P_repair_supplier_org_id
257 from hr_organization_information
258 where ORG_INFORMATION_CONTEXT = 'Customer/Supplier Association'
259 and ORG_INFORMATION3 = P_repair_supplier_id; ---> 1159 (Vendor Id parameter)
260 Exception
261 When No_Data_Found then
262 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_REPAIR_SUPPLIER_ORG');
263 FND_MESSAGE.SET_TOKEN ('REPAIR_SUPPLIER_NAME', l_VENDOR_NAME, TRUE);
264 --(OR) No organization is linked with the repair supplier Id.
265 FND_MSG_PUB.ADD;
266 RAISE EXCP_USER_DEFINED;
267 End;
268
269 /*
270 Begin
271 SELECT LOCATION_ID
272 INTO l_supplier_org_location_id
273 FROM HR_ORGANIZATION_UNITS
274 WHERE ORGANIZATION_ID = L_repair_supplier_org_id;
275 Exception
276 When no_data_found then
277 l_supplier_org_location_id := Null;
278 End;
279 */
280
281 /*
282 -------------------------------------------------------
283 Get Vendor Id based on Repair supplier Organization Id
284 -------------------------------------------------------
285 Begin
286 select a.vendor_id
287 from po_vendors a, hr_organization_information b
288 where b.organization_id = 3201 --> P_repair_supplier_org_id (Repair supplier Org Id parameter)
289 and a.vendor_id = b.ORG_INFORMATION3
290 and b.ORG_INFORMATION_CONTEXT = 'Customer/Supplier Association'
291 and NVL(a.START_DATE_ACTIVE,SYSDATE) <= SYSDATE
292 and NVL(a.END_DATE_ACTIVE,SYSDATE) >= SYSDATE;
293 Exception
294 When no_data_found then
295 FND_MESSAGE.SET_NAME ('CSP','CSP_MISSING_PARAMETERS');
296 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'Repair Supplier ID cannot be null', TRUE);
297 FND_MSG_PUB.ADD;
298 RAISE EXCP_USER_DEFINED;
299 End;
300 */
301
302 --------------------------------------------------
303 /* VALIDATION for P_repair_program parameter */
304 --------------------------------------------------
305
306 Begin
307 Select MEANING, LOOKUP_CODE
308 into l_MEANING, l_LOOKUP_CODE
309 from mfg_lookups
310 Where LOOKUP_TYPE = 'INV_REPAIR_PROGRAMS' --'MRP_REPAIR_PROGRAM_DEFINITIONS'
311 and ENABLED_FLAG = 'Y'
312 and NVL(START_DATE_ACTIVE,SYSDATE) <= SYSDATE
313 and NVL(END_DATE_ACTIVE,SYSDATE) >= SYSDATE
314 and lookup_code = p_repair_program;
315 Exception
316 When no_data_found then
317 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_VALID_REPAIR_PROGRAM');
318 FND_MSG_PUB.ADD;
319 RAISE EXCP_USER_DEFINED;
320 End;
321
322 select csp_repair_po_headers_s1.nextval
323 into l_repair_po_header_id
324 from dual;
325
326 /* In "CSP_PARTS_ORDER.PROCESS_PURCHASE_REQ" API inserting into
327 "PO_REQUISITIONS_INTERFACE_ALL" table SOURCE_TYPE_CODE as "VENDOR".
328 So this item must be Purchasing_enabled in both Repair supplier org and destination org. */
329
330 L_REPAIR_ORGANIZATION_NAME := GET_ORGANIZATION_NAME(P_repair_supplier_org_id);
331
332 If L_REPAIR_ORGANIZATION_NAME is null then
333 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_REPAIR_SUPPLIER_ORG');
334 FND_MESSAGE.SET_TOKEN ('REPAIR_SUPPLIER_NAME', l_VENDOR_NAME, TRUE);
335 --(OR) No organization is linked with the repair supplier Id.
336 FND_MSG_PUB.ADD;
337 RAISE EXCP_USER_DEFINED;
338
339 Elsif L_REPAIR_ORGANIZATION_NAME is not null then
340 CSP_REPAIR_PO_GRP.GET_ITEM_DETAILS
341 (P_repair_supplier_org_id
342 ,P_repair_to_item_id
343 ,x_item_number
344 ,x_item_description
345 ,x_primary_uom_code
346 ,x_return_status
347 ,x_msg_data
348 ,x_msg_count
349 );
350 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
351 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_REPAIRITEM_AT_REPAIRORG');
352 FND_MESSAGE.SET_TOKEN ('REPAIR_SUPPLIER_ORG_NAME', L_REPAIR_ORGANIZATION_NAME,TRUE);
353 FND_MSG_PUB.ADD;
354 RAISE EXCP_USER_DEFINED;
355 END IF;
356
357 Begin
358 SELECT LOCATION_ID
359 INTO l_supplier_org_location_id
360 FROM HR_ORGANIZATION_UNITS
361 WHERE ORGANIZATION_ID = L_repair_supplier_org_id;
362 Exception
363 When no_data_found then
364 --l_supplier_org_location_id := Null;
365 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_SHIPFROM_LOCATION_ID');
366 FND_MESSAGE.SET_TOKEN ('REPAIR_SUPPLIER_ORG', L_REPAIR_ORGANIZATION_NAME, TRUE);
367 FND_MSG_PUB.ADD;
368 RAISE EXCP_USER_DEFINED;
369 End;
370
371 End if;
372
373 /* Find the Ship to Location for the Destination Warehouse and
374 a customer must be associated with this location in Purchasing*/
375
376 L_DEST_ORGANIZATION_NAME := GET_ORGANIZATION_NAME(P_dest_organization_id);
377 If L_DEST_ORGANIZATION_NAME is null then
378 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_VALID_DEST_ORG');
379 FND_MESSAGE.SET_TOKEN ('DEST_ORG_ID', to_char(P_dest_organization_id), TRUE);
380 FND_MSG_PUB.ADD;
381 RAISE EXCP_USER_DEFINED;
382 Elsif L_DEST_ORGANIZATION_NAME is not null then
383
384 CSP_REPAIR_PO_GRP.GET_ITEM_DETAILS
385 (P_dest_organization_id
386 ,P_repair_to_item_id
387 ,x_item_number
388 ,x_item_description
389 ,x_primary_uom_code
390 ,x_return_status
391 ,x_msg_data
392 ,x_msg_count
393 );
394 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
395 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_REPAIRITEM_AT_DESTORG');
396 FND_MESSAGE.SET_TOKEN ('DEST_ORG_NAME', L_DEST_ORGANIZATION_NAME,TRUE);
397 FND_MSG_PUB.ADD;
398 RAISE EXCP_USER_DEFINED;
399 END IF;
400
401 Begin
402 SELECT LOCATION_ID
403 INTO l_ship_to_location_id
404 FROM HR_ORGANIZATION_UNITS
405 WHERE ORGANIZATION_ID = P_dest_organization_id;
406 Exception
407 When no_data_found then
408 --l_ship_to_location_id := Null;
409 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_SHIPTO_LOCATION_ID');
410 FND_MESSAGE.SET_TOKEN ('DESTINATION_ORG', L_DEST_ORGANIZATION_NAME, TRUE);
411 FND_MSG_PUB.ADD;
412 RAISE EXCP_USER_DEFINED;
413 End;
414 End if;
415 -------- No shipping network between Org's with Default shipping method assigned ---------
416
417 Begin
418 SELECT SHIP_METHOD
419 INTO l_shipping_method_code
420 FROM MTL_INTERORG_SHIP_METHODS
421 WHERE FROM_ORGANIZATION_ID = P_repair_supplier_org_id
422 AND TO_ORGANIZATION_ID = P_dest_organization_id
423 AND FROM_LOCATION_ID = l_supplier_org_location_id
424 AND TO_LOCATION_ID = l_ship_to_location_id
425 AND DEFAULT_FLAG = 1;
426 Exception
427 When no_data_found then
428 L_REPAIR_ORGANIZATION_NAME := GET_ORGANIZATION_NAME(P_repair_supplier_org_id);
429 L_DEST_ORGANIZATION_NAME := GET_ORGANIZATION_NAME(P_dest_organization_id);
430 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_DEFAULT_SHIPPING_METHOD');
431 FND_MESSAGE.SET_TOKEN ('FROM_ORG', L_REPAIR_ORGANIZATION_NAME, TRUE);
432 FND_MESSAGE.SET_TOKEN ('TO_ORG', L_DEST_ORGANIZATION_NAME, TRUE);
433 FND_MSG_PUB.ADD;
434 RAISE EXCP_USER_DEFINED;
435 End;
436
437 If nvl(P_quantity,0) <= 0 then
438 FND_MESSAGE.SET_NAME ('CSP','CSP_INVALID_REPAIRPO_NEED_QTY');
439 FND_MSG_PUB.ADD;
440 RAISE EXCP_USER_DEFINED;
441 End if;
442
443 If trunc(nvl(P_need_by_date,sysdate)) <= trunc(sysdate) then
444 FND_MESSAGE.SET_NAME ('CSP','CSP_INVALID_REPAIRPO_NEED_DATE');
445 FND_MSG_PUB.ADD;
446 RAISE EXCP_USER_DEFINED;
447 End if;
448
449 l_header_rec.ship_to_location_id := l_ship_to_location_id;
450 l_header_rec.need_by_date := P_need_by_date;
451 l_header_rec.dest_organization_id := P_dest_organization_id;
452 l_header_Rec.requirement_header_id := l_repair_po_header_id;
453 l_header_rec.operation := csp_parts_order.G_OPR_CREATE; -- 'CREATE';
454
455 l_line_rec.inventory_item_id := P_repair_to_item_id;
456 l_line_rec.item_description := X_ITEM_DESCRIPTION;
457 l_line_rec.unit_of_measure := X_PRIMARY_UOM_CODE;
458
459 l_line_rec.shipping_method_code := l_shipping_method_code;
460 l_line_rec.ordered_quantity := P_quantity;
461
462 --l_line_rec.dest_subinventory := Null;
463
464 /* This value is needed for Iternal requisition only */
465
466 l_line_rec.source_organization_id := P_repair_supplier_org_id;
467 --l_line_rec.source_location_id := l_supplier_org_location_id;
468
469 /*Add these parameters in the CSP_PARTS_REQUIREMENT.Header_Rec_Type and pass the repair_supplier_id to this parameter */
470
471 l_header_rec.suggested_vendor_id := P_repair_supplier_id;
472 l_header_rec.SUGGESTED_VENDOR_NAME := l_VENDOR_NAME;
473
474 l_lines_tbl(1) := l_line_rec;
475
476 ---------------------------------------------------------------------
477 /*
478 Call to Create PO Requisition.
479 This API Inserts record into PO_REQUISITIONS_INTERFACE_ALL table
480 */
481 ---------------------------------------------------------------------
482
483 /* REQUISITION_TYPE is passed as "PURCHASE" in the follwoing API.
484 but this is not in valid values such as BLANKET,PLANNED,SCHEDULED and STANDARD
485 */
486
487 --dbms_output.put_line('First l_header_rec.requisition_header_id '||l_header_rec.requisition_header_id);
488
489 l_header_rec.called_from := 'REPAIR_EXECUTION';
490 l_header_rec.justification := l_LOOKUP_CODE ||' - '||l_MEANING;
491 l_header_rec.note_to_buyer := l_LOOKUP_CODE ||' - '||l_MEANING;
492
493 /*
494 select po_notes_s.nextval
495 into p_note_id
496 from dual;
497
498 Insert into PO_NOTES
499 (
500 PO_NOTE_ID
501 ,LAST_UPDATE_DATE
502 ,LAST_UPDATED_BY
503 ,LAST_UPDATE_LOGIN
504 ,CREATION_DATE
505 ,CREATED_BY
506 ,TITLE
507 ,USAGE_ID
508 ,NOTE_TYPE
509 ,START_DATE_ACTIVE
510 ,END_DATE_ACTIVE
511 ,REQUEST_ID
512 ,PROGRAM_APPLICATION_ID
513 ,PROGRAM_ID
514 ,PROGRAM_UPDATE_DATE
515 ,DOCUMENT_ID
516 ,APP_SOURCE_VERSION
517 ,NOTE
518 )
519 VALUES
520 (
521 p_note_id
522 ,SYSDATE
523 ,l_user_id
524 ,l_login_id
525 ,SYSDATE
526 ,l_user_id
527 ,'REPAIR AND RETURN: DEFECTIVE PARTS DETAILS'
528 ,3 -- 'Note to Buyer'
529 ,'S'
530 ,SYSDATE
531 ,NULL
532 ,NULL
533 ,NULL
534 ,NULL
535 ,NULL
536 ,NULL --p_document_id
537 ,'1.0'
538 ,p_note
539 );
540 */
541 fnd_documents_pkg.Insert_Row
542 (X_Rowid => X_Rowid,
543 X_document_id => X_document_id,
544 X_creation_date => SYSDATE,
545 X_created_by => l_user_id,
546 X_last_update_date => SYSDATE,
547 X_last_updated_by => l_user_id,
548 X_last_update_login => l_login_id,
549 X_datatype_id => 2, -- Longtext
550 X_category_id => 34, -- To Buyer
551 X_security_type => 4, -- None
552 X_security_id => NULL,
553 X_publish_flag => 'Y',
554 X_image_type => NULL,
555 X_storage_type => NULL, -- 1
556 X_usage_type => 'O', -- 'S'(Standard)
557 X_start_date_active => SYSDATE,
558 X_end_date_active => NULL,
559 X_request_id => NULL,
560 X_program_application_id => NULL,
561 X_program_id => NULL,
562 X_program_update_date => NULL,
563 X_language => USERENV('LANG'),
564 X_description => 'Repair Purchase Order Defective Parts Details: '||L_REPAIR_PO_HEADER_ID,
565 X_file_name => NULL,
566 X_media_id => X_media_id,
567 X_Attribute_Category => NULL,
568 X_Attribute1 => NULL,
569 X_Attribute2 => NULL,
570 X_Attribute3 => NULL,
571 X_Attribute4 => NULL,
572 X_Attribute5 => NULL,
573 X_Attribute6 => NULL,
574 X_Attribute7 => NULL,
575 X_Attribute8 => NULL,
576 X_Attribute9 => NULL,
577 X_Attribute10 => NULL,
578 X_Attribute11 => NULL,
579 X_Attribute12 => NULL,
580 X_Attribute13 => NULL,
581 X_Attribute14 => NULL,
582 X_Attribute15 => NULL,
583 X_create_doc => 'N');
584
585 INSERT INTO fnd_documents_long_text
586 (MEDIA_ID, LONG_TEXT)
587 VALUES(X_media_id, p_note);
588
589 l_header_rec.NOTE1_ID := Null; --p_note_id;
590 l_header_rec.NOTE1_TITLE := 'Repair Purchase Order Defective Parts Details: '||L_REPAIR_PO_HEADER_ID;
591
592 CSP_PARTS_ORDER.process_purchase_req
593 (P_API_VERSION => 1.0
594 ,P_INIT_MSG_LIST => 'F'
595 ,P_COMMIT => 'F'
596 ,px_header_rec => l_header_rec
597 ,px_line_table => l_lines_tbl
598 ,x_return_status => l_return_status
599 ,x_msg_count => l_msg_count
600 ,x_msg_data => l_msg_data
601 );
602
603 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
604 RAISE FND_API.G_EXC_ERROR;
605 End if;
606
607 /* Out values from the header record (l_header_rec) of PO Requisition Call */
608
609 x_repair_po_header_id := l_header_Rec.requirement_header_id;
610 x_requisition_header_id := l_header_rec.requisition_header_id;
611 x_order_header_id := l_header_rec.order_header_id;
612 x_requisition_number := l_header_rec.requisition_number;
613
614 --dbms_output.put_line('second l_header_rec.requisition_header_id '||l_header_rec.requisition_header_id);
615
616 /* Out values from the lines table (l_lines_tbl) of PO Requisition Call */
617
618 -- This Loop may not be necessary because there is always a single requisition line
619 FOR I IN 1..l_lines_tbl.COUNT
620 LOOP
621 l_line_rec := l_lines_tbl(I);
622 --l_line_rec := l_lines_tbl(1);
623
624 x_requisition_line_id := l_line_rec.requisition_line_id;
625 x_order_line_id := l_line_rec.order_line_id;
626 x_line_num := l_line_rec.line_num;
627 x_poreq_line_reservation_id := l_line_rec.reservation_id;
628 x_repair_po_line_id := l_line_rec.requirement_line_id;
629
630 /* Insert into CSP_REPAIR_PO_HEADERS table the Requisition Header Id with the Item details */
631
632 INSERT INTO CSP_REPAIR_PO_HEADERS
633 (REPAIR_PO_HEADER_ID
634 ,REQUISITION_HEADER_ID
635 ,PURCHASE_ORDER_HEADER_ID
636 ,INTERNAL_ORDER_HEADER_ID
637 ,WIP_ID
638 ,STATUS
639 ,INVENTORY_ITEM_ID
640 ,QUANTITY
641 ,DEST_ORGANIZATION_ID
642 ,NEED_BY_DATE
643 ,REQUISITION_NUMBER
644 ,REQUISITION_LINE_ID
645 ,ORDER_LINE_ID
646 ,LINE_NUM
647 ,POREQ_LINE_RESERVATION_ID
648 ,POREQ_LINE_ID
649 ,ERROR_MESSAGE
650 ,REPAIR_PROGRAM
651 ,PO_NUMBER
652 ,REPAIR_SUPPLIER_ID
653 ,REPAIR_SUPPLIER_ORG_ID
654 ,RECEIVED_QTY
655 --,SCRAP_QTY
656 --,ADJUSTED_QTY
657 )
658 VALUES
659 (
660 x_repair_po_header_id,
661 x_requisition_header_id , -- (+ 1 is stored in PO_REQUISITIONS_INTERFACE_ALL)
662 NULL,
663 NULL,
664 NULL,
665 '1',
666 l_line_rec.inventory_item_id,
667 l_line_rec.ordered_quantity,
668 l_header_rec.dest_organization_id,
669 l_header_rec.need_by_date,
670 x_requisition_number, --( REQ_NUMBER_SEGMENT1 from PO_REQUISITIONS_INTERFACE_ALL)
671 x_requisition_line_id,
672 x_order_line_id, ---- NULL
673 x_line_num, ---- NULL
674 x_poreq_line_reservation_id,---- NULL
675 x_repair_po_line_id, ---- NULL
676 NULL,
677 P_repair_program,
678 NULL,--PO_NUMBER
679 P_repair_supplier_id, --repair_supplier_id
680 P_repair_supplier_org_id, --Use "L_repair_supplier_org_id" if only P_repair_supplier_id is passed
681 NULL--received_qty
682 --NULL,--scrap_qty
683 --NULL--adjusted_qty
684 );
685 END LOOP;
686
687 --COMMIT;
688
689 /* Insert into CSP_REPAIR_PO_LINES table all the reservation made for defective parts */
690
691 FOR I IN 1..l_out_reservation_tbl.COUNT
692 LOOP
693 l_out_reservation_rec := l_out_reservation_tbl(I);
694
695 select csp_repair_po_lines_s1.nextval
696 into l_repair_po_line_id
697 from dual;
698
699 INSERT INTO CSP_REPAIR_PO_LINES
700 (
701 REPAIR_PO_LINE_ID
702 ,REPAIR_PO_HEADER_ID
703 ,DEFECTIVE_ORGANIZATION_ID
704 ,INVENTORY_ITEM_ID
705 ,QUANTITY
706 ,RESERVATION_ID
707 )
708 VALUES
709 (
710 l_repair_po_line_id,
711 x_repair_po_header_id,
712 l_out_reservation_rec.organization_id, ------- Defective Warehouse
713 l_out_reservation_rec.item_id, -------- Defective Item
714 l_out_reservation_rec.quantity_needed, -------- Defective Item Qty
715 l_out_reservation_rec.reservation_id -------- Reservation Id for the Defective Item
716 --P_need_by_date -------- Same as the need by date of Repair to item
717 );
718 END LOOP;
719 /*
720 IF (p_commit = FND_API.G_TRUE) THEN
721 COMMIT;
722 END IF;
723 */
724
725 IF FND_API.to_Boolean( p_commit ) THEN
726 COMMIT WORK;
727 END IF;
728
729
730 EXCEPTION
731 WHEN EXCP_USER_DEFINED THEN
732 Rollback to CREATE_REPAIR_PO_PUB;
733 x_return_status := FND_API.G_RET_STS_ERROR;
734 fnd_msg_pub.count_and_get
735 (p_count => x_msg_count
736 ,p_data => x_msg_data);
737 WHEN FND_API.G_EXC_ERROR THEN
738 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
739 P_API_NAME => L_API_NAME
740 ,P_PKG_NAME => G_PKG_NAME
741 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
742 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
743 ,X_MSG_COUNT => X_MSG_COUNT
744 ,X_MSG_DATA => X_MSG_DATA
745 ,X_RETURN_STATUS => X_RETURN_STATUS);
746 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
747 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
748 P_API_NAME => L_API_NAME
749 ,P_PKG_NAME => G_PKG_NAME
750 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
751 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
752 ,X_MSG_COUNT => X_MSG_COUNT
753 ,X_MSG_DATA => X_MSG_DATA
754 ,X_RETURN_STATUS => X_RETURN_STATUS);
755 WHEN OTHERS THEN
756 Rollback to CREATE_REPAIR_PO_PUB;
757 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
758 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
759 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
760 FND_MSG_PUB.ADD;
761 fnd_msg_pub.count_and_get
762 (p_count => x_msg_count
763 ,p_data => x_msg_data);
764 x_return_status := FND_API.G_RET_STS_ERROR;
765 END;
766
767 FUNCTION GET_ORGANIZATION_NAME
768 (P_dest_organization_id NUMBER
769 ) return VARCHAR2 IS
770 L_DEST_ORGANIZATION_NAME VARCHAR2(240);
771
772 Cursor org_cur(P_dest_organization_id Number) is
773 Select haou.name
774 from hr_all_organization_units haou
775 where haou.organization_id = p_dest_organization_id;
776 BEGIN
777
778 OPEN org_cur(P_dest_organization_id);
779 LOOP
780 FETCH org_cur INTO L_DEST_ORGANIZATION_NAME;
781 EXIT WHEN org_cur%NOTFOUND;
782 END LOOP;
783 CLOSE org_cur;
784
785 return L_DEST_ORGANIZATION_NAME;
786 EXCEPTION
787 WHEN OTHERS THEN
788 L_DEST_ORGANIZATION_NAME := NULL;
789 return L_DEST_ORGANIZATION_NAME;
790 END;
791
792 PROCEDURE GET_ITEM_DETAILS
793 (P_organization_id IN NUMBER
794 ,P_inventory_item_id IN NUMBER
795 ,x_item_number OUT NOCOPY VARCHAR2
796 ,x_item_description OUT NOCOPY VARCHAR2
797 ,x_primary_uom_code OUT NOCOPY VARCHAR2
798 ,x_return_status OUT NOCOPY VARCHAR2
799 ,x_msg_data OUT NOCOPY VARCHAR2
800 ,x_msg_count OUT NOCOPY NUMBER) IS
801
802 Cursor item_cur (P_organization_id Number,P_inventory_item_id Number) is
803 Select MSIK.concatenated_segments item_number,
804 MSIK.description item_description,
805 MSIK.primary_uom_code
806 From mtl_system_items_kfv MSIK
807 Where MSIK.organization_id = P_organization_id
808 and MSIK.inventory_item_id = P_inventory_item_id
809 and sysdate between nvl(MSIK.start_date_active,sysdate)
810 and nvl(MSIK.end_date_active,sysdate);
811
812 l_api_name VARCHAR2(60) := 'CSP_REPAIR_PO_GRP.GET_ITEM_DETAILS';
813
814 BEGIN
815 x_return_status := FND_API.G_RET_STS_SUCCESS;
816 OPEN item_cur(P_organization_id,P_inventory_item_id);
817 LOOP
818 FETCH item_cur INTO x_item_number,x_item_description,x_primary_uom_code;
819 EXIT WHEN item_cur%NOTFOUND;
820 END LOOP;
821 CLOSE item_cur;
822
823 EXCEPTION
824 WHEN OTHERS THEN
825 FND_MESSAGE.SET_NAME('CSP','CSP_UNEXPECTED_EXEC_ERRORS');
826 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
827 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
828 FND_MSG_PUB.ADD;
829 fnd_msg_pub.count_and_get
830 (p_count => x_msg_count
831 ,p_data => x_msg_data);
832 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
833 return;
834 END;
835
836
837 Procedure CREATE_CSP_SNAP_LOG IS
838
839 lv_dummy1 VARCHAR2(2000);
840 lv_dummy2 VARCHAR2(2000);
841 lv_inv_schema VARCHAR2(32);
842 lv_retval BOOLEAN;
843 v_applsys_schema VARCHAR2(200);
844
845 FUNCTION GET_SCHEMA_NAME( p_apps_id IN NUMBER)
846 RETURN VARCHAR2 IS
847 lv_schema VARCHAR2(30);
848 lv_prod_short_name VARCHAR2(30);
849 lv_retval boolean;
850 lv_dummy1 varchar2(32);
851 lv_dummy2 varchar2(32);
852 lv_is_new_ts_mode VARCHAR2(10);
853 BEGIN
854
855 ad_tspace_util.is_new_ts_mode(lv_is_new_ts_mode);
856 IF(upper(lv_is_new_ts_mode) = 'N') THEN
857 SELECT a.oracle_username
858 INTO lv_schema
859 FROM FND_ORACLE_USERID a,
860 FND_PRODUCT_INSTALLATIONS b
861 WHERE a.oracle_id = b.oracle_id
862 AND b.application_id = p_apps_id;
863
864 ELSE
865 lv_prod_short_name := AD_TSPACE_UTIL.get_product_short_name(p_apps_id);
866 lv_retval := FND_INSTALLATION.GET_APP_INFO (lv_prod_short_name, lv_dummy1, lv_dummy2, lv_schema);
867 END IF;
868
869 RETURN lv_schema;
870
871 EXCEPTION
872 WHEN OTHERS THEN
873 raise_application_error(-20001, 'Error getting the Schema : ' || sqlerrm);
874 END GET_SCHEMA_NAME;
875
876 PROCEDURE CREATE_SNAP_LOG( p_schema in VARCHAR2,
877 p_table in VARCHAR2,
878 p_applsys_schema IN VARCHAR2)
879 IS
880 v_sql_stmt VARCHAR2(6000);
881 BEGIN
882
883 v_sql_stmt:=
884 ' CREATE SNAPSHOT LOG ON '||p_schema ||'.'||p_table||' WITH ROWID ' ;
885
886 ad_ddl.do_ddl( applsys_schema => p_applsys_schema,
887 application_short_name => p_schema,
888 statement_type => AD_DDL.CREATE_TABLE,
889 statement => v_sql_stmt,
890 object_name => p_table);
891
892 EXCEPTION
893 WHEN OTHERS THEN
894
895 IF SQLCODE IN (-12000) THEN
896 /*Snapshot Log already EXISTS*/
897 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Snapshot Log on ' ||p_table||' already exists...');
898
899 ELSIF SQLCODE IN (-00942) THEN
900 /*Base Table does not exist*/
901 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Table '||p_table||' does not exist...');
902 ELSE
903 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM);
904 END IF;
905 END CREATE_SNAP_LOG; --create_snap Log
906
907
908 begin
909 lv_retval := FND_INSTALLATION.GET_APP_INFO(
910 'FND', lv_dummy1,lv_dummy2, v_applsys_schema);
911 lv_inv_schema := GET_SCHEMA_NAME(523);
912 CREATE_SNAP_LOG (lv_inv_schema,'CSP_REPAIR_PO_HEADERS',v_applsys_schema);
913 end CREATE_CSP_SNAP_LOG ;
914
915
916 Procedure create_csp_index (p_sql_stmt IN varchar2,p_object IN varchar2) is
917 lv_dummy1 VARCHAR2(2000);
918 lv_dummy2 VARCHAR2(2000);
919 lv_retval BOOLEAN;
920 v_applsys_schema VARCHAR2(200);
921 lv_prod_short_name VARCHAR2(30);
922
923 begin
924 lv_retval := FND_INSTALLATION.GET_APP_INFO(
925 'FND', lv_dummy1,lv_dummy2, v_applsys_schema);
926
927 lv_prod_short_name := AD_TSPACE_UTIL.get_product_short_name(523);
928 ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
929 application_short_name => lv_prod_short_name,
930 statement_type => AD_DDL.CREATE_INDEX,
931 statement => p_sql_stmt,
932 object_name => p_object);
933
934
935 EXCEPTION
936 WHEN OTHERS THEN
937 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM);
938 RAISE;
939 end create_csp_index;
940
941
942
943
944 END CSP_REPAIR_PO_GRP;