[Home] [Help]
PACKAGE BODY: APPS.CSP_REPAIR_ORDER_PKG
Source
1 PACKAGE BODY csp_repair_order_pkg AS
2 /* $Header: cspgrorb.pls 120.0.12020000.5 2013/03/21 04:15:28 sunarasi noship $ */
3 -- Start of Comments
4 -- Package name : csp_repair_order_pkg
5 -- Purpose :
6 -- History :
7 -- 01-Mar-13, sunarasi
8 --
9 -- NOTE :
10 -- End of Comments
11 G_PKG_NAME CONSTANT VARCHAR2(30):='CSP_REPAIR_ORDER_PKG';
12 FUNCTION REPAIR_ORDER_DETAILS
13 (p_item_id NUMBER,
14 p_org_id NUMBER,
15 p_item_number VARCHAR2,
16 p_uom VARCHAR2,
17 p_quantity NUMBER,
18 p_subinv VARCHAR2) RETURN ref_cursor IS
19 the_cursor ref_cursor;
20 l_org_id number ;
21 l_dest_org_id number ;
22 l_item_id number ;
23 l_item_number VARCHAR2(240);
24 l_qty NUMBER;
25 l_uom VARCHAR2(30);
26 l_subinv_code varchar2(50) ;
27 rownumber number;
28 l_tab_page_name Varchar2(200);
29 l_Serviceable VARCHAR2(30);
30 l_repair_supplier_id NUMBER;
31 l_source_type VARCHAR2(30);
32 l_supplier_name VARCHAR2(240);
33 l_supplier_type VARCHAR2(240);
34
35 cursor destination_organization is
36 select misl.organization_id
37 from MRP_ITEM_SOURCING_LEVELS_V misl, csp_planning_parameters cpp
38 where cpp.organization_type = 'W'
39 and misl.organization_id = cpp.organization_id
40 and misl.assignment_set_id =cpp.defective_assignment_set_id
41 and misl.source_organization_id = l_org_id
42 and misl.inventory_item_id = l_item_id
43 and SOURCE_TYPE in (1,3)
44 and sourcing_level = (select min(sourcing_level) from MRP_ITEM_SOURCING_LEVELS_V
45 where organization_id = cpp.organization_id
46 and assignment_set_id = cpp.defective_assignment_set_id
47 and inventory_item_id = misl.inventory_item_id
48 and sourcing_level not in (2,9))
49 order by misl.rank;
50
51 CURSOR repair_suppliers_cur IS
52 select misl.source_type, misl.source_organization_id, misl.vendor_id
53 from MRP_ITEM_SOURCING_LEVELS_V misl, csp_planning_parameters cpp
54 where cpp.organization_id = l_dest_org_id
55 and misl.organization_id = cpp.organization_id
56 and misl.assignment_set_id =cpp.repair_assignment_set_id
57 and inventory_item_id = l_item_id
58 and SOURCE_TYPE in (1,3)
59 and sourcing_level = (select min(sourcing_level) from MRP_ITEM_SOURCING_LEVELS_V
60 where organization_id = l_dest_org_id
61 and assignment_set_id = cpp.repair_assignment_set_id
62 and inventory_item_id = l_item_id
63 and sourcing_level not in (2,9))
64 order by misl.rank;
65 BEGIN
66 l_item_id := p_item_id;
67 l_org_id := p_org_id;
68 l_item_number := p_item_number;
69 l_qty := p_quantity;
70 l_uom := p_uom;
71 l_subinv_code := p_subinv;
72
73 open destination_organization;
74 fetch destination_organization into l_dest_org_id;
75 close destination_organization;
76
77
78 -- Find repair supplier for the warehouse
79 FOR rsc IN repair_suppliers_cur LOOP
80 IF (rsc.source_type = 1) THEN
81 BEGIN
82 select serv_req_enabled_code
83 into l_Serviceable
84 from mtl_system_items
85 where inventory_item_id = l_item_id
86 and organization_id = rsc.source_organization_id;
87 EXCEPTION
88 when no_data_found then
89 null;
90 END;
91 IF l_serviceable = 'E' THEN
92 l_repair_supplier_id := rsc.source_organization_id;
93 l_source_type := 'REPAIR';
94 exit;
95 END IF;
96 ELSE
97 l_repair_supplier_id := rsc.vendor_id;
98 l_source_type := 'EXTREPAIR';
99 exit;
100 END IF;
101 END LOOP;
102 IF (l_Repair_Supplier_id IS NOT NULL) THEN
103 BEGIN
104 IF (l_source_type = 'REPAIR') THEN
105 select name
106 into l_supplier_name
107 from hr_all_organization_units
108 where organization_id = l_Repair_supplier_id;
109 ELSE
110 select vendor_name
111 into l_supplier_name
112 from po_vendors
113 where vendor_id = l_Repair_Supplier_id;
114 END IF;
115
116 select meaning into l_Supplier_type
117 from fnd_lookups
118 where lookup_type = 'CSP_NOTIF_DTL_SRC_TYPE'
119 and lookup_code = l_source_type;
120 EXCEPTION
121 WHEN NO_DATA_FOUND THEN
122 null;
123 END;
124 END IF;
125 --dbms_output.put_line(l_supplier_name);
126 open the_cursor FOR
127 select l_supplier_type,l_source_type,l_supplier_name,l_repair_supplier_id,l_dest_org_id,l_item_number,l_qty,l_uom,l_subinv_code,l_item_id,l_org_id
128 from DUAL;
129 RETURN the_cursor;
130 END;
131 FUNCTION create_repair_order
132 (p_item_id NUMBER,
133 p_org_id NUMBER,
134 p_dest_org_id NUMBER,
135 p_need_by_date DATE,
136 p_uom VARCHAR2,
137 p_qty NUMBER,
138 p_repair_supplier_id NUMBER) RETURN ORD_CURSOR is
139 l_header_rec csp_parts_requirement.header_rec_type;
140 L_LINE_TBL CSP_PARTS_REQUIREMENT.LINE_TBL_TYPE;
141 l_msg_data VARCHAR2(4000);
142 L_MSG_COUNT NUMBER;
143 l_return_status VARCHAR2(10);
144 L_ORDER_NUMBER NUMBER;
145 ORDER_CURSOR ORD_CURSOR;
146 X_ORDER_NUMBER NUMBER;
147 X_RET_STATUS NUMBER;
148 x_msg VARCHAR2(4000);
149 BEGIN
150 l_header_rec.dest_organization_id := p_dest_org_id;
151 l_header_Rec.need_by_date := p_need_by_date;
152 l_line_tbl(1).inventory_item_id := p_item_id;
153 l_line_tbl(1).quantity := p_qty;
154 l_line_tbl(1).ordered_quantity := p_qty;
155 l_line_Tbl(1).unit_of_measure := p_uom;
156 l_line_tbl(1).booked_flag := 'Y';
157 l_line_tbl(1).source_organization_id := p_org_id;
158
159 csp_parts_repair.create_orders( p_api_Version => 1.0,
160 p_init_msg_list => null,
161 p_commit => null,
162 px_header_rec => l_header_rec,
163 px_line_table => l_line_tbl,
164 p_repair_supplier_id => p_repair_supplier_id,
165 x_return_status => l_return_status,
166 x_msg_count => l_msg_count,
167 x_msg_data => l_msg_data
168 );
169
170 IF (l_return_status <> csp_pick_utils.Get_Ret_Sts_Success) THEN
171 IF nvl(l_msg_count,0) > 0 then
172 jtf_plsql_api.Get_Messages(
173 p_message_count =>l_msg_count,
174 x_msgs => l_msg_data);
175 x_ret_status := -1;
176 x_msg := l_msg_data;
177 ELSE
178 x_ret_status := -1;
179 x_msg := 'CSP_UNKNOWN_ERRORS';
180 END IF;
181 ELSE
182 csp_pick_utils.Issue_commit;
183 begin
184 select order_number
185 into l_order_number
186 from oe_order_headers_all
187 where header_id = l_header_rec.order_header_id;
188 exception
189 when no_Data_found then
190 l_order_number := null;
191 END;
192 x_order_number := l_order_number;
193 X_RET_STATUS := 0;
194 X_MSG := 'CSP_REPAIR_ORDER_CREATED';
195 END IF;
196 OPEN ORDER_CURSOR FOR
197 SELECT X_ORDER_NUMBER,X_RET_STATUS,X_MSG FROM DUAL;
198 RETURN order_cursor;
199 END;
200 END;