1 PACKAGE BODY AR_TP_STMT_PKG AS
2 -- $Header: ARSTMTRPTPB.pls 120.2 2008/03/05 13:54:33 sgudupat noship $
3 /*===========================================================================+
4 --*************************************************************************
5 -- Copyright (c) 2000 Oracle Product Development
6 -- All rights reserved
7 --*************************************************************************
8 --
9 -- HEADER
10 -- Source control Body
11 --
12 -- PROGRAM NAME
13 -- ARSTMTRPTPB.pls
14 --
15 -- DESCRIPTION
16 -- This script creates the package body of AR_TP_STMT_PKG
17 -- This package is used for Supplier/Customer Statement Reports.
18 --
19 -- USAGE
20 -- To install sqlplus <apps_user>/<apps_pwd> @ARSTMTRPTPB.pls
21 -- To execute sqlplus <apps_user>/<apps_pwd> AR_TP_STMT_PKG.
22 --
23 -- PROGRAM LIST DESCRIPTION
24 --
25 -- BEFOREREPORT This function is used to dynamically get the
26 -- WHERE clause in SELECT statement.
27 --
28 -- DEPENDENCIES
29 -- None
30 --
31 -- CALLED BY
32 --
33 --
34 -- LAST UPDATE DATE 03-Sep-2007
35 -- Date the program has been modified for the last time.
36 --
37 -- HISTORY
38 -- =======
39 --
40 -- VERSION DATE AUTHOR(S) DESCRIPTION
41 -- ------- ----------- --------------- --------------------------------------
42 -- Draft1A 03-Sep-2007 Sandeep Kumar G Initial Creation
43 +===========================================================================*/
44
45 --=====================================================================
46 --=====================================================================
47 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
48 PROCEDURE set_to_receivables
49 IS
50 BEGIN
51 --****************************************************
52 -- Based on P_REPORTING_LEVEL the data will be filtered
53 -- else we receive all the Org Specific information
54 -- those are accesible for the Responsibility.
55 --****************************************************
56 -- IF P_ORG_ID IS NOT NULL THEN
57 -- gc_org_id := ' AND rct.org_id = :P_ORG_ID ';
58 -- gc_rcpt_org_id := ' AND acr.org_id = :P_ORG_ID ';
59 -- END IF;
60
61 IF P_REPORTING_LEVEL = 1000 THEN
62 -- Implies Reporting Level is Ledger
63 gc_reporting_entity := ' AND hou.set_of_books_id = :P_REPORTING_ENTITY_ID ';
64 gc_org_id := ' AND gled.ledger_id = :P_REPORTING_ENTITY_ID ';
65 gc_rcpt_org_id := ' AND gled.ledger_id = :P_REPORTING_ENTITY_ID ';
66 ELSIF P_REPORTING_LEVEL = 3000 THEN
67 -- Implies Reporting Level is Operating Unit
68 gc_reporting_entity := ' AND hou.organization_id = :P_REPORTING_ENTITY_ID ';
69 gc_org_id := ' AND rct.org_id = :P_REPORTING_ENTITY_ID ';
70 gc_rcpt_org_id := ' AND acr.org_id = :P_REPORTING_ENTITY_ID ';
71 ELSIF P_REPORTING_LEVEL = 2000 THEN
72 -- Implies Reporting Level is Legal Entity
73 -- gc_reporting_entity := ' AND hou.organization_id = :P_REPORTING_ENTITY_ID ';
74 gc_org_id := ' AND rct.legal_entity_id = :P_REPORTING_ENTITY_ID ';
75 gc_rcpt_org_id := ' AND acr.legal_entity_id = :P_REPORTING_ENTITY_ID ';
76 END IF;
77 IF PG_DEBUG in ('Y', 'C') THEN
78 arp_util.debug('gc_reporting_entity : ' || gc_reporting_entity);
79 arp_util.debug('gc_org_id : ' || gc_org_id);
80 arp_util.debug('gc_rcpt_org_id : ' || gc_rcpt_org_id);
81 END IF;
82 --****************************************************
83 -- Based on P_CUST_CLASS the data will be filtered
84 -- else we will fetch all the Customers of any Customer Class
85 --****************************************************
86 IF P_CUST_CLASS IS NOT NULL THEN
87 gc_cust_class := ' AND hca.customer_class_code = :P_CUST_CLASS ';
88 END IF;
89 IF PG_DEBUG in ('Y', 'C') THEN
90 arp_util.debug('gc_cust_class : ' || gc_cust_class);
91 END IF;
92 --****************************************************
93 -- Based on P_CUST_CATEGORY the data will be filtered
94 -- else we will fetch all the Customers of any Customer Category
95 --****************************************************
96 IF P_CUST_CATEGORY IS NOT NULL THEN
97 gc_cust_category := ' AND hpar.category_code = :P_CUST_CATEGORY ';
98 END IF;
99 IF PG_DEBUG in ('Y', 'C') THEN
100 arp_util.debug('gc_cust_category : ' || gc_cust_category);
101 END IF;
102 --****************************************************
103 -- Based on P_CURRENCY the data will be filtered
104 -- else we receive the information for all Currencies
105 --****************************************************
106 IF P_CURRENCY <> 'ANY' THEN
107 gc_currency := ' AND rct.invoice_currency_code = :P_CURRENCY ';
108 gc_rcpt_currency := ' AND acr.currency_code = :P_CURRENCY ';
109 END IF;
110 IF PG_DEBUG in ('Y', 'C') THEN
111 arp_util.debug('gc_currency : ' || gc_currency);
112 arp_util.debug('gc_rcpt_currency : ' || gc_rcpt_currency);
113 END IF;
114 --****************************************************
115 -- Based on P_ACCOUNTED the data will be filtered
116 -- for 'Accounted' --> Only Accounted Records will be fetched
117 -- for 'Unaccounted' --> Only Unaccounted Records will be fetched
118 -- for 'Both' --> Both Accounted/Unaccounted Records will be fetched
119 --****************************************************
120 IF P_ACCOUNTED = 'ACCOUNTED' THEN
121 gc_accounted := ' AND rctld.posting_control_id <> -3 ';
122 gc_rcpt_accounted := ' AND acrh.posting_control_id <> -3 ';
123 gc_adj_accounted := ' AND aa.posting_control_id <> -3 ';
124 gc_app_accounted := ' AND ara.posting_control_id <> -3 ';
125 ELSIF P_ACCOUNTED = 'UNACCOUNTED' THEN
126 gc_accounted := ' AND rctld.posting_control_id = -3 ';
127 gc_rcpt_accounted := ' AND acrh.posting_control_id = -3 ';
128 gc_adj_accounted := ' AND aa.posting_control_id = -3 ';
129 gc_app_accounted := ' AND ara.posting_control_id = -3 ';
130 END IF;
131 IF PG_DEBUG in ('Y', 'C') THEN
132 arp_util.debug('gc_accounted : ' || gc_accounted);
133 arp_util.debug('gc_rcpt_accounted : ' || gc_rcpt_accounted);
134 arp_util.debug('gc_adj_accounted : ' || gc_adj_accounted);
135 arp_util.debug('gc_app_accounted : ' || gc_app_accounted);
136 END IF;
137 --****************************************************
138 -- Based on P_INCOMPLETE_TRX the data will be filtered
139 -- for 'Y' --> Pick all Transactions (Complete/Incomplete)
140 -- for 'N' --> Pick Only Completed Transactions
141 --****************************************************
142 IF P_INCOMPLETE_TRX = 'N' THEN
143 gc_incomplete_trx := ' AND rct.complete_flag = ''Y'' ';
144 END IF;
145 IF PG_DEBUG in ('Y', 'C') THEN
146 arp_util.debug('gc_incomplete_trx : ' || gc_incomplete_trx);
147 END IF;
148 END set_to_receivables;
149 --**********************************************************
150 -- Before Report function used to obtain the Dynamic Queries
151 -- Based on the Input Parameter Values
152 --**********************************************************
153 FUNCTION beforereport RETURN BOOLEAN
154 IS
155 BEGIN
156 IF PG_DEBUG in ('Y', 'C') THEN
157 arp_util.debug('P_REPORTING_LEVEL : '||P_REPORTING_LEVEL);
158 arp_util.debug('P_REPORTING_ENTITY_ID : '||P_REPORTING_ENTITY_ID);
159 arp_util.debug('P_REPORTING_ENTITY_NAME : '||P_REPORTING_ENTITY_NAME);
160 arp_util.debug('P_FROM_DOC_DATE : '||P_FROM_DOC_DATE);
161 arp_util.debug('P_TO_DOC_DATE : '||P_TO_DOC_DATE);
162 arp_util.debug('P_FROM_GL_DATE : '||P_FROM_GL_DATE);
163 arp_util.debug('P_TO_GL_DATE : '||P_TO_GL_DATE);
164 arp_util.debug('P_FROM_CUST_NAME : '||P_FROM_CUST_NAME);
165 arp_util.debug('P_TO_CUST_NAME : '||P_TO_CUST_NAME);
166 arp_util.debug('P_CURRENCY : '||P_CURRENCY);
167 arp_util.debug('P_CUST_CATEGORY : '||P_CUST_CATEGORY);
168 arp_util.debug('P_CUST_CLASS : '||P_CUST_CLASS);
169 arp_util.debug('P_INCOMPLETE_TRX : '||P_INCOMPLETE_TRX);
170 arp_util.debug('P_ACCOUNTED : '||P_ACCOUNTED);
171 END IF;
172 set_to_receivables();
173 --****************************************************
174 -- Based on P_FROM_CUST_NAME and P_TO_CUST_NAME the
175 -- data will be filtered else we receive the information
176 -- for all the Customers
177 --****************************************************
178 IF P_FROM_CUST_NAME IS NOT NULL AND P_TO_CUST_NAME IS NOT NULL THEN
179 gc_customer_name := ' AND hpar.party_name >= :P_FROM_CUST_NAME
180 AND hpar.party_name <= :P_TO_CUST_NAME ';
181 ELSIF P_FROM_CUST_NAME IS NULL AND P_TO_CUST_NAME IS NOT NULL THEN
182 gc_customer_name := ' AND hpar.party_name <= :P_TO_CUST_NAME ';
183 ELSIF P_FROM_CUST_NAME IS NOT NULL AND P_TO_CUST_NAME IS NULL THEN
184 gc_customer_name := ' AND hpar.party_name >= :P_FROM_CUST_NAME ';
185 ELSE
186 gc_customer_name := ' AND 1 = 1 ';
187 END IF;
188 IF PG_DEBUG in ('Y', 'C') THEN
189 arp_util.debug('gc_customer_name : ' || gc_customer_name);
190 END IF;
191 RETURN (TRUE);
192 END beforereport;
193 --**********************************************************
194 -- Balance forward function used to obtain the Opening Balance
195 -- Of a Customer at Site Level
196 --**********************************************************
197
198 FUNCTION balance_brought_forward (p_in_cust_account_id IN NUMBER
199 ,p_in_site_use_id IN NUMBER
200 ,p_in_org_id IN NUMBER)
201 RETURN NUMBER
202 IS
203 ln_amount NUMBER := 0;
204 BEGIN
205 SELECT SUM(DECODE(trx_type,'R',-1*accounted_amount,accounted_amount)) amount
206 INTO ln_amount
207 FROM (SELECT 'T' trx_type
208 ,SUM(NVL(rctld.acctd_amount,0)) accounted_amount
209 FROM ra_customer_trx rct
210 ,ra_cust_trx_line_gl_dist_all rctld
211 ,ra_cust_trx_types_all rctt
212 WHERE rct.customer_trx_id = rctld.customer_trx_id
213 AND rct.cust_trx_type_id = rctt.cust_trx_type_id
214 AND rct.org_id = rctt.org_id
215 AND rctld.latest_rec_flag = 'Y'
216 AND rctld.account_class = 'REC'
217 AND rctt.post_to_gl = 'Y'
218 AND rctt.type IN ('CB','INV','DM','CM','BR','DEP')
219 AND rctld.gl_date < TO_DATE(P_FROM_GL_DATE,'RRRR/MM/DD HH24:MI:SS')
220 AND rct.bill_to_customer_id = p_in_cust_account_id
221 AND rct.bill_to_site_use_id = p_in_site_use_id
222 AND rct.org_id = p_in_org_id
223 AND rct.invoice_currency_code = NVL2(P_CURRENCY,DECODE(P_CURRENCY,'ANY',rct.invoice_currency_code,P_CURRENCY),rct.invoice_currency_code)
224 AND rct.complete_flag = DECODE(P_INCOMPLETE_TRX,'N','Y', rct.complete_flag)
225 AND ( (P_ACCOUNTED = 'ACCOUNTED' AND rctld.posting_control_id <> -3 )
226 OR (P_ACCOUNTED = 'UNACCOUNTED' AND rctld.posting_control_id = -3 )
227 OR (P_ACCOUNTED = 'BOTH'))
228 UNION ALL
229 SELECT 'R' trx_type
230 ,SUM(NVL(acr.amount * NVL(acr.exchange_rate,1),0)) accounted_amount
231 FROM ar_cash_receipts acr
232 ,ar_cash_receipt_history_all acrh
233 WHERE acr.cash_receipt_id = acrh.cash_receipt_id
234 AND acr.org_id = acrh.org_id
235 AND acrh.first_posted_record_flag = 'Y'
236 AND acrh.gl_date < TO_DATE(P_FROM_GL_DATE,'RRRR/MM/DD HH24:MI:SS')
237 AND acr.pay_from_customer = p_in_cust_account_id
238 AND acr.customer_site_use_id = p_in_site_use_id
239 AND acr.org_id = p_in_org_id
240 AND acr.currency_code = NVL2(P_CURRENCY,DECODE(P_CURRENCY,'ANY',acr.currency_code,P_CURRENCY),acr.currency_code)
241 AND ( (P_ACCOUNTED = 'ACCOUNTED' AND acrh.posting_control_id <> -3 )
242 OR (P_ACCOUNTED = 'UNACCOUNTED' AND acrh.posting_control_id = -3 )
243 OR (P_ACCOUNTED = 'BOTH'))
244 UNION ALL
245 SELECT 'RE' trx_type
246 ,SUM(NVL(acr.amount * NVL(acr.exchange_rate,1),0)) accounted_amount
247 FROM ar_cash_receipts acr
248 ,ar_cash_receipt_history_all acrh
249 WHERE acr.cash_receipt_id = acrh.cash_receipt_id
250 AND acr.org_id = acrh.org_id
251 AND acr.reversal_date IS NOT NULL
252 AND acrh.current_record_flag = 'Y'
253 AND acrh.gl_date < TO_DATE(P_FROM_GL_DATE,'RRRR/MM/DD HH24:MI:SS')
254 AND acr.pay_from_customer = p_in_cust_account_id
255 AND acr.customer_site_use_id = p_in_site_use_id
256 AND acr.org_id = p_in_org_id
257 AND acr.currency_code = NVL2(P_CURRENCY,DECODE(P_CURRENCY,'ANY',acr.currency_code,P_CURRENCY),acr.currency_code)
258 AND ( (P_ACCOUNTED = 'ACCOUNTED' AND acrh.posting_control_id <> -3 )
259 OR (P_ACCOUNTED = 'UNACCOUNTED' AND acrh.posting_control_id = -3 )
260 OR (P_ACCOUNTED = 'BOTH'))
261 UNION ALL
262 SELECT 'A' trx_type
263 ,SUM(NVL(aa.acctd_amount,0)) accounted_amount
264 FROM ar_adjustments aa
265 ,ra_customer_trx_all rct
266 ,ra_cust_trx_types_all rctt
267 WHERE rct.customer_trx_id = aa.customer_trx_id
268 AND rct.org_id = aa.org_id
269 AND rct.cust_trx_type_id = rctt.cust_trx_type_id
270 AND rct.org_id = rctt.org_id
271 AND aa.status = 'A' -- For approved Adjustments
272 AND aa.gl_date < TO_DATE(P_FROM_GL_DATE,'RRRR/MM/DD HH24:MI:SS')
273 AND rct.bill_to_customer_id = p_in_cust_account_id
274 AND rct.bill_to_site_use_id = p_in_site_use_id
275 AND rct.org_id = p_in_org_id
276 AND rctt.post_to_gl = 'Y' -- Only Postable to GL are picked
277 AND rctt.type IN ('CB','INV','DM','CM','BR','DEP') -- Guarantees are not picked
278 AND rct.invoice_currency_code = NVL2(P_CURRENCY,DECODE(P_CURRENCY,'ANY',rct.invoice_currency_code,P_CURRENCY),rct.invoice_currency_code)
279 AND rct.complete_flag = DECODE(P_INCOMPLETE_TRX,'N','Y', rct.complete_flag)
280 AND ( (P_ACCOUNTED = 'ACCOUNTED' AND aa.posting_control_id <> -3)
281 OR (P_ACCOUNTED = 'UNACCOUNTED' AND aa.posting_control_id = -3)
282 OR (P_ACCOUNTED = 'BOTH'))
283 UNION ALL
284 SELECT 'RE' trx_type
285 ,SUM(ara.acctd_amount_applied_from) accounted_amount
286 FROM ar_cash_receipts acr
287 ,ar_receivable_applications_all ara
288 ,ar_receivables_trx_all art
289 WHERE acr.cash_receipt_id = ara.cash_receipt_id
290 AND acr.org_id = ara.org_id
291 AND ara.receivables_trx_id = art.receivables_trx_id
292 AND ara.org_id = art.org_id
293 AND ara.gl_date < TO_DATE(P_FROM_GL_DATE,'RRRR/MM/DD HH24:MI:SS')
294 AND acr.pay_from_customer = p_in_cust_account_id
295 AND acr.customer_site_use_id = p_in_site_use_id
296 AND acr.org_id = p_in_org_id
297 AND art.type = 'WRITEOFF'
298 AND acr.currency_code = NVL2(P_CURRENCY,DECODE(P_CURRENCY,'ANY',acr.currency_code,P_CURRENCY),acr.currency_code)
299 AND ( (P_ACCOUNTED = 'ACCOUNTED' AND ara.posting_control_id <> -3)
300 OR (P_ACCOUNTED = 'UNACCOUNTED' AND ara.posting_control_id = -3)
301 OR (P_ACCOUNTED = 'BOTH')));
302 RETURN (NVL(ln_amount,0));
303 END balance_brought_forward;
304
305 FUNCTION contact_details(p_owner_table_id IN NUMBER
306 ,p_contact_type IN VARCHAR2)
307 RETURN VARCHAR2
308 IS
309 lc_cust_phone_number VARCHAR2(1000);
310 lc_primary_flag VARCHAR2(1);
311 BEGIN
312 IF p_contact_type <> 'FAX' THEN
313 BEGIN
314 SELECT REPLACE(LTRIM(hcp.phone_area_code||'-'||
315 hcp.phone_country_code||'-'||
316 hcp.phone_number,'-'),'--','-')
317 ,hcp.primary_flag
318 INTO lc_cust_phone_number
319 ,lc_primary_flag
320 FROM hz_contact_points hcp
321 WHERE hcp.status = 'A'
322 AND hcp.owner_table_id = p_owner_table_id
323 AND hcp.contact_point_type = 'PHONE'
324 AND hcp.phone_line_type IN ('GEN','PHONE','MOBILE')
325 AND hcp.primary_flag = 'Y';
326 EXCEPTION
327 WHEN NO_DATA_FOUND THEN
328 SELECT REPLACE(LTRIM(hcp.phone_area_code||'-'||
329 hcp.phone_country_code||'-'||
330 hcp.phone_number,'-'),'--','-')
331 ,hcp.primary_flag
332 INTO lc_cust_phone_number
333 ,lc_primary_flag
334 FROM hz_contact_points hcp
335 WHERE hcp.contact_point_id = (SELECT MIN(hcp1.contact_point_id)
336 FROM hz_contact_points hcp1
337 WHERE hcp1.status = 'A'
338 AND hcp1.owner_table_id = p_owner_table_id
339 AND hcp1.contact_point_type = 'PHONE'
340 AND hcp1.phone_line_type IN ('GEN','PHONE','MOBILE'));
341 END;
342 ELSE
343 BEGIN
344 SELECT REPLACE(LTRIM(hcp.phone_area_code||'-'||
345 hcp.phone_country_code||'-'||
346 hcp.phone_number,'-'),'--','-')
347 ,hcp.primary_flag
348 INTO lc_cust_phone_number
349 ,lc_primary_flag
350 FROM hz_contact_points hcp
351 WHERE hcp.status = 'A'
352 AND hcp.owner_table_id = p_owner_table_id
353 AND hcp.contact_point_type = 'PHONE'
354 AND hcp.phone_line_type = 'FAX'
355 AND hcp.primary_flag = 'Y';
356 EXCEPTION
357 WHEN NO_DATA_FOUND THEN
358 SELECT REPLACE(LTRIM(hcp.phone_area_code||'-'||
359 hcp.phone_country_code||'-'||
360 hcp.phone_number,'-'),'--','-')
361 ,hcp.primary_flag
362 INTO lc_cust_phone_number
363 ,lc_primary_flag
364 FROM hz_contact_points hcp
365 WHERE hcp.contact_point_id = (SELECT MIN(hcp1.contact_point_id)
366 FROM hz_contact_points hcp1
367 WHERE hcp1.status = 'A'
368 AND hcp1.owner_table_id = p_owner_table_id
369 AND hcp1.contact_point_type = 'PHONE'
370 AND hcp1.phone_line_type = 'FAX');
371 END;
372 END IF;
373 RETURN (lc_primary_flag||lc_cust_phone_number);
374 END contact_details;
375
376 END AR_TP_STMT_PKG;
[Home] [Help]