[Home] [Help]
PACKAGE BODY: APPS.JE_JEITXLRG_XMLP_PKG
Source
1 PACKAGE BODY JE_JEITXLRG_XMLP_PKG AS
2 /* $Header: JEITXLRGB.pls 120.1 2007/12/25 16:56:27 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 DECLARE
6 P_LEDGER_ID NUMBER;
7 --P_FIRST_DAY DATE;
8 --P_LAST_DAY DATE;
9 P_NAME VARCHAR2(30);
10 P_ADDRESS1 VARCHAR2(40);
11 P_ADDRESS2 VARCHAR2(40);
12 P_ADDRESS3 VARCHAR2(40);
13 P_CODE_CITY VARCHAR2(50);
14 P_COUNTRY VARCHAR2(50);
15 P_SET_OF_BKS_ID NUMBER;
16 P_EFFECTIVE_DATE DATE;
17 X_RETURN_STATUS VARCHAR2(100);
18 X_MSG_COUNT NUMBER;
19 X_MSG_DATA VARCHAR2(100);
20 X_ME_PARTY_ID NUMBER;
21 X_REGISTRATION_NUMBER VARCHAR2(100);
22 ERRBUF VARCHAR2(200);
23 RETCODE NUMBER;
24 L_LE_ID_COUNT NUMBER;
25 BEGIN
26 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
27 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
28 /*SRW.MESSAGE('100'
29 ,'Starting BR Trigger 000')*/NULL;
30 po_moac_utils_pvt.set_org_context(FND_GLOBAL.ORG_ID);
31
32 SELECT
33 NVL(A.LEGAL_ENTITY_ID
34 ,B.LEGAL_ENTITY_ID)
35 INTO P_LEGAL_ENTITY_ID
36 FROM
37 JG_ZZ_VAT_REP_ENTITIES A,
38 JG_ZZ_VAT_REP_ENTITIES B
39 WHERE A.MAPPING_VAT_REP_ENTITY_ID = B.VAT_REPORTING_ENTITY_ID (+)
40 AND A.VAT_REPORTING_ENTITY_ID = P_REP_IDENTIFIER;
41 SELECT
42 count(*)
43 INTO L_LE_ID_COUNT
44 FROM
45 JE_IT_EXEMPT_LETTERS
46 WHERE SET_OF_BOOKS_ID = FND_PROFILE.VALUE('GL_SET_OF_BKS_ID')
47 AND LEGAL_ENTITY_ID IS NULL;
48 IF L_LE_ID_COUNT > 0 THEN
49 JE_IT_TAX_EX_UPGRADE.UPGRADE_MAIN(ERRBUF
50 ,RETCODE
51 ,P_SET_OF_BOOKS_ID
52 ,P_LEGAL_ENTITY_ID);
53 END IF;
54 IF P_SET_OF_BOOKS_ID IS NULL THEN
55 P_LEDGER_ID := FND_PROFILE.VALUE('GL_SET_OF_BKS_ID');
56 ELSE
57 P_LEDGER_ID := TO_NUMBER(P_SET_OF_BOOKS_ID);
58 END IF;
59 SELECT
60 TO_DATE(START_DATE
61 ,'DD-MM-RRRR'),
62 PERIOD_YEAR
63 INTO P_FIRST_DAY,CP_YEAR
64 FROM
65 GL_PERIODS
66 WHERE PERIOD_SET_NAME = P_PERIOD_SET_NAME
67 AND PERIOD_NAME = P_PERIOD_FROM;
68 SELECT
69 TO_DATE(END_DATE
70 ,'DD-MM-RRRR')
71 INTO P_LAST_DAY
72 FROM
73 GL_PERIODS
74 WHERE PERIOD_SET_NAME = P_PERIOD_SET_NAME
75 AND PERIOD_NAME = P_PERIOD_TO;
76 SELECT
77 NAME
78 INTO P_NAME
79 FROM
80 GL_LEDGERS_PUBLIC_V
81 WHERE LEDGER_ID = P_LEDGER_ID;
82 C_NAME := P_NAME;
83 BEGIN
84 SELECT
85 sysdate
86 INTO P_EFFECTIVE_DATE
87 FROM
88 DUAL;
89 SELECT
90 ETBP.PARTY_ID
91 INTO X_ME_PARTY_ID
92 FROM
93 XLE_ETB_PROFILES ETBP
94 WHERE ETBP.MAIN_ESTABLISHMENT_FLAG = 'Y'
95 AND ETBP.LEGAL_ENTITY_ID = P_LEGAL_ENTITY_ID;
96 IF X_ME_PARTY_ID IS NOT NULL THEN
97 X_REGISTRATION_NUMBER := ZX_API_PUB.GET_DEFAULT_TAX_REG(P_API_VERSION => 1.0
98 ,P_INIT_MSG_LIST => NULL
99 ,P_COMMIT => NULL
100 ,P_VALIDATION_LEVEL => NULL
101 ,X_RETURN_STATUS => X_RETURN_STATUS
102 ,X_MSG_COUNT => X_MSG_COUNT
103 ,X_MSG_DATA => X_MSG_DATA
104 ,P_PARTY_ID => X_ME_PARTY_ID
105 ,P_PARTY_TYPE => 'LEGAL_ESTABLISHMENT'
106 ,P_EFFECTIVE_DATE => P_EFFECTIVE_DATE);
107 END IF;
108 C_VAT_REG_NUM := X_REGISTRATION_NUMBER;
109 SELECT
110 REGISTRATION_NUMBER
111 INTO C_TAXPAYER_ID
112 FROM
113 XLE_REGISTRATIONS_V
114 WHERE LEGAL_ENTITY_ID = P_LEGAL_ENTITY_ID
115 AND LEGISLATIVE_CATEGORY = 'INCOME_TAX';
116 SELECT
117 REGISTRATION_NUMBER
118 INTO C_COMMERCIAL_NUM
119 FROM
120 XLE_REGISTRATIONS_V
121 WHERE LEGAL_ENTITY_ID = P_LEGAL_ENTITY_ID
122 AND LEGISLATIVE_CATEGORY = 'COMMERCIAL_LAW';
123 SELECT
124 SUBSTR(XFIV.ADDRESS_LINE_1
125 ,1
126 ,40) ADDRESS_LINE1,
127 NVL(SUBSTR(XFIV.ADDRESS_LINE_2
128 ,1
129 ,40)
130 ,' ') ADDRESS_LINE2,
131 NVL(SUBSTR(XFIV.ADDRESS_LINE_3
132 ,1
133 ,40)
134 ,' ') ADDRESS_LINE3,
135 XFIV.POSTAL_CODE || ' ' || XFIV.TOWN_OR_CITY CODE_CITY,
136 XFIV.COUNTRY COUNTRY
137 INTO P_ADDRESS1,P_ADDRESS2,P_ADDRESS3,P_CODE_CITY,P_COUNTRY
138 FROM
139 XLE_FIRSTPARTY_INFORMATION_V XFIV
140 WHERE XFIV.LEGAL_ENTITY_ID = P_LEGAL_ENTITY_ID;
141 C_ADDRESS1 := P_ADDRESS1 || ' ' || P_ADDRESS2 || ' ' || P_ADDRESS3;
142 C_POSTAL_CODE_CITY := P_CODE_CITY;
143 C_COUNTRY := P_COUNTRY;
144 BEGIN
145 SELECT
146 GEOGRAPHY_NAME
147 INTO CP_COUNTRY_NAME
148 FROM
149 HZ_GEOGRAPHIES
150 WHERE GEOGRAPHY_CODE = P_COUNTRY
151 AND GEOGRAPHY_TYPE = 'COUNTRY';
152 EXCEPTION
153 WHEN NO_DATA_FOUND THEN
154 CP_COUNTRY_NAME := '';
155 WHEN OTHERS THEN
156 CP_COUNTRY_NAME := '';
157 END;
158 EXCEPTION
159 WHEN TOO_MANY_ROWS THEN
160 /*SRW.MESSAGE(9999
161 ,'Esiste piu'' di una company nell'' organizzazione')*/NULL;
162 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
163 WHEN NO_DATA_FOUND THEN
164 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
165 END;
166 LP_LEGAL_ENTITY_ID:=P_LEGAL_ENTITY_ID;
167 END;
168 RETURN (TRUE);
169 END BEFOREREPORT;
170
171 FUNCTION AFTERREPORT RETURN BOOLEAN IS
172 BEGIN
173 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
174 RETURN (TRUE);
175 END AFTERREPORT;
176
177 FUNCTION G_VENDOR_LETTERSGROUPFILTER RETURN BOOLEAN IS
178 BEGIN
179 RETURN (TRUE);
180 END G_VENDOR_LETTERSGROUPFILTER;
181
182 FUNCTION CF_ISSUE_DATEFORMULA(ISSUE_DATE IN DATE) RETURN CHAR IS
183 BEGIN
184 RETURN FND_DATE.DATE_TO_CHARDATE(ISSUE_DATE);
185 END CF_ISSUE_DATEFORMULA;
186
187 FUNCTION CF_ISSUE_DATE1FORMULA(ISSUE_DATE IN DATE) RETURN CHAR IS
188 BEGIN
189 RETURN FND_DATE.DATE_TO_CHARDATE(ISSUE_DATE);
190 END CF_ISSUE_DATE1FORMULA;
191
192 FUNCTION CF_ASTERISKSFORMULA RETURN CHAR IS
193 L_ASTER VARCHAR2(195);
194 BEGIN
195 L_ASTER := RPAD('*'
196 ,195
197 ,'*');
198 RETURN (L_ASTER);
199 END CF_ASTERISKSFORMULA;
200
201 FUNCTION P_FIRST_DAY_P RETURN DATE IS
202 BEGIN
203 RETURN P_FIRST_DAY;
204 END P_FIRST_DAY_P;
205
206 FUNCTION P_LAST_DAY_P RETURN DATE IS
207 BEGIN
208 RETURN P_LAST_DAY;
209 END P_LAST_DAY_P;
210
211 FUNCTION C_NAME_P RETURN VARCHAR2 IS
212 BEGIN
213 RETURN C_NAME;
214 END C_NAME_P;
215
216 FUNCTION C_ADDRESS1_P RETURN VARCHAR2 IS
217 BEGIN
218 RETURN C_ADDRESS1;
219 END C_ADDRESS1_P;
220
221 FUNCTION C_POSTAL_CODE_CITY_P RETURN VARCHAR2 IS
222 BEGIN
223 RETURN C_POSTAL_CODE_CITY;
224 END C_POSTAL_CODE_CITY_P;
225
226 FUNCTION CP_MONTH_P RETURN VARCHAR2 IS
227 BEGIN
228 RETURN CP_MONTH;
229 END CP_MONTH_P;
230
231 FUNCTION C_COUNTRY_P RETURN VARCHAR2 IS
232 BEGIN
233 RETURN C_COUNTRY;
234 END C_COUNTRY_P;
235
236 FUNCTION C_VAT_REG_NUM_P RETURN VARCHAR2 IS
237 BEGIN
238 RETURN C_VAT_REG_NUM;
239 END C_VAT_REG_NUM_P;
240
241 FUNCTION C_TAXPAYER_ID_P RETURN VARCHAR2 IS
242 BEGIN
243 RETURN C_TAXPAYER_ID;
244 END C_TAXPAYER_ID_P;
245
246 FUNCTION C_COMMERCIAL_NUM_P RETURN VARCHAR2 IS
247 BEGIN
248 RETURN C_COMMERCIAL_NUM;
249 END C_COMMERCIAL_NUM_P;
250
251 FUNCTION CP_YEAR_P RETURN NUMBER IS
252 BEGIN
253 RETURN CP_YEAR;
254 END CP_YEAR_P;
255
256 FUNCTION CP_COUNTRY_NAME_P RETURN VARCHAR2 IS
257 BEGIN
258 RETURN CP_COUNTRY_NAME;
259 END CP_COUNTRY_NAME_P;
260
261 END JE_JEITXLRG_XMLP_PKG;
262
263