DBA Data[Home] [Help]

PACKAGE BODY: APPS.CSP_REPAIR_PO_GRP

Source


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