1 PACKAGE BODY JG_ZZ_VAT_YEARLY_EXT_PKG
2 -- $Header: jgzzyexrb.pls 120.1 2010/08/05 14:43:10 gkumares noship $
3 --*************************************************************************
4 -- Copyright (c) 2000 Oracle Product Development
5 -- All rights reserved
6 --*************************************************************************
7
8 -- HEADER
9 -- Source control header
10
11 -- PROGRAM NAME
12 -- jgzzyexrb.pls
13
14 -- DESCRIPTION
15 -- This script creates the package specification of JG_ZZ_VAT_YEARLY_EXT_PKG
16 -- This package body is used to report on EMEA VAT: Yearly Extract Report.
17
18 -- USAGE
19 -- To install sqlplus <apps_user>/<apps_pwd> @jgzzyexrb.pls
20 -- To execute sqlplus <apps_user>/<apps_pwd> JG_ZZ_VAT_YEARLY_EXT_PKG
21
22 -- PROGRAM LIST DESCRIPTION
23 -- beforeReport This Function is used to dynamically get the values for the Lexical References used in the Query of the Data template.
24
25 -- DEPENDENCIES
26 -- None
27
28 -- CALLED BY
29 -- EMEA VAT: Yearly Extract Report.
30
31 -- LAST UPDATE DATE 20-Oct-2008
32 -- Date the program has been modified for the last time
33
34 -- HISTORY
35 -- =======
36
37 -- VERSION DATE AUTHOR(S) DESCRIPTION
38 -- ------- ----------- --------------- ------------------------------------
39 -- Draft1A 20-Oct-2008 Rakesh Pulla Initial Creation
40 --************************************************************************
41 AS
42 FUNCTION beforeReport RETURN BOOLEAN IS
43
44 CURSOR c_vat_period(ln_reporting_entity_id IN NUMBER, ln_tax_cal_year IN NUMBER) IS
45 SELECT DISTINCT jgvrs.source source
46 FROM jg_zz_vat_rep_entities jgvre
47 ,jg_zz_vat_rep_status jgvrs
48 WHERE jgvrs.vat_reporting_entity_id = jgvre.vat_reporting_entity_id
49 AND jgvre.vat_reporting_entity_id = ln_reporting_entity_id
50 AND jgvrs.final_reporting_status_flag = 'S'
51 AND jgvrs.tax_calendar_year = ln_tax_cal_year
52 ORDER BY jgvrs.source;
53
54 CURSOR c_vat_act_period(lc_tax_cal_name IN VARCHAR2,lc_source IN VARCHAR2, ln_reporting_entity_id IN NUMBER,
55 ln_tax_cal_year IN NUMBER ) IS
56 SELECT glp.period_name period_name
57 FROM gl_periods glp
58 WHERE glp.period_year = ln_tax_cal_year
59 AND glp.period_set_name = lc_tax_cal_name
60 AND glp.period_name NOT IN
61 (SELECT jgvrs.tax_calendar_period period_name
62 FROM jg_zz_vat_rep_entities jgvre,
63 jg_zz_vat_rep_status jgvrs
64 WHERE jgvrs.vat_reporting_entity_id = jgvre.vat_reporting_entity_id
65 AND jgvre.vat_reporting_entity_id = ln_reporting_entity_id
66 AND jgvrs.final_reporting_status_flag = 'S'
67 AND jgvrs.source = lc_source
68 AND jgvrs.tax_calendar_year = ln_tax_cal_year)
69 ORDER BY glp.period_name;
70
71 CURSOR c_tax_calendar_name IS
72 SELECT DISTINCT jgvrs.tax_calendar_name
73 FROM jg_zz_vat_rep_status jgvrs
74 WHERE jgvrs.vat_reporting_entity_id = p_reporting_entity_id
75 AND jgvrs.tax_calendar_year = p_tax_calendar_year;
76
77 lc_final_not_run VARCHAR2(150);
78 lc_final_run VARCHAR2(300);
79 lc_tax_calendar_name VARCHAR2(30);
80 ln_count NUMBER;
81
82 BEGIN
83 -- Begining of the Function beforereport
84 OPEN c_tax_calendar_name;
85 LOOP
86 FETCH c_tax_calendar_name INTO lc_tax_calendar_name;
87 EXIT WHEN c_tax_calendar_name%NOTFOUND;
88 END LOOP;
89 CLOSE c_tax_calendar_name;
90
91 FOR i IN c_vat_period(p_reporting_entity_id, p_tax_calendar_year) LOOP
92 ln_count := 1;
93 FOR j IN c_vat_act_period(lc_tax_calendar_name, i.source, p_reporting_entity_id
94 ,p_tax_calendar_year ) LOOP
95 IF ln_count = 1 THEN
96 lc_final_not_run:= FND_MESSAGE.get_string('JG','JG_ZZ_FINAL_REPORTING_NOTRUN');
97 FND_FILE.put_line(FND_FILE.log,lc_final_not_run || i.source);
98 END IF;
99 FND_FILE.put_line(FND_FILE.log, j.period_name);
100 GN_RETURN_CODE := 1;
101 ln_count:= ln_count + 1;
102 END LOOP;
103
104 lc_final_run := FND_MESSAGE.get_string('JG','JG_ZZ_FINAL_REPORTING_RUN');
105 FND_FILE.PUT_LINE(FND_FILE.log, lc_final_run);
106
107 END LOOP;
108
109 RETURN(TRUE);
110 END beforeReport;
111 -- End of the beforereport
112
113 FUNCTION afterReport RETURN BOOLEAN IS
114 ln_request_id NUMBER;
115 l_temp BOOLEAN;
116
117 BEGIN
118
119 MO_GLOBAL.init('JG');
120 ln_request_id := FND_GLOBAL.conc_request_id ;
121
122 IF GN_RETURN_CODE = 1 THEN
123 l_temp := FND_CONCURRENT.set_completion_status
124 (status => 'WARNING'
125 ,message => NULL);
126
127 END IF;
128 RETURN(TRUE);
129 END afterReport;
130 -- End of the afterReport
131
132 END JG_ZZ_VAT_YEARLY_EXT_PKG;
[Home] [Help]