1 PACKAGE CS_SR_DUP_CHK_PVT AUTHID CURRENT_USER AS
2 /* $Header: csdpchks.pls 115.3 2003/10/17 18:14:47 aneemuch noship $ */
3
4 TYPE CS_Extended_Attr_Rec IS RECORD
5 (
6 incident_type_id NUMBER,
7 sr_attribute_code VARCHAR2(30),
8 sr_attribute_value VARCHAR2(80)
9 );
10
11 TYPE CS_Extended_Attr_Tbl is TABLE OF CS_Extended_Attr_Rec INDEX BY BINARY_INTEGER;
12
13 TYPE Sr_Dupl_Rec IS RECORD
14 ( incident_id NUMBER,
15 reason_desc VARCHAR2 (2000) );
16
17 TYPE Sr_Dupl_Tbl is TABLE OF Sr_Dupl_Rec
18 INDEX BY BINARY_INTEGER;
19
20 TYPE SR_Dupl_Link_Rec IS RECORD
21 ( incident_id NUMBER,
22 incident_link_id NUMBER,
23 incident_link_number NUMBER,
24 reason_desc VARCHAR2 (2000) );
25
26 TYPE SR_Dupl_Link_Tbl is TABLE OF SR_Dupl_Link_Rec
27 INDEX BY BINARY_INTEGER;
28
29 TYPE CS_Incident_Address_Rec IS RECORD
30 (
31 INCIDENT_ADDRESS cs_incidents_all_b.incident_address%type,
32 INCIDENT_CITY cs_incidents_all_b.incident_city%type,
33 INCIDENT_STATE cs_incidents_all_b.incident_state%type,
34 INCIDENT_COUNTRY cs_incidents_all_b.incident_country%type,
35 INCIDENT_PROVINCE cs_incidents_all_b.incident_province%type,
36 INCIDENT_POSTAL_CODE cs_incidents_all_b.incident_postal_code%type,
37 INCIDENT_COUNTY cs_incidents_all_b.incident_county%type
38 );
39
40 G_PKG_NAME VARCHAR2(30) := 'TEST_AN_CS_SR_DUP_CHK_PVT';
41
42 PROCEDURE Duplicate_Check
43 (
44 p_api_version IN NUMBER,
45 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
46 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
47 p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full,
48 p_incident_id IN NUMBER,
49 p_incident_type_id IN NUMBER,
50 p_customer_product_id IN NUMBER,
51 p_instance_serial_number IN VARCHAR2,
52 p_current_serial_number IN VARCHAR2,
53 p_inv_item_serial_number IN VARCHAR2,
54 p_customer_id IN NUMBER,
55 p_inventory_item_id IN NUMBER,
56 p_cs_extended_attr IN cs_extended_attr_tbl,
57 p_incident_address IN cs_incident_address_rec,
58 x_duplicate_flag OUT NOCOPY VARCHAR2,
59 x_sr_dupl_rec OUT NOCOPY Sr_Dupl_Tbl,
60 x_dup_found_at OUT NOCOPY VARCHAR2,
61 x_return_status OUT NOCOPY VARCHAR2,
62 x_msg_count OUT NOCOPY NUMBER,
63 x_msg_data OUT NOCOPY VARCHAR2
64 );
65
66
67 PROCEDURE Check_EA_Duplicate_Setup
68 (
69 p_incident_id IN NUMBER,
70 p_incident_type_id IN NUMBER,
71 p_cs_extended_attr IN cs_extended_attr_tbl,
72 p_incident_address IN cs_incident_address_rec,
73 p_ea_attr_dup_flag IN OUT NOCOPY varchar2,
74 p_cs_ea_dup_rec OUT NOCOPY sr_dupl_tbl,
75 p_ea_ia_dup OUT NOCOPY VARCHAR2,
76 p_ea_ea_dup OUT NOCOPY VARCHAR2,
77 p_return_status OUT NOCOPY VARCHAR2
78 );
79
80 PROCEDURE Perform_EA_Duplicate
81 (
82 p_incident_id IN NUMBER,
83 p_incident_type_id IN NUMBER,
84 p_cs_extended_attr IN cs_extended_attr_tbl,
85 p_incident_address IN cs_incident_address_rec,
86 p_ea_attr_dup_flag IN OUT NOCOPY varchar2,
87 p_cs_ea_dup_rec OUT NOCOPY sr_dupl_tbl,
88 p_ea_ia_dup OUT NOCOPY VARCHAR2,
89 p_ea_ea_dup OUT NOCOPY VARCHAR2,
90 p_return_status OUT NOCOPY VARCHAR2
91 );
92
93
94 PROCEDURE Perform_Dup_on_SR_field
95 ( p_customer_product_id IN NUMBER,
96 p_customer_id IN NUMBER,
97 p_inventory_item_id IN NUMBER,
98 p_instance_serial_number IN VARCHAR2,
99 p_current_serial_number IN VARCHAR2,
100 p_inv_item_serial_number IN VARCHAR2,
101 p_incident_id IN NUMBER,
102 p_cs_sr_dup_rec IN OUT NOCOPY SR_DUPL_TBL,
103 p_cs_sr_dup_flag IN OUT NOCOPY VARCHAR2,
104 p_dup_from IN OUT NOCOPY NUMBER,
105 p_return_status OUT NOCOPY VARCHAR2
106 );
107
108
109 PROCEDURE Check_SR_Instance_Dup
110 (
111 p_customer_product_id IN NUMBER,
112 p_incident_id IN NUMBER,
113 p_cs_sr_dup_link_rec IN OUT NOCOPY SR_Dupl_Link_Tbl,
114 p_cs_sr_dup_flag IN OUT NOCOPY VARCHAR2,
115 p_return_status OUT NOCOPY VARCHAR2
116 );
117
118
119 PROCEDURE Check_SR_SerialNum_Dup
120 (
121 p_instance_serial_number IN VARCHAR2,
122 p_current_serial_number IN VARCHAR2,
123 p_inv_item_serial_number IN VARCHAR2,
124 p_incident_id IN NUMBER,
125 p_cs_sr_dup_link_rec IN OUT NOCOPY SR_Dupl_Link_Tbl,
126 p_cs_sr_dup_flag IN OUT NOCOPY VARCHAR2,
127 p_return_status OUT NOCOPY VARCHAR2
128 );
129
130
131 PROCEDURE Check_SR_CustProd_Dup
132 (
133 p_customer_id IN NUMBER,
134 p_inventory_item_id IN NUMBER,
135 p_incident_id IN NUMBER,
136 p_cs_sr_dup_link_rec IN OUT NOCOPY SR_Dupl_Link_Tbl,
137 p_cs_sr_dup_flag IN OUT NOCOPY VARCHAR2,
138 p_return_status OUT NOCOPY VARCHAR2
139 );
140
141
142 PROCEDURE Check_SR_CustProdSerial_Dup
143 (
144 p_customer_id IN NUMBER,
145 p_inventory_item_id IN NUMBER,
146 p_instance_serial_number IN VARCHAR2,
147 p_current_serial_number IN VARCHAR2,
148 p_inv_item_serial_number IN VARCHAR2,
149 p_incident_id IN NUMBER,
150 p_cs_sr_dup_link_rec IN OUT NOCOPY SR_Dupl_Link_Tbl,
151 p_cs_sr_dup_flag IN OUT NOCOPY VARCHAR2,
152 p_return_status OUT NOCOPY VARCHAR2
153 );
154
155
156 PROCEDURE Construct_Unique_list_dup_sr
157 (
158 p_cs_ea_dup_rec IN Sr_Dupl_Tbl,
159 p_ea_attr_dup_flag IN VARCHAR2,
160 p_cs_sr_dup_rec IN Sr_Dupl_Tbl,
161 p_cs_sr_dup_flag IN VARCHAR2,
162 p_dup_from IN NUMBER,
163 p_ea_ea_dup IN VARCHAR2,
164 p_ea_ia_dup IN VARCHAR2,
165 p_sr_dup_rec IN OUT NOCOPY Sr_Dupl_Tbl,
166 p_duplicate_flag IN OUT NOCOPY VARCHAR2,
167 p_return_status OUT NOCOPY VARCHAR2
168 );
169
170
171 PROCEDURE Check_Dup_SR_Link
172 (
173 p_dup_found_tbl IN Sr_Dupl_Link_Tbl,
174 p_dup_tbl IN OUT NOCOPY Sr_Dupl_Tbl,
175 p_return_status OUT NOCOPY VARCHAR2
176 );
177
178
179 FUNCTION Check_if_already_in_list
180 (
181 p_dup_tbl IN Sr_Dupl_Tbl,
182 p_sr_link_id IN NUMBER
183 ) return varchar2;
184
185
186 FUNCTION Get_Dup_Message
187 (
188 p_lookup_code IN VARCHAR2
189 ) return varchar2;
190
191
192 PROCEDURE CALCULATE_DUPLICATE_TIME_FRAME
193 (
194 p_incident_type_id IN NUMBER,
195 p_duplicate_time_frame OUT NOCOPY DATE
196 );
197
198
199 PROCEDURE CALCULATE_DUPLICATE_TIME_FRAME
200 ( p_duplicate_time_frame OUT NOCOPY DATE);
201
202
203 END CS_SR_DUP_CHK_PVT;