1 PACKAGE OKE_DELIVERABLE_BILLING_PVT AUTHID CURRENT_USER AS
2 /* $Header: OKEVDVBS.pls 120.1 2005/10/14 16:55:51 ifilimon noship $ */
3 --
4 -- Name : Create_Billing_Event
5 -- Pre-reqs : None
6 -- Function : This procedure creates a billing event in PA
7 --
8 --
9 -- Parameters :
10 -- IN : P_Commit
11 -- P_Deliverable_ID
12 -- OUT : X_Event_ID
13 -- X_Event_Num
14 -- X_Return_Status
15 -- X_Msg_Count
16 -- X_Msg_Data
17 --
18 -- Returns : None
19 --
20 PROCEDURE Create_Billing_Event
21 ( P_Commit IN VARCHAR2
22 , P_Event_ID IN NUMBER
23 , X_Event_ID OUT NOCOPY NUMBER
24 , X_Event_Num OUT NOCOPY NUMBER
25 , X_Return_Status OUT NOCOPY VARCHAR2
26 , X_Msg_Count OUT NOCOPY NUMBER
27 , X_Msg_Data OUT NOCOPY VARCHAR2
28 );
29
30 PROCEDURE Create_Billing_Event
31 ( P_Commit IN VARCHAR2
32 , P_Event_Type IN VARCHAR2
33 , P_Event_Date IN DATE
34 , P_Project_ID IN NUMBER
35 , P_Task_ID IN NUMBER
36 , P_Organization_ID IN NUMBER
37 , P_Description IN VARCHAR2
38 , P_Unit_Price IN NUMBER
39 , P_Bill_Quantity IN NUMBER
40 , P_UOM_Code IN VARCHAR2
41 , P_Bill_Amount IN NUMBER
42 , P_Revenue_Amount IN NUMBER
43 , P_Item_ID IN NUMBER
44 , P_Inventory_Org_ID IN NUMBER
45 , P_Contract_Num IN VARCHAR2
46 , P_Order_Num IN VARCHAR2
47 , P_Line_Num IN VARCHAR2
48 , P_Chg_Request_Num IN VARCHAR2
49 , P_Bill_Of_Lading IN VARCHAR2
50 , P_Serial_Num IN VARCHAR2
51 , P_Fund_Ref1 IN VARCHAR2
52 , P_Fund_Ref2 IN VARCHAR2
53 , P_Fund_Ref3 IN VARCHAR2
54 , P_Event_Num_Reversed IN NUMBER
55 , P_Bill_Trans_Currency_Code IN VARCHAR2 DEFAULT NULL
56 , P_Bill_Trans_Bill_Amount IN NUMBER DEFAULT NULL
57 , P_Bill_Trans_rev_Amount IN NUMBER DEFAULT NULL
58 , P_Project_Currency_Code IN VARCHAR2 DEFAULT NULL
59 , P_Project_Rate_Type IN VARCHAR2 DEFAULT NULL
60 , P_Project_Rate_Date IN DATE DEFAULT NULL
61 , P_Project_Exchange_Rate IN NUMBER DEFAULT NULL
62 , P_Project_Inv_Rate_Date IN DATE DEFAULT NULL
63 , P_Project_Inv_Exchange_Rate IN NUMBER DEFAULT NULL
64 , P_Project_Bill_Amount IN NUMBER DEFAULT NULL
65 , P_Project_Rev_Rate_Date IN DATE DEFAULT NULL
66 , P_Project_Rev_Exchange_Rate IN NUMBER DEFAULT NULL
67 , P_Project_Revenue_Amount IN NUMBER DEFAULT NULL
68 , P_ProjFunc_Currency_Code IN VARCHAR2 DEFAULT NULL
69 , P_ProjFunc_Rate_Type IN VARCHAR2 DEFAULT NULL
70 , P_ProjFunc_Rate_Date IN DATE DEFAULT NULL
71 , P_ProjFunc_Exchange_Rate IN NUMBER DEFAULT NULL
72 , P_ProjFunc_Inv_Rate_Date IN DATE DEFAULT NULL
73 , P_ProjFunc_Inv_Exchange_Rate IN NUMBER DEFAULT NULL
74 , P_ProjFunc_Bill_Amount IN NUMBER DEFAULT NULL
75 , P_ProjFunc_Rev_Rate_Date IN DATE DEFAULT NULL
76 , P_Projfunc_Rev_Exchange_Rate IN NUMBER DEFAULT NULL
77 , P_ProjFunc_Revenue_Amount IN NUMBER DEFAULT NULL
78 , P_Funding_Rate_Type IN VARCHAR2 DEFAULT NULL
79 , P_Funding_Rate_Date IN DATE DEFAULT NULL
80 , P_Funding_Exchange_Rate IN NUMBER DEFAULT NULL
81 , P_Invproc_Currency_Code IN VARCHAR2 DEFAULT NULL
82 , P_Invproc_Rate_Type IN VARCHAR2 DEFAULT NULL
83 , P_Invproc_Rate_Date IN DATE DEFAULT NULL
84 , P_Invproc_Exchange_Rate IN NUMBER DEFAULT NULL
85 , P_Revproc_Currency_Code IN VARCHAR2 DEFAULT NULL
86 , P_Revproc_Rate_Type IN VARCHAR2 DEFAULT NULL
87 , P_Revproc_Rate_Date IN DATE DEFAULT NULL
88 , P_Revproc_Exchange_Rate IN NUMBER DEFAULT NULL
89 , P_Inv_Gen_Rejection_Code IN VARCHAR2 DEFAULT NULL
90 , X_Event_ID OUT NOCOPY NUMBER
91 , X_Event_Num OUT NOCOPY NUMBER
92 , X_Return_Status OUT NOCOPY VARCHAR2
93 , X_Msg_Count OUT NOCOPY NUMBER
94 , X_Msg_Data OUT NOCOPY VARCHAR2
95 );
96
97
98
99
100 --
101 -- Name : Update_Billing_Event
102 -- Pre-reqs : None
103 -- Function : This procedure updates a previously created
104 -- billing event in PA
105 --
106 --
107 -- Parameters :
108 -- IN : P_Commit
109 -- P_Deliverable_ID
110 -- OUT : X_Event_ID
111 -- X_Event_Num
112 -- X_Return_Status
113 -- X_Msg_Count
114 -- X_Msg_Data
115 --
116 -- Returns : None
117 --
118
119 PROCEDURE Update_Billing_Event
120 ( P_Commit IN VARCHAR2
121 , P_Deliverable_ID IN NUMBER
122 , P_Event_ID IN NUMBER
123 , P_Event_Type IN VARCHAR2
124 , P_Event_Date IN DATE
125 , P_Project_ID IN NUMBER
126 , P_Task_ID IN NUMBER
127 , P_Organization_ID IN NUMBER
128 , P_Description IN VARCHAR2
129 , P_Unit_Price IN NUMBER
130 , P_Bill_Quantity IN NUMBER
131 , P_UOM_Code IN VARCHAR2
132 , P_Bill_Amount IN NUMBER
133 , P_Revenue_Amount IN NUMBER
134 , P_Item_ID IN NUMBER
135 , P_Inventory_Org_ID IN NUMBER
136 , P_Contract_Num IN VARCHAR2
137 , P_Order_Num IN VARCHAR2
138 , P_Line_Num IN VARCHAR2
139 , P_Chg_Request_Num IN VARCHAR2
140 , P_Bill_Of_Lading IN VARCHAR2
141 , P_Serial_Num IN VARCHAR2
142 , P_Fund_Ref1 IN VARCHAR2
143 , P_Fund_Ref2 IN VARCHAR2
144 , P_Fund_Ref3 IN VARCHAR2
145 , P_Bill_Trans_Currency_Code IN VARCHAR2 DEFAULT NULL
146 , P_Bill_Trans_Bill_Amount IN NUMBER DEFAULT NULL
147 , P_Bill_Trans_rev_Amount IN NUMBER DEFAULT NULL
148 , P_Project_Currency_Code IN VARCHAR2 DEFAULT NULL
149 , P_Project_Rate_Type IN VARCHAR2 DEFAULT NULL
150 , P_Project_Rate_Date IN DATE DEFAULT NULL
151 , P_Project_Exchange_Rate IN NUMBER DEFAULT NULL
152 , P_Project_Inv_Rate_Date IN DATE DEFAULT NULL
153 , P_Project_Inv_Exchange_Rate IN NUMBER DEFAULT NULL
154 , P_Project_Bill_Amount IN NUMBER DEFAULT NULL
155 , P_Project_Rev_Rate_Date IN DATE DEFAULT NULL
156 , P_Project_Rev_Exchange_Rate IN NUMBER DEFAULT NULL
157 , P_Project_Revenue_Amount IN NUMBER DEFAULT NULL
158 , P_ProjFunc_Currency_Code IN VARCHAR2 DEFAULT NULL
159 , P_ProjFunc_Rate_Type IN VARCHAR2 DEFAULT NULL
160 , P_ProjFunc_Rate_Date IN DATE DEFAULT NULL
161 , P_ProjFunc_Exchange_Rate IN NUMBER DEFAULT NULL
162 , P_ProjFunc_Inv_Rate_Date IN DATE DEFAULT NULL
163 , P_ProjFunc_Inv_Exchange_Rate IN NUMBER DEFAULT NULL
164 , P_ProjFunc_Bill_Amount IN NUMBER DEFAULT NULL
165 , P_ProjFunc_Rev_Rate_Date IN DATE DEFAULT NULL
166 , P_Projfunc_Rev_Exchange_Rate IN NUMBER DEFAULT NULL
167 , P_ProjFunc_Revenue_Amount IN NUMBER DEFAULT NULL
168 , P_Funding_Rate_Type IN VARCHAR2 DEFAULT NULL
169 , P_Funding_Rate_Date IN DATE DEFAULT NULL
170 , P_Funding_Exchange_Rate IN NUMBER DEFAULT NULL
171 , P_Invproc_Currency_Code IN VARCHAR2 DEFAULT NULL
172 , P_Invproc_Rate_Type IN VARCHAR2 DEFAULT NULL
173 , P_Invproc_Rate_Date IN DATE DEFAULT NULL
174 , P_Invproc_Exchange_Rate IN NUMBER DEFAULT NULL
175 , P_Revproc_Currency_Code IN VARCHAR2 DEFAULT NULL
176 , P_Revproc_Rate_Type IN VARCHAR2 DEFAULT NULL
177 , P_Revproc_Rate_Date IN DATE DEFAULT NULL
178 , P_Revproc_Exchange_Rate IN NUMBER DEFAULT NULL
179 , P_Inv_Gen_Rejection_Code IN VARCHAR2 DEFAULT NULL
180 , X_Return_Status OUT NOCOPY VARCHAR2
181 , X_Msg_Count OUT NOCOPY NUMBER
182 , X_Msg_Data OUT NOCOPY VARCHAR2
183 );
184
185
186 PROCEDURE Insert_Billing_Info
187 ( P_Deliverable_Id IN NUMBER
188 , P_Billing_Event_Id IN NUMBER
189 , P_Pa_Event_Id IN NUMBER
190 , P_K_Header_Id IN NUMBER
191 , P_K_Line_Id IN NUMBER
192 , P_Bill_Event_Type IN VARCHAR2
193 , P_Bill_Event_Date IN DATE
194 , P_Bill_Item_Id IN NUMBER
195 , P_Bill_Line_Id IN NUMBER
196 , P_Bill_Chg_Req_Id IN NUMBER
197 , P_Bill_Project_Id IN NUMBER
198 , P_Bill_Task_Id IN NUMBER
199 , P_Bill_Organization_Id IN NUMBER
200 , P_Bill_Fund_Ref1 IN VARCHAR2
201 , P_Bill_Fund_Ref2 IN VARCHAR2
202 , P_Bill_Fund_Ref3 IN VARCHAR2
203 , P_Bill_Bill_Of_Lading IN VARCHAR2
204 , P_Bill_Serial_Num IN VARCHAR2
205 , P_Bill_Currency_Code IN VARCHAR2
206 , P_Bill_Rate_Type IN VARCHAR2
207 , P_Bill_Rate_Date IN DATE
208 , P_Bill_Exchange_Rate IN NUMBER
209 , P_Bill_Description IN VARCHAR2
210 , P_Bill_Quantity IN NUMBER
211 , P_Bill_Unit_Price IN NUMBER
212 , P_Revenue_Amount IN NUMBER
213 , P_Created_By IN NUMBER
214 , P_Creation_Date IN DATE
215 , P_LAST_UPDATED_BY IN NUMBER
216 , P_LAST_UPDATE_LOGIN IN NUMBER
217 , P_LAST_UPDATE_DATE IN DATE
218 );
219
220
221 PROCEDURE Update_Billing_Info
222 ( P_Deliverable_ID IN NUMBER
223 , P_Billing_Event_ID IN NUMBER
224 , P_Bill_Event_Type IN VARCHAR2
225 , P_Bill_Event_Date IN DATE
226 , P_Bill_Project_ID IN NUMBER
227 , P_Bill_Task_ID IN NUMBER
228 , P_Bill_Org_ID IN NUMBER
229 , P_Bill_Line_ID IN NUMBER
230 , P_Bill_Chg_Req_ID IN NUMBER
231 , P_Bill_Item_ID IN NUMBER
232 , P_Bill_Description IN VARCHAR2
233 , P_Bill_Unit_Price IN NUMBER
234 , P_Bill_Quantity IN NUMBER
235 , P_Bill_Currency_Code IN VARCHAR2
236 , P_Bill_Rate_Type IN VARCHAR2
237 , P_Bill_Rate_Date IN DATE
238 , P_Bill_Exchange_Rate IN NUMBER
239 , P_Revenue_Amount IN NUMBER
240 , P_Bill_Of_Lading IN VARCHAR2
241 , P_Bill_Serial_Num IN VARCHAR2
242 , P_Bill_Fund_Ref1 IN VARCHAR2
243 , P_Bill_Fund_Ref2 IN VARCHAR2
244 , P_Bill_Fund_Ref3 IN VARCHAR2
245 , P_LAST_UPDATED_BY IN NUMBER
246 , P_LAST_UPDATE_LOGIN IN NUMBER
247 , P_LAST_UPDATE_DATE IN DATE
248 );
249
250 PROCEDURE Delete_Billing_Info (
251 P_Billing_Event_ID IN NUMBER
252 );
253
254 PROCEDURE Populate_MC_Columns ( P_Event_ID IN NUMBER
255 , x_Bill_Trans_Currency_Code OUT NOCOPY VARCHAR2
256 , x_Bill_Trans_Bill_Amount OUT NOCOPY NUMBER
257 , x_Bill_Trans_rev_Amount OUT NOCOPY NUMBER
258 , x_Project_Currency_Code OUT NOCOPY VARCHAR2
259 , x_Project_Rate_Type OUT NOCOPY VARCHAR2
260 , x_Project_Rate_Date OUT NOCOPY DATE
261 , x_Project_Exchange_Rate OUT NOCOPY NUMBER
262 , x_Project_inv_Rate_Date OUT NOCOPY DATE
263 , x_Project_Inv_Exchange_Rate OUT NOCOPY NUMBER
264 , x_Project_Bill_Amount OUT NOCOPY NUMBER
265 , x_Project_Rev_Rate_Date OUT NOCOPY DATE
266 , x_Project_Rev_Exchange_Rate OUT NOCOPY NUMBER
267 , x_Project_Revenue_Amount OUT NOCOPY NUMBER
268 , x_ProjFunc_Currency_Code OUT NOCOPY VARCHAR2
269 , x_ProjFunc_Rate_Type OUT NOCOPY VARCHAR2
270 , x_ProjFunc_Rate_Date OUT NOCOPY DATE
271 , x_ProjFunc_Exchange_Rate OUT NOCOPY NUMBER
272 , x_ProjFunc_Inv_Rate_Date OUT NOCOPY DATE
273 , x_ProjFunc_Inv_Exchange_Rate OUT NOCOPY NUMBER
274 , x_ProjFunc_Bill_Amount OUT NOCOPY NUMBER
275 , x_ProjFunc_Rev_Rate_Date OUT NOCOPY DATE
276 , x_Projfunc_Rev_Exchange_Rate OUT NOCOPY NUMBER
277 , x_ProjFunc_Revenue_Amount OUT NOCOPY NUMBER
278 , x_Funding_Rate_Type OUT NOCOPY VARCHAR2
279 , x_Funding_Rate_Date OUT NOCOPY DATE
280 , x_Funding_Exchange_Rate OUT NOCOPY NUMBER
281 , x_Invproc_Currency_Code OUT NOCOPY VARCHAR2
282 , x_Invproc_Rate_Type OUT NOCOPY VARCHAR2
283 , x_Invproc_Rate_Date OUT NOCOPY DATE
284 , x_Invproc_Exchange_Rate OUT NOCOPY NUMBER
285 , x_Revproc_Currency_Code OUT NOCOPY VARCHAR2
286 , x_Revproc_Rate_Type OUT NOCOPY VARCHAR2
287 , x_Revproc_Rate_Date OUT NOCOPY DATE
288 , x_Revproc_Exchange_Rate OUT NOCOPY NUMBER
289 , x_Inv_Gen_Rejection_Code OUT NOCOPY VARCHAR2 );
290
291
292 PROCEDURE Lock_Billing_Info
293 ( P_Deliverable_ID IN NUMBER
294 , P_Billing_Event_ID IN NUMBER
295 , P_Bill_Event_Type IN VARCHAR2
296 , P_Bill_Event_Date IN DATE
297 , P_Bill_Project_ID IN NUMBER
298 , P_Bill_Task_ID IN NUMBER
299 , P_Bill_Org_ID IN NUMBER
300 , P_Bill_Line_ID IN NUMBER
301 , P_Bill_Chg_Req_ID IN NUMBER
302 , P_Bill_Item_ID IN NUMBER
303 , P_Bill_Description IN VARCHAR2
304 , P_Bill_Unit_Price IN NUMBER
305 , P_Bill_Quantity IN NUMBER
306 , P_Bill_Currency_Code IN VARCHAR2
307 , P_Bill_Rate_Type IN VARCHAR2
308 , P_Bill_Rate_Date IN DATE
309 , P_Bill_Exchange_Rate IN NUMBER
310 , P_Revenue_Amount IN NUMBER
311 , P_Bill_Of_Lading IN VARCHAR2
312 , P_Bill_Serial_Num IN VARCHAR2
313 , P_Bill_Fund_Ref1 IN VARCHAR2
314 , P_Bill_Fund_Ref2 IN VARCHAR2
315 , P_Bill_Fund_Ref3 IN VARCHAR2
316 );
317
318 FUNCTION Event_Level
319 ( P_Project_ID IN NUMBER
320 , P_Event_ID IN NUMBER
321 ) RETURN VARCHAR2;
322
323 END OKE_DELIVERABLE_BILLING_PVT;