1 PACKAGE BODY JE_ZZ_AUTO_INVOICE as
2 /* $Header: jezzraib.pls 120.6 2011/08/18 12:51:51 spasupun ship $ */
3
4 /*----------------------------------------------------------------------------*
5 | PUBLIC FUNCTIONS/PROCEDURES |
6 *----------------------------------------------------------------------------*/
7
8 /*----------------------------------------------------------------------------*
9 | FUNCTION |
10 | validate_gdff |
11 | |
12 | DESCRIPTION |
13 | |
14 | PARAMETERS |
15 | INPUT |
16 | p_request_id Number -- Concurrent Request_id |
17 | |
18 | RETURNS |
19 | 0 Number -- Validation Fails, if there is any |
20 | exceptional case which is handled |
21 | in WHEN OTHERS |
22 | 1 Number -- Validation Succeeds |
23 | Although this package was really meant for validation we can 'bend' its |
24 | use to insert the HU data we want and pass back to the JG package |
25 | indicating that everything passes validation. |
26 *----------------------------------------------------------------------------*/
27 PG_DEBUG varchar2(1);
28
29 FUNCTION validate_gdff (p_request_id IN NUMBER) RETURN NUMBER IS
30
31 return_code NUMBER (1);
32 l_country_code VARCHAR2(2);
33 l_org_id NUMBER;
34
35
36 ------------------------------------------------------------
37 -- Main function body. --
38 ------------------------------------------------------------
39 BEGIN
40
41 PG_DEBUG := NVL(FND_PROFILE.value('MSC_ATP_DEBUG'), 'N');
42
43 IF PG_DEBUG in ('Y', 'C') THEN
44 arp_standard.debug('JE JE_ZZ_AUTO_INVOICE.validate_gdff()+');
45 END IF;
46
47 l_org_id := arp_global.sysparam.org_id;
48
49 return_code := 1;
50 l_country_code := JG_ZZ_SHARED_PKG.GET_COUNTRY(l_org_id);
51
52 IF PG_DEBUG in ('Y', 'C') THEN
53 arp_standard.debug('validate_gdff: ' || '-- Country Code: '||l_country_code);
54 arp_standard.debug('validate_gdff: ' || '-- Request Id: '||to_char(p_request_id));
55 END IF;
56
57 --bug#11891772
58 IF l_country_code IN ('HU','PL','CZ','SK') THEN
59
60 arp_standard.debug('JG Updating Tax Invoice Date ');
61
62 BEGIN
63
64 UPDATE zx_lines_det_factors zxld
65 SET zxld.tax_invoice_date = (SELECT MAX(trxd.gl_date)
66 FROM ra_cust_trx_line_gl_dist_all trxd
67 WHERE trxd.request_id= p_request_id
68 AND trxd.org_id= l_org_id
69 AND trxd.customer_trx_id=zxld.trx_id
70 AND trxd.set_of_books_id = zxld.ledger_id
71 AND trxd.account_class = 'REC'
72 AND trxd.latest_rec_flag = 'Y'
73 AND zxld.application_id=222
74 AND zxld.tax_invoice_date IS NULL)
75 WHERE EXISTS (SELECT 1
76 FROM ra_cust_trx_line_gl_dist_all trxd1
77 WHERE trxd1.request_id= p_request_id
78 AND trxd1.org_id= l_org_id
79 AND trxd1.customer_trx_id=zxld.trx_id
80 AND trxd1.set_of_books_id = zxld.ledger_id
81 AND trxd1.account_class = 'REC'
82 AND trxd1.latest_rec_flag = 'Y' )
83 AND zxld.application_id=222
84 AND zxld.tax_invoice_date IS NULL ;
85
86 EXCEPTION
87 WHEN OTHERS THEN
88
89 arp_standard.debug('JG Error while updating the Tax Invoice Date');
90 arp_standard.debug('JG SQL Error : '||SQLERRM);
91
92 END;
93
94 END IF;
95 /* ------------------------------------------------------------
96 -- Check for HU country code, if present then we want to --
97 -- enter the context and attribute1 data --
98 ------------------------------------------------------------
99
100 IF l_country_code = 'HU' THEN
101
102
103 IF PG_DEBUG in ('Y', 'C') THEN
104 arp_standard.debug('validate_gdff: ' || '-- Inserting HU specific GDF data');
105 END IF;
106
107 update ra_interface_lines_gt
108 set HEADER_GDF_ATTR_CATEGORY = 'JE.HU.ARXTWMAI.TAX_DATE'
109 , HEADER_GDF_ATTRIBUTE1 = fnd_date.date_to_canonical(GL_DATE)
110 where request_id = p_request_id ;
111
112
113 ------------------------------------------------------------
114 -- Check for PL country code, if present then we want to --
115 -- enter the context and attribute1 data --
116 ------------------------------------------------------------
117
118 ELSIF l_country_code = 'PL' THEN
119
120
121 IF PG_DEBUG in ('Y', 'C') THEN
122 arp_standard.debug('validate_gdff: ' || '-- Inserting PL specific GDF data');
123 END IF;
124
125 update ra_interface_lines_gt
126 set HEADER_GDF_ATTR_CATEGORY = 'JE.PL.ARXTWMAI.TAX_DATE'
127 , HEADER_GDF_ATTRIBUTE1 = fnd_date.date_to_canonical(GL_DATE)
128 where request_id = p_request_id ;
129
130 ------------------------------------------------------------
131 -- Check for CZ country code, if present then we want to --
132 -- enter the context and attribute1 data --
133 -- Czech requirements are that the tax date should be: --
134 -- Tax Date : = Booking date of Sales Order if item is --
135 -- not shippable or the oldest Ship Confirm date from all --
136 -- item lines if item is ship. These are the rules --
137 -- Autoinvoice uses to derive the GL date so we can just --
138 -- use that as the tax date --
139 ------------------------------------------------------------
140
141
142
143 ELSIF l_country_code = 'CZ' THEN
144
145 IF PG_DEBUG in ('Y', 'C') THEN
146 arp_standard.debug('validate_gdff: ' || '-- Inserting CZ specific GDF data');
147 END IF;
148
149 update ra_interface_lines_gt
150 set HEADER_GDF_ATTR_CATEGORY = 'JE.CZ.ARXTWMAI.TAX_DATE'
151 , HEADER_GDF_ATTRIBUTE1 = fnd_date.date_to_canonical(GL_DATE)
152 where request_id = p_request_id ;
153
154
155
156 END IF;
157 */
158
159 RETURN return_code;
160
161 EXCEPTION
162 WHEN OTHERS THEN
163
164 IF PG_DEBUG in ('Y', 'C') THEN
165 arp_standard.debug('validate_gdff: ' || '-- Return From Exception when others');
166 arp_standard.debug('validate_gdff: ' || '-- Return Code: 0');
167 arp_standard.debug('JE_ZZ_AUTO_INVOICE.validate_gdff()-');
168 END IF;
169
170 RETURN 0;
171
172 END validate_gdff;
173
174
175 END JE_ZZ_AUTO_INVOICE;
[Home] [Help]