1 PACKAGE BODY AP_TP_STMT_PKG AS
2 -- $Header: APTPSTMTPB.pls 120.1 2008/04/03 12:58:54 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 -- APARTPSTMTPB.pls
14 --
15 -- DESCRIPTION
16 -- This script creates the package body of AP_TP_STMT_PKG
17 -- This package is used for Supplier Statement Reports.
18 --
19 -- USAGE
20 -- To install sqlplus <apps_user>/<apps_pwd> @APTPSTMTPB.pls
21 -- To execute sqlplus <apps_user>/<apps_pwd> AP_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 PROCEDURE set_to_payables
48 IS
49 BEGIN
50 FND_FILE.put_line(FND_FILE.log,'In Set to Payables');
51 --****************************************************
52 -- Based on P_REPORTING_ENTITY_ID 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_REPORTING_LEVEL = 1000 THEN
57 -- Implies Reporting Level is Ledger
58 gc_reporting_entity := ' AND hro.set_of_books_id = :P_REPORTING_ENTITY_ID ';
59 gc_org_id := ' AND gled.ledger_id = :P_REPORTING_ENTITY_ID ';
60 gc_pmt_org_id := ' AND gled.ledger_id = :P_REPORTING_ENTITY_ID ';
61 ELSIF P_REPORTING_LEVEL = 3000 THEN
62 -- Implies Reporting Level is Operating Unit
63 gc_reporting_entity := ' AND hro.organization_id = :P_REPORTING_ENTITY_ID ';
64 gc_org_id := ' AND ai.org_id = :P_REPORTING_ENTITY_ID ';
65 gc_pmt_org_id := ' AND ac.org_id = :P_REPORTING_ENTITY_ID ';
66 END IF;
67
68 --****************************************************
69 -- Based on P_VEND_TYPE the data will be filtered
70 -- else we will fetch all the Supplier Types
71 --****************************************************
72 IF P_VEND_TYPE IS NOT NULL THEN
73 gc_vend_type := ' AND asup.vendor_type_lookup_code = :P_VEND_TYPE ';
74 END IF;
75 FND_FILE.put_line(FND_FILE.log,'gc_vend_type := '||gc_vend_type);
76 --****************************************************
77 -- Based on P_PAY_GROUP the data will be filtered
78 -- else Suppliers irrespective of their Pay Group will be picked
79 --****************************************************
80 IF P_PAY_GROUP IS NOT NULL THEN
81 gc_pay_group := ' AND asup.pay_group_lookup_code = :P_PAY_GROUP ';
82 END IF;
83 FND_FILE.put_line(FND_FILE.log,'gc_pay_group := '||gc_pay_group);
84 --****************************************************
85 -- Based on P_CURRENCY the data will be filtered
86 -- else we receive the information for all Currencies
87 --****************************************************
88 IF P_CURRENCY <> 'ANY' THEN
89 gc_currency := ' AND ai.invoice_currency_code = :P_CURRENCY ';
90 gc_pmt_currency := ' AND ac.currency_code = :P_CURRENCY ';
91 END IF;
92 --****************************************************
93 -- Based on P_ACCOUNTED the data will be filtered
94 -- for 'Accounted' --> Only Accounted Records will be fetched
95 -- for 'Unaccounted' --> Only Unaccounted Records will be fetched
96 -- for 'Both' --> Both Accounted/Unaccounted Records will be fetched
97 --****************************************************
98 IF P_ACCOUNTED = 'ACCOUNTED' THEN
99 gc_pmt_accounted := ' AND aip.posted_flag = ''Y'' ';
100 ELSIF P_ACCOUNTED = 'UNACCOUNTED' THEN
101 gc_pmt_accounted := ' AND aip.posted_flag = ''N'' ';
102 END IF;
103 --****************************************************
104 -- Based on P_UNVALIDATED_TRX the data will be filtered
105 -- for 'Y' --> Pick all Transactions (Validated/Unvalidated)
106 -- for 'N' --> Pick Only Validated Transactions
107 --****************************************************
108 IF P_UNVALIDATED_TRX = 'N' THEN
109 gc_validate_inv := ' AND ai.invoice_id IN ';
110 gc_validate_inv := gc_validate_inv||' (SELECT i.invoice_id';
111 gc_validate_inv := gc_validate_inv||' FROM ap_invoices_all i, ap_invoice_distributions d';
112 gc_validate_inv := gc_validate_inv||' WHERE d.invoice_id = i.invoice_id';
113 gc_validate_inv := gc_validate_inv||' AND i.invoice_id = ai.invoice_id';
114 gc_validate_inv := gc_validate_inv||' AND d.posted_flag IN (''N'', ''P'')';
115 gc_validate_inv := gc_validate_inv||' AND i.validation_request_id IS NULL';
116 gc_validate_inv := gc_validate_inv||' AND ( ';
117 gc_validate_inv := gc_validate_inv||' NOT EXISTS (';
118 gc_validate_inv := gc_validate_inv||' SELECT ''Unreleased Hold exists''';
119 gc_validate_inv := gc_validate_inv||' FROM ap_holds h';
120 gc_validate_inv := gc_validate_inv||' WHERE h.invoice_id = i.invoice_id';
121 gc_validate_inv := gc_validate_inv||' AND h.hold_lookup_code IN';
122 gc_validate_inv := gc_validate_inv||' (''QTY ORD'', ''QTY REC'', ''AMT ORD'', ''AMT REC'',';
123 gc_validate_inv := gc_validate_inv||' ''QUALITY'', ''PRICE'', ''TAX DIFFERENCE'',';
124 gc_validate_inv := gc_validate_inv||' ''CURRENCY DIFFERENCE'', ''REC EXCEPTION'',';
125 gc_validate_inv := gc_validate_inv||' ''TAX VARIANCE'', ''PO NOT APPROVED'', ''PO REQUIRED'',';
126 gc_validate_inv := gc_validate_inv||' ''MAX SHIP AMOUNT'', ''MAX RATE AMOUNT'',';
127 gc_validate_inv := gc_validate_inv||' ''MAX TOTAL AMOUNT'', ''TAX AMOUNT RANGE'',';
128 gc_validate_inv := gc_validate_inv||' ''MAX QTY ORD'', ''MAX QTY REC'', ''MAX AMT ORD'',';
129 gc_validate_inv := gc_validate_inv||' ''MAX AMT REC'', ''CANT CLOSE PO'', ''CANT TRY PO CLOSE'',';
130 gc_validate_inv := gc_validate_inv||' ''LINE VARIANCE'')';
131 gc_validate_inv := gc_validate_inv||' AND h.release_lookup_code IS NULL)))';
132 END IF;
133 --****************************************************
134 -- Based on P_UNAPPROVED_TRX the data will be filtered
135 -- The Parameter says to Pick Only Approved Invoices or All
136 -- for 'Y' --> Pick all Transactions (Unapproved/Approved/Approval Not Required)
137 -- for 'N' --> Pick Only Approved/Approval Not Required Transactions
138 --****************************************************
139 IF P_UNAPPROVED_TRX = 'N' THEN
140 gc_unapproved_trx := ' AND ai.wfapproval_status IN (''ACCEPT'',''APPROVED'',''MANUALLY APPROVED'',''NOT REQUIRED'',''WFAPPROVED'',''ACKNOWLEDGE'',''CANCEL'',''CANCELLED'') ';
141 END IF;
142 END set_to_payables;
143
144 --=====================================================================
145 --=====================================================================
146 FUNCTION beforereport RETURN BOOLEAN
147 IS
148 BEGIN
149 set_to_payables();
150 -- The Parameters that are common to both the Modules are set here
151 --****************************************************
152 -- Based on P_FROM_SUPP_NAME and P_TO_SUPP_NAME the
153 -- data will be filtered else we receive the information
154 -- for all the Customers
155 --****************************************************
156 IF P_FROM_SUPP_NAME IS NOT NULL AND P_TO_SUPP_NAME IS NOT NULL THEN
157 gc_supplier_name := ' AND asup.vendor_name >= :P_FROM_SUPP_NAME
158 AND asup.vendor_name <= :P_TO_SUPP_NAME ';
159 ELSIF P_FROM_SUPP_NAME IS NULL AND P_TO_SUPP_NAME IS NOT NULL THEN
160 gc_supplier_name := ' AND asup.vendor_name <= :P_TO_SUPP_NAME ';
161 ELSIF P_FROM_SUPP_NAME IS NOT NULL AND P_TO_SUPP_NAME IS NULL THEN
162 gc_supplier_name := ' AND asup.vendor_name >= :P_FROM_SUPP_NAME ';
163 END IF;
164 RETURN (TRUE);
165 END beforereport;
166
167 --=====================================================================
168 --=====================================================================
169 FUNCTION invoice_validate_status (p_in_inv_id IN NUMBER)
170 RETURN VARCHAR2
171 IS
172 ln_inv_id NUMBER;
173 BEGIN
174 SELECT count(1)
175 INTO ln_inv_id
176 FROM (SELECT i.invoice_id invoice_id
177 FROM ap_invoices_all i, ap_invoice_distributions d
178 WHERE d.invoice_id = i.invoice_id
179 AND i.invoice_id = p_in_inv_id
180 AND d.posted_flag IN ('N', 'P')
181 AND i.validation_request_id IS NULL
182 AND ( NOT EXISTS (
183 SELECT 'Unreleased Hold exists'
184 FROM ap_holds h
185 WHERE h.invoice_id = i.invoice_id
186 AND h.hold_lookup_code IN
187 ('QTY ORD', 'QTY REC', 'AMT ORD', 'AMT REC',
188 'QUALITY', 'PRICE', 'TAX DIFFERENCE',
189 'CURRENCY DIFFERENCE', 'REC EXCEPTION',
190 'TAX VARIANCE', 'PO NOT APPROVED', 'PO REQUIRED',
191 'MAX SHIP AMOUNT', 'MAX RATE AMOUNT',
192 'MAX TOTAL AMOUNT', 'TAX AMOUNT RANGE',
193 'MAX QTY ORD', 'MAX QTY REC', 'MAX AMT ORD',
194 'MAX AMT REC', 'CANT CLOSE PO', 'CANT TRY PO CLOSE',
195 'LINE VARIANCE')
196 AND h.release_lookup_code IS NULL)));
197 IF ln_inv_id <> 0 THEN
198 RETURN (1);
199 ELSE
200 RETURN (0);
201 END IF;
202 EXCEPTION
203 WHEN NO_DATA_FOUND THEN
204 RETURN(0);
205 END invoice_validate_status;
206 --=====================================================================
207 --=====================================================================
208 FUNCTION balance_brought_forward (p_in_vendor_id IN NUMBER
209 ,p_in_vendor_site_id IN NUMBER
210 ,p_in_org_id IN NUMBER)
211 RETURN NUMBER
212 IS
213 ln_amount NUMBER;
214 BEGIN
215 SELECT SUM(DECODE(transaction_type,'P',-1*accounted_amount,accounted_amount)) amount
216 INTO ln_amount
217 FROM
218 ( SELECT 'I' transaction_type
219 ,SUM(NVL(ai.invoice_amount * NVL(ai.exchange_rate,1),0)) accounted_amount
220 FROM ap_invoices ai
221 WHERE ai.vendor_id = p_in_vendor_id
222 AND ai.vendor_site_id = p_in_vendor_site_id
223 AND ai.gl_date < TO_DATE(P_FROM_GL_DATE,'RRRR/MM/DD HH24:MI:SS')
224 AND ai.invoice_currency_code = NVL2(P_CURRENCY,DECODE(P_CURRENCY,'ANY',ai.invoice_currency_code,P_CURRENCY),ai.invoice_currency_code)
225 AND ai.org_id = p_in_org_id
226 AND ((AP_TP_STMT_PKG.invoice_validate_status(ai.invoice_id) = 'Y'
227 AND P_UNVALIDATED_TRX = 'N')
228 OR P_UNVALIDATED_TRX = 'Y')
229 AND AP_INVOICES_PKG.get_posting_status(ai.invoice_id)
230 = DECODE(P_ACCOUNTED,'ACCOUNTED','Y'
231 ,'UNACCOUNTED','N'
232 ,AP_INVOICES_PKG.get_posting_status(ai.invoice_id))
233 AND ((P_UNAPPROVED_TRX = 'N'
234 AND ai.wfapproval_status IN ('ACCEPT','APPROVED','MANUALLY APPROVED','NOT REQUIRED','WFAPPROVED','ACKNOWLEDGE','CANCEL','CANCELLED'))
235 OR (P_UNAPPROVED_TRX = 'Y' ))
236 UNION ALL
237 SELECT 'P' transaction_type
238 ,SUM(NVL(aip.amount * NVL(aip.exchange_rate,1),0)) accounted_amount
239 FROM ap_invoice_payments aip
240 ,ap_checks ac
241 WHERE aip.check_id = ac.check_id
242 AND ac.vendor_id = p_in_vendor_id
243 AND ac.vendor_site_id = p_in_vendor_site_id
244 AND ac.check_date < TO_DATE(P_FROM_DOC_DATE,'RRRR/MM/DD HH24:MI:SS')
245 AND aip.accounting_date < TO_DATE(P_FROM_GL_DATE,'RRRR/MM/DD HH24:MI:SS')
246 AND ac.org_id = p_in_org_id
247 AND ac.currency_code = NVL2(P_CURRENCY,DECODE(P_CURRENCY,'ANY',ac.currency_code,P_CURRENCY),ac.currency_code)
248 AND ((P_ACCOUNTED = 'ACCOUNTED' AND aip.posted_flag = 'Y')
249 OR (P_ACCOUNTED = 'UNACCOUNTED' AND aip.posted_flag = 'N')
250 OR (P_ACCOUNTED = 'BOTH'))
251 UNION ALL
252 SELECT 'A' transaction_type
253 ,SUM((NVL(aid.amount,0)* NVL(ai.exchange_rate,1))*-1) amount_applied
254 FROM ap_invoices ai
255 ,ap_invoice_distributions_all aid
256 ,ap_invoices aipre
257 ,ap_invoice_distributions_all aidpre
258 WHERE ai.invoice_id = aid.invoice_id
259 AND aid.prepay_distribution_id = aidpre.invoice_distribution_id
260 AND aipre.invoice_id = aidpre.invoice_id
261 AND aid.accounting_date < TO_DATE(P_FROM_GL_DATE,'RRRR/MM/DD HH24:MI:SS')
262 AND ai.vendor_id = p_in_vendor_id
263 AND ai.vendor_site_id = p_in_vendor_site_id
264 AND ai.invoice_currency_code = NVL2(P_CURRENCY,DECODE(P_CURRENCY,'ANY',ai.invoice_currency_code,P_CURRENCY),ai.invoice_currency_code)
265 AND ai.org_id = p_in_org_id
266 AND ai.wfapproval_status NOT IN ('CANCEL','CANCELLED')
267 AND AP_INVOICES_PKG.get_posting_status(ai.invoice_id)
268 = DECODE(P_ACCOUNTED,'ACCOUNTED','Y'
269 ,'UNACCOUNTED','N'
270 ,AP_INVOICES_PKG.get_posting_status(ai.invoice_id))
271 AND ((P_UNAPPROVED_TRX = 'N'
272 AND ai.wfapproval_status IN ('ACCEPT','APPROVED','MANUALLY APPROVED','NOT REQUIRED','WFAPPROVED','ACKNOWLEDGE'))
273 OR (P_UNAPPROVED_TRX = 'Y' ))
274 );
275 RETURN (NVL(ln_amount,0));
276 END balance_brought_forward;
277
278 END AP_TP_STMT_PKG;
[Home] [Help]