[Home] [Help]
PACKAGE BODY: APPS.IGI_IGIPCBAP_XMLP_PKG
Source
1 PACKAGE BODY IGI_IGIPCBAP_XMLP_PKG AS
2 /* $Header: IGIPCBAPB.pls 120.0.12010000.1 2008/07/29 08:59:00 appldev ship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 IF P_RUN_AOL = 'Y' THEN
6 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
7 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
8 END IF;
9 GET_PREVIOUS_YEAR;
10 IF P_DEBUG_SWITCH = 'Y' THEN
11 /*SRW.MESSAGE(100
12 ,' Completed GET_PREVIOUS_YEAR')*/NULL;
13 END IF;
14 GET_CURRENT_YEAR;
15 IF P_DEBUG_SWITCH = 'Y' THEN
16 /*SRW.MESSAGE(101
17 ,'Completed GET_CURRENT_YEAR ')*/NULL;
18 END IF;
19 FIND_INVOICE;
20 IF P_DEBUG_SWITCH = 'Y' THEN
21 /*SRW.MESSAGE(102
22 ,'Completed FIND_INVOICE')*/NULL;
23 END IF;
24 FIND_CLOSING_BALANCE;
25 IF P_DEBUG_SWITCH = 'Y' THEN
26 /*SRW.MESSAGE(103
27 ,'Completed FIND_CLOSING_BALANCE')*/NULL;
28 END IF;
29 INSERT_INTERFACE;
30 IF P_DEBUG_SWITCH = 'Y' THEN
31 /*SRW.MESSAGE(104
32 ,'Completed INSERT_INTERFACE ')*/NULL;
33 END IF;
34 RETURN (TRUE);
35 END BEFOREREPORT;
36
37 FUNCTION AFTERPFORM RETURN BOOLEAN IS
38 BEGIN
39 SELECT
40 GP.END_DATE,
41 GSOB.CURRENCY_CODE
42 INTO LP_END_DATE,LP_CURRENCY_CODE
43 FROM
44 GL_PERIODS GP,
45 GL_SETS_OF_BOOKS GSOB
46 WHERE GSOB.SET_OF_BOOKS_ID = P_SOB_ID
47 AND GSOB.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
48 AND GP.PERIOD_NAME = P_TO_PERIOD;
49 IF P_FROM_PERIOD IS NOT NULL THEN
50 SELECT
51 GP.START_DATE
52 INTO LP_START_DATE
53 FROM
54 GL_PERIODS GP,
55 GL_SETS_OF_BOOKS GSOB
56 WHERE GSOB.SET_OF_BOOKS_ID = P_SOB_ID
57 AND GSOB.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
58 AND GP.PERIOD_NAME = P_FROM_PERIOD;
59 ELSE
60 SELECT
61 GP.START_DATE
62 INTO LP_START_DATE
63 FROM
64 GL_PERIODS GP,
65 GL_SETS_OF_BOOKS GSOB
66 WHERE GSOB.SET_OF_BOOKS_ID = P_SOB_ID
67 AND GSOB.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
68 AND GP.PERIOD_NUM = 1
69 AND GP.PERIOD_TYPE = GSOB.ACCOUNTED_PERIOD_TYPE
70 AND GP.PERIOD_YEAR = (
71 SELECT
72 G.PERIOD_YEAR
73 FROM
74 GL_PERIODS G,
75 GL_SETS_OF_BOOKS GS
76 WHERE GS.SET_OF_BOOKS_ID = P_SOB_ID
77 AND GS.PERIOD_SET_NAME = G.PERIOD_SET_NAME
78 AND G.PERIOD_NAME = P_TO_PERIOD );
79 END IF;
80 SELECT
81 TO_CHAR(TO_DATE('3112'
82 ,' DDMM')
83 ,'DD-MON-') || TO_CHAR(ADD_MONTHS(TO_DATE(LP_END_DATE
84 ,'DD-MON-RRRR')
85 ,-12)
86 ,'RRRR')
87 INTO LP_LAST_DAY
88 FROM
89 SYS.DUAL;
90 LP_YEAR := TO_CHAR(TO_DATE(LP_LAST_DAY
91 ,'DD-MON-RRRR')
92 ,'RRRR');
93 RETURN (TRUE);
94 END AFTERPFORM;
95
96 FUNCTION AFTERREPORT RETURN BOOLEAN IS
97 BEGIN
98 ROLLBACK;
99 IF P_DEBUG_SWITCH = 'Y' THEN
100 /*SRW.MESSAGE(110
101 ,'Report has completed')*/NULL;
102 END IF;
103 IF P_RUN_AOL = 'Y' THEN
104 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
105 END IF;
106 RETURN (TRUE);
107 END AFTERREPORT;
108
109 FUNCTION CF_SOB_NAMEFORMULA RETURN CHAR IS
110 L_NAME GL_SETS_OF_BOOKS.NAME%TYPE;
111 BEGIN
112 SELECT
113 NAME
114 INTO L_NAME
115 FROM
116 GL_SETS_OF_BOOKS
117 WHERE SET_OF_BOOKS_ID = P_SOB_ID;
118 RETURN (L_NAME);
119 EXCEPTION
120 WHEN NO_DATA_FOUND THEN
121 /*SRW.MESSAGE(200
122 ,'ERROR: Set of Books was not found!')*/NULL;
123 END CF_SOB_NAMEFORMULA;
124
125 FUNCTION CF_NO_DATAFORMULA(INVOICE_NUM IN VARCHAR2) RETURN NUMBER IS
126 BEGIN
127 IF INVOICE_NUM IS NOT NULL THEN
128 CP_NO_DATA_FOUND := 'Y';
129 END IF;
130 RETURN (0);
131 END CF_NO_DATAFORMULA;
132
133 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
134 BEGIN
135 RETURN (TRUE);
136 END BEFOREPFORM;
137
138 FUNCTION BETWEENPAGE RETURN BOOLEAN IS
139 BEGIN
140 RETURN (TRUE);
141 END BETWEENPAGE;
142
143 FUNCTION P_SOB_IDVALIDTRIGGER RETURN BOOLEAN IS
144 BEGIN
145 RETURN (TRUE);
146 END P_SOB_IDVALIDTRIGGER;
147
148 FUNCTION CP_NO_DATA_FOUND_P RETURN VARCHAR2 IS
149 BEGIN
150 RETURN CP_NO_DATA_FOUND;
151 END CP_NO_DATA_FOUND_P;
152
153 PROCEDURE FIND_CLOSING_BALANCE IS
154 L_SEGMENT VARCHAR2(25);
155 L_INSERT VARCHAR2(5000);
156 BEGIN
157 L_SEGMENT := P_ACCT_SEGMENT;
158 L_INSERT := 'INSERT INTO IGI_CBR_AP_RECONCILE(
159 ACCOUNT_NUM
160 , INVOICE_AMOUNT
161 , ORG_ID
162 , TYPE)
163 SELECT gcc.' || L_SEGMENT || ' account
164 , SUM(idv.amount * (air.remainder) /
165 DECODE( ai.invoice_currency_code,
166 asp.base_currency_code,
167 DECODE( ai.invoice_amount, 0, 1, ai.invoice_amount),
168 DECODE(ai.base_amount, 0, 1, ai.base_amount)) ) invoice_amount
169 , ai.org_id
170 , ''UB'' Type
171 FROM AP_INVOICES_ALL AI
172 , PO_VENDORS PV
173 , IGI_CBR_AP_INV_RECONCILE AIR
174 , IGI_CBR_AP_INV_DIST_V IDV
175 , AP_SYSTEM_PARAMETERS_ALL ASP
176 , GL_CODE_COMBINATIONS GCC
177 WHERE ai.set_of_books_id = ' || P_SOB_ID || '
178 AND ai.invoice_id = idv.invoice_id
179 AND ai.invoice_id = air.invoice_id
180 AND ai.vendor_id = pv.vendor_id
181 AND asp.set_of_books_id = ai.set_of_books_id
182 AND NVL(ai.org_id, -1) = NVL(asp.org_id, -1)
183 AND gcc.code_combination_id = idv.dist_code_combination_id
184 AND gcc.enabled_flag = ''Y''
185 AND air.status = ''P''
186 AND gcc.' || L_SEGMENT || ' BETWEEN ''' || P_FROM_SEGMENT || ''' AND ''' || P_TO_SEGMENT || '''
187 GROUP BY
188 gcc.' || L_SEGMENT || '
189 , ai.org_id';
190 EXECUTE IMMEDIATE
191 L_INSERT;
192 END FIND_CLOSING_BALANCE;
193
194 PROCEDURE FIND_INVOICE IS
195 L_SEGMENT VARCHAR2(25);
196 L_INSERT VARCHAR2(5000);
197 BEGIN
198 L_SEGMENT := P_ACCT_SEGMENT;
199 L_INSERT := 'INSERT INTO IGI_CBR_AP_RECONCILE(
200 ACCOUNT_NUM
201 , DOC_SEQ_NUM
202 , INVOICE_NUM
203 , VENDOR_NUM
204 , VENDOR_NAME
205 , INVOICE_DATE
206 , ORG_ID
207 , INVOICE_AMOUNT)
208 SELECT gcc.' || L_SEGMENT || ' account
209 , ai.doc_sequence_value doc_sec_num
210 , ai.invoice_num
211 , pv.segment1
212 , pv.vendor_name
213 , ai.invoice_date
214 , ai.org_id
215 , (idv.amount * air.remainder) /
216 DECODE( ai.invoice_currency_code,
217 asp.base_currency_code,
218 DECODE( ai.invoice_amount, 0, 1, ai.invoice_amount),
219 DECODE( ai.base_amount, 0, 1, ai.base_amount) ) invoice_amount
220 FROM AP_INVOICES_ALL ai
221 , IGI_CBR_AP_INV_DIST_V idv
222 , IGI_CBR_AP_INV_RECONCILE air
223 , PO_VENDORS pv
224 , AP_SYSTEM_PARAMETERS_ALL asp
225 , GL_CODE_COMBINATIONS gcc
226 WHERE ai.set_of_books_id = ' || P_SOB_ID || '
227 AND ai.invoice_id = idv.invoice_id
228 AND ai.invoice_id = air.invoice_id
229 AND idv.invoice_id = air.invoice_id
230 AND pv.vendor_id = ai.vendor_id
231 AND asp.set_of_books_id = ai.set_of_books_id
232 AND gcc.code_combination_id = idv.dist_code_combination_id
233 AND gcc.enabled_flag = ''Y''
234 AND air.status = ''C''
235 AND gcc.' || L_SEGMENT || ' BETWEEN ''' || P_FROM_SEGMENT || ''' AND ''' || P_TO_SEGMENT || '''
236 AND NVL(ai.org_id, -1) = NVL(asp.org_id, -1)
237 AND NVL(ai.org_id, -1) = NVL(air.org_id, -1)';
238 EXECUTE IMMEDIATE
239 L_INSERT;
240 END FIND_INVOICE;
241
242 PROCEDURE GET_PREVIOUS_YEAR IS
243 L_SEGMENT VARCHAR2(25);
244 L_INSERT VARCHAR2(5000);
245 BEGIN
246 L_SEGMENT := P_ACCT_SEGMENT;
247 L_INSERT := 'INSERT INTO IGI_CBR_AP_INV_RECONCILE(
248 INVOICE_ID
249 , REMAINDER
250 , ORG_ID
251 , STATUS)
252 SELECT AI.invoice_id
253 , (NVL(ai.invoice_amount ,0) - SUM(NVL(aip.amount, 0)) )
254 , AI.org_id
255 , ''P''
256 FROM AP_INVOICES_ALL AI,
257 AP_INVOICE_PAYMENTS_ALL AIP
258 WHERE AI.INVOICE_ID = AIP.INVOICE_ID (+)
259 AND AI.PAYMENT_STATUS_FLAG = ''N''
260 AND EXISTS ( SELECT ''Y''
261 FROM XLA_AE_HEADERS AEH1,
262 AP_INVOICE_DISTRIBUTIONS_ALL AID
263 WHERE AI.INVOICE_ID = AID.INVOICE_ID
264 AND AEH1.LEDGER_ID = ' || P_SOB_ID || '
265 AND NVL(AEH1.GL_TRANSFER_STATUS_CODE, ''N'') = ''Y''
266 AND to_char(AEH1.ACCOUNTING_DATE, ''RRRR'') = ' || LP_YEAR || '
267 AND AEH1.APPLICATION_ID = 200
268 AND AID.ACCOUNTING_EVENT_ID = AEH1.EVENT_ID
269 AND AID.SET_OF_BOOKS_ID = AEH1.LEDGER_ID)
270 GROUP BY ai.invoice_id,ai.org_id,ai.invoice_amount
271 HAVING (NVL(ai.invoice_amount, 0) - SUM(NVL(aip.amount, 0))) <> 0
272 UNION
273 SELECT AI.invoice_id
274 , (NVL(ai.invoice_amount ,0) - SUM(NVL(aip.amount, 0)) )
275 , AI.org_id
276 , ''P''
277 FROM AP_INVOICES_ALL AI,
278 AP_INVOICE_PAYMENTS_ALL AIP
279 WHERE AI.INVOICE_ID = AIP.INVOICE_ID
280 AND AI.PAYMENT_STATUS_FLAG = ''P''
281 AND EXISTS (SELECT ''Y''
282 FROM XLA_AE_HEADERS AEH1
283 WHERE AEH1.LEDGER_ID = ' || P_SOB_ID || '
284 AND NVL(AEH1.GL_TRANSFER_STATUS_CODE, ''N'') = ''Y''
285 AND to_char(AEH1.ACCOUNTING_DATE, ''RRRR'') = ' || LP_YEAR || '
286 AND AEH1.APPLICATION_ID = 200
287 AND AIP.ACCOUNTING_EVENT_ID = AEH1.EVENT_ID
288 AND AIP.SET_OF_BOOKS_ID = AEH1.LEDGER_ID)
289 AND NOT EXISTS ( SELECT ''Y''
290 FROM XLA_AE_HEADERS AEH2
291 WHERE AEH2.LEDGER_ID = ' || P_CASH_SOB_ID || '
292 AND NVL(AEH2.GL_TRANSFER_STATUS_CODE, ''N'') = ''Y''
293 AND to_char(AEH2.ACCOUNTING_DATE, ''RRRR'') = ' || LP_YEAR || '
294 AND AEH2.APPLICATION_ID = 200
295 AND AIP.ACCOUNTING_EVENT_ID = AEH2.EVENT_ID
296 )
297
298 GROUP BY ai.invoice_id,ai.org_id,ai.invoice_amount
299 HAVING (NVL(ai.invoice_amount, 0) - SUM(NVL(aip.amount, 0))) <> 0';
300 EXECUTE IMMEDIATE
301 L_INSERT;
302 END GET_PREVIOUS_YEAR;
303
304 PROCEDURE GET_CURRENT_YEAR IS
305 L_SEGMENT VARCHAR2(25);
306 L_INSERT VARCHAR2(5000);
307 BEGIN
308 L_SEGMENT := P_ACCT_SEGMENT;
309 L_INSERT := 'INSERT INTO IGI_CBR_AP_INV_RECONCILE(
310 INVOICE_ID
311 , REMAINDER
312 , ORG_ID
313 , STATUS)
314
315 SELECT
316 AI.invoice_id
317 , (NVL(ai.invoice_amount ,0) - SUM(NVL(aip.amount, 0)) )
318 , AI.org_id
319 , ''C''
320 FROM AP_INVOICES_ALL AI,
321 AP_INVOICE_PAYMENTS_ALL AIP
322 WHERE AI.INVOICE_ID = AIP.INVOICE_ID (+)
323 AND AI.PAYMENT_STATUS_FLAG = ''N''
324 AND EXISTS ( SELECT ''Y''
325 FROM XLA_AE_HEADERS AEH1,
326 AP_INVOICE_DISTRIBUTIONS_ALL AID
327 WHERE AI.INVOICE_ID = AID.INVOICE_ID
328 AND AEH1.LEDGER_ID = ' || P_SOB_ID || '
329 AND NVL(AEH1.GL_TRANSFER_STATUS_CODE, ''N'') = ''Y''
330 AND AEH1.ACCOUNTING_DATE BETWEEN ''' || LP_START_DATE || ''' AND ''' || LP_END_DATE || '''
331 AND AEH1.APPLICATION_ID = 200
332 AND AID.ACCOUNTING_EVENT_ID = AEH1.EVENT_ID
333 AND AID.SET_OF_BOOKS_ID = AEH1.LEDGER_ID )
334 GROUP BY ai.invoice_id,ai.org_id,ai.invoice_amount
335 HAVING (NVL(ai.invoice_amount, 0) - SUM(NVL(aip.amount, 0))) <> 0
336 UNION
337 SELECT AI.invoice_id
338 , (NVL(ai.invoice_amount ,0) - SUM(NVL(aip.amount, 0)) )
339 , AI.org_id
340 , ''C''
341 FROM AP_INVOICES_ALL AI,
342 AP_INVOICE_PAYMENTS_ALL AIP
343 WHERE AI.INVOICE_ID = AIP.INVOICE_ID
344 AND AI.PAYMENT_STATUS_FLAG = ''P''
345 AND EXISTS (SELECT ''Y''
346 FROM XLA_AE_HEADERS AEH1
347 WHERE AEH1.LEDGER_ID = ' || P_SOB_ID || '
348 AND NVL(AEH1.GL_TRANSFER_STATUS_CODE, ''N'') = ''Y''
349 AND AEH1.ACCOUNTING_DATE BETWEEN ''' || LP_START_DATE || ''' AND ''' || LP_END_DATE || '''
350 AND AEH1.APPLICATION_ID = 200
351 AND AIP.ACCOUNTING_EVENT_ID = AEH1.EVENT_ID
352 AND AIP.SET_OF_BOOKS_ID = AEH1.LEDGER_ID)
353 AND NOT EXISTS ( SELECT ''Y''
354 FROM XLA_AE_HEADERS AEH2
355 WHERE AEH2.LEDGER_ID = ' || P_CASH_SOB_ID || '
356 AND NVL(AEH2.GL_TRANSFER_STATUS_CODE, ''N'') = ''Y''
357 AND AEH2.ACCOUNTING_DATE BETWEEN ''' || LP_START_DATE || ''' AND ''' || LP_END_DATE || '''
358 AND AEH2.APPLICATION_ID = 200
359 AND AIP.ACCOUNTING_EVENT_ID = AEH2.EVENT_ID
360 )
361
362 GROUP BY ai.invoice_id,ai.org_id,ai.invoice_amount
363 HAVING (NVL(ai.invoice_amount, 0) - SUM(NVL(aip.amount, 0))) <> 0';
364 EXECUTE IMMEDIATE
365 L_INSERT;
366 END GET_CURRENT_YEAR;
367
368 PROCEDURE INSERT_INTERFACE IS
369 L_SEGMENT VARCHAR2(25);
370 L_INSERT VARCHAR2(5000);
371 BEGIN
372 L_SEGMENT := P_ACCT_SEGMENT;
373 L_INSERT := 'INSERT INTO IGI_CBR_AP_INTERFACE(
374 ORG_ID
375 , ORGANIZATION
376 , ACCOUNT_NUM
377 , DOC_SEQ_NUM
378 , INVOICE_NUM
379 , VENDOR_NUM
380 , INVOICE_DATE
381 , OPENING_VAR
382 , AMOUNT)
383 SELECT car.org_id
384 , SUBSTR(hr.name, 1, 30) Organization
385 , car.account_num
386 , car.doc_seq_num
387 , car.invoice_num
388 , car.vendor_num
389 , DECODE(car.type, ''UB'', to_date(''' || LP_LAST_DAY || ''', ''DD-MON-RRRR''), car.invoice_date) invoice_date
390 , DECODE(car.type, ''UB'', car.invoice_amount) opening_var
391 , DECODE(car.type, null, car.invoice_amount) amount
392 FROM IGI_CBR_AP_RECONCILE car
393 , HR_OPERATING_UNITS hr
394 WHERE NVL(car.org_id, -1) = NVL(hr.organization_id , -1)';
395 EXECUTE IMMEDIATE
396 L_INSERT;
397 END INSERT_INTERFACE;
398
399 END IGI_IGIPCBAP_XMLP_PKG;