1 PACKAGE BODY pay_gb_retro_overlap
2 -- /* $Header: pygbenro.pkb 120.1 2007/08/29 10:35:16 rlingama noship $ */
3 AS
4 gv_package_name VARCHAR2(100);
5
6 -- This procedure is used to Enable Retro Overlap functionality
7 -- ----------------------------------------------------------------------------
8 -- |-------------------------< enable_retro_overlap >--------------------------|
9 -- ----------------------------------------------------------------------------
10 PROCEDURE enable_retro_overlap( errbuf OUT NOCOPY varchar2
11 ,retcode OUT NOCOPY varchar2
12 )
13 IS
14
15 -- Cursor to fetch legislation rule mode
16 CURSOR c_retro_rule_check( cp_rule_type IN varchar2
17 ,cp_legislation_code IN Varchar2)
18 IS
19 SELECT rule_mode
20 FROM pay_legislation_rules
21 WHERE legislation_code = cp_legislation_code
22 AND rule_type = cp_rule_type;
23
24 -- Cursor to fetch upgrade definition id of Enhanced Retropay
25 CURSOR c_upgrade_definitions( cp_short_name IN varchar2
26 ,cp_legislation_code IN Varchar2)
27 IS
28 SELECT upgrade_definition_id
29 FROM pay_upgrade_definitions
30 WHERE legislation_code = cp_legislation_code
31 AND short_name=cp_short_name;
32
33 -- Cursor to fetch the status of Enhanced Retropay
34 CURSOR c_upgrade_status( cp_upgrade_defINation_id IN varchar2
35 ,cp_legISlation_code IN Varchar2)
36 IS
37 SELECT status
38 FROM pay_upgrade_status
39 WHERE legislation_code = cp_legislation_code
40 AND upgrade_definition_id = cp_upgrade_defination_id;
41 --
42
43 lv_qualified VARCHAR2(1);
44 lv_procedure_name VARCHAR2(100);
45 lv_legislation_code VARCHAR2(150);
46 lv_short_name VARCHAR2(100);
47 lv_upgrade_defination_id NUMBER;
48 ln_exists VARCHAR2(1);
49 lv_upgrade_status VARCHAR2(1);
50
51 TYPE character_data_table IS TABLE OF VARCHAR2(280)
52 INDEX BY BINARY_INTEGER;
53 ltt_rule_type character_data_table;
54 ltt_rule_mode character_data_table;
55
56 BEGIN
57
58 lv_procedure_name := '.enable_retro_overlap';
59 fnd_file.put_line(fnd_file.log,'Entering ' || gv_package_name || lv_procedure_name);
60 lv_legislation_code := 'GB';
61
62 -- These are the legislation rules to check whether Enhanced Retropay is enabled or not
63 ltt_rule_type(1) := 'RETRO_DELETE';
64 ltt_rule_mode(1) := 'N';
65 ltt_rule_type(2) := 'ADVANCED_RETRO';
66 ltt_rule_mode(2) := 'Y';
67
68 -- Legislation rules for enabling Retro Overlap
69 ltt_rule_type(3) := 'RETRO_OVERLAP';
70 ltt_rule_mode(3) := 'N';
71
72 -- Checking whether Enhanced Retropay is enabled or not
73 FOR i in 1 ..2 LOOP
74 OPEN c_retro_rule_check(ltt_rule_type(i),lv_legislation_code) ;
75 FETCH c_retro_rule_check into ln_exists;
76
77 IF (c_retro_rule_check%FOUND) AND (ltt_rule_mode(i) = ln_exists )
78 THEN
79 lv_qualified := 'Y';
80 ELSE
81 lv_qualified := 'N';
82 fnd_file.put_line(fnd_file.log,'Retro Overlap upgrade not performed when '||ltt_rule_type(i)||' legislation rule is not set to '||ltt_rule_mode(i));
83 fnd_file.put_line(fnd_file.log,'Enhanced RetroPay is not enabled');
84 exit;
85 END IF;
86
87 CLOSE c_retro_rule_check;
88 END LOOP;
89
90 IF lv_qualified = 'Y'
91 THEN
92 lv_short_name := 'GB_ENHANCED_RETROPAY';
93 OPEN c_upgrade_definitions(lv_short_name,lv_legislation_code);
94 FETCH c_upgrade_definitions into lv_upgrade_defination_id;
95
96 -- Checking whether Enhanced Retropay entire is found in the table pay_upgrade_definitions or not
97 IF(c_upgrade_definitions%FOUND)
98 THEN
99 lv_upgrade_status := 'C';
100 OPEN c_upgrade_status(lv_upgrade_defination_id,lv_legislation_code);
101 FETCH c_upgrade_status into ln_exists;
102
103 -- Checking the status of Enhanced Retro pay
104 IF(c_upgrade_status%FOUND) AND lv_upgrade_status = ln_exists
105 THEN
106 OPEN c_retro_rule_check(ltt_rule_type(3),lv_legislation_code);
107 FETCH c_retro_rule_check into ln_exists;
108
109 -- Checking whether Retro Overlap is Enabled or not
110 IF c_retro_rule_check%FOUND
111 THEN
112 IF (ltt_rule_mode(3)= ln_exists)
113 THEN
114 fnd_file.put_line(fnd_file.log,'Retro Overlap is already Enabled');
115 ELSE
116 -- Updating Rero Ovelap rule mode to N
117 UPDATE pay_legislation_rules
118 SET RULE_MODE = ltt_rule_mode(3)
119 WHERE legislation_code = lv_legislation_code
120 AND rule_type = ltt_rule_type(3);
121 fnd_file.put_line(fnd_file.log,'Retro Overlap Enabled successfully');
122 END IF;
123
124 ELSE
125 --If Retro Overlap is not enabled inserting new legislation rule
126 INSERT INTO pay_legislation_rules(legislation_code,rule_type,rule_mode)
127 VALUES( lv_legislation_code,ltt_rule_type(3),ltt_rule_mode(3));
128 fnd_file.put_line(fnd_file.log,'Retro Overlap Enabled successfully');
129 END IF;
130
131 CLOSE c_retro_rule_check;
132 ElSE
133 fnd_file.put_line(fnd_file.log,'The status of Enhanced RetroPay must be C in pay_upgrade_status table');
134 END IF;
135 CLOSE c_upgrade_status;
136 ELSE
137 fnd_file.put_line(fnd_file.log,'There is no entrie found in pay_upgrade_definitions table for Enhanced RetroPay');
138 END IF;
139 CLOSE c_upgrade_definitions;
140 END IF;
141
142 fnd_file.put_line(fnd_file.log,'Leaving ' || gv_package_name || lv_procedure_name);
143
144 EXCEPTION
145 WHEN others
146 THEN
147 fnd_file.put_line(fnd_file.log,gv_package_name || lv_procedure_name);
148 fnd_file.put_line(fnd_file.log,'ERROR:' || sqlcode ||'-'|| substr(sqlerrm,1,80));
149 RAISE;
150 END enable_retro_overlap;
151
152 BEGIN
153 gv_package_name := 'pay_gb_retro_overlap';
154 END pay_gb_retro_overlap;
[Home] [Help]