1 package body ad_releases_pvt as
2 /* $Header: adphrlsb.pls 120.1 2006/04/10 01:44:35 rahkumar noship $ */
3 procedure CreateRelease
4 (p_major_version number,
5 p_minor_version number,
6 p_tape_version number,
7 p_row_src_comments varchar2,
8 p_base_rel_flag varchar2,
9 p_start_dt date,
10 p_end_dt date default null,
11 p_created_by_user_id number,
12 p_release_id out nocopy number)
13 is
14 v_release_id number;
15 begin
16
17 -- Have a savepoint here
18 --
19 savepoint S1;
20
21 begin
22 select release_id
23 into p_release_id
24 from ad_releases
25 where major_version = p_major_version
26 and minor_version = p_minor_version
27 and tape_version = p_tape_version;
28
29 return;
30 exception when no_data_found then
31 null;
32 end;
33
34 -- Validate p_base_rel_flag
35 --
36 if(upper(p_base_rel_flag) not in ('Y','N') and
37 p_base_rel_flag is not null )
38 then
39 raise_application_error(-20000, 'Error:Invalid parameters');
40 end if;
41
42
43 -- Validate p_end_dt > p_start_dt
44 --
45 if p_start_dt is null then
46 raise_application_error(-20000, 'Error: Start-date is null.');
47 end if;
48
49 if p_end_dt < p_start_dt then
50 raise_application_error(-20000,
51 'Error:End-date is earlier than start-date');
52 end if;
53
54 -- Lock the table exclusively to do further validations
55 -- Major and Minor ids and update thge table
56 -- If somebody has the lock then exit with a error message
57 --
58 -- Do not reference table AD_RELEASES via the schema name APPS as some
59 -- customers do not have an APPS schema. Bug 2042101.
60
61 lock table ad_releases in exclusive mode nowait;
62
63 update ad_releases
64 set end_date_active = p_start_dt - 1 / (24*60*60)
65 where release_id in (select release_id
66 from ad_releases
67 where major_version = p_major_version
68 and minor_version = p_minor_version
69 and sysdate between start_date_active and
70 nvl(end_date_active, sysdate+1));
71
72 select ad_releases_s.nextval
73 into v_release_id
74 from dual;
75
76 insert into ad_releases
77 (
78 RELEASE_ID,
79 MAJOR_VERSION,
80 MINOR_VERSION,
81 TAPE_VERSION,
82 ROW_SOURCE_COMMENTS,
83 ARU_RELEASE_NAME,
84 BASE_RELEASE_FLAG,
85 START_DATE_ACTIVE,
86 END_DATE_ACTIVE,
87 CREATION_DATE,
88 CREATED_BY,
89 LAST_UPDATE_DATE,
90 LAST_UPDATED_BY
91 )
92 values
93 (
94 v_release_id ,
95 p_major_version,
96 p_minor_version,
97 p_tape_version ,
98 p_row_src_comments,
99 'R12', /* @@ Be sure to update this in future apps releases */
100 UPPER(p_base_rel_flag),
101 p_start_dt,
102 p_end_dt,
103 sysdate,
104 p_created_by_user_id,
105 sysdate,
106 p_created_by_user_id
107 );
108
109
110 p_release_id:=v_release_id;
111
112 exception when others then
113 rollback to S1;
114 raise;
115 end CreateRelease;
116
117
118 end ad_releases_pvt;
[Home] [Help]