[Home] [Help]
PACKAGE BODY: APPS.PJM_UNIT_EFF_CONC
Source
1 PACKAGE BODY PJM_UNIT_EFF_CONC AS
2 /* $Header: PJMUEFCB.pls 115.11 2002/10/29 20:15:41 alaw ship $ */
3 -- ---------------------------------------------------------------------
4 -- Global Variables
5 -- ---------------------------------------------------------------------
6
7 -- ---------------------------------------------------------------------
8 -- Private Functions / Procedures
9 -- ---------------------------------------------------------------------
10
11
12 -- ---------------------------------------------------------------------
13 -- Public Functions / Procedures
14 -- ---------------------------------------------------------------------
15
16 --
17 -- Name : Generate
18 -- Pre-reqs : None
19 -- Function : This function creates new unit numbers based on
20 -- input parameters
21 --
22 --
23 -- Parameters :
24 -- IN : X_master_org_id NUMBER
25 -- X_end_item_id NUMBER
26 -- X_prefix VARCHAR2
27 -- X_start_num NUMBER
28 -- X_counts NUMBER
29 -- X_numeric_width NUMBER
30 --
31 -- OUT : ERRBUF VARCHAR2
32 -- RETCODE NUMBER
33 --
34 -- Returns : None
35 --
36 PROCEDURE Generate
37 ( ERRBUF OUT NOCOPY VARCHAR2
38 , RETCODE OUT NOCOPY NUMBER
39 , X_master_org_id IN NUMBER
40 , X_end_item_id IN NUMBER
41 , X_prefix IN VARCHAR2
42 , X_start_num IN NUMBER
43 , X_counts IN NUMBER
44 , X_numeric_width IN NUMBER
45 ) is
46 i NUMBER;
47 n NUMBER;
48 L_pad_width NUMBER;
49 L_current_unit_number VARCHAR2(30);
50 L_end_num NUMBER;
51 L_user_id NUMBER;
52 L_login_id NUMBER;
53 L_request_id NUMBER;
54 L_prog_appl_id NUMBER;
55 L_prog_id NUMBER;
56 BEGIN
57
58 if ( PJM_UNIT_EFF.Enabled = 'N' ) then
59 fnd_message.set_name('PJM','UEFF-Function Not Available');
60 ERRBUF := fnd_message.get;
61 PJM_CONC.new_line(1);
62 PJM_CONC.put_line(ERRBUF);
63 PJM_CONC.new_line(1);
64 RETCODE := PJM_CONC.G_conc_failure;
65 return;
66 end if;
67
68 PJM_CONC.new_line(1);
69 PJM_CONC.put_line('[MASTER_ORG_ID] = ' || X_master_org_id);
70 PJM_CONC.put_line('[END_ITEM_ID] = ' || X_end_item_id);
71 PJM_CONC.put_line('[PREFIX] = ' || X_prefix);
72 PJM_CONC.put_line('[START_NUM] = ' || X_start_num);
73 PJM_CONC.put_line('[COUNTS] = ' || X_counts);
74 PJM_CONC.put_line('[NUMERIC_WIDTH] = ' || X_numeric_width);
75 PJM_CONC.new_line(1);
76
77 --
78 -- Calculate End_Num based on X_Start_Num and X_Counts
79 --
80 L_end_num := X_start_Num + X_Counts - 1;
81
82 if ( length(to_char(L_end_num)) > X_numeric_width ) then
83 fnd_message.set_name('PJM','UEFF-Exc Unit Num Length');
84 fnd_message.set_token('LEN1', to_char(L_end_num));
85 fnd_message.set_token('LEN2', X_numeric_width);
86 PJM_CONC.new_line(1);
87 PJM_CONC.put_line(fnd_message.get);
88 PJM_CONC.new_line(1);
89 L_pad_width := length(to_char(L_end_num));
90 else
91 L_pad_width := X_numeric_width;
92 end if;
93
94 if nvl(length(X_prefix),0) + L_pad_width > 30 then
95 fnd_message.set_name('PJM','UEFF-Max Unit Num Length');
96 errbuf := fnd_message.get;
97 PJM_CONC.new_line(1);
98 PJM_CONC.put_line(errbuf);
99 PJM_CONC.new_line(1);
100 retcode := PJM_CONC.G_conc_failure;
101 return;
102 end if;
103
104 L_user_id := fnd_global.user_id;
105 L_login_id := fnd_global.conc_login_id;
106 L_request_id := fnd_global.conc_request_id;
107 L_prog_appl_id := fnd_global.prog_appl_id;
108 L_prog_id := fnd_global.conc_program_id;
109
110 PJM_CONC.new_line(1);
111 PJM_CONC.put_line(fnd_message.get_string('PJM','CONC-UEFGN BEGIN'));
112 PJM_CONC.new_line(1);
113
114 n := 0;
115
116 FOR i in X_start_num..L_end_num
117 LOOP
118 --
119 -- Construct Unit Number from X_prefix and i
120 --
121 L_current_unit_number := X_prefix || lpad(to_char(i), L_pad_width, '0');
122
123 BEGIN
124 INSERT INTO pjm_unit_numbers
125 ( unit_number
126 , creation_date
127 , created_by
128 , last_update_date
129 , last_updated_by
130 , last_update_login
131 , request_id
132 , program_application_id
133 , program_id
134 , program_update_date
135 , end_item_id
136 , master_organization_id
137 , prefix)
138 SELECT L_current_unit_number
139 , sysdate
140 , L_user_id
141 , sysdate
142 , L_user_id
143 , L_login_id
144 , L_request_id
145 , L_prog_appl_id
146 , L_prog_id
147 , sysdate
148 , X_end_item_id
149 , X_master_org_id
150 , X_prefix
151 FROM dual;
152
153 n := n + 1;
154
155 fnd_message.set_name('PJM','CONC-UEFGN UNITNUM CREATED');
156 fnd_message.set_token('UNIT', L_current_unit_number);
157 PJM_CONC.put_line(fnd_message.get);
158
159 EXCEPTION
160 WHEN dup_val_on_index THEN
161 fnd_message.set_name('PJM','CONC-UEFGN UNITNUM EXISTS');
162 fnd_message.set_token('UNIT', L_current_unit_number);
163 PJM_CONC.put_line(fnd_message.get);
164 WHEN others THEN
165 errbuf := sqlerrm;
166 retcode := PJM_CONC.G_conc_failure;
167 return;
168 END;
169
170 END LOOP;
171
172 PJM_CONC.new_line(1);
173 fnd_message.set_name('PJM','CONC-UEFGN DONE');
174 fnd_message.set_token('NUM', n);
175 PJM_CONC.put_line(fnd_message.get);
176 PJM_CONC.new_line(1);
177
178 retcode := PJM_CONC.G_conc_success;
179
180 END Generate;
181
182
183 END PJM_UNIT_EFF_CONC;