[Home] [Help]
PACKAGE BODY: APPS.CHV_BUILD_REVISION
Source
1 PACKAGE BODY CHV_BUILD_REVISION as
2 /* $Header: CHVPRBRB.pls 115.0 99/07/17 01:29:39 porting ship $ */
3
4 /*======================= CHV_BUILD_REVISION=================================*/
5
6 /*=============================================================================
7
8 PROCEDURE NAME: create_schedule_revision
9
10 =============================================================================*/
11 PROCEDURE create_schedule_revision(
12 p_schedule_id in NUMBER,
13 p_owner_id in NUMBER,
14 p_batch_id in NUMBER) IS
15
16 -- Following variables whose values should be retrieved from schedule select
17 x_vendor_id NUMBER;
18 x_vendor_site_id NUMBER;
19 x_organization_id NUMBER;
20 x_bucket_pattern_id NUMBER;
21 x_mrp_compile_designator VARCHAR2(10);
22 x_mps_schedule_designator VARCHAR2(10);
23 x_drp_compile_designator VARCHAR2(10);
24 x_schedule_subtype VARCHAR2(25);
25 x_schedule_type VARCHAR2(25);
26 x_horizon_start_date DATE;
27 x_horizon_end_date DATE;
28 x_schedule_num VARCHAR2(25);
29 x_include_future_releases VARCHAR2(1);
30 x_schedule_id NUMBER;
31 x_schedule_revision NUMBER;
32 x_multi_org_flag VARCHAR2(1);
33 x_item_created VARCHAR2(1);
34
35 -- Following variables whose values are calculated within the procedure.
36
37 x_user_id NUMBER;
38 x_login_id NUMBER;
39
40 x_progress VARCHAR2(3);
41
42 -- 3 PL/SQL tables used for calculating bucket quantities.
43 x_bucket_descriptor_table chv_create_buckets.bkttable;
44 x_bucket_start_date_table chv_create_buckets.bkttable;
45 x_bucket_end_date_table chv_create_buckets.bkttable;
46
47 x_bucket_count BINARY_INTEGER := 1; -- DEBUG need this?
48
49 CURSOR C_REVISION IS
50 SELECT
51 vendor_id,
52 vendor_site_id,
53 schedule_type,
54 schedule_subtype,
55 schedule_horizon_start,
56 schedule_horizon_end,
57 bucket_pattern_id,
58 schedule_num,
59 organization_id,
60 mps_schedule_designator,
61 mrp_compile_designator,
62 drp_compile_designator,
63 include_future_releases_flag
64 FROM chv_schedule_headers csh
65 WHERE schedule_id = p_schedule_id;
66
67 BEGIN
68
69 --dbms_output.put_line('Entering create schedule revision');
70
71 -- Get x_user_id and x_login_id from the global variable set.
72 x_user_id := NVL(fnd_global.user_id, 0);
73 x_login_id := NVL(fnd_global.login_id, 0);
74
75 x_progress := '010';
76
77 OPEN C_REVISION;
78
79 x_progress := '020';
80
81 --dbms_output.put_line('Create_Schedule_Revision: fetch into');
82 FETCH C_REVISION INTO
83 x_vendor_id,
84 x_vendor_site_id,
85 x_schedule_type,
86 x_schedule_subtype,
87 x_horizon_start_date,
88 x_horizon_end_date,
89 x_bucket_pattern_id,
90 x_schedule_num,
91 x_organization_id,
92 x_mps_schedule_designator,
93 x_mrp_compile_designator,
94 x_drp_compile_designator,
95 x_include_future_releases;
96
97 x_progress := '030';
98
99 -- Create 3 temp bucket tables (descriptor, start_date and end_date)
100 -- and get x_horizon_end_date in the meantime
101 --dbms_output.put_line('Create_Schedule_Revision: create_bucket_template');
102
103 chv_create_buckets.create_bucket_template(x_horizon_start_date,
104 x_include_future_releases,
105 x_bucket_pattern_id,
106 x_horizon_end_date,
107 x_bucket_descriptor_table,
108 x_bucket_start_date_table,
109 x_bucket_end_date_table);
110
111 --dbms_output.put_line('Build Schedules: end Date'||x_horizon_end_date);
112
113 x_progress := '040';
114
115 chv_build_schedules.get_schedule_number('REVISION',
116 x_vendor_id,
117 x_vendor_site_id,
118 x_schedule_num,
119 x_schedule_revision);
120
121 -- Insert a new row into CHV_SCHEDULE_HEADERS.
122 -- Before that, get a new unique schedule header ID.
123 -- Any error will be caught at the exception at the end of the procedure.
124 x_progress := '050';
125 SELECT chv_schedule_headers_s.NEXTVAL
126 INTO x_schedule_id
127 FROM DUAL;
128
129 x_progress := '060';
130 INSERT INTO chv_schedule_headers(schedule_id,
131 vendor_id,
132 vendor_site_id,
133 schedule_type,
134 schedule_subtype,
135 schedule_num,
136 schedule_revision,
137 schedule_horizon_start,
138 schedule_horizon_end,
139 bucket_pattern_id,
140 schedule_owner_id,
141 last_update_date,
142 last_updated_by,
143 creation_date,
144 created_by,
145 organization_id,
146 mps_schedule_designator,
147 mrp_compile_designator,
148 drp_compile_designator,
149 schedule_status,
150 inquiry_flag,
151 include_future_releases_flag,
152 last_update_login,
153 batch_id)
154 VALUES (x_schedule_id,
155 x_vendor_id,
156 x_vendor_site_id,
157 x_schedule_type,
158 x_schedule_subtype,
159 x_schedule_num,
160 x_schedule_revision,
161 x_horizon_start_date,
162 x_horizon_end_date,
163 x_bucket_pattern_id,
164 p_owner_id,
165 SYSDATE, -- last_update_date
166 x_user_id, -- last_updated_by
167 SYSDATE, -- creation_date
168 x_user_id, -- created_by
169 x_organization_id,
170 x_mps_schedule_designator,
171 x_mrp_compile_designator,
172 x_drp_compile_designator,
173 'IN_PROCESS', -- schedule_status
174 'N', -- inquiry_flag
175 x_include_future_releases,
176 x_login_id,
177 p_batch_id); -- last_update_login
178
179
180 x_progress := '070';
181
182
183 IF (x_organization_id > 0 ) THEN
184 x_multi_org_flag := 'Y';
185 ELSE
186 x_multi_org_flag := 'N';
187 END IF;
188
189
190 chv_build_schedules.create_items('REVISION',
191 'N',
192 x_schedule_type,
193 x_schedule_subtype,
194 x_schedule_id,
195 null,
196 null,
197 x_horizon_start_date,
198 null,
199 x_include_future_releases,
200 x_mrp_compile_designator,
201 x_mps_schedule_designator,
202 x_drp_compile_designator,
203 x_organization_id,
204 x_multi_org_flag,
205 x_vendor_id,
206 x_vendor_site_id,
207 null,
208 null,
209 null,
210 null,
211 null,
212 null,
213 x_user_id,
214 x_login_id,
215 x_horizon_end_date,
216 x_bucket_descriptor_table,
217 x_bucket_start_date_table,
218 x_bucket_end_date_table,
219 x_item_created,
220 p_schedule_id,
221 x_bucket_pattern_id,
222 x_schedule_subtype,
223 p_batch_id);
224
225 x_progress := '090';
226
227 CLOSE C_REVISION;
228
229 EXCEPTION
230 WHEN OTHERS THEN
231 CLOSE C_REVISION;
232 po_message_s.sql_error('create_schedule_revision', x_progress, sqlcode);
233 RAISE;
234
235 END create_schedule_revision;
236
237 END CHV_BUILD_REVISION;