1 package PA_CI_SUPPLIER_UTILS AUTHID CURRENT_USER AS
2 --$Header: PASIUTLS.pls 120.0.12010000.9 2010/04/26 15:18:26 gboomina ship $
3
4 --TYPE PA_VC_1000_150 IS VARRAY(1000) OF VARCHAR2(150);
5 --TYPE PA_VC_1000_NUM IS VARRAY(1000) OF NUMBER;
6
7 PROCEDURE print_msg(p_msg varchar2);
8
9
10 PROCEDURE deleteSIrecord(P_CALLING_MODE IN varchar2
11 ,p_ROWID IN varchar2
12 ,P_CI_TRANSACTION_ID IN number
13 ,X_RETURN_STATUS IN OUT NOCOPY varchar2
14 ,x_MSG_DATA IN OUT NOCOPY varchar2
15 ,X_MSG_COUNT IN OUT NOCOPY number );
16
17 PROCEDURE validate_SI_record(
18 p_RECORD_STATUS IN VARCHAR2
19 ,p_CI_ID IN NUMBER
20 ,P_CALLING_MODE IN VARCHAR2
21 ,P_CI_STATUS IN VARCHAR2
22 ,P_ORG_ID IN VARCHAR2
23 ,x_VENDOR_ID IN OUT NOCOPY NUMBER
24 ,p_VENDOR_NAME IN VARCHAR2
25 ,x_PO_HEADER_ID IN OUT NOCOPY NUMBER
26 ,p_PO_NUMBER IN VARCHAR2
27 ,x_PO_LINE_ID IN OUT NOCOPY NUMBER
28 ,p_PO_LINE_NUM IN NUMBER
29 ,p_ADJUSTED_TRANSACTION_ID IN NUMBER
30 ,p_CURRENCY_CODE IN VARCHAR2
31 ,p_CHANGE_AMOUNT IN NUMBER
32 ,p_CHANGE_TYPE IN VARCHAR2
33 ,p_CHANGE_DESCRIPTION IN VARCHAR2
34 ,p_Task_Id IN VARCHAR2
35 ,p_Resource_List_Mem_Id IN VARCHAR2
36 ,p_From_Date IN VARCHAR2
37 ,p_To_Date IN VARCHAR2
38 ,p_Estimated_Cost IN VARCHAR2
39 ,p_Quoted_Cost IN VARCHAR2
40 ,p_Negotiated_Cost IN VARCHAR2
41 ,p_Burdened_cost IN VARCHAR2
42 ,p_revenue_override_rate IN varchar2
43 ,p_audit_history_number in number default null
44 ,p_current_audit_flag in varchar2 default 'Y'
45 ,p_Original_supp_trans_id in number default null
46 ,p_Source_supp_trans_id in number default null
47 ,p_Sup_ref_no in number default null
48 ,p_version_type in varchar2 default 'ALL'
49 ,p_ci_transaction_id IN NUMBER
50 ,x_return_status OUT NOCOPY VARCHAR2
51 ,x_error_msg_code OUT NOCOPY VARCHAR2
52 );
53
54 PROCEDURE validate_insert_SI(
55 p_ROWID IN OUT NOCOPY PA_VC_1000_150
56 ,p_RECORD_STATUS IN PA_VC_1000_150
57 ,p_CI_ID IN PA_VC_1000_150 --PA_VC_1000_NUM
58 ,p_CI_TYPE_ID IN PA_VC_1000_150
59 ,p_CI_IMPACT_ID IN PA_VC_1000_150
60 ,P_CALLING_MODE IN VARCHAR2
61 ,P_CI_STATUS IN PA_VC_1000_150
62 ,P_ORG_ID IN PA_VC_1000_150
63 ,x_VENDOR_ID IN PA_VC_1000_150 --PA_VC_1000_NUM
64 ,p_VENDOR_NAME IN PA_VC_1000_150
65 ,x_PO_HEADER_ID IN PA_VC_1000_150 --PA_VC_1000_NUM
66 ,p_PO_NUMBER IN PA_VC_1000_150
67 ,x_PO_LINE_ID IN PA_VC_1000_150 --PA_VC_1000_NUM
68 ,p_PO_LINE_NUM IN PA_VC_1000_150 --PA_VC_1000_NUM
69 ,p_ADJUSTED_TRANSACTION_ID IN PA_VC_1000_150 --PA_VC_1000_NUM
70 ,p_CURRENCY_CODE IN PA_VC_1000_150
71 ,p_CHANGE_AMOUNT IN PA_VC_1000_150 --PA_VC_1000_NUM
72 ,p_CHANGE_TYPE IN PA_VC_1000_150
73 ,p_CHANGE_DESCRIPTION IN PA_VC_1000_150
74 ,p_Task_Id IN PA_VC_1000_150
75 ,p_Resource_List_Mem_Id IN PA_VC_1000_150
76 ,p_From_Date IN PA_VC_1000_150
77 ,p_To_Date IN PA_VC_1000_150
78 ,p_Estimated_Cost IN PA_VC_1000_150
79 ,p_Quoted_Cost IN PA_VC_1000_150
80 ,p_Negotiated_Cost IN PA_VC_1000_150
81 ,p_Burdened_cost IN PA_VC_1000_150
82 ,p_revenue_override_rate IN PA_VC_1000_150
83 ,p_audit_history_number in number default null
84 ,p_current_audit_flag in varchar2 default 'Y'
85 ,p_Original_supp_trans_id in number default null
86 ,p_Source_supp_trans_id in number default null
87 ,p_Sup_ref_no in number default null
88 ,p_version_type in varchar2 default 'ALL'
89 -- gboomina modified for supplier cost 12.1.3 requirement - start
90 ,p_expenditure_type in varchar2
91 ,p_expenditure_org_id in number
92 ,p_change_reason_code in varchar2
93 ,p_quote_negotiation_reference in varchar2
94 ,p_need_by_date in varchar2
95 -- gboomina modified for supplier cost 12.1.3 requirement - end
96 ,p_ci_transaction_id IN OUT NOCOPY PA_VC_1000_150
97 ,p_RECORD_ID IN OUT NOCOPY PA_VC_1000_150
98 ,p_REC_RETURN_STATUS IN OUT NOCOPY PA_VC_1000_150
99 ,x_return_status IN OUT NOCOPY VARCHAR2
100 ,x_msg_data IN OUT NOCOPY VARCHAR2
101 ,x_msg_count IN OUT NOCOPY NUMBER
102 );
103 -- This API calls validate the supplier impact record and calls table
104 -- handler depending on the record status if record status is NEW it inserts
105 -- else it UPDATES pa_info_supplier_info table.
106 -- This API is called from PaSupplierImplementVORowImpl.java method for each row
107 PROCEDURE validateSI(p_ROWID IN OUT NOCOPY VARCHAR2
108 ,p_RECORD_STATUS IN VARCHAR2
109 ,p_CI_ID IN VARCHAR2
110 ,p_CI_TYPE_ID IN VARCHAR2
111 ,p_CI_IMPACT_ID IN VARCHAR2
112 ,P_CALLING_MODE IN VARCHAR2
113 ,P_ORG_ID IN VARCHAR2
114 ,p_VENDOR_NAME IN VARCHAR2
115 ,p_PO_NUMBER IN VARCHAR2
116 ,p_PO_LINE_NUM IN VARCHAR2
117 ,p_ADJUSTED_TRANSACTION_ID IN VARCHAR2
118 ,p_CURRENCY_CODE IN VARCHAR2
119 ,p_CHANGE_AMOUNT IN VARCHAR2
120 ,p_CHANGE_TYPE IN VARCHAR2
121 ,p_CHANGE_DESCRIPTION IN VARCHAR2
122 ,p_Task_Id IN VARCHAR2
123 ,p_Resource_List_Mem_Id IN VARCHAR2
124 ,p_From_Date IN VARCHAR2
125 ,p_To_Date IN VARCHAR2
126 ,p_Estimated_Cost IN VARCHAR2
127 ,p_Quoted_Cost IN VARCHAR2
128 ,p_Negotiated_Cost IN VARCHAR2
129 ,p_Burdened_cost IN VARCHAR2
130 ,p_Revenue IN VARCHAR2 default NULL
131 ,p_revenue_override_rate IN varchar2
132 ,p_audit_history_number in number default null
133 ,p_current_audit_flag in varchar2 default 'Y'
134 ,p_Original_supp_trans_id in number default null
135 ,p_Source_supp_trans_id in number default null
136 ,p_Sup_ref_no in number default null
137 ,p_version_type in varchar2 default 'ALL'
138 -- gboomina modified for supplier cost 12.1.3 requirement - start
139 ,p_expenditure_type in varchar2
140 ,p_expenditure_org_id in number
141 ,p_change_reason_code in varchar2
142 ,p_quote_negotiation_reference in varchar2
143 ,p_need_by_date in varchar2
144 -- gboomina modified for supplier cost 12.1.3 requirement - end
145 ,p_CI_TRANSACTION_ID IN OUT NOCOPY VARCHAR2
146 ,x_return_status IN OUT NOCOPY VARCHAR2
147 ,x_msg_data IN OUT NOCOPY VARCHAR2
148 ,x_msg_count IN OUT NOCOPY NUMBER
149 );
150
151 PROCEDURE Merge_suppliers
152 ( p_from_ci_item_id IN NUMBER
153 ,p_to_ci_item_id IN NUMBER
154 ,x_return_status OUT NOCOPY VARCHAR2
155 ,x_error_msg OUT NOCOPY VARCHAR2
156 );
157
158 PROCEDURE DELETE_IMPACT(p_ci_id IN NUMBER
159 ,x_return_status OUT NOCOPY VARCHAR2
160 ,x_msg_data OUT NOCOPY VARCHAR2
161 ,x_msg_count OUT NOCOPY NUMBER
162 );
163
164 PROCEDURE IS_SI_DELETE_OK(p_ci_id IN NUMBER
165 ,x_return_status OUT NOCOPY VARCHAR2
166 ,x_msg_data OUT NOCOPY VARCHAR2
167 ,x_msg_count OUT NOCOPY NUMBER
168 );
169
170 PROCEDURE GET_RESOURCE_LIST_ID(p_project_id IN NUMBER
171 ,x_res_list_id OUT NOCOPY number
172 ,x_return_status OUT NOCOPY VARCHAR2
173 ,x_msg_data OUT NOCOPY VARCHAR2
174 ,x_msg_count OUT NOCOPY NUMBER);
175
176 PROCEDURE GET_Original_Ci_ID(p_ci_id IN NUMBER
177 ,x_original_ci_id OUT NOCOPY number
178 ,x_return_status OUT NOCOPY VARCHAR2
179 ,x_msg_data OUT NOCOPY VARCHAR2
180 ,x_msg_count OUT NOCOPY NUMBER);
181
182
183 PROCEDURE GET_TOTAL_COST(p_ci_id IN NUMBER
184 ,x_total_cost OUT NOCOPY NUMBER
185 ,x_return_status OUT NOCOPY VARCHAR2
186 ,x_msg_data OUT NOCOPY VARCHAR2
187 ,x_msg_count OUT NOCOPY NUMBER);
188
189
190
191 FUNCTION IS_SI_DELETE_OK(p_ci_id IN NUMBER) return varchar2;
192
193 FUNCTION get_formated_amount(p_currency_code varchar2
194 ,p_amount number ) return varchar2 ;
195
196
197 -- gboomina added for 12.1.3 supplier cost requirement - start
198 G_PKG_NAME varchar2(30) := 'PA';
199 PROCEDURE delete_supplier_costs(
200 p_api_version IN NUMBER,
201 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
202 x_return_status OUT NOCOPY VARCHAR2,
203 x_msg_count OUT NOCOPY NUMBER,
204 x_msg_data OUT NOCOPY VARCHAR2,
205 p_ci_transaction_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE ,
206 p_ci_id IN NUMBER,
207 p_task_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
208 p_expenditure_type_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
209 p_rlmi_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
210 p_currency_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
211 p_resource_assignment_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
212 );
213
214 procedure save_supplier_costs(
215 p_api_version IN NUMBER
216 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
217 ,x_return_status IN OUT NOCOPY VARCHAR2
218 ,x_msg_data IN OUT NOCOPY VARCHAR2
219 ,x_msg_count IN OUT NOCOPY NUMBER
220 ,p_rowid_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
221 ,p_ci_transaction_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
222 ,p_ci_id IN NUMBER
223 ,p_ci_type_id IN NUMBER
224 ,p_ci_impact_id IN NUMBER DEFAULT NULL
225 ,p_calling_mode IN VARCHAR2
226 ,p_org_id IN NUMBER
227 ,p_version_type IN VARCHAR2 DEFAULT 'ALL'
228 ,p_record_status_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
229 ,p_vendor_name_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
230 ,p_vendor_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
231 ,p_po_number_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
232 ,p_po_header_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
233 ,p_po_line_num_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
234 ,p_po_line_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
235 ,p_currency_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
236 ,p_change_amount_tbl IN SYSTEM.PA_NUM_TBL_TYPE
237 ,p_change_type_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
238 ,p_change_description_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
239 ,p_task_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
240 ,p_resource_list_mem_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
241 ,p_adjusted_transaction_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
242 ,p_from_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE
243 ,p_to_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE
244 ,p_need_by_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE
245 ,p_estimated_cost_tbl IN SYSTEM.PA_NUM_TBL_TYPE
246 ,p_quoted_cost_tbl IN SYSTEM.PA_NUM_TBL_TYPE
247 ,p_negotiated_cost_tbl IN SYSTEM.PA_NUM_TBL_TYPE
248 ,p_burdened_cost_tbl IN SYSTEM.PA_NUM_TBL_TYPE
249 ,p_revenue_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT NULL
250 ,p_revenue_override_rate_tbl IN SYSTEM.PA_NUM_TBL_TYPE
251 ,p_audit_history_number_tbl IN SYSTEM.PA_NUM_TBL_TYPE
252 ,p_current_audit_flag_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
253 ,p_original_supp_trans_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
254 ,p_source_supp_trans_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
255 ,p_sup_ref_no_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT NULL
256 ,p_expenditure_type_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
257 ,p_expenditure_org_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
258 ,p_change_reason_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
259 ,p_quote_negotiation_ref_tbl IN SYSTEM.PA_VARCHAR2_150_TBL_TYPE
260 ,p_resource_assignment_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
261 );
262 -- gboomina added for 12.1.3 supplier cost requirement - end
263
264
265
266 END PA_CI_SUPPLIER_UTILS;
[Home] [Help]