1 PACKAGE CSD_REPAIR_MANAGER_UTIL as
2 /* $Header: csdurpms.pls 120.2.12020000.2 2013/03/26 06:27:33 subhat ship $ */
3 -- Start of Comments
4 -- Package name : CSD_REPAIR_MANAGER_UTIL
5 -- Purpose : This package will be used as utility package for repair manager portal
6 --
7 --
8 -- History : 15/07/2009, Created by Sudheer Bhat
9 -- History :
10 -- History :
11 -- NOTE :
12 -- End of Comments
13
14 g_pkg_name constant varchar2(30) := 'CSD_REPAIR_MANAGER_UTIL';
15
16 TYPE sr_rec_type is record (
17 sr_account_id number,
18 sr_party_id number,
19 sr_incident_id number,
20 sr_incident_summary varchar2(80),
21 sr_bill_to_site_use_id number,
22 sr_ship_to_site_use_id number,
23 sr_type_id number,
24 sr_status_id number,
25 sr_severity_id number,
26 sr_urgency_id number,
27 sr_owner_id number,
28 create_sr_flag varchar2(1)
29 );
30 type sr_tbl_type is table of sr_rec_type index by binary_integer;
31
32 /******************************************************************************/
33 /* Function Name: get_item_quality_threshold */
34 /* Description: Returns the applicable quality threshold thats set up. */
35 /* @param p_inventory_item_id */
36 /* @param p_organization_id */
37 /******************************************************************************/
38 FUNCTION get_item_quality_threshold(p_inventory_item_id IN NUMBER,
39 p_organization_id IN NUMBER,
40 p_item_revision IN VARCHAR2) RETURN NUMBER;
41
42 /******************************************************************************/
43 /* Function Name: get_aging_threshold */
44 /* Description: Returns the applicable aging threshold thats set up. */
45 /* @param p_inventory_item_id */
46 /* @param p_organization_id */
47 /******************************************************************************/
48 FUNCTION get_aging_threshold(p_organization_id IN NUMBER,
49 p_inventory_item_id IN NUMBER,
50 p_repair_type_id IN NUMBER,
51 p_flow_status_id IN NUMBER,
52 p_revision IN VARCHAR2,
53 p_repair_line_id IN NUMBER) RETURN NUMBER;
54
55 /******************************************************************************/
56 /* Procedure Name: mass_update_repair_orders */
57 /* Description: This procedure provides a utility to mass update the repair */
58 /* orders. The procedure treats each logical action as a seperate*/
59 /* transaction. */
60 /******************************************************************************/
61 PROCEDURE mass_update_repair_orders(p_api_version IN NUMBER DEFAULT 1.0,
62 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
63 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
64 p_repair_line_ids IN JTF_NUMBER_TABLE,
65 p_from_ro_status IN JTF_NUMBER_TABLE,
66 p_orig_ro_type_ids IN JTF_NUMBER_TABLE,
67 p_ro_obj_ver_nos IN JTF_NUMBER_TABLE,
68 p_to_ro_status IN NUMBER DEFAULT NULL,
69 p_ro_type_id IN NUMBER DEFAULT NULL,
70 p_ro_owner_id IN NUMBER DEFAULT NULL,
71 p_ro_org_id IN NUMBER DEFAULT NULL,
72 p_ro_priority_id IN NUMBER DEFAULT NULL,
73 p_ro_escalation_code IN VARCHAR2 DEFAULT NULL,
74 p_note_type IN VARCHAR2 DEFAULT NULL,
75 p_note_visibility IN VARCHAR2 DEFAULT NULL,
76 p_attach_title IN VARCHAR2 DEFAULT NULL,
77 p_attach_descr IN VARCHAR2 DEFAULT NULL,
78 p_attach_cat_id IN NUMBER DEFAULT NULL,
79 p_attach_type IN VARCHAR2 DEFAULT NULL,
80 p_attach_file IN BLOB DEFAULT NULL,
81 p_attach_url IN VARCHAR2 DEFAULT NULL,
82 p_attach_text IN VARCHAR2 DEFAULT NULL,
83 p_file_name IN VARCHAR2 DEFAULT NULL,
84 p_content_type IN VARCHAR2 DEFAULT NULL,
85 p_note_text IN VARCHAR2 DEFAULT NULL,
86 x_return_status OUT NOCOPY VARCHAR2,
87 x_msg_count OUT NOCOPY NUMBER,
88 x_msg_data OUT NOCOPY VARCHAR2,
89 l_error_messages_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_1000,
90 p_ro_promise_date IN DATE DEFAULT NULL
91 );
92 /******************************************************************************/
93 /* Procedure Name: mass_create_attachments */
94 /* Description: The api provides utility to create attachments for a set of */
95 /* repair orders. The API gets called from mass_update_repair_orders */
96 /******************************************************************************/
97
98 PROCEDURE mass_create_attachments(p_api_version IN NUMBER DEFAULT 1.0,
99 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
100 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
101 p_repair_line_ids IN JTF_NUMBER_TABLE,
102 p_attach_type IN VARCHAR2,
103 p_attach_cat_id IN NUMBER,
104 p_attach_descr IN VARCHAR2 DEFAULT NULL,
105 p_attach_title IN VARCHAR2,
106 p_file_input IN BLOB DEFAULT NULL,
107 p_url IN VARCHAR2 DEFAULT NULL,
108 p_text IN VARCHAR2 DEFAULT NULL,
109 p_file_name IN VARCHAR2 DEFAULT NULL,
110 p_content_type IN VARCHAR2 DEFAULT NULL,
111 x_return_status OUT NOCOPY VARCHAR2,
112 x_msg_count OUT NOCOPY NUMBER,
113 x_msg_data OUT NOCOPY VARCHAR2
114 );
115 /******************************************************************************/
116 /* Procedure Name: mass_create_repair_orders */
117 /* Description: This is a OAF wrapper for creation of SR and repair orders. */
118 /* OAF can call this with multiple records too. */
119 /******************************************************************************/
120
121 PROCEDURE mass_create_repair_orders(p_api_version IN NUMBER DEFAULT 1.0,
122 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
123 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
124 p_item_ids IN JTF_NUMBER_TABLE,
125 p_serial_numbers IN JTF_VARCHAR2_TABLE_100,
126 p_quantity IN JTF_NUMBER_TABLE,
127 p_uom_code IN JTF_VARCHAR2_TABLE_100,
128 p_external_ref IN JTF_VARCHAR2_TABLE_100,
129 p_lot_nums IN JTF_VARCHAR2_TABLE_100,
130 p_item_revisions IN JTF_VARCHAR2_TABLE_100,
131 p_repair_type_ids IN JTF_NUMBER_TABLE,
132 p_instance_ids IN JTF_NUMBER_TABLE,
133 p_serial_ctrl_flag IN JTF_NUMBER_TABLE,
134 p_rev_ctrl_flag IN JTF_NUMBER_TABLE,
135 p_ib_ctrl_flag IN JTF_VARCHAR2_TABLE_100,
136 p_party_id IN NUMBER,
137 p_account_id IN NUMBER,
138 x_return_status OUT NOCOPY VARCHAR2,
139 x_msg_count OUT NOCOPY NUMBER,
140 x_msg_data OUT NOCOPY VARCHAR2,
141 x_incident_id OUT NOCOPY NUMBER);
142 /******************************************************************************/
143 /* Procedure Name: mass_create_repair_orders_cp */
144 /* Description: The concurrent wrapper to process the records from */
145 /* csd_repairs_interface table. The API does minimal validation and then */
146 /* calls create_sr_repair_order in a loop. */
147 /******************************************************************************/
148 procedure mass_create_repair_orders_cp(errbuf out nocopy varchar2,
149 retcode out nocopy varchar2,
150 p_one_sr_per_group in varchar2 default 'Y',
151 p_group_id in number
152 );
153 /******************************************************************************/
154 /* Procedure Name: create_sr_repair_order */
155 /* Description: Creates a service request, and repair order. The API delegates*/
156 /* the call to private API's for creation of these entities. Upon creating*/
157 /* repair orders, the API will also enter default logistics line. */
158 /******************************************************************************/
159 procedure create_sr_repair_order(p_api_version IN NUMBER,
160 p_init_msg_list in varchar2 default fnd_api.g_false,
161 p_commit in varchar2 default fnd_api.g_false,
162 p_sr_rec in sr_rec_type,
163 p_repln_rec in OUT NOCOPY csd_repairs_pub.repln_rec_type,
164 p_rev_ctrl_flag in number,
165 p_serial_ctrl_flag in number,
166 p_ib_ctrl_flag in varchar2,
167 x_incident_id IN OUT NOCOPY number,
168 x_repair_line_id out nocopy number,
169 x_return_status out nocopy varchar2,
170 x_msg_count out nocopy number,
171 x_msg_data out nocopy varchar2,
172 p_external_reference in varchar2 default null,
173 p_lot_num in varchar2 default null
174 );
175 procedure update_external_reference
176 (p_external_reference in varchar2,
177 p_instance_id in number,
178 x_return_status OUT NOCOPY varchar2,
179 x_msg_count OUT NOCOPY number,
180 x_msg_data OUT NOCOPY varchar2);
181
182 /**
183 Aggregates the given string table type of JTF_VARCHAR2_TABLE.
184 The return string would be , delimited list of individual
185 entries in the table type. If the minimum DB version for
186 APPS becomes 11g use inbuilt STRAGGR method instead of this.
187 **/
188
189
190 FUNCTION string_aggregate(input IN JTF_VARCHAR2_TABLE_100)
191 RETURN VARCHAR2;
192
193 END CSD_REPAIR_MANAGER_UTIL;