1 PACKAGE INV_TRANSACTION_FLOW_PVT AUTHID CURRENT_USER AS
2 /* $Header: INVICTFS.pls 115.5 2003/10/09 12:23:32 viberry noship $ */
3
4 TYPE TABLE_OF_NUMBERS IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
5
6
7 TYPE TRX_FLOW_LINE_REC IS RECORD (
8 HEADER_ID NUMBER,
9 LINE_NUMBER NUMBER,
10 FROM_ORG_ID NUMBER,
11 FROM_ORGANIZATION_ID NUMBER,
12 TO_ORG_ID NUMBER,
13 TO_ORGANIZATION_ID NUMBER,
14 ATTRIBUTE_CATEGORY VARCHAR2(30),
15 ATTRIBUTE1 VARCHAR2(150),
16 ATTRIBUTE2 VARCHAR2(150),
17 ATTRIBUTE3 VARCHAR2(150),
18 ATTRIBUTE4 VARCHAR2(150),
19 ATTRIBUTE5 VARCHAR2(150),
20 ATTRIBUTE6 VARCHAR2(150),
21 ATTRIBUTE7 VARCHAR2(150),
22 ATTRIBUTE8 VARCHAR2(150),
23 ATTRIBUTE9 VARCHAR2(150),
24 ATTRIBUTE10 VARCHAR2(150),
25 ATTRIBUTE11 VARCHAR2(150),
26 ATTRIBUTE12 VARCHAR2(150),
27 ATTRIBUTE13 VARCHAR2(150),
28 ATTRIBUTE14 VARCHAR2(150),
29 ATTRIBUTE15 VARCHAR2(150)
30 );
31 TYPE TRX_FLOW_LINES_TAB IS TABLE OF TRX_FLOW_LINE_REC INDEX BY BINARY_INTEGER;
32
33
34 /** Inserts the data into thr header table:mtl_transaction_flow_header.<br>
35 * @param p_header_id unique identifier for header table
36 * @param p_start_org_id organization_id of the start operating unit
37 * @param p_end_org_id organization_id of the end operating unit
38 * @param p_last_update_date Who column
39 * @param p_last_updated_by Who Column
40 * @param p_creation_date Who Column
41 * @param p_created_by Who Column
42 * @param p_last update login Who Column
43 * @param p_flow_type Indicated whether flow type is shipping or procuring
44 * @param p_organization_id The ship From/To Organization Id
45 * @param p_qualifier_code This indicates the qualifier code for the flow type.At present it can be null or Category
46 * @param p_qualifier_value_id This is the value of the qualifier code if selected as Category
47 * @param p_asset_item_pricing_option This gives the Asset pricing option as either PO or Transfer if flow type is procuring
48 * @param p_expense_item_pricing_option This gives the Expense pricing option as either PO or Transfer if flow type is procuring
49 * @param p_start_date The start date with time when the trx flow becomes active
50 * @param p_end_date The end date with time when the trx flow ceases to be active
51 * @param p_new_accounting_flag Indicates whether the user is going for the new accounting or old accounting.
52 <br>If flow is procuring then it should be new.
53 <br> FOR shipping if number of lines greater than 1 then new
54 * @param p_attribute_category Attribute context column
55 * @param p_attribute1 Attribute column
56 * @param p_attribute2 Attribute column
57 * @param p_attribute3 Attribute column
58 * @param p_attribute4 Attribute column
59 * @param p_attribute5 Attribute column
60 * @param p_attribute6 Attribute column
61 * @param p_attribute7 Attribute column
62 * @param p_attribute8 Attribute column
63 * @param p_attribute9 Attribute column
64 * @param p_attribute10 Attribute column
65 * @param p_attribute11 Attribute column
66 * @param p_attribute12 Attribute column
67 * @param p_attribute13 Attribute column
68 * @param p_attribute14 Attribute column
69 * @param p_attribute15 Attribute column
70 */
71 PROCEDURE Insert_Trx_Flow_Header (
72 P_Header_Id IN NUMBER,
73 P_Start_Org_Id IN NUMBER,
74 P_End_Org_Id IN NUMBER,
75 P_Last_Update_Date IN DATE,
76 P_Last_Updated_By IN NUMBER,
77 P_Creation_Date IN DATE,
78 P_Created_By IN NUMBER,
79 P_Last_Update_Login IN NUMBER,
80 P_Flow_Type IN NUMBER,
81 P_Organization_Id IN NUMBER,
82 P_Qualifier_Code IN NUMBER,
83 P_Qualifier_Value_Id IN NUMBER,
84 P_Asset_Item_Pricing_Option IN NUMBER,
85 P_Expense_Item_Pricing_Option IN NUMBER,
86 P_Start_Date IN DATE,
87 P_End_Date IN DATE,
88 P_New_Accounting_Flag IN VARCHAR2,
89 P_Attribute_Category IN VARCHAR2,
90 P_Attribute1 IN VARCHAR2,
91 P_Attribute2 IN VARCHAR2,
92 P_Attribute3 IN VARCHAR2,
93 P_Attribute4 IN VARCHAR2,
94 P_Attribute5 IN VARCHAR2,
95 P_Attribute6 IN VARCHAR2,
96 P_Attribute7 IN VARCHAR2,
97 P_Attribute8 IN VARCHAR2,
98 P_Attribute9 IN VARCHAR2,
99 P_Attribute10 IN VARCHAR2,
100 P_Attribute11 IN VARCHAR2,
101 P_Attribute12 IN VARCHAR2,
102 P_Attribute13 IN VARCHAR2,
103 P_Attribute14 IN VARCHAR2,
104 P_Attribute15 IN VARCHAR2
105 );
106
107
108
109
110 /** This is a Table Handler for the header block.<br>
111 <br>It will lock a row for update for the mtl_transaction_flow_headers.<br>
112 * @param x_row_id rowid for the table
113 * @param p_header_id unique identifier for header table
114 * @param p_start_org_id organization_id of the start operating unit
115 * @param p_end_org_id organization_id of the end operating unit
116 * @param p_last_update_date Who column
117 * @param p_last_updated_by Who Column
118 * @param p_creation_date Who Column
119 * @param p_created_by Who Column
120 * @param p_last update login Who Column
121 * @param p_flow_type Indicated whether flow type is shipping or procuring
122 * @param p_organization_id The ship From/To Organization Id
123 * @param p_qualifier_code This indicates the qualifier code for the flow type.At present it can be null or Category
124 * @param p_qualifier_value_id This is the value of the qualifier code if selected as Category
125 * @param p_asset_item_pricing_option This gives the Asset pricing option as either PO or Transfer if flow type is procuring
126 * @param p_expense_item_pricing_option This gives the Expense pricing option as either PO or Transfer if flow type is procuring
127 * @param p_start_date The start date with time when the trx flow becomes active
128 * @param p_end_date The end date with time when the trx flow ceases to be active
129 * @param p_new_accounting_flag Indicates whether the user is going for the new accounting or old accounting.
130 <br>If flow is procuring then it should be new.
131 <br> FOR shipping if number of lines greater than 1 then new
132 * @param p_attribute_category Attribute context column
133 * @param p_attribute1 Attribute column
134 * @param p_attribute2 Attribute column
135 * @param p_attribute3 Attribute column
136 * @param p_attribute4 Attribute column
137 * @param p_attribute5 Attribute column
138 * @param p_attribute6 Attribute column
139 * @param p_attribute7 Attribute column
140 * @param p_attribute8 Attribute column
141 * @param p_attribute9 Attribute column
142 * @param p_attribute10 Attribute column
143 * @param p_attribute11 Attribute column
144 * @param p_attribute12 Attribute column
145 * @param p_attribute13 Attribute column
146 * @param p_attribute14 Attribute column
147 * @param p_attribute15 Attribute column
148 */
149
150 PROCEDURE Lock_Trx_Flow_Header (
151 P_Header_Id IN NUMBER,
152 P_Start_Org_Id IN NUMBER,
153 P_End_Org_Id IN NUMBER,
154 P_Last_Update_Date IN DATE,
155 P_Last_Updated_By IN NUMBER,
156 P_Creation_Date IN DATE,
157 P_Created_By IN NUMBER,
158 P_Last_Update_Login IN NUMBER,
159 P_Flow_Type IN NUMBER,
160 P_Organization_Id IN NUMBER,
161 P_Qualifier_Code IN NUMBER,
162 P_Qualifier_Value_Id IN NUMBER,
163 P_Asset_Item_Pricing_Option IN NUMBER,
164 P_Expense_Item_Pricing_Option IN NUMBER,
165 P_Start_Date IN DATE,
166 P_End_Date IN DATE,
167 P_New_Accounting_Flag IN VARCHAR2,
168 P_Attribute_Category IN VARCHAR2,
169 P_Attribute1 IN VARCHAR2,
170 P_Attribute2 IN VARCHAR2,
171 P_Attribute3 IN VARCHAR2,
175 P_Attribute7 IN VARCHAR2,
172 P_Attribute4 IN VARCHAR2,
173 P_Attribute5 IN VARCHAR2,
174 P_Attribute6 IN VARCHAR2,
176 P_Attribute8 IN VARCHAR2,
177 P_Attribute9 IN VARCHAR2,
178 P_Attribute10 IN VARCHAR2,
179 P_Attribute11 IN VARCHAR2,
180 P_Attribute12 IN VARCHAR2,
181 P_Attribute13 IN VARCHAR2,
182 P_Attribute14 IN VARCHAR2,
183 P_Attribute15 IN VARCHAR2
184 );
185
186 /** This is a Table Handler for the header block.<br>
187 <br>It is for update of the mtl_transaction_flow_headers.only start,end dates and
188 <br> dff columns are updateable<br>
189 * @param p_header_id unique identifier for header table
190 * @param p_start_date The start date with time when the trx flow becomes active
191 * @param p_end_date The end date with time when the trx flow ceases to be active
192 * @param p_last_update_date Who column
193 * @param p_last_updated_by Who Column
194 * @param p_last update login Who Column
195 * @param p_attribute_category Attribute context column
196 * @param p_attribute1 Attribute column
197 * @param p_attribute2 Attribute column
198 * @param p_attribute3 Attribute column
199 * @param p_attribute4 Attribute column
200 * @param p_attribute5 Attribute column
201 * @param p_attribute6 Attribute column
202 * @param p_attribute7 Attribute column
203 * @param p_attribute8 Attribute column
204 * @param p_attribute9 Attribute column
205 * @param p_attribute10 Attribute column
206 * @param p_attribute11 Attribute column
207 * @param p_attribute12 Attribute column
208 * @param p_attribute13 Attribute column
209 * @param p_attribute14 Attribute column
210 * @param p_attribute15 Attribute column
211 */
212
213 PROCEDURE Update_Trx_Flow_Header(
214 P_Header_Id IN NUMBER,
215 P_Last_Update_Date IN DATE,
216 P_Last_Updated_By IN NUMBER,
217 P_Last_Update_Login IN NUMBER,
218 P_Start_Date IN DATE,
219 P_End_Date IN DATE,
220 P_Attribute_Category IN VARCHAR2,
221 P_Attribute1 IN VARCHAR2,
222 P_Attribute2 IN VARCHAR2,
223 P_Attribute3 IN VARCHAR2,
224 P_Attribute4 IN VARCHAR2,
225 P_Attribute5 IN VARCHAR2,
226 P_Attribute6 IN VARCHAR2,
227 P_Attribute7 IN VARCHAR2,
228 P_Attribute8 IN VARCHAR2,
229 P_Attribute9 IN VARCHAR2,
230 P_Attribute10 IN VARCHAR2,
231 P_Attribute11 IN VARCHAR2,
232 P_Attribute12 IN VARCHAR2,
233 P_Attribute13 IN VARCHAR2,
234 P_Attribute14 IN VARCHAR2,
235 P_Attribute15 IN VARCHAR2
236 );
237
238 /** This is a Table Handler for the lines block
239 <br>It will insert a row into the mtl_transaction_flow_lines.<br>
240 * @param p_header_id identifier for lines table that gives the join condition for the
241 <br>lines table to join to header table
242 * @param p_line_number The line number
243 * @param p_from_org_id Organization id for the from operating unit
244 * @param p_from_organization_id Organization Id for the from organization that is under the from operating unit.
245 * @param p_to_org_id Organization id for the to operating unit
246 * @param p_to_organization_id Organization Id for the to organization that is under the to operating unit.
247 * @param p_last_update_date Who column
248 * @param p_last_updated_by Who Column
249 * @param p_creation_date Who Column
250 * @param p_created_by Who Column
251 * @param p_last update login Who Column
252 * @param p_attribute_category Attribute context column
253 * @param p_attribute1 Attribute column
254 * @param p_attribute2 Attribute column
255 * @param p_attribute3 Attribute column
256 * @param p_attribute4 Attribute column
260 * @param p_attribute8 Attribute column
257 * @param p_attribute5 Attribute column
258 * @param p_attribute6 Attribute column
259 * @param p_attribute7 Attribute column
261 * @param p_attribute9 Attribute column
262 * @param p_attribute10 Attribute column
263 * @param p_attribute11 Attribute column
264 * @param p_attribute12 Attribute column
265 * @param p_attribute13 Attribute column
266 * @param p_attribute14 Attribute column
267 * @param p_attribute15 Attribute column
268 */
269 PROCEDURE Insert_Trx_Flow_Lines (
270 P_Header_Id IN NUMBER,
271 P_Line_Number IN NUMBER,
272 P_From_Org_Id IN NUMBER,
273 P_From_Organization_Id IN NUMBER,
274 P_To_Org_Id IN NUMBER,
275 P_To_Organization_Id IN NUMBER,
276 P_Last_Updated_By IN NUMBER,
277 P_Last_Update_Date IN DATE,
278 P_Creation_Date IN DATE,
279 P_Created_By IN NUMBER,
280 P_Last_Update_Login IN NUMBER,
281 P_Attribute_Category IN VARCHAR2,
282 P_Attribute1 IN VARCHAR2,
283 P_Attribute2 IN VARCHAR2,
284 P_Attribute3 IN VARCHAR2,
285 P_Attribute4 IN VARCHAR2,
286 P_Attribute5 IN VARCHAR2,
287 P_Attribute6 IN VARCHAR2,
288 P_Attribute7 IN VARCHAR2,
289 P_Attribute8 IN VARCHAR2,
290 P_Attribute9 IN VARCHAR2,
291 P_Attribute10 IN VARCHAR2,
292 P_Attribute11 IN VARCHAR2,
293 P_Attribute12 IN VARCHAR2,
294 P_Attribute13 IN VARCHAR2,
295 P_Attribute14 IN VARCHAR2,
296 P_Attribute15 IN VARCHAR2
297 );
298
299
300 /** This is a Table Handler for the lines block
301 <br>It will lock a row for the mtl_transaction_flow_lines.<br>
302 * @param x_row_id rowid for the table
303 * @param p_header_id identifier for lines table that gives the join condition for the
304 <br>lines table to join to header table
305 * @param p_line_number The line number
306 * @param p_from_org_id Organization id for the from operating unit
307 * @param p_from_organization_id Organization Id for the from organization that is under the from operating unit.
308 * @param p_to_org_id Organization id for the to operating unit
309 * @param p_to_organization_id Organization Id for the to organization that is under the to operating unit.
310 * @param p_last_update_date Who column
311 * @param p_last_updated_by Who Column
312 * @param p_creation_date Who Column
313 * @param p_created_by Who Column
314 * @param p_last update login Who Column
315 * @param p_attribute_category Attribute context column
316 * @param p_attribute1 Attribute column
317 * @param p_attribute2 Attribute column
318 * @param p_attribute3 Attribute column
319 * @param p_attribute4 Attribute column
320 * @param p_attribute5 Attribute column
321 * @param p_attribute6 Attribute column
322 * @param p_attribute7 Attribute column
323 * @param p_attribute8 Attribute column
324 * @param p_attribute9 Attribute column
325 * @param p_attribute10 Attribute column
326 * @param p_attribute11 Attribute column
327 * @param p_attribute12 Attribute column
328 * @param p_attribute13 Attribute column
329 * @param p_attribute14 Attribute column
330 * @param p_attribute15 Attribute column
331 */
332
333 PROCEDURE Lock_Trx_Flow_Lines (
334 P_Header_Id IN NUMBER,
335 P_Line_Number IN NUMBER,
336 P_From_Org_Id IN NUMBER,
337 P_From_Organization_Id IN NUMBER,
338 P_To_Org_Id IN NUMBER,
339 P_To_Organization_Id IN NUMBER,
340 P_Last_Updated_By IN NUMBER,
341 P_Last_Update_Date IN DATE,
342 P_Creation_Date IN DATE,
346 P_Attribute1 IN VARCHAR2,
343 P_Created_By IN NUMBER,
344 P_Last_Update_Login IN NUMBER,
345 P_Attribute_Category IN VARCHAR2,
347 P_Attribute2 IN VARCHAR2,
348 P_Attribute3 IN VARCHAR2,
349 P_Attribute4 IN VARCHAR2,
350 P_Attribute5 IN VARCHAR2,
351 P_Attribute6 IN VARCHAR2,
352 P_Attribute7 IN VARCHAR2,
353 P_Attribute8 IN VARCHAR2,
354 P_Attribute9 IN VARCHAR2,
355 P_Attribute10 IN VARCHAR2,
356 P_Attribute11 IN VARCHAR2,
357 P_Attribute12 IN VARCHAR2,
358 P_Attribute13 IN VARCHAR2,
359 P_Attribute14 IN VARCHAR2,
360 P_Attribute15 IN VARCHAR2
361 );
362
363
364 /** This is a Table Handler for the lines block
365 <br>It is for the update of mtl_transaction_flow_lines.Only Dff columns are updateable <br>
366 * @param p_header_id identifier for lines table that gives the join condition for the
367 <br>lines table to join to header table
368 * @param p_line_number The line number
369 * @param p_last_update_date Who column
370 * @param p_last_updated_by Who Column
371 * @param p_last update login Who Column
372 * @param p_attribute_category Attribute context column
373 * @param p_attribute1 Attribute column
374 * @param p_attribute2 Attribute column
375 * @param p_attribute3 Attribute column
376 * @param p_attribute4 Attribute column
377 * @param p_attribute5 Attribute column
378 * @param p_attribute6 Attribute column
379 * @param p_attribute7 Attribute column
380 * @param p_attribute8 Attribute column
381 * @param p_attribute9 Attribute column
382 * @param p_attribute10 Attribute column
383 * @param p_attribute11 Attribute column
384 * @param p_attribute12 Attribute column
385 * @param p_attribute13 Attribute column
386 * @param p_attribute14 Attribute column
387 * @param p_attribute15 Attribute column
388 */
389
390 PROCEDURE Update_Trx_Flow_Lines (
391 P_Header_Id IN NUMBER,
392 P_Line_Number IN NUMBER,
393 P_Last_Update_Date IN DATE,
394 P_Last_Updated_By IN NUMBER,
395 P_Last_Update_Login IN NUMBER,
396 P_Attribute_Category IN VARCHAR2,
397 P_Attribute1 IN VARCHAR2,
398 P_Attribute2 IN VARCHAR2,
399 P_Attribute3 IN VARCHAR2,
400 P_Attribute4 IN VARCHAR2,
401 P_Attribute5 IN VARCHAR2,
402 P_Attribute6 IN VARCHAR2,
403 P_Attribute7 IN VARCHAR2,
404 P_Attribute8 IN VARCHAR2,
405 P_Attribute9 IN VARCHAR2,
406 P_Attribute10 IN VARCHAR2,
407 P_Attribute11 IN VARCHAR2,
408 P_Attribute12 IN VARCHAR2,
409 P_Attribute13 IN VARCHAR2,
410 P_Attribute14 IN VARCHAR2,
411 P_Attribute15 IN VARCHAR2
412 ) ;
413
414
415 /** This functions takes the following parameters and checks if a
416 <br> trx flow with same attributes alraedy exists.
417 <br> Retuns true if the duplicate flow is not found. else returns false.
418 <br> Here the idea is to prevent the user from creating a duplicate flow.<br>
419 * @param p_header_id header id of transaction flow to be validate
420 * @param p_start_org_id organization_id of the start operating unit
421 * @param p_end_org_id organization_id of the end operating unit
422 * @param p_flow_type Indicated whether flow type is shipping or procuring
423 * @param p_organization_id The ship From/To Organization Id
424 * @param p_qualifier_code This indicates the qualifier code for the flow type.At present it can be null or Category
425 * @param p_qualifier_value_id This is the value of the qualifier code if selected as Category
426 * @param p_start_date The start date with time when the trx flow becomes active
430 P_HEADER_ID IN NUMBER,
427 * @param p_end_date The end date with time when the trx flow ceases to be active
428 */
429 /*FUNCTION Validate_Header (
431 P_START_ORG_ID IN NUMBER,
432 P_END_ORG_ID IN NUMBER,
433 P_FLOW_TYPE IN NUMBER,
434 P_ORGANIZATION_ID IN NUMBER,
435 P_QUALIFIER_CODE IN NUMBER,
436 P_QUALIFIER_VALUE_ID IN NUMBER,
437 P_START_DATE IN DATE,
438 P_END_DATE IN DATE
439
440 ) RETURN BOOLEAN;*/
441
442
443 /** This functions takes the following parameters and checks for the validity
444 <br>of the start date. Following cases are checked as part of the validation.
445 <br>1.The start date cannot be before the system date. For this the parameter
446 <br> p_ref_date is passed . this p_ref_date carries the value of the sysdate.
447 <br>The value of sysdate has to be stored in this parameter because there
448 <br>will always be some time lag beween the user entering the sysdate and
449 <br>validation taking place.
450 <br>2.It is seen that user is not able to create a overlapping flow that is he
451 <br> does not fill start date which lies between a present start date and end date.
452 <br>3.Also if a transaction for a null end date and strat date prior to the current date
453 <br>exists then the user should not be able to create the flow.
454 <br>If all validation pass then it returns true else returns false <br>
455
456 * @param p_header_id unique identifier for header table
457 * @param p_start_org_id organization_id of the start operating unit
458 * @param p_end_org_id organization_id of the end operating unit
459 * @param p_flow_type Indicated whether flow type is shipping or procuring
460 * @param p_organization_id The ship From/To Organization Id
461 * @param p_qualifier_code This indicates the qualifier code for the flow type.At present it can be null or Category
462 * @param p_qualifier_value_id This is the value of the qualifier code if selected as Category
463 * @param p_start_date The start date with time when the trx flow becomes active
464 * @param p_ref_date The ref date which carries the system date
465 */
466
467
468
469 FUNCTION Validate_Start_Date (
470 P_HEADER_ID IN NUMBER,
471 P_START_ORG_ID IN NUMBER,
472 P_END_ORG_ID IN NUMBER,
473 P_FLOW_TYPE IN NUMBER,
474 P_ORGANIZATION_ID IN NUMBER,
475 P_QUALIFIER_CODE IN NUMBER,
476 P_QUALIFIER_VALUE_ID IN NUMBER,
477 P_START_DATE IN DATE,
478 P_REF_DATE IN DATE
479 ) RETURN BOOLEAN;
480
481
482
483 /** This functions takes the following parameters and checks the validity of the end date
484 <br>It does the following checks.
485 <br>1. Firstly it checks that the end date should not be less than the start date
486 <br>2.It checks that the end date should not cause a overlapping transaction
487 <br>that is it should not lie between the start date and end date of a existing transaction
488 <br>3.If it is to be null then no other transaction with start date greater than the
489 <br> start date of the current flow should exist
490 <br>4.If a Inter-company Transaction Flow with same attributes and NULL End Date exists
491 <br> Then new Inter-company Transaction Flow can only be defined for End Date less then
492 <br> the Start Date of existing Inter-company Transaction Flow.
493 <br>5.This functions also checks if a
494 <br> trx flow with same attributes alraedy exists.
495 <br> Here the idea is to prevent the user from creating a duplicate flow.<br>
496
497 * @param p_header_id unique identifier for header table
498 * @param p_start_org_id organization_id of the start operating unit
499 * @param p_end_org_id organization_id of the end operating unit
500 * @param p_flow_type Indicated whether flow type is shipping or procuring
501 * @param p_organization_id The ship From/To Organization Id
502 * @param p_qualifier_code This indicates the qualifier code for the flow type.At present it can be null or Category
503 * @param p_qualifier_value_id This is the value of the qualifier code if selected as Category
504 * @param p_start_date The start date with time when the trx flow becomes active
505 * @param p_end_date The end date with time when the trx flow ceases to be active
506 * @param p_ref_date The ref date with time when the trx is created or updated
507 */
508
509
510 FUNCTION Validate_End_Date (
511 P_HEADER_ID IN NUMBER,
512 P_START_ORG_ID IN NUMBER,
513 P_END_ORG_ID IN NUMBER,
514 P_FLOW_TYPE IN NUMBER,
515 P_ORGANIZATION_ID IN NUMBER,
516 P_QUALIFIER_CODE IN NUMBER,
517 P_QUALIFIER_VALUE_ID IN NUMBER,
518 P_START_DATE IN DATE,
522
519 P_END_DATE IN DATE,
520 P_REF_DATE IN DATE
521 ) RETURN BOOLEAN;
523
524
525 /** This procedure takes the parameters and checks for the gap in the existing
526 <br> transaction flows with the same attributes. It has certain OUT parameters like
527 <br> x_gap_exists which is boolean in nature and its value is true if the gap exists and
528 <br> false when value is false. Besides this the other out parameters carry the start
529 <br> and the end dates of the first gap found. These values are used to default the dates
530 <br> when the user tries to enter a new header record. This is done to make sure that no gaps are
531 <br> created by the user. <br>
532
533 * @param x_start_date This is the out parameter giving the start Date for the first gap
534 * @param x_end_date This is the out parameter giving the end date of the first gap
535 * @param x_ref_date This is the out parameter which carries the system date
536 * @param x_gap_exists This is a boolean out parameter that retuns true if gap exists false otherwise
537 * @param x_return_status This is a out variable carrying the status whether future trxns with null
538 <br> end date exist
539 * @param p_start_org_id organization_id of the start operating unit
540 * @param p_end_org_id organization_id of the end operating unit
541 * @param p_flow_type Indicated whether flow type is shipping or procuring
542 * @param p_organization_id The ship From/To Organization Id
543 * @param p_qualifier_code This indicates the qualifier code for the flow type.At present it can be null or Category
544 * @param p_qualifier_value_id This is the value of the qualifier code if selected as Category
545 */
546
547
548
549
550 PROCEDURE Gap_Exists ( X_START_DATE OUT NOCOPY DATE,
551 X_END_DATE OUT NOCOPY DATE,
552 X_REF_DATE OUT NOCOPY DATE,
553 X_GAP_EXISTS OUT NOCOPY BOOLEAN,
554 X_RETURN_STATUS OUT NOCOPY NUMBER,
555 P_START_ORG_ID IN NUMBER,
556 P_END_ORG_ID IN NUMBER,
557 P_FLOW_TYPE IN NUMBER,
558 P_ORGANIZATION_ID IN NUMBER,
559 P_QUALIFIER_CODE IN NUMBER,
560 P_QUALIFIER_VALUE_ID IN NUMBER
561 );
562
563
564
565 /** This procedure takes the parameters and is used for defaultin the dates for the user when
566 <br> the user creates a new transaction flow. This is done to avoid the gaps being craeted.
567 <br> This procedure internally calls the Gap_Exists procedure to get the first gap and the
568 <br> start and the end date.For different cases X_return code is used for which the
569 <br> description is given as below.<br>
570
571 * @param x_start_date This is the out parameter giving the start Date for the first gap
572 * @param x_end_date This is the out parameter giving the end date of the first gap
573 * @param x_ref_date This is the out parameter which carries the system date
574 * @param x_return_code This is a out paramter which signifies different scenarios that are used for
575 <br> defaulting the dates.When its value is 0 the
576 <br> either no Trx flows with same attributes exists.So the start date is
577 <br> defaulted with sysdate or no gaps exists for the existing Trx flows.
578 <br> so the start date is defaulted to Max of end dates of existing transactions.
579 <br> If vbalue is 1 then a existing transaction with NULL end date exists so
580 <br> no new transaction can be created.
581 <br> If the value is 2 Then gap exists and the stsrt date and end date out
582 <br> parameters are defaulted with the first gap.
583 * @param p_start_org_id organization_id of the start operating unit
584 * @param p_end_org_id organization_id of the end operating unit
585 * @param p_flow_type Indicated whether flow type is shipping or procuring
586 * @param p_organization_id The ship From/To Organization Id
587 * @param p_qualifier_code This indicates the qualifier code for the flow type.At present it can be null or Category
588 * @param p_qualifier_value_id This is the value of the qualifier code if selected as Category
589 */
590
591 PROCEDURE Get_Default_Dates ( X_START_DATE OUT NOCOPY DATE,
592 X_END_DATE OUT NOCOPY DATE,
593 X_REF_DATE OUT NOCOPY DATE,
594 X_RETURN_CODE OUT NOCOPY NUMBER,
595 P_START_ORG_ID IN NUMBER,
596 P_END_ORG_ID IN NUMBER,
597 P_FLOW_TYPE IN NUMBER,
598 P_ORGANIZATION_ID IN NUMBER,
599 P_QUALIFIER_CODE IN NUMBER,
600 P_QUALIFIER_VALUE_ID IN NUMBER
601 );
602
603
607 <br> to issue a warning to the user whenever the gap is created.<br>
604 /** This functions takes the following parameters and checks for for a new gap that
605 <br> has been created. It returns the value true whenever user creates a gap
606 <br> and false when gap is not created. The return boolean value is checked
608
609 * @param p_start_org_id organization_id of the start operating unit
610 * @param p_end_org_id organization_id of the end operating unit
611 * @param p_flow_type Indicated whether flow type is shipping or procuring
612 * @param p_organization_id The ship From/To Organization Id
613 * @param p_qualifier_code This indicates the qualifier code for the flow type.At present it can be null or Category
614 * @param p_qualifier_value_id This is the value of the qualifier code if selected as Category
615 * @param p_start_date The start date with time when the trx flow becomes active
616 * @param p_end_date The end date with time when the trx flow ceases to be active
617 * @param p_ref_date The ref date which carries the system date
618 */
619
620
621 FUNCTION New_Gap_Created (
622 P_START_ORG_ID IN NUMBER,
623 P_END_ORG_ID IN NUMBER,
624 P_FLOW_TYPE IN NUMBER,
625 P_ORGANIZATION_ID IN NUMBER,
626 P_QUALIFIER_CODE IN NUMBER,
627 P_QUALIFIER_VALUE_ID IN NUMBER,
628 P_START_DATE IN DATE,
629 P_END_DATE IN DATE,
630 P_REF_DATE IN DATE
631
632 ) RETURN BOOLEAN;
633
634 /** This procedure takes in the context and the flex name as the parameters and returns
635 <br> a table containing enabled segments for that flex field and context. <br>
636
637 * @param x_return_status return_status(OUT Parameter)
638 * @param x_msg_count Count of the recent message(OUT Parameter)
639 * @param x_msg_data Data of the message(OUT Parameter)
640 * @param x_enabled_segs A table containing enabled segments for that flex field and context.
641 * @param p_context
642 * @param p_flex_name Name of the flex field for which the validation is to be done
643
644
645 PROCEDURE Txn_Flow_Dff ( X_RETURN_STATUS OUT NOCOPY VARCHAR2
646 ,X_MSG_COUNT OUT NOCOPY NUMBER
647 ,X_MSG_DATA OUT NOCOPY VARCHAR2
648 ,X_ENABLED_SEGS OUT NOCOPY inv_lot_sel_attr.lot_sel_attributes_tbl_type
649 ,P_CONTEXT IN VARCHAR2
650 ,P_FLEX_NAME IN VARCHAR2
651 );
652
653
654 /** This function takes the following parameters and checks that the attribute columns
655 <br> are valid or not. The check is done bote for global context as well as the user
656 <br> defined context. The validation is done for the required segments in the DFF,
657 <br> for the value set and also if any wrong columns not part of DFF are passed.<br>
658
659 * @param p_flex_name name of the flex field for which the validation is to be done
660 * @param p_attribute_category Attribute context column
661 * @param p_attribute1 Attribute column
662 * @param p_attribute2 Attribute column
663 * @param p_attribute3 Attribute column
664 * @param p_attribute4 Attribute column
665 * @param p_attribute5 Attribute column
666 * @param p_attribute6 Attribute column
667 * @param p_attribute7 Attribute column
668 * @param p_attribute8 Attribute column
669 * @param p_attribute9 Attribute column
670 * @param p_attribute10 Attribute column
671 * @param p_attribute11 Attribute column
672 * @param p_attribute12 Attribute column
673 * @param p_attribute13 Attribute column
674 * @param p_attribute14 Attribute column
675 * @param p_attribute15 Attribute column
676 */
677 FUNCTION Validate_Dff(P_FLEX_NAME IN VARCHAR2,
678 P_ATTRIBUTE1 IN VARCHAR2,
679 P_ATTRIBUTE2 IN VARCHAR2,
680 P_ATTRIBUTE3 IN VARCHAR2,
681 P_ATTRIBUTE4 IN VARCHAR2,
682 P_ATTRIBUTE5 IN VARCHAR2,
683 P_ATTRIBUTE6 IN VARCHAR2,
684 P_ATTRIBUTE7 IN VARCHAR2,
685 P_ATTRIBUTE8 IN VARCHAR2,
686 P_ATTRIBUTE9 IN VARCHAR2,
687 P_ATTRIBUTE10 IN VARCHAR2,
688 P_ATTRIBUTE11 IN VARCHAR2,
689 P_ATTRIBUTE12 IN VARCHAR2,
690 P_ATTRIBUTE13 IN VARCHAR2,
691 P_ATTRIBUTE14 IN VARCHAR2,
692 P_ATTRIBUTE15 IN VARCHAR2,
693 P_ATTRIBUTE_CATEGORY IN VARCHAR2
694 ) RETURN BOOLEAN;
695
696
697 /** This is the API that will be used by third parties to create a transaction flow.
698 <br>This procedure does the following
699 <br>It first validates all the parameters such as start_org_id,end_org_id,flow_type,
700 <br>qualifier_value,qualifier_value_id for their validity i.e. all the are valid id's for
701 <br> that org.Then it calls the pvt procedures such as validate_header,validate_start_date,
702 <br> validate_end_date,validate_lines to check the validity of all these values.
703 <br> Finally if all validations are true it inserts a row into the header table and
707 * @param x_msg_count Count of the recent message(OUT Parameter)
704 <br> the required number of rows in the lines table.<br>
705
706 * @param x_return_status return_status(OUT Parameter)
708 * @param x_msg_data Data of the message(OUT Parameter)
709 * @param p_header_id unique identifier for header table
710 * @param p_commit Used for getting the savepoints
711 * @param p_validation_level Used to signify whether the procedure is used by a third party
712 * @param p_start_org_id organization_id of the start operating unit
713 * @param p_end_org_id organization_id of the end operating unit
714 * @param p_last_update_date Who column
715 * @param p_last_updated_by Who Column
716 * @param p_creation_date Who Column
717 * @param p_created_by Who Column
718 * @param p_last update login Who Column
719 * @param p_flow_type Indicated whether flow type is shipping or procuring
720 * @param p_organization_id The ship From/To Organization Id
721 * @param p_qualifier_code This indicates the qualifier code for the flow type.At present it can be null or Category
722 * @param p_qualifier_value_id This is the value of the qualifier code if selected as Category
723 * @param p_asset_item_pricing_option This gives the Asset pricing option as either PO or Transfer if flow type is procuring
724 * @param p_expense_item_pricing_option This gives the Expense pricing option as either PO or Transfer if flow type is procuring
725 * @param p_start_date The start date with time when the trx flow becomes active
726 * @param p_end_date The end date with time when the trx flow ceases to be active
727 * @param p_new_accounting_flag Indicates whether the user is going for the new accounting or old accounting.
728 <br>If flow is procuring then it should be new.
729 <br> FOR shipping if number of lines greater than 1 then new
730 * @param p_attribute_category Attribute context column
731 * @param p_attribute1 Attribute column
732 * @param p_attribute2 Attribute column
733 * @param p_attribute3 Attribute column
734 * @param p_attribute4 Attribute column
735 * @param p_attribute5 Attribute column
736 * @param p_attribute6 Attribute column
737 * @param p_attribute7 Attribute column
738 * @param p_attribute8 Attribute column
739 * @param p_attribute9 Attribute column
740 * @param p_attribute10 Attribute column
741 * @param p_attribute11 Attribute column
742 * @param p_attribute12 Attribute column
743 * @param p_attribute13 Attribute column
744 * @param p_attribute14 Attribute column
745 * @param p_attribute15 Attribute column
746 * @param p_ref_date The date type variable which carries the date when user creating the trx flow.
747 <br> It should be the latest value of sysdate.
748 * @param p_lines_tab Table of lines
749 */
750
751 PROCEDURE Create_IC_Transaction_Flow(
752 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
753 X_MSG_COUNT OUT NOCOPY NUMBER,
754 X_MSG_DATA OUT NOCOPY VARCHAR2,
755 P_HEADER_ID IN NUMBER,
756 P_COMMIT IN BOOLEAN DEFAULT FALSE,
757 P_VALIDATION_LEVEL IN NUMBER,--0=>No Validation,1=>Flow Validation
758 P_START_ORG_ID IN NUMBER,
759 P_END_ORG_ID IN NUMBER,
760 P_FLOW_TYPE IN NUMBER,
761 P_ORGANIZATION_ID IN NUMBER,
762 P_QUALIFIER_CODE IN NUMBER,
763 P_QUALIFIER_VALUE_ID IN NUMBER,
764 P_ASSET_ITEM_PRICING_OPTION IN NUMBER,
765 P_EXPENSE_ITEM_PRICING_OPTION IN NUMBER,
766 P_START_DATE IN DATE,
767 P_END_DATE IN DATE,
768 P_NEW_ACCOUNTING_FLAG IN VARCHAR2,
769 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
770 P_ATTRIBUTE1 IN VARCHAR2,
771 P_ATTRIBUTE2 IN VARCHAR2,
772 P_ATTRIBUTE3 IN VARCHAR2,
773 P_ATTRIBUTE4 IN VARCHAR2,
774 P_ATTRIBUTE5 IN VARCHAR2,
775 P_ATTRIBUTE6 IN VARCHAR2,
776 P_ATTRIBUTE7 IN VARCHAR2,
777 P_ATTRIBUTE8 IN VARCHAR2,
778 P_ATTRIBUTE9 IN VARCHAR2,
782 P_ATTRIBUTE13 IN VARCHAR2,
779 P_ATTRIBUTE10 IN VARCHAR2,
780 P_ATTRIBUTE11 IN VARCHAR2,
781 P_ATTRIBUTE12 IN VARCHAR2,
783 P_ATTRIBUTE14 IN VARCHAR2,
784 P_ATTRIBUTE15 IN VARCHAR2,
785 P_REF_DATE IN DATE,
786 P_LINES_TAB IN INV_TRANSACTION_FLOW_PVT.TRX_FLOW_LINES_TAB
787 ) ;
788
789
790
791 /** This functions takes the following parameters and checks if a
792 <br> for the validity of the lines block.
793 <br> Retuns true if the all validations are passed. else returns false.
794 <br> Here the idea is to prevent the user from creating non valid.
795 <br> The check is done for the validity of the from_org_id, from_organization_id,
796 <br> to_org_id,to_organization_id.Then it is checked that user completes the flow.
797 <br> that is the end_org_id(header block) should be equal to the to_org_id to finish the flow.
798 <br> Also checks that for all lines the intercompany relations has been set up before saving the data.
799 <br> Also checks the validity of the new accounting flag that is it should be
800 <br> yes always if it is a procuring flow. Else if shipping flow then it
801 <br> should be yes if number of lines is graeter than one.
802 <br> Also checks like no org should come twice in the lines is done.<br>
803
804 * @param p_lines_tab This is a table type of a in paramter wghich carries
805 <br> the value for all the lines that are going to be
806 <br> inserted. For each line the value of from_org_id,
807 <br> from_organization_id, to_org_id, to_orgaization_id,etc
808 <br> are populated in the table and table is passed as a parameter.
809 * @param p_start_org_id organization_id of the start operating unit
810 * @param p_end_org_id organization_id of the end operating unit
811 * @param p_flow_type Indicated whether flow type is shipping or procuring
812 * @param p_ship_from_to_organization_id The ship From/To Organization Id
813 * @param p_new_accounting_flag Indicates whether the user is going for the new accounting or old accounting.
814 <br>If flow is procuring then it should be new.
815 <br> FOR shipping if number of lines greater than 1 then new
816 */
817 FUNCTION Validate_Trx_Flow_Lines(
818 P_LINES_TAB IN INV_TRANSACTION_FLOW_PVT.TRX_FLOW_LINES_TAB,
819 P_SHIP_FROM_TO_ORGANIZATION_ID IN NUMBER,
820 P_FLOW_TYPE IN NUMBER,
821 P_START_ORG_ID IN NUMBER,
822 P_END_ORG_ID IN NUMBER,
823 P_NEW_ACCOUNTING_FLAG IN VARCHAR2
824 ) RETURN BOOLEAN;
825
826
827
828
829
830 /** This procedure is used for updating of a IC transaction flow.Since only the
831 <br>start_date and the end_date are updateable so only the statrt datr and end date
832 <br> along with the attribute columns are passed.<br>
833 * @param x_return_status return_status(OUT Parameter)
834 * @param x_msg_count Count of the recent message(OUT Parameter)
835 * @param x_msg_data Data of the message(OUT Parameter)
836 * @param p_header_id unique identifier for header table
837 * @param p_commit Used for getting the savepoints
838 * @param p_start_date The start date with time when the trx flow becomes active
839 * @param p_end_date The end date with time when the trx flow ceases to be active
840 * @param p_ref_date The date type variable which carries the date when user creating the trx flow.
841 <br> It should be the latest value of sysdate.
842 * @param p_attribute_category Attribute context column
843 * @param p_attribute1 Attribute column
844 * @param p_attribute2 Attribute column
845 * @param p_attribute3 Attribute column
846 * @param p_attribute4 Attribute column
847 * @param p_attribute5 Attribute column
848 * @param p_attribute6 Attribute column
849 * @param p_attribute7 Attribute column
850 * @param p_attribute8 Attribute column
851 * @param p_attribute9 Attribute column
852 * @param p_attribute10 Attribute column
853 * @param p_attribute11 Attribute column
854 * @param p_attribute12 Attribute column
855 * @param p_attribute13 Attribute column
856 * @param p_attribute14 Attribute column
857 * @param p_attribute15 Attribute column
858 */
859 PROCEDURE Update_IC_Transaction_Flow(
860 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
861 X_MSG_COUNT OUT NOCOPY NUMBER,
862 X_MSG_DATA OUT NOCOPY VARCHAR2,
863 P_COMMIT IN BOOLEAN DEFAULT FALSE,
864 P_HEADER_ID IN NUMBER,
868 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
865 P_START_DATE IN DATE,
866 P_END_DATE IN DATE,
867 P_REF_DATE IN DATE,
869 P_ATTRIBUTE1 IN VARCHAR2,
870 P_ATTRIBUTE2 IN VARCHAR2,
871 P_ATTRIBUTE3 IN VARCHAR2,
872 P_ATTRIBUTE4 IN VARCHAR2,
873 P_ATTRIBUTE5 IN VARCHAR2,
874 P_ATTRIBUTE6 IN VARCHAR2,
875 P_ATTRIBUTE7 IN VARCHAR2,
876 P_ATTRIBUTE8 IN VARCHAR2,
877 P_ATTRIBUTE9 IN VARCHAR2,
878 P_ATTRIBUTE10 IN VARCHAR2,
879 P_ATTRIBUTE11 IN VARCHAR2,
880 P_ATTRIBUTE12 IN VARCHAR2,
881 P_ATTRIBUTE13 IN VARCHAR2,
882 P_ATTRIBUTE14 IN VARCHAR2,
883 P_ATTRIBUTE15 IN VARCHAR2,
884 P_LINES_TAB IN INV_TRANSACTION_FLOW_PVT.TRX_FLOW_LINES_TAB
885 ) ;
886
887
888 PROCEDURE update_ic_txn_flow_hdr
889 (X_RETURN_STATUS OUT NOCOPY VARCHAR2,
890 X_MSG_COUNT OUT NOCOPY NUMBER,
891 X_MSG_DATA OUT NOCOPY VARCHAR2,
892 P_COMMIT IN BOOLEAN DEFAULT FALSE,
893 P_HEADER_ID IN NUMBER,
894 P_START_DATE IN DATE,
895 P_END_DATE IN DATE,
896 P_REF_DATE IN DATE,
897 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
898 P_ATTRIBUTE1 IN VARCHAR2,
899 P_ATTRIBUTE2 IN VARCHAR2,
900 P_ATTRIBUTE3 IN VARCHAR2,
901 P_ATTRIBUTE4 IN VARCHAR2,
902 P_ATTRIBUTE5 IN VARCHAR2,
903 P_ATTRIBUTE6 IN VARCHAR2,
904 P_ATTRIBUTE7 IN VARCHAR2,
905 P_ATTRIBUTE8 IN VARCHAR2,
906 P_ATTRIBUTE9 IN VARCHAR2,
907 P_ATTRIBUTE10 IN VARCHAR2,
908 P_ATTRIBUTE11 IN VARCHAR2,
909 P_ATTRIBUTE12 IN VARCHAR2,
910 P_ATTRIBUTE13 IN VARCHAR2,
911 P_ATTRIBUTE14 IN VARCHAR2,
912 P_ATTRIBUTE15 IN VARCHAR2
913 );
914
915 PROCEDURE Update_IC_Txn_Flow_line(
916 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
917 X_MSG_COUNT OUT NOCOPY NUMBER,
918 X_MSG_DATA OUT NOCOPY VARCHAR2,
919 P_COMMIT IN BOOLEAN DEFAULT FALSE,
920 P_HEADER_ID IN NUMBER,
921 P_LINE_NUMBER IN NUMBER,
922 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
923 P_ATTRIBUTE1 IN VARCHAR2,
924 P_ATTRIBUTE2 IN VARCHAR2,
925 P_ATTRIBUTE3 IN VARCHAR2,
926 P_ATTRIBUTE4 IN VARCHAR2,
927 P_ATTRIBUTE5 IN VARCHAR2,
928 P_ATTRIBUTE6 IN VARCHAR2,
929 P_ATTRIBUTE7 IN VARCHAR2,
930 P_ATTRIBUTE8 IN VARCHAR2,
931 P_ATTRIBUTE9 IN VARCHAR2,
932 P_ATTRIBUTE10 IN VARCHAR2,
933 P_ATTRIBUTE11 IN VARCHAR2,
934 P_ATTRIBUTE12 IN VARCHAR2,
935 P_ATTRIBUTE13 IN VARCHAR2,
936 P_ATTRIBUTE14 IN VARCHAR2,
937 P_ATTRIBUTE15 IN VARCHAR2
938 );
939 END;-- INV_TRANSACTION_FLOW_PVT
940
941
[Home] [Help]