1 PACKAGE BODY AR_WL_REW_PKG AS
2 -- $Header: ARWLREWB.pls 120.1 2008/09/04 14:18:23 tthangav 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 -- ARWLREWS.pls
14 --
15 -- DESCRIPTION
16 -- This script creates the package body of AR_WL_REW_PKG
17 -- This package is used for Cash Application work load review report.
18 --
19 -- USAGE
20 -- To install sqlplus <apps_user>/<apps_pwd> @ARWLREWS.pls
21 -- To execute sqlplus <apps_user>/<apps_pwd> AR_WL_REW_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 25-Jul-2008
35 -- Date the program has been modified for the last time.
36 --
37 -- HISTORY
38 -- =======
39 --
40 -- VERSION DATE AUTHOR(S) DESCRIPTION
41 -- ------- ----------- --------------- --------------------------------------
42 -- 25-Jul-2008 Thirumalaisamy Initial Creation
43 +===========================================================================*/
44
45 --**********************************************************
46 -- Before Report function used to obtain the Dynamic Queries
47 -- Based on the Input Parameter Values
48 --**********************************************************
49 FUNCTION beforereport RETURN BOOLEAN
50 IS
51 BEGIN
52 --****************************************************
53 -- Based on p_org_id the data will be filtered
54 -- else we will fetch all the Receipts of any org
55 --****************************************************
56 IF p_org_id IS NOT NULL THEN
57 gc_org_id := ' AND acr.org_id = :p_org_id ';
58 END IF;
59
60 --****************************************************
61 -- Based on p_cash_appln_owner_from and p_cash_appln_owner_to the
62 -- data will be filtered else we receive the information
63 -- for all the Cash Application Owners
64 --****************************************************
65 IF p_cash_appln_owner_from IS NOT NULL AND p_cash_appln_owner_to IS NOT NULL THEN
66 gc_cash_appln_owner := ' AND fu.user_name >= :p_cash_appln_owner_from
67 AND fu.user_name <= :p_cash_appln_owner_to ';
68 ELSIF p_cash_appln_owner_from IS NULL AND p_cash_appln_owner_to IS NOT NULL THEN
69 gc_cash_appln_owner := ' AND fu.user_name <= :p_cash_appln_owner_to ';
70 ELSIF p_cash_appln_owner_from IS NOT NULL AND p_cash_appln_owner_to IS NULL THEN
71 gc_cash_appln_owner := ' AND fu.user_name >= :p_cash_appln_owner_from ';
72 ELSE
73 gc_cash_appln_owner := ' AND 1 = 1 ';
74 END IF;
75
76 --****************************************************
77 -- Based on p_recpt_date_from and p_recpt_date_to the
78 -- data will be filtered else we receive the information
79 -- for all the Receipt Dates
80 --****************************************************
81 IF p_recpt_date_from IS NOT NULL AND p_recpt_date_to IS NOT NULL THEN
82 gc_recpt_date := ' AND acr.receipt_date >= :p_recpt_date_from
83 AND acr.receipt_date <= :p_recpt_date_to ';
84 ELSIF p_recpt_date_from IS NULL AND p_recpt_date_to IS NOT NULL THEN
85 gc_recpt_date := ' AND acr.receipt_date <= :p_recpt_date_to ';
86 ELSIF p_recpt_date_from IS NOT NULL AND p_recpt_date_to IS NULL THEN
87 gc_recpt_date := ' AND acr.receipt_date >= :p_recpt_date_from ';
88 ELSE
89 gc_recpt_date := ' AND 1 = 1 ';
90 END IF;
91
92 --****************************************************
93 -- Based on p_cust_from and p_cust_to the
94 -- data will be filtered else we receive the information
95 -- for all the Customers
96 --****************************************************
97 IF p_cust_from IS NOT NULL AND p_cust_to IS NOT NULL THEN
98 gc_cust := ' AND hca.account_number >= :p_cust_from
99 AND hca.account_number <= :p_cust_to ';
100 ELSIF p_cust_from IS NULL AND p_cust_to IS NOT NULL THEN
101 gc_cust := ' AND hca.account_number <= :p_cust_to ';
102 ELSIF p_cust_from IS NOT NULL AND p_cust_to IS NULL THEN
103 gc_cust := ' AND hca.account_number >= :p_cust_from ';
104 ELSE
105 gc_cust := ' AND 1 = 1 ';
106 END IF;
107
108 --****************************************************
109 -- Based on p_work_item_status_from and p_work_item_status_to the
110 -- data will be filtered else we receive the information
111 -- for all the Status
112 --****************************************************
113 IF p_work_item_status_from IS NOT NULL AND p_work_item_status_to IS NOT NULL THEN
114 gc_work_item_status := ' AND lu.meaning >= :p_work_item_status_from
115 AND lu.meaning <= :p_work_item_status_to ';
116 ELSIF p_work_item_status_from IS NULL AND p_work_item_status_to IS NOT NULL THEN
117 gc_work_item_status := ' AND lu.meaning <= :p_work_item_status_to ';
118 ELSIF p_work_item_status_from IS NOT NULL AND p_work_item_status_to IS NULL THEN
119 gc_work_item_status := ' AND lu.meaning >= :p_work_item_status_from ';
120 ELSE
121 gc_work_item_status := ' AND 1 = 1 ';
122 END IF;
123
124 --****************************************************
125 -- Based on p_assgn_date_from and p_assgn_date_to the
126 -- data will be filtered else we receive the information
127 -- for all the Assignment Dates
128 --****************************************************
129 IF p_assgn_date_from IS NOT NULL AND p_assgn_date_to IS NOT NULL THEN
130 gc_assgn_date := ' AND acr.work_item_assignment_date >= :p_assgn_date_from
131 AND acr.work_item_assignment_date <= :p_assgn_date_to ';
132 ELSIF p_assgn_date_from IS NULL AND p_assgn_date_to IS NOT NULL THEN
133 gc_assgn_date := ' AND acr.work_item_assignment_date <= :p_assgn_date_to ';
134 ELSIF p_assgn_date_from IS NOT NULL AND p_assgn_date_to IS NULL THEN
135 gc_assgn_date := ' AND acr.work_item_assignment_date >= :p_assgn_date_from ';
136 ELSE
137 gc_assgn_date := ' AND 1 = 1 ';
138 END IF;
139
140 RETURN (TRUE);
141 END beforereport;
142
143 END AR_WL_REW_PKG;
[Home] [Help]