[Home] [Help]
PACKAGE BODY: APPS.OKE_K_APPROVAL_WF2
Source
1 PACKAGE BODY OKE_K_APPROVAL_WF2 AS
2 /* $Header: OKEWKA2B.pls 120.1 2005/06/02 12:01:49 appldev $ */
3
4 --
5 -- Global Variables
6 --
7 CR VARCHAR2(10) := FND_GLOBAL.newline;
8 BS VARCHAR2(10) := ' ';
9
10 --
11 -- Private Functions and Procedures
12 --
13
14 --
15 -- Public Functions and Procedures
16 --
17
18 --
19 -- Name : Contract_Number_Link
20 -- Pre-reqs : Must be called from WF activity
21 -- Function : This PL/SQL document procedure returns the contract
22 -- number with a link to contract flowdown viewer
23 --
24 -- Parameters :
25 -- IN : ItemType
26 -- ItemKey
27 -- ActID
28 -- FuncMode
29 -- OUT NOCOPY /* file.sql.39 change */ : ResultOut ( None )
30 --
31 -- Returns : None
32 --
33 PROCEDURE Contract_Number_Link
34 ( Document_ID IN VARCHAR2
35 , Display_Type IN VARCHAR2
36 , Document OUT NOCOPY /* file.sql.39 change */ VARCHAR2
37 , Document_Type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
38 ) IS
39
40 ItemType WF_ITEMS.item_type%TYPE;
41 ItemKey WF_ITEMS.item_key%TYPE;
42
43 L_K_Header_ID NUMBER;
44 L_K_Number VARCHAR2(240);
45
46 FlowdownURL VARCHAR2(2000);
47
48 BEGIN
49
50 ItemType := substr( Document_ID , 1 , instr(Document_ID , ':') - 1 );
51 ItemKey := substr( Document_ID , instr(Document_ID , ':') + 1
52 , length(Document_ID) - 2);
53
54 L_K_Header_ID := WF_ENGINE.GetItemAttrNumber(ItemType , ItemKey , 'CONTRACT_ID');
55 L_K_Number := WF_ENGINE.GetItemAttrText(ItemType , ItemKey , 'K_NUMBER');
56
57 IF ( Display_Type = 'text/plain' ) THEN
58
59 Document := 'L_K_Number';
60
61 ELSE
62
63 FlowdownURL := OKE_FLOWDOWN_UTILS.Flowdown_URL
64 ( X_Business_Area => 'APPROVAL'
65 , X_Object_Name => 'OKE_K_HEADERS'
66 , X_PK1 => L_K_Header_ID
67 , X_PK2 => NULL );
68
69 Document := '<a href="' || FlowdownURL || '">' || L_K_Number || '</a>';
70 Document_Type := 'text/html';
71
72 END IF;
73
74 END Contract_Number_Link;
75
76
77 --
78 -- Name : Show_Approval_History
79 -- Pre-reqs : Must be called from WF activity
80 -- Function : This PL/SQL document procedure returns the approval
81 -- history as maintained in SET_APPROVAL_HISTORY() for
82 -- use in various notifications
83 --
84 -- Parameters :
85 -- IN : Document_ID ( ItemType:ItemKey )
86 -- Display_Type
87 -- Document_Type
88 -- OUT NOCOPY /* file.sql.39 change */ : Document
89 -- Document_Type
90 --
91 -- Returns : None
92 --
93 PROCEDURE Show_Approval_History
94 ( Document_ID IN VARCHAR2
95 , Display_Type IN VARCHAR2
96 , Document OUT NOCOPY /* file.sql.39 change */ VARCHAR2
97 , Document_Type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
98 ) IS
99
100 ItemType WF_ITEMS.item_type%TYPE;
101 ItemKey WF_ITEMS.item_key%TYPE;
102 DocOut VARCHAR2(32767);
103
104 L_K_Header_ID NUMBER;
105
106 CURSOR h ( C_Header_ID NUMBER ) IS
107 SELECT ah.action_code
108 , ac.action_name
109 , ah.action_date
110 , ah.approver_role_id
111 , pr.meaning approver_role
112 , ah.performer
113 , ah.note
114 FROM oke_approval_history ah
115 , ( select lookup_code action_code
116 , meaning action_name
117 from fnd_lookup_values
118 where view_application_id = 777
119 and lookup_type = 'APPROVAL_ACTION'
120 and language = userenv('LANG') ) ac
121 , pa_project_role_types pr
122 WHERE ah.k_header_id = C_Header_ID
123 AND ah.chg_request_id IS NULL
124 AND ac.action_code = ah.action_code
125 AND pr.project_role_id (+) = ah.approver_role_id
126 ORDER BY action_sequence DESC;
127 hrec h%rowtype;
128
129 CURSOR r ( C_role_name VARCHAR2 ) IS
130 SELECT nvl(p.full_name , u.user_name)
131 , u.email_address
132 FROM fnd_user u
133 , per_all_people_f p
134 WHERE u.user_name = C_role_name
135 AND p.person_id = u.employee_id
136 AND trunc(sysdate) BETWEEN p.effective_start_date AND p.effective_end_date;
137
138 Performer_Display_Name VARCHAR2(240);
139 Performer_Email_Address VARCHAR2(240);
140
141 BEGIN
142
143 ItemType := substr( Document_ID , 1 , instr(Document_ID , ':') - 1 );
144 ItemKey := substr( Document_ID , instr(Document_ID , ':') + 1
145 , length(Document_ID) - 2);
146
147 L_K_Header_ID := WF_ENGINE.GetItemAttrNumber(ItemType , ItemKey , 'CONTRACT_ID');
148
149 IF ( Display_Type = 'text/plain' ) THEN
150
151 Document := '';
152
153 ELSE
154 DocOut := CR || CR || '<!-- SHOW_APPROVAL_HISTORY -->' || CR || CR;
155 --
156 -- Section Header
157 --
158 DocOut := DocOut
159 || '<table border=0 cellspacing=2 cellpadding=2 width=100%>'
160 || '<tr><td class=OraHeader>' || fnd_message.get_string('OKE' , 'OKE_WFNTF_APPROVAL_HISTORY')
161 || '</td></tr>' || CR
162 || '<tr><td class=OraBGAccentDark></td></tr>' || CR;
163
164 --
165 -- Table Header
166 --
167 DocOut := DocOut || '<tr><td>' || CR;
168 DocOut := DocOut
169 || '<table class=OraTable border=0 cellspacing=2 cellpadding=2 width=100%>' || CR || '<tr>' || CR;
170 DocOut := DocOut
171 || '<th class=OraTableColumnHeader width=15%>'
172 || fnd_message.get_string('OKE' , 'OKE_WFNTF_ACTION')
173 || '</th>' || CR;
174 DocOut := DocOut
175 || '<th class=OraTableColumnHeader width=20%>'
176 || fnd_message.get_string('OKE' , 'OKE_WFNTF_PERFORMER')
177 || '</th>' || CR;
178 DocOut := DocOut
179 || '<th class=OraTableColumnHeader width=20%>'
180 || fnd_message.get_string('OKE' , 'OKE_WFNTF_ROLE')
181 || '</th>' || CR;
182 DocOut := DocOut
183 || '<th class=OraTableColumnHeader width=15%>'
184 || fnd_message.get_string('OKE' , 'OKE_WFNTF_DATE')
185 || '</th>' || CR;
186 DocOut := DocOut
187 || '<th class=OraTableColumnHeader width=30%>'
188 || fnd_message.get_string('OKE' , 'OKE_WFNTF_NOTE')
189 || '</th>' || CR || '</tr>' || CR;
190
191 FOR hrec IN h ( L_K_Header_ID ) LOOP
192
193 OPEN r ( hrec.Performer );
194 FETCH r INTO Performer_Display_Name , Performer_Email_Address;
195 CLOSE r;
196
197 IF ( Performer_Display_Name IS NULL ) THEN
198 Performer_Display_Name := hrec.Performer;
199 END IF;
200
201 DocOut := DocOut || '<tr>' || CR;
202 DocOut := DocOut
203 || '<td class=OraTableCellText>'
204 || hrec.Action_Name
205 || '</td>' || CR;
206 IF ( Performer_Email_Address IS NOT NULL ) THEN
207 DocOut := DocOut
208 || '<td class=OraTableCellText>'
209 || '<a href="mailto:' || Performer_Email_Address || '">'
210 || Performer_Display_Name || '</a>'
211 || '</td>' || CR;
212 ELSE
213 DocOut := DocOut
214 || '<td class=OraTableCellText>'
215 || Performer_Display_Name
216 || '</td>' || CR;
217 END IF;
218 DocOut := DocOut
219 || '<td class=OraTableCellText>'
220 || nvl(hrec.Approver_Role , BS)
221 || '</td>' || CR;
222 DocOut := DocOut
223 || '<td class=OraTableCellText>'
224 || fnd_date.date_to_displaydate(hrec.Action_Date)
225 || '</td>' || CR;
226 DocOut := DocOut
227 || '<td class=OraTableCellText>'
228 || nvl(hrec.Note , BS)
229 || '</td>' || CR || '</tr>' || CR;
230
231 END LOOP;
232
233 DocOut := DocOut
234 || '</td></tr></table>' || CR
235 || '</table>' || CR || '<p>' || CR;
236
237 Document := DocOut;
238 Document_Type := 'text/html';
239
240 END IF;
241
242 END Show_Approval_History;
243
244
245 END OKE_K_APPROVAL_WF2;