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