1 PACKAGE pon_clo_renumber_pkg AUTHID CURRENT_USER AS
2 /* $Header: PONCLORNS.pls 120.15.12020000.2 2013/02/09 11:15:01 hvutukur ship $ */
3
4 /********************Type Declarations **************************/
5 -- Changing the following table type to genric PO_TBL_VARCHAR100 as some of these procedures
6 -- will be called from Java/Middle-tier -- serukull
7 -- TYPE TBL_VARCHAR2_100 IS TABLE OF VARCHAR2(100);
8
9
10 /************User Defined Exceptions used in the body ***********/
11
12 NO_MORE_LINES_CAN_BE_ADDED EXCEPTION;
13 CLIN_NUMBERS_EXHAUSTED EXCEPTION;
14 ELIN_NUMBERS_EXHAUSTED EXCEPTION;
15 INVALID_CLIN_NUMBER EXCEPTION;
16 INVALID_ELIN_NUMBER EXCEPTION;
17 INVALID_INPUT_CLIN_ARRAY EXCEPTION;
18 INFO_SLIN_NUM_EXHAUSTED EXCEPTION;
19 PRICED_SLIN_NUM_EXHAUSTED EXCEPTION;
20
21 /*****************Public Methods*********************************/
22 /*******************************
23 Name : NEXT_CLIN_NUM (Only PLSQL Implementation required)
24 Input : Array / PLSQL table of already assigned Clin numbers
25 Output : Next lowest Clin number available.
26 ********************************/
27 FUNCTION NEXT_CLIN_NUM ( ASSIGNED_NUM_ARRAY PO_TBL_VARCHAR100 ) return VARCHAR2;
28
29 FUNCTION NEXT_CLIN_NUM_WRAPPER(p_Document_ID IN NUMBER,
30 p_product_code IN VARCHAR2) RETURN varchar2;
31
32 FUNCTION ELIN_TO_DECIMAL(linNum VARCHAR2) RETURN NUMBER;
33
34 FUNCTION DECIMAL_TO_ELIN(elin_dec NUMBER) RETURN VARCHAR2;
35
36 FUNCTION NEXT_ELIN_NUM_WRAPPER ( p_Document_ID IN NUMBER, p_product_code IN VARCHAR2, p_exhibit_number IN VARCHAR2) return VARCHAR2;
37
38 FUNCTION NEXT_ELIN_NUM_WRAPPER2 ( p_Document_ID IN NUMBER, p_product_code IN VARCHAR2, p_exhibit_number IN VARCHAR2) return VARCHAR2;
39 /*
40 This function takes table of newly created line numbers in this session.
41 This is to get latest possible next clin number.
42 */
43 FUNCTION NEXT_CLIN_NUM_WRAPPER2(p_Document_ID IN NUMBER,
44 p_product_code IN VARCHAR2,
45 new_lines IN PO_TBL_VARCHAR100) RETURN VARCHAR2;
46
47
48 /* When user enters a clin number manually, this procudure validates the clin number */
49 PROCEDURE VALIDATE_CLIN_NUMBER( p_line_id IN NUMBER, p_doc_id IN NUMBER, p_new_value IN varchar2,
50 p_product_code IN VARCHAR2,p_error_message OUT NOCOPY VARCHAR2);
51
52
53 /*********************************
54 Name : RenumberAllLines()
55 Input : Document_ID
56 Product_Code,
57 Lines_table_name
58 Output : Status
59 Exception_message
60 Desc: Can be used when renumbering the entire document, at a time when no lines have been manually renumbered by user. Eg. At the time of autocreate or automatic creation of new docs.
61
62 Algorithm :
63 1. Maintain 1 variable <LastClinNum> having the last Clin number, initialized to "0001"
64 2. Loop Thru all the CLINs for the given document_id
65 3. find all the options of this CLIN and update the clm_base line_num to <LastClinNum>
66 a. This can be done using a simple update query.
67 4. Assign the <LastClinNum> to current clin.
68 5. Initialize 2 variables for line numbers of Info and priced Slins, initialized as
69 a. <LastInfoSlin> = "<LastClinNum>01"
70 b. and
71 c. <LastPricedSlin> = "<LastClinNum>AA"
72 6. Loop thru all the slins
73 7. if the slin is a priced Slin then
74 a. Find all the options of this Slin and set clm_base_line_num = <LastPricedSlin>
75 b. assign the <LastPricedSlin> to this slin and call <LastPricedSlin> = INCREMENT_PRICED_SLIN_NUM(<LastPricedSlin>)
76 8. if the slin is an Info Slin,
77 a. Find all the options of this Slin and set clm_base_line_num = <LastPricedSlin>
78 b. assign the <LastInfoSlin> to this slin and call <LastInfoSlin> = INCREMENT_INFO_SLIN_NUM(<LastInfoSlin>)
79 9. Change the group_line_id to <LastClinNum>
80 10. End loop at line 5
81 11. <LastPricedSlin> = INCREMENT_CLIN_NUM(<LastClinNum>)
82 12. End loop at line 2
83 *********************************/
84
85
86 PROCEDURE RenumberAllLines (
87 p_Document_ID IN NUMBER,
88 p_product_code IN VARCHAR2,
89 x_status OUT NOCOPY varchar2);
90
91
92 FUNCTION GetNextSlinNumber(p_product_code IN VARCHAR2,P_Document_ID IN NUMBER,p_ClinNumber IN VARCHAR2,p_ClmInfo_Flag IN VARCHAR2)
93 return VARCHAR2;
94
95 PROCEDURE RenumberSlinStructure(
96 p_product_code IN VARCHAR2,
97 p_Document_ID IN NUMBER,
98 p_ClinNumber IN VARCHAR2,
99 x_status OUT NOCOPY varchar2)
100 ;
101
102 FUNCTION INCREMENT_CLIN_NUMBER (ClinNum IN VARCHAR2)
103 RETURN varchar2;
104
105 FUNCTION INCREMENT_ELIN_NUMBER (ElinNum IN VARCHAR2)
106 RETURN varchar2;
107
108 FUNCTION INCREMENT_INFO_SLIN_NUMBER (SlinNum IN VARCHAR2)
109 RETURN VARCHAR2;
110
111 FUNCTION INCREMENT_PRICED_SLIN_NUMBER (SlinNum IN VARCHAR2)
112 RETURN VARCHAR2;
113
114
115 PROCEDURE RenumberSlin(p_auction_header_id IN Number,
116 p_line_number IN Number,
117 x_status OUT NOCOPY varchar2 );
118 --CLM_SPLIT
119 FUNCTION FIRST_INFO_SLIN_NUMBER (CLINNUM IN VARCHAR2)
120 RETURN VARCHAR2;
121 --END
122
123
124 /**************test Functions ***********************************/
125 FUNCTION testIncrementClinNum(ClinNum IN VARCHAR2) return varchar2;
126 FUNCTION testIncrementPricedSlinNum(SlinNum IN VARCHAR2) return VARCHAR2;
127 FUNCTION testIncrementInfoSlinNum(SlinNum IN VARCHAR2) return VARCHAR2;
128
129
130 /* Bug 9883780 - Start */
131 /* Added the following procedure to regenerate SLINs for Solicitations
132 during Solicitation to Award Creation Flow. */
133
134 PROCEDURE GenerateSlinStructure(
135 p_product_code IN VARCHAR2,
136 p_interface_header_id IN NUMBER,
137 p_clinnumber IN VARCHAR2,
138 x_status OUT NOCOPY varchar2);
139
140 --Unsolicited Lines Project
141 FUNCTION NEXT_CLIN_NUM_WRAPPER3(p_bid_number IN NUMBER,
142 P_auction_id IN NUMBER,
143 p_product_code IN VARCHAR2)
144 RETURN VARCHAR2;
145
146 /*CLM PHASE-2 :Autocreate Grouping
147 Moving the function to spec*/
148
149 FUNCTION getLineid(p_product_code IN VARCHAR2,
150 p_Document_ID IN NUMBER,
151 p_ClinNumber IN VARCHAR2)
152 RETURN NUMBER;
153
154
155 END PON_CLO_RENUMBER_PKG;
[Home] [Help]