[Home] [Help]
PACKAGE BODY: APPS.OKE_DTS_WSH_PKG
Source
1 PACKAGE BODY OKE_DTS_WSH_PKG AS
2 /* $Header: OKEPWSHB.pls 120.2.12010000.2 2008/08/08 06:12:59 aveeraba ship $ */
3
4 --
5 -- Name : Bill_To_Location
6 -- Pre-reqs : None
7 -- Function : This function returns bill_to_location_id if it is defined
8 -- as party roles in the contract structure, return -9999 if
9 -- no such role defined in the contract
10 --
11 --
12 -- Parameters :
13 -- IN : P_Deliverable_ID NUMBER
14 --
15 -- OUT : None
16 --
17 -- Returns : NUMBER
18 --
19
20
21 FUNCTION Bill_To_Location ( P_Deliverable_ID NUMBER ) RETURN NUMBER IS
22
23 CURSOR Line_C IS
24 SELECT L.ID, DECODE(L.CLE_ID, NULL, 'T', 'S'), L.DNZ_CHR_ID
25 FROM okc_k_lines_b L, oke_k_deliverables_b D
26 WHERE L.ID = D.K_Line_ID
27 AND D.Deliverable_ID = P_Deliverable_ID;
28
29 CURSOR C1 ( P_ID NUMBER, p_header_id number ) IS
30 SELECT C.Location_ID
31 FROM okc_k_party_roles_b R, oke_cust_site_uses_v C
32 WHERE R.dnz_chr_id = P_header_ID
33 AND R.Cle_ID = P_ID
34 AND R.JTOT_Object1_Code = 'OKE_BILLTO'
35 AND R.Object1_ID1 = C.ID1;
36
37 CURSOR C2 ( P_ID NUMBER ) IS
38 SELECT Cle_ID_Ascendant, Level_Sequence
39 FROM okc_ancestrys
40 WHERE Cle_ID = P_ID
41 ORDER BY Level_Sequence desc;
42
43 CURSOR C3 ( P_ID NUMBER ) IS
44 SELECT C.Location_ID
45 FROM okc_k_party_roles_b R, oke_cust_site_uses_v C
46 WHERE R.dnz_Chr_ID = P_ID
47 AND R.Chr_ID = P_ID
48 AND R.JTOT_Object1_Code = 'OKE_BILLTO'
49 AND R.Object1_ID1 = C.ID1;
50
51
52 L_ID NUMBER;
53 L_Line_ID NUMBER;
54 L_Header_ID NUMBER;
55 L_Level VARCHAR2(1);
56 L_Counter NUMBER := 0;
57
58 BEGIN
59
60 IF P_Deliverable_ID > 0 THEN
61
62 OPEN Line_C;
63 FETCH Line_C INTO L_Line_ID, L_Level, L_Header_ID;
64 CLOSE Line_C;
65
66 -- Check immediate level of roles defined
67
68 FOR C1_Rec IN C1 ( L_Line_ID,l_header_id ) LOOP
69
70 L_ID := C1_Rec.Location_Id;
71 L_Counter := L_Counter + 1;
72
73 END LOOP;
74
75
76 IF L_Counter = 1 THEN
77
78 RETURN L_ID;
79
80 ELSIF L_Counter > 1 THEN
81
82 RETURN -9999;
83
84 ELSE
85
86 -- Check line position within the contract hierarchy
87
88 IF L_Level = 'T' THEN
89
90 FOR C3_Rec IN C3 ( L_Header_ID ) LOOP
91
92 L_ID := C3_Rec.Location_Id;
93 L_Counter := L_Counter + 1;
94
95 END LOOP;
96
97 IF L_Counter = 1 THEN
98
99 RETURN L_ID;
100
101 ELSE
102
103 RETURN -9999;
104
105 END IF;
106
107 ELSIF L_Level = 'S' THEN
108
109 FOR C2_Rec IN C2 ( L_Line_ID ) LOOP
110
111 FOR C1_Rec IN C1 ( C2_Rec.Cle_ID_Ascendant,l_header_id ) LOOP
112
113 L_ID := C1_Rec.Location_Id;
114 L_Counter := L_Counter + 1;
115
116 END LOOP;
117
118 EXIT WHEN L_COUNTER = 1;
119
120 END LOOP;
121
122 IF L_COUNTER = 1 THEN
123
124 RETURN L_ID;
125
126 ELSIF L_Counter > 1 THEN
127
128 RETURN -9999;
129
130 ELSE
131
132 FOR C3_Rec IN C3 ( L_Header_ID ) LOOP
133
134 L_ID := C3_Rec.Location_Id;
135 L_Counter := L_Counter + 1;
136
137 END LOOP;
138
139 IF L_Counter = 1 THEN
140
141 RETURN L_ID;
142
143 ELSE
144
145 RETURN -9999;
146
147 END IF;
148
149 END IF;
150
151 END IF;
152
153 END IF;
154
155 END IF;
156
157 --bug 7277190
158 return -9999;
159 --bug 7277190
160
161 EXCEPTION
162 WHEN OTHERS THEN
163
164 RETURN -9999;
165
166 END Bill_To_Location;
167
168
169
170 END;
171