1 PACKAGE BODY AP_WEB_DB_EXP_ATTENDEES_PKG AS
2 /* $Header: apwdbeab.pls 120.2 2006/09/22 08:38:12 mvadera noship $ */
3
4 -----------------------------------------------------------------------------
5 PROCEDURE DeleteAttendees(P_ReportID IN NUMBER) IS
6 --------------------------------------------------------------------------------
7 l_temp OIE_ATTENDEES.EMPLOYEE_FLAG%type;
8 l_curr_calling_sequence VARCHAR2(100) := 'DeleteAttendees';
9
10 -- Selects report lines to delete. The actual value being selected does not
11 -- matter. For some reason the compiler complains when the OF column-name
12 -- in the FOR UPDATE is missing and NOWAIT is used, so the OF
13 -- EMPLOYEE_FLAG is used as a place holder.
14 CURSOR ExpAttendees IS
15 SELECT EMPLOYEE_FLAG
16 FROM OIE_ATTENDEES oat, AP_EXPENSE_REPORT_LINES el
17 WHERE (el.REPORT_HEADER_ID = P_ReportID AND
18 el.REPORT_LINE_ID = oat.REPORT_LINE_ID)
19 FOR UPDATE OF EMPLOYEE_FLAG NOWAIT;
20
21 BEGIN
22 -- Delete the report distributions from table. An exception will occur if the row
23 -- locks cannot be attained because of the NOWAIT argument for select.
24 -- We are guaranteed a lock on the records because of the FOR UPDATE
25 OPEN ExpAttendees;
26
27 LOOP
28 FETCH ExpAttendees into l_temp;
29 EXIT WHEN ExpAttendees%NOTFOUND;
30
31 -- Delete matching line
32 DELETE OIE_ATTENDEES WHERE CURRENT OF ExpAttendees;
33 END LOOP;
34
35 CLOSE ExpAttendees;
36
37
38 EXCEPTION
39 WHEN NO_DATA_FOUND THEN
40 NULL;
41 WHEN OTHERS THEN
42 AP_WEB_DB_UTIL_PKG.RaiseException('DeleteAttendees');
43 APP_EXCEPTION.RAISE_EXCEPTION;
44 END DeleteAttendees;
45
46 PROCEDURE DuplicateAttendeeInfo(p_user_id IN NUMBER,
47 p_source_report_line_id IN AP_WEB_DB_EXPLINE_PKG.expLines_report_line_id,
48 p_target_report_line_id IN AP_WEB_DB_EXPLINE_PKG.expLines_report_line_id) IS
49
50 l_new_attendee_id NUMBER(15);
51 l_attendee_id NUMBER(15);
52
53 CURSOR Attendees IS
54 SELECT ATTENDEE_LINE_ID
55 FROM OIE_ATTENDEES
56 WHERE REPORT_LINE_ID = p_source_report_line_id;
57
58
59 BEGIN
60
61 -- Find all attendees
62 OPEN Attendees;
63
64 LOOP
65
66 FETCH Attendees into l_attendee_id;
67
68 EXIT WHEN Attendees%NOTFOUND;
69
70 -- Get new ID from sequence
71 SELECT OIE_ATTENDEES_S.NEXTVAL
72 INTO l_new_attendee_id
73 FROM DUAL;
74
75 -- For each line, duplicate its columns
76 INSERT INTO OIE_ATTENDEES
77 (
78 ATTENDEE_LINE_ID,
79 REPORT_LINE_ID,
80 EMPLOYEE_FLAG,
81 EMPLOYEE_ID,
82 ATTENDEE_TYPE,
83 NAME,
84 TITLE,
85 EMPLOYER,
86 EMPLOYER_ADDRESS,
87 TAX_ID,
88 ORG_ID,
89 LAST_UPDATE_DATE,
90 LAST_UPDATED_BY,
91 CREATION_DATE,
92 CREATED_BY
93 )
94 SELECT l_new_attendee_id AS ATTENDEE_LINE_ID,
95 p_target_report_line_id AS REPORT_LINE_ID,
96 EMPLOYEE_FLAG,
97 EMPLOYEE_ID,
98 ATTENDEE_TYPE,
99 NAME,
100 TITLE,
101 EMPLOYER,
102 EMPLOYER_ADDRESS,
103 TAX_ID,
104 ORG_ID,
105 sysdate AS CREATION_DATE,
106 p_user_id AS CREATED_BY,
107 sysdate AS LAST_UPDATE_DATE,
108 p_user_id AS LAST_UPDATED_BY
109 FROM OIE_ATTENDEES
110 WHERE ATTENDEE_LINE_ID = l_attendee_id;
111
112 END LOOP;
113
114 CLOSE Attendees;
115
116 END DuplicateAttendeeInfo;
117
118
119 END AP_WEB_DB_EXP_ATTENDEES_PKG;
[Home] [Help]