[Home] [Help]
PACKAGE BODY: APPS.OKE_DELIVERABLE_UTILS
Source
1 PACKAGE BODY OKE_DELIVERABLE_UTILS AS
2 /* $Header: OKEDUTLB.pls 120.1 2005/06/24 10:42:44 ausmani noship $ */
3
4 --
5 -- Global Declarations
6 --
7
8 --
9 -- Private Procedures and Functions
10 --
11 --
12 FUNCTION GET_PARTY ( x_deliverable_id NUMBER, x_role_code VARCHAR2 ) RETURN NUMBER IS
13
14 CURSOR PartySite
15 ( C_Deliverable_ID NUMBER
16 , C_Role_Code VARCHAR2 ) IS
17 SELECT pr.jtot_object1_code Object_Code
18 , pr.object1_id1 ID1
19 , pr.code
20 , pr.facility
21 FROM okc_k_party_roles_b pr
22 , oke_k_deliverables_b kd
23 , ( select cle_id , cle_id_ascendant , level_sequence
24 from okc_ancestrys
25 union all
26 select id , id , 99999 from okc_k_lines_b ) a
27 WHERE kd.deliverable_id = C_Deliverable_ID
28 AND pr.rle_code = C_Role_Code
29 AND pr.dnz_chr_id = kd.k_header_id
30 AND a.cle_id = kd.k_line_id
31 AND ( ( pr.cle_id IS NULL AND a.cle_id = a.cle_id_ascendant )
32 OR pr.cle_id = a.cle_id_ascendant )
33 ORDER BY DECODE(pr.cle_id , null , 0 , a.level_sequence) DESC;
34
35 BillToRec PartySite%RowType;
36
37
38 BEGIN
39
40 -- Fetch Contract Parties Information
41 --
42
43 OPEN PartySite( x_deliverable_id , x_role_code );
44 FETCH PartySite INTO BillToRec;
45 CLOSE PartySite;
46
47 RETURN (BillToRec.ID1);
48
49 EXCEPTION
50 WHEN OTHERS THEN
51 RETURN ( TO_NUMBER(NULL) );
52
53 END GET_PARTY;
54
55
56 FUNCTION GET_TERM_VALUE ( x_deliverable_id NUMBER, x_term_code VARCHAR2 ) RETURN VARCHAR2 IS
57
58 CURSOR TermValue
59 ( C_Deliverable_ID NUMBER
60 , C_Term_Code VARCHAR2 ) IS
61 SELECT kt.term_value_pk1 Code
62 , OKE_UTILS.Get_Term_Values
63 ( kt.term_code , kt.term_value_pk1
64 , kt.term_value_pk2 , 'MEANING' ) Name
65 FROM oke_k_terms kt
66 , oke_k_deliverables_b kd
67 , ( select cle_id , cle_id_ascendant , level_sequence
68 from okc_ancestrys
69 union all
70 select id , id , 99999 from okc_k_lines_b ) a
71 WHERE kd.deliverable_id = C_Deliverable_ID
72 AND kt.term_code = C_Term_Code
73 AND kt.k_header_id = kd.k_header_id
74 AND a.cle_id = kd.k_line_id
75 AND ( ( kt.k_line_id IS NULL AND a.cle_id = a.cle_id_ascendant )
76 OR kt.k_line_id = a.cle_id_ascendant )
77 ORDER BY DECODE(kt.k_line_id , null , 0 , a.level_sequence) DESC;
78
79 DiscTermsRec TermValue%RowType;
80
81 BEGIN
82
83 -- Fetch Contract Term Value Information
84 --
85
86 OPEN TermValue( x_deliverable_id , x_term_code );
87 FETCH TermValue INTO DiscTermsRec;
88 CLOSE TermValue;
89
90 RETURN (DiscTermsRec.Name);
91
92 EXCEPTION
93 WHEN OTHERS THEN
94 RETURN NULL;
95
96
97
98 END GET_TERM_VALUE;
99
100 --
101 -- Modified in 01/13/2003, use ':' instead of '-' for seperator for bug 2741941
102 --
103
104 FUNCTION Get_K_Reference ( P_Deliverable_ID NUMBER, P_Source_Code VARCHAR2 ) RETURN VARCHAR2 IS
105
106 CURSOR C IS
107 SELECT Rtrim(Ltrim(H.Contract_Number, ' '), ' ') K_Number
108 , Rtrim(Ltrim(L.Line_Number, ' '), ' ') L_Number
109 , Rtrim(Ltrim(D.Deliverable_Num, ' '), ' ') D_Number
110 FROM okc_k_headers_all_b H
111 , okc_k_lines_b L
112 , oke_k_deliverables_b D
113 WHERE D.Deliverable_ID = P_Deliverable_ID
114 AND L.ID = D.K_Line_ID
115 AND H.ID = D.K_Header_ID;
116
117 CURSOR C1 IS
118 SELECT Rtrim(Ltrim(H.Deliverable_Number, ' '), ' ') D_Number
119 , Rtrim(Ltrim(L.Action_Name, ' '), ' ') A_Number
120 FROM oke_deliverables_b H
121 , oke_deliverable_actions L
122 WHERE H.Deliverable_ID = P_Deliverable_ID
123 AND H.Deliverable_ID = L.Deliverable_ID
124 AND L.Action_Type = 'WSH'
125 AND L.Reference2 > 0;
126
127 L_K_Num VARCHAR2(150);
128 L_L_Num VARCHAR2(150);
129 L_D_Num VARCHAR2(150);
130 L_A_NUM VARCHAR2(150);
131 L_Length NUMBER;
132 L_Allowed_Length CONSTANT NUMBER := 62;
133 L_Ref VARCHAR2(62);
134
135
136 BEGIN
137
138 IF P_Source_Code = 'OKE' THEN
139
140 IF P_Deliverable_ID > 0 THEN
141
142 OPEN C;
143 FETCH C INTO L_K_Num, L_L_Num, L_D_Num;
144 CLOSE C;
145
146 IF L_K_Num IS NULL THEN -- PA record
147
148 OPEN C1;
149 FETCH C1 INTO L_D_Num, L_A_Num;
150 CLOSE C1;
151
152 L_Length := Length(L_D_Num || ':'|| L_A_Num);
153 WHILE L_Length > L_Allowed_Length LOOP
154
155 IF Length(L_A_Num) > 24 THEN
156
157 L_A_Num := Substr(L_A_Num, 1, 24);
158
159 ELSE
160
161 L_D_Num := Substr(L_D_Num, 1, L_Allowed_Length - Length(':'|| L_D_Num));
162 END IF;
163
164 L_Length := Length(L_D_Num || ':'|| L_A_Num);
165
166 END LOOP;
167 L_Ref := L_D_Num || ':' || L_A_Num;
168
169 ELSE -- OKE record
170
171 L_Length := Length(L_K_Num || ':'|| L_L_Num || ':' || L_D_Num);
172
173 WHILE L_Length > L_Allowed_Length LOOP
174
175 IF Length(L_D_Num) > 6 THEN
176
177 L_D_Num := Substr(L_D_Num, 1, 6);
178
179 ELSIF Length(L_L_Num) > 6 THEN
180
181 L_L_Num := Substr(L_L_Num, 1, 6);
182
183 ELSE
184
185 L_K_Num := Substr(L_K_Num, 1, L_Allowed_Length - Length(':'|| L_L_Num || ':' || L_D_Num));
186
187 END IF;
188
189 L_Length := Length(L_K_Num || ':'|| L_L_Num || ':' || L_D_Num);
190
191 END LOOP;
192
193 L_Ref := L_K_Num || ':' || L_L_Num || ':' || L_D_Num;
194 END IF;
195 END IF;
196 END IF;
197
198 RETURN L_Ref;
199
200 EXCEPTION
201 WHEN OTHERS THEN
202 RETURN NULL;
203
204 END Get_K_Reference;
205
206
207 END OKE_DELIVERABLE_UTILS;