[Home] [Help]
PACKAGE BODY: APPS.JE_REVERSE_SALES_LIST_PKG
Source
1 PACKAGE BODY JE_REVERSE_SALES_LIST_PKG AS
2 /*$Header: jeukrslrb.pls 120.6 2008/04/15 13:54:07 ashdas noship $*/
3 --------------------------------------------------------------------------------
4 --Global Variables
5 --------------------------------------------------------------------------------
6 g_current_runtime_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
7 g_level_statement CONSTANT NUMBER := fnd_log.level_statement;
8 g_level_procedure CONSTANT NUMBER := fnd_log.level_procedure;
9 g_level_event CONSTANT NUMBER := fnd_log.level_event;
10 g_level_exception CONSTANT NUMBER := fnd_log.level_exception;
11 g_level_error CONSTANT NUMBER := fnd_log.level_error;
12 g_level_unexpected CONSTANT NUMBER := fnd_log.level_unexpected;
13 --------------------------------------------------------------------------------
14 --Private Methods Declaration
15 --------------------------------------------------------------------------------
16 --PUBLIC Methods
17 --------------------------------------------------------------------------------
18 /*===========================================================================+
19 | FUNCTION |
20 | beforeReport() |
21 | |
22 | DESCRIPTION |
23 | This function |
24 | (1) Is called from the Before Report Trigger of CP Reverse Charge |
25 | Sales Listing Report |
26 | SCOPE - Public |
27 | |
28 | NOTES |
29 | |
30 | MODIFICATION HISTORY |
31 | Date Author Description |
32 | ============ ============== ================================= |
33 | 18-DEC-2007 Ashanka Das Initial Version. |
34 | 10-APR-2008 Ashanka Das Modified the code in c_period_range |
35 | 15-APR-2008 Ashanka Das Modified the code in query fetching the |
36 | sales amount.
37 +===========================================================================*/
38 FUNCTION beforeReport RETURN BOOLEAN
39 AS
40 ln_sales_amount NUMBER;
41 ln_total_sales NUMBER;
42 lv_dynamic_sales VARCHAR2(4000);
43 ld_from_date DATE;
44 ld_to_date DATE;
45 lv_cust_vat_reg_num VARCHAR2(30);
46 CURSOR c_period_range IS
47 SELECT ADD_MONTHS(TO_DATE(P_FROM_DATE,'YYYY/MM/DD HH24:MI:SS'),ROWNUM-1) PERIOD
48 FROM fnd_application
49 WHERE ROWNUM <=DECODE( ROUND(MONTHS_BETWEEN(TRUNC(TO_DATE(P_TO_DATE,'YYYY/MM/DD HH24:MI:SS')),
50 TRUNC(TO_DATE(P_FROM_DATE,'YYYY/MM/DD HH24:MI:SS')))),0,1, ROUND(MONTHS_BETWEEN(TRUNC(TO_DATE(P_TO_DATE,'YYYY/MM/DD HH24:MI:SS')),
51 TRUNC(TO_DATE(P_FROM_DATE,'YYYY/MM/DD HH24:MI:SS')))));
52 CURSOR c_customers IS
53 SELECT
54 DISTINCT ctrx.bill_to_customer_id CUSTOMER_ID,
55 hca.account_number CUSTOMER_NUMBER,
56 hp.party_name CUSTOMER_NAME,
57 zl.tax_registration_number CUST_VAT_REG_NUM
58 FROM
59 hz_cust_accounts hca,
60 hz_parties hp,
61 ra_customer_trx_all ctrx,
62 zx_lines zl
63 WHERE ctrx.legal_entity_id = P_LEGAL_ENTITY
64 AND ctrx.complete_flag = 'Y'
65 AND TRUNC(ctrx.trx_date) BETWEEN TRUNC(TO_DATE(P_FROM_DATE,'YYYY/MM/DD HH24:MI:SS'))
66 AND TRUNC(TO_DATE(P_TO_DATE,'YYYY/MM/DD HH24:MI:SS'))
67 AND ctrx.bill_to_customer_id = hca.cust_account_id
68 AND hca.party_id = hp.party_id
69 AND zl.trx_id = ctrx.customer_trx_id
70 AND zl.hq_estb_reg_number = P_TAX_REG_NUM
71 AND zl.legal_entity_id = P_LEGAL_ENTITY
72 AND zl.application_id = 222
73 ORDER BY CUSTOMER_NAME;
74 BEGIN
75 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
76 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_REVERSE_SALES_LIST_PKG.beforeReport','Start FUNCTION beforeReport');
77 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_REVERSE_SALES_LIST_PKG.beforeReport','Parameters are :');
78 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_REVERSE_SALES_LIST_PKG.beforeReport',' P_LEGAL_ENTITY ='||P_LEGAL_ENTITY);
79 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_REVERSE_SALES_LIST_PKG.beforeReport',' P_TAX_REG_NUM ='||P_TAX_REG_NUM);
80 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_REVERSE_SALES_LIST_PKG.beforeReport',' P_FROM_DATE ='||P_FROM_DATE);
81 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_REVERSE_SALES_LIST_PKG.beforeReport',' P_TO_DATE ='||P_TO_DATE);
82 END IF;
83 FOR rec_customers IN c_customers LOOP
84 lv_dynamic_sales := 'EMPTY';
85 ln_total_sales := 0;
86 lv_cust_vat_reg_num := NVL(SUBSTR(rec_customers.CUST_VAT_REG_NUM,3,LENGTH(rec_customers.CUST_VAT_REG_NUM)),-99);
87 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
88 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_REVERSE_SALES_LIST_PKG.beforeReport','In Cursor c_customers');
89 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_REVERSE_SALES_LIST_PKG.beforeReport','CUSTOMER_ID'||rec_customers.CUSTOMER_ID);
90 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_REVERSE_SALES_LIST_PKG.beforeReport','CUSTOMER_NUMBER'||rec_customers.CUSTOMER_NUMBER);
91 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_REVERSE_SALES_LIST_PKG.beforeReport','CUSTOMER_NAME'||rec_customers.CUSTOMER_NAME);
92 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_REVERSE_SALES_LIST_PKG.beforeReport','CUST_VAT_REG_NUM'||rec_customers.CUST_VAT_REG_NUM);
93 END IF;
94 FOR rec_period_range IN c_period_range LOOP
95 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
96 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_REVERSE_SALES_LIST_PKG.beforeReport','Period Range:'||rec_period_range.PERIOD);
97 END IF;
98 ln_sales_amount := 0;
99 IF TO_CHAR(TO_DATE(P_FROM_DATE,'YYYY/MM/DD HH24:MI:SS'),'Mon-YYYY')= TO_CHAR(rec_period_range.PERIOD,'Mon-YYYY') THEN
100 ld_from_date := TO_DATE(P_FROM_DATE,'YYYY/MM/DD HH24:MI:SS');
101 ELSE
102 ld_from_date :=rec_period_range.PERIOD;
103 END IF;
104 IF TO_CHAR(TO_DATE(P_TO_DATE,'YYYY/MM/DD HH24:MI:SS'),'Mon-YYYY')= TO_CHAR(rec_period_range.PERIOD,'Mon-YYYY') THEN
105 ld_to_date := TO_DATE(P_TO_DATE,'YYYY/MM/DD HH24:MI:SS');
106 ELSE
107 ld_to_date := LAST_DAY(rec_period_range.PERIOD);
108 END IF;
109 IF(G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
110 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_REVERSE_SALES_LIST_PKG.beforeReport','From Date:'||ld_from_date);
111 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_REVERSE_SALES_LIST_PKG.beforeReport','To Date:'||ld_to_date);
112 END IF;
113 BEGIN
114 SELECT ROUND(NVL(SUM(trx_lines.extended_amount),0))
115 INTO ln_sales_amount
116 FROM ra_customer_trx_all trx,
117 ra_customer_trx_lines_all trx_lines,
118 zx_lines lines,
119 ra_customer_trx_lines_all trx_tax_lines,
120 zx_report_codes_assoc zrc,
121 zx_reporting_types_vl zrt
122 WHERE trx_lines.line_type ='LINE'
123 AND trx_lines.customer_trx_id = trx.customer_trx_id
124 AND trx_tax_lines.line_type ='TAX'
125 AND trx_lines.customer_trx_line_id = trx_tax_lines.link_to_cust_trx_line_id
126 AND trx_tax_lines.customer_trx_id = trx.customer_trx_id
127 AND trx.bill_to_customer_id = rec_customers.CUSTOMER_ID
128 AND TRUNC(trx.trx_date) BETWEEN TRUNC(ld_from_date) AND TRUNC(ld_to_date)
129 AND trx.legal_entity_id =P_LEGAL_ENTITY
130 AND trx.complete_flag ='Y'
131 AND lines.trx_line_id = trx_lines.customer_trx_line_id
132 AND lines.trx_id = trx.customer_trx_id
133 AND lines.hq_estb_reg_number =P_TAX_REG_NUM
134 AND NVL(lines.tax_registration_number,-99) = NVL(rec_customers.CUST_VAT_REG_NUM,-99)
135 AND lines.legal_entity_id =P_LEGAL_ENTITY
136 AND lines.application_id =222
137 AND trx_tax_lines.vat_tax_id = zrc.entity_id
138 AND zrc.reporting_type_id = zrt.reporting_type_id
139 AND DECODE(zrt.reporting_type_datatype_code,'TEXT',SUBSTR(UPPER(zrc.reporting_code_char_value),1,1),'YES_NO',zrc.reporting_code_char_value,'N') = 'Y' -- Not sure what kind of set up user will do
140 AND TRUNC(trx.trx_date) BETWEEN TRUNC(zrc.effective_from) AND TRUNC(NVL(zrc.effective_to,trx.trx_date))
141 AND zrt.reporting_type_code ='REVERSE_CHARGE_VAT';
142 EXCEPTION
143 WHEN others THEN
144 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
145 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_REVERSE_SALES_LIST_PKG.beforeReport','Exception in getting Sales Amount');
146 END IF;
147 ln_sales_amount := 0;
148 END;
149 IF lv_dynamic_sales = 'EMPTY' THEN
150 lv_dynamic_sales := lv_cust_vat_reg_num||','||ln_sales_amount;
151 ELSE
152 lv_dynamic_sales := lv_dynamic_sales||','||ln_sales_amount;
153 END IF;
154 ln_total_sales := ln_total_sales + ln_sales_amount;
155 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
156 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JE.plsql.JE_REVERSE_SALES_LIST_PKG.beforeReport','Sales Amount'||TO_CHAR(ln_sales_amount));
157 END IF;
158 BEGIN
159 INSERT INTO je_uk_sales_trx_gt
160 (
161 je_info_v1,--Identifier
162 je_info_n1,--CustId
163 je_info_v2,--CustNum
164 je_info_v3,--CustName
165 je_info_v4,--CustVATRegNum
166 je_info_v5,--Mon-YYYY
167 je_info_n2,--SalesAmt
168 je_info_d1 --Date
169 )
170 VALUES
171 (
172 'CL',
173 rec_customers.CUSTOMER_ID,
174 rec_customers.CUSTOMER_NUMBER,
175 rec_customers.CUSTOMER_NAME,
176 lv_cust_vat_reg_num,
177 TO_CHAR(rec_period_range.PERIOD,'Mon-YYYY'),
178 ln_sales_amount,
179 ld_from_date
180 );
181 EXCEPTION
182 WHEN others THEN
183 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
184 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_REVERSE_SALES_LIST_PKG.beforeReport','Exception in Inserting in GT for CL');
185 END IF;
186 END;
187 END LOOP; -- End Loop rec_period_range
188 IF ln_total_sales = 0 THEN
189 DELETE je_uk_sales_trx_gt
190 WHERE je_info_v1 = 'CL'
191 AND je_info_n1 = rec_customers.CUSTOMER_ID
192 AND je_info_v2 = rec_customers.CUSTOMER_NUMBER
193 AND je_info_v4 = lv_cust_vat_reg_num;
194 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
195 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JE.plsql.JE_REVERSE_SALES_LIST_PKG.beforeReport','In DELETE je_uk_sales_trx_gt');
196 END IF;
197 ELSE
198 BEGIN
199 INSERT INTO je_uk_sales_trx_gt
200 (
201 je_info_v1,
202 je_info_v2
203 )
204 VALUES
205 (
206 'CS',
207 lv_dynamic_sales
208 );
209 lv_dynamic_sales := 'EMPTY';
210 EXCEPTION
211 WHEN others THEN
212 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
213 FND_LOG.STRING(G_LEVEL_EXCEPTION,'JE.plsql.JE_REVERSE_SALES_LIST_PKG.beforeReport','In INSERT je_uk_sales_trx_gt for type CS');
214 END IF;
215 NULL;
216 END;
217 END IF;
218 END LOOP; --End Loop rec_customers
219 IF(G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
220 FND_LOG.STRING(G_LEVEL_PROCEDURE,'JE.plsql.JE_REVERSE_SALES_LIST_PKG.beforeReport','Exiting before Report');
221 END IF;
222 RETURN TRUE;
223 END beforeReport;
224 END JE_REVERSE_SALES_LIST_PKG;