1 Package Body PER_GB_P11D_TEMPLATE as
2 /* $Header: pegbxdtp.pkb 120.0 2005/05/31 09:15:46 appldev noship $ */
3 PROCEDURE end_date_2003(p_file_id NUMBER)
4 IS
5 l_upload_name VARCHAR2(1000);
6 l_file_name VARCHAR2(1000);
7 l_gb_file_id NUMBER;
8 l_start_date DATE := TO_DATE('01/01/1900', 'dd/mm/yyyy');
9 l_end_date DATE := TO_DATE('05/04/2003', 'dd/mm/yyyy');
10 BEGIN
11 -- program_name will be used to store the file_name
12 -- this is bcos the file_name in fnd_lobs contains
13 -- the full patch of the doc and not just the file name
14 SELECT program_name
15 INTO l_file_name
16 FROM fnd_lobs
17 WHERE file_id = p_file_id;
18
19 -- the delete will ensure that the patch is rerunnable!
20 DELETE FROM per_gb_xdo_templates
21 WHERE file_name = l_file_name AND
22 effective_start_date = l_start_date AND
23 effective_end_date = l_end_date;
24
25 INSERT INTO per_gb_xdo_templates
26 (file_id,
27 file_name,
28 file_description,
29 effective_start_date,
30 effective_end_date)
31 SELECT p_file_id, l_file_name, 'Template for year 2002-2003',
32 l_start_date, l_end_date
33 FROM fnd_lobs
34 WHERE file_id = p_file_id;
35 END;
36 --
37 PROCEDURE end_date_2004(p_file_id NUMBER)
38 IS
39 l_upload_name VARCHAR2(1000);
40 l_file_name VARCHAR2(1000);
41 l_gb_file_id NUMBER;
42 l_start_date DATE := TO_DATE('06/04/2003', 'dd/mm/yyyy');
43 l_end_date DATE := TO_DATE('05/04/2004', 'dd/mm/yyyy');
44 BEGIN
45 -- program_name will be used to store the file_name
46 -- this is bcos the file_name in fnd_lobs contains
47 -- the full patch of the doc and not just the file name
48 SELECT program_name
49 INTO l_file_name
50 FROM fnd_lobs
51 WHERE file_id = p_file_id;
52
53 -- the delete will ensure that the patch is rerunnable!
54 DELETE FROM per_gb_xdo_templates
55 WHERE file_name = l_file_name AND
56 effective_start_date = l_start_date AND
57 effective_end_date = l_end_date;
58
59 INSERT INTO per_gb_xdo_templates
60 (file_id,
61 file_name,
62 file_description,
63 effective_start_date,
64 effective_end_date)
65 SELECT p_file_id, l_file_name, 'Template for year 2003-2004',
66 l_start_date, l_end_date
67 FROM fnd_lobs
68 WHERE file_id = p_file_id;
69 END;
70 --
71 PROCEDURE end_date_2005(p_file_id NUMBER)
72 IS
73 l_upload_name VARCHAR2(1000);
74 l_file_name VARCHAR2(1000);
75 l_gb_file_id NUMBER;
76 l_start_date DATE := TO_DATE('06/04/2004', 'dd/mm/yyyy');
77 l_end_date DATE := TO_DATE('05/04/2005', 'dd/mm/yyyy');
78 BEGIN
79 -- program_name will be used to store the file_name
80 -- this is bcos the file_name in fnd_lobs contains
81 -- the full patch of the doc and not just the file name
82 SELECT program_name
83 INTO l_file_name
84 FROM fnd_lobs
85 WHERE file_id = p_file_id;
86
87 -- the delete will ensure that the patch is rerunnable!
88 DELETE FROM per_gb_xdo_templates
89 WHERE file_name = l_file_name AND
90 effective_start_date = l_start_date AND
91 effective_end_date = l_end_date;
92
93 INSERT INTO per_gb_xdo_templates
94 (file_id,
95 file_name,
96 file_description,
97 effective_start_date,
98 effective_end_date)
99 SELECT p_file_id, l_file_name, 'Template for year 2004-2005',
100 l_start_date, l_end_date
101 FROM fnd_lobs
102 WHERE file_id = p_file_id;
103 END;
104 --
105 END;