[Home] [Help]
PACKAGE BODY: APPS.IGS_HE_UV_PKG
Source
1 PACKAGE BODY IGS_HE_UV_PKG AS
2 /* $Header: IGSHE18B.pls 120.1 2006/02/07 14:53:31 jbaber noship $
3 | HISTORY |
4 | |
5 | Date Name Comments |
6 | --------- ---------------- ------------------------------ |
7 | 02-Jan-02 M. S. GARCHA Created |
8 | 08-Feb-02 A Kataria Added TBH's |
9 | 09-Apr-02 sbaliga Changed code corresponding to the addition |
10 | of location_cd to igs_he_st_unt_vs_all |
11 | table as part of #2278825 |
12 | 26-Jan-05 jbaber Modified x_c2 for HE355 - Org Unit Cost |
13 | Centre Link |
14 | 24-Nov-05 jbaber Added exclude_flag to x_c1 for HE305 |
15 +=========================================================================+
16 */
17
18 PROCEDURE copy_unit_version (
19 p_c_old_unit_cd IN VARCHAR2
20 ,p_n_old_version_number IN NUMBER
21 ,p_c_new_unit_cd IN VARCHAR2
22 ,p_n_new_version_number IN NUMBER
23 ,p_n_status OUT NOCOPY NUMBER
24 ,p_c_message OUT NOCOPY VARCHAR2
25 ) IS
26
27 x_err_msg VARCHAR2(240);
28 x_seq_id NUMBER := '';
29 x_org_id NUMBER(15);
30 l_location_cd igs_he_st_unt_vs.location_cd%TYPE;
31
32 CURSOR x_c1 IS
33 SELECT rowid
34 ,prop_of_teaching_in_welsh
35 ,credit_transfer_scheme
36 ,module_length
37 ,proportion_of_fte
38 ,location_cd
39 ,exclude_flag
40 FROM igs_he_st_unt_vs
41 WHERE unit_cd = p_c_old_unit_cd
42 AND version_number = p_n_old_version_number
43 AND NOT EXISTS (
44 SELECT 'x'
45 FROM igs_he_st_unt_vs
46 WHERE unit_cd = p_c_new_unit_cd
47 AND version_number = p_n_new_version_number
48 );
49
50 CURSOR x_c2 IS
51 SELECT rowid
52 ,org_unit_cd
53 ,cost_centre
54 ,subject
55 ,proportion
56 FROM igs_he_unt_ou_cc
57 WHERE unit_cd = p_c_old_unit_cd
58 AND version_number = p_n_old_version_number
59 AND NOT EXISTS (
60 SELECT 'x'
61 FROM igs_he_unt_ou_cc
62 WHERE unit_cd = p_c_new_unit_cd
63 AND version_number = p_n_new_version_number
64 );
65
66
67 BEGIN
68
69 p_n_status := 0;
70
71 IF p_c_old_unit_cd is NULL OR
72 p_n_old_version_number is NULL OR
73 p_c_new_unit_cd is NULL OR
74 p_n_new_version_number is NULL
75 THEN
76 p_n_status := 2;
77 p_c_message := 'IGS_HE_INV_PARAMS';
78 RETURN;
79 END IF;
80
81 x_org_id := IGS_GE_GEN_003.GET_ORG_ID;
82
83 FOR x_c1_rec IN x_c1 LOOP
84 IF p_c_old_unit_cd = p_c_new_unit_cd THEN
85 l_location_cd:= x_c1_rec.location_cd;
86 ELSE
87 l_location_cd:= NULL;
88 END IF;
89
90 -- p_c_message := 'WILL NOW INSERT INTO IGS_HE_ST_UNT_VS_ALL';
91 -- IGSWI24B.pls
92 IGS_HE_ST_UNT_VS_ALL_PKG.Insert_Row(
93 X_ROWID => x_c1_rec.rowid,
94 X_HESA_ST_UNT_VS_ID => x_seq_id,--Sequence generated in handler!!
95 X_ORG_ID => x_org_id,
96 X_UNIT_CD => p_c_new_unit_cd,
97 X_VERSION_NUMBER => p_n_new_version_number,
98 X_PROP_OF_TEACHING_IN_WELSH => x_c1_rec.prop_of_teaching_in_welsh,
99 X_CREDIT_TRANSFER_SCHEME => x_c1_rec.credit_transfer_scheme,
100 X_MODULE_LENGTH => x_c1_rec.module_length,
101 X_PROPORTION_OF_FTE => x_c1_rec.proportion_of_fte,
102 X_LOCATION_CD => l_location_cd,
103 X_MODE => 'R',
104 X_EXCLUDE_FLAG => x_c1_rec.exclude_flag
105 );
106 END LOOP;
107
108
109 FOR x_c2_rec IN x_c2 LOOP
110
111 -- IGSWI46B.pls
112 -- p_c_message := 'WILL NOW INSERT INTO IGS_HE_ST_UV_CC_ALL';
113 IGS_HE_UNT_OU_CC_PKG.Insert_Row(
114 X_ROWID => x_c2_rec.rowid,
115 X_HESA_UNIT_CC_ID => x_seq_id, --Sequence generated in handler!!
116 X_UNIT_CD => p_c_new_unit_cd,
117 X_VERSION_NUMBER => p_n_new_version_number,
118 X_ORG_UNIT_CD => x_c2_rec.org_unit_cd,
119 X_COST_CENTRE => x_c2_rec.cost_centre,
120 X_SUBJECT => x_c2_rec.subject,
121 X_PROPORTION => x_c2_rec.proportion,
122 X_MODE => 'R'
123 );
124
125 END LOOP;
126
127 EXCEPTION
128 WHEN OTHERS THEN
129 p_n_status := 2;
130 x_err_msg := SUBSTR(SQLERRM, 1, 240);
131 RAISE_APPLICATION_ERROR (-20000, x_err_msg);
132 App_Exception.Raise_Exception;
133 END copy_unit_version;
134
135 END IGS_HE_UV_PKG;