1 PACKAGE BODY AP_CHECK_PAYMT_SUPP_RPT_PKG
2 -- $Header: AP_CHECKPAYMTSUPPB.pls 120.2.12000000.1 2007/10/24 18:04:18 sgudupat noship $
3 --*************************************************************************
4 -- Copyright (c) 2000 Oracle Product Development
5 -- All rights reserved
6 --*************************************************************************
7 --
8 -- HEADER
9 -- Source control header
10 --
11 -- PROGRAM NAME
12 -- AP_CHECK_PAYMT_SUPP_RPT_PKG
13 --
14 -- DESCRIPTION
15 -- This script creates the package Body of AP_CHECK_PAYMT_SUPP_RPT_PKG
16 -- This package is used to report on AP Check payments to Suppliers.
17 --
18 -- USAGE
19 -- To install sqlplus <apps_user>/<apps_pwd> @AP_CHECKPAYMTSUPPB.pls
20 -- To execute sqlplus <apps_user>/<apps_pwd> AP_CHECK_PAYMT_SUPP_RPT_PKG.
21 --
22 -- PROGRAM LIST DESCRIPTION
23 -- beforereport This function is used to dynamically get the
24 -- WHERE clause in SELECT statement.
25 -- DEPENDENCIES
26 -- None
27 --
28 -- CALLED BY
29 -- AP Check Payments to Suppliers Report.
30 --
31 -- LAST UPDATE DATE 08-Feb-2007
32 -- Date the program has been modified for the last time.
33 --
34 -- HISTORY
35 -- =======
36 --
37 -- VERSION DATE AUTHOR(S) DESCRIPTION
38 -- ------- ----------- --------------- --------------------------------------
39 -- Draft1A 08-Feb-2007 Rakesh Pulla Initial Creation
40 -- Draft1B 22-Jun-2007 Rakesh Pulla Incorporated the review comments of the Product team.
41 -- Draft1C 12-Jul-2007 Rakesh Pulla Incorporated the product team review comments.
42 -- Draft1D 12-Jul-2007 Rakesh Pulla Replaced CHCK with CHECK as suggested by the product team.
43 --************************************************************************
44 AS
45
46 FUNCTION beforereport RETURN BOOLEAN
47 IS
48 lc_attribute VARCHAR2(50);
49 lc_attribute_col VARCHAR2(80);
50 BEGIN -- Begining of the Function beforereport
51
52 BEGIN
53 SELECT FDU.application_column_name attr_column
54 INTO lc_attribute
55 FROM fnd_descr_flex_col_usage_vl FDU
56 ,fnd_application FA
57 WHERE FDU.descriptive_flexfield_name = 'AP_CHECKS'
58 AND FDU.descriptive_flex_context_code = 'Global Data Elements'
59 AND FDU.application_id = '200'
60 AND FA.application_short_name = 'SQLAP'
61 AND FDU.end_user_column_name = 'Cek_No';
62
63 EXCEPTION
64 WHEN NO_DATA_FOUND THEN
65 NULL;
66 END;
67
68 IF lc_attribute IS NOT NULL THEN
69 lc_attribute_col:= 'APC.'||lc_attribute;
70 ELSE
71 lc_attribute_col:= NULL;
72 END IF;
73
74 GC_ATTRIBUTE_COL:= lc_attribute_col;
75
76
77 IF (P_VNDR_NUMBER_FROM IS NOT NULL) AND (P_VNDR_NUMBER_TO IS NOT NULL) THEN
78 GC_WHERE:= GC_WHERE||' AND DECODE(APC.vendor_id,'''',DECODE(APC.party_id,''''
79 ,''******************************'',NULL), APS.segment1)
80 BETWEEN :P_VNDR_NUMBER_FROM AND :P_VNDR_NUMBER_TO';
81 ELSIF (P_VNDR_NUMBER_FROM IS NULL) AND (P_VNDR_NUMBER_TO IS NOT NULL) THEN
82 GC_WHERE:= GC_WHERE||' AND DECODE(APC.vendor_id,'''',DECODE(APC.party_id,''''
83 ,''******************************'',NULL), APS.segment1)
84 <= :P_VNDR_NUMBER_TO';
85 ELSIF (P_VNDR_NUMBER_FROM IS NOT NULL) AND (P_VNDR_NUMBER_TO IS NULL) THEN
86 GC_WHERE:= GC_WHERE||' AND DECODE(APC.vendor_id,'''',DECODE(APC.party_id,''''
87 ,''******************************'',NULL), APS.segment1)
88 >= :P_VNDR_NUMBER_FROM';
89 END IF;
90
91 IF (P_CHECK_DATE_FROM IS NOT NULL) AND (P_CHECK_DATE_TO IS NOT NULL) THEN
92 GC_WHERE:= GC_WHERE||' AND APC.check_date
93 BETWEEN TO_DATE((TO_CHAR(:P_CHECK_DATE_FROM,''DD-MON-YYYY'')||''00-00-00'')
94 ,''DD-MON-YYYY HH24:MI:SS'') AND TO_DATE((TO_CHAR(NVL(:P_CHECK_DATE_TO,SYSDATE)
95 ,''DD-MON-YYYY'')||''23:59:59''),''DD-MON-YYYY HH24:MI:SS'')';
96 ELSIF (P_CHECK_DATE_FROM IS NULL) AND (P_CHECK_DATE_TO IS NOT NULL) THEN
97 GC_WHERE:= GC_WHERE||' AND APC.check_date <=
98 TO_DATE((TO_CHAR(:P_CHECK_DATE_TO,''DD-MON-YYYY'')||''23:59:59'')
99 ,''DD-MON-YYYY HH24:MI:SS'')';
100 ELSIF (P_CHECK_DATE_FROM IS NOT NULL) AND (P_CHECK_DATE_TO IS NULL) THEN
101 GC_WHERE:= GC_WHERE||' AND APC.check_date >=
102 TO_DATE((TO_CHAR(:P_CHECK_DATE_FROM,''DD-MON-YYYY'')||''00-00-00'')
103 ,''DD-MON-YYYY HH24:MI:SS'')';
104 END IF;
105
106 IF (P_CHECK_DUE_DATE_FROM IS NOT NULL) AND (P_CHECK_DUE_DATE_TO IS NOT NULL) THEN
107 GC_WHERE:= GC_WHERE||' AND APC.future_pay_due_date
108 BETWEEN TO_DATE((TO_CHAR(:P_CHECK_DUE_DATE_FROM,''DD-MON-YYYY'')||''00-00-00''),''DD-MON-YYYY HH24:MI:SS'')
109 AND TO_DATE((TO_CHAR(NVL(:P_CHECK_DUE_DATE_TO,SYSDATE),''DD-MON-YYYY'')||''23:59:59'')
110 ,''DD-MON-YYYY HH24:MI:SS'')';
111 ELSIF (P_CHECK_DUE_DATE_FROM IS NULL) AND (P_CHECK_DUE_DATE_TO IS NOT NULL) THEN
112 GC_WHERE:= GC_WHERE||' AND APC.future_pay_due_date <=
113 TO_DATE((TO_CHAR(:P_CHECK_DUE_DATE_TO,''DD-MON-YYYY'')||''23:59:59'')
114 ,''DD-MON-YYYY HH24:MI:SS'')';
115 ELSIF (P_CHECK_DUE_DATE_FROM IS NOT NULL) AND (P_CHECK_DUE_DATE_TO IS NULL) THEN
116 GC_WHERE:= GC_WHERE||' AND APC.future_pay_due_date >=
117 TO_DATE((TO_CHAR(:P_CHECK_DUE_DATE_FROM,''DD-MON-YYYY'')||''00-00-00'')
118 ,''DD-MON-YYYY HH24:MI:SS'')';
119 END IF;
120
121 IF (P_CURRENCY IS NOT NULL) THEN
122 GC_WHERE:= GC_WHERE||' AND APC.currency_code= :P_CURRENCY';
123 END IF;
124
125 IF (P_CHECK_STAT IS NOT NULL) THEN
126 GC_WHERE:= GC_WHERE||' AND APLC.lookup_code= :P_CHECK_STAT';
127 ELSE
128 GC_WHERE:= GC_WHERE||' AND APC.status_lookup_code <> ''VOIDED''';
129 END IF;
130
131 RETURN(TRUE);
132
133 END beforereport; --End of the beforereport.
134
135 END AP_CHECK_PAYMT_SUPP_RPT_PKG ;
[Home] [Help]