1 PACKAGE BODY AP_TP_STMT_PKG AS
2 -- $Header: APTPSTMTPB.pls 120.3.12020000.2 2012/11/06 05:29:40 sabadu ship $
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'', ''Y'')'; --Included correct validation status , Bug9397505
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', 'Y') --Included correct validation status , Bug9397505
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 --Bug9252303: Added quotes to sync the return value with return type.
199 --Bug9397505 : changed Y/N to 1/0 to support rft format.
200 RETURN ('Y');
201 ELSE
202 RETURN ('N');
203 END IF;
204 EXCEPTION
205 WHEN NO_DATA_FOUND THEN
206 RETURN('N');
207 END invoice_validate_status;
208 --=====================================================================
209 --=====================================================================
210 FUNCTION balance_brought_forward (p_in_vendor_id IN NUMBER
211 ,p_in_vendor_site_id IN NUMBER
212 ,p_in_org_id IN NUMBER)
213 RETURN NUMBER
214 IS
215 ln_amount NUMBER;
216 BEGIN
217 SELECT SUM(DECODE(transaction_type,'P',-1*accounted_amount,accounted_amount)) amount
218 INTO ln_amount
219 FROM
220 ( SELECT 'I' transaction_type
221 ,SUM(NVL(ai.invoice_amount * NVL(ai.exchange_rate,1),0)) accounted_amount
222 FROM ap_invoices ai
223 WHERE ai.vendor_id = p_in_vendor_id
224 AND ai.vendor_site_id = p_in_vendor_site_id
225 AND ai.invoice_type_lookup_code <> 'PREPAYMENT' --Bug9252303
226 AND ai.gl_date < TO_DATE(P_FROM_GL_DATE,'RRRR/MM/DD HH24:MI:SS')
227 AND ai.invoice_currency_code = NVL2(P_CURRENCY,DECODE(P_CURRENCY,'ANY',ai.invoice_currency_code,P_CURRENCY),ai.invoice_currency_code)
228 AND ai.org_id = p_in_org_id
229 AND ((AP_TP_STMT_PKG.invoice_validate_status(ai.invoice_id) = 'Y' --'Y' Bug9252303
230 AND P_UNVALIDATED_TRX = 'N')
231 OR P_UNVALIDATED_TRX = 'Y')
232 AND AP_INVOICES_PKG.get_posting_status(ai.invoice_id)
233 = DECODE(P_ACCOUNTED,'ACCOUNTED','Y'
234 ,'UNACCOUNTED','N'
235 ,AP_INVOICES_PKG.get_posting_status(ai.invoice_id))
236 AND ((P_UNAPPROVED_TRX = 'N'
237 AND ai.wfapproval_status IN ('ACCEPT','APPROVED','MANUALLY APPROVED','NOT REQUIRED','WFAPPROVED','ACKNOWLEDGE','CANCEL','CANCELLED'))
238 OR (P_UNAPPROVED_TRX = 'Y' ))
239 UNION ALL
240 SELECT 'P' transaction_type
241 ,SUM(NVL(aip.amount * NVL(aip.exchange_rate,1),0)) accounted_amount
242 FROM ap_invoice_payments aip
243 ,ap_checks ac
244 WHERE aip.check_id = ac.check_id
245 AND ac.vendor_id = p_in_vendor_id
246 AND ac.vendor_site_id = p_in_vendor_site_id
247 -- AND ac.check_date < TO_DATE(P_FROM_DOC_DATE,'RRRR/MM/DD HH24:MI:SS') Bug 14724500
248 AND aip.accounting_date < TO_DATE(P_FROM_GL_DATE,'RRRR/MM/DD HH24:MI:SS')
249 AND ac.org_id = p_in_org_id
250 AND ac.currency_code = NVL2(P_CURRENCY,DECODE(P_CURRENCY,'ANY',ac.currency_code,P_CURRENCY),ac.currency_code)
251 AND ((P_ACCOUNTED = 'ACCOUNTED' AND aip.posted_flag = 'Y')
252 OR (P_ACCOUNTED = 'UNACCOUNTED' AND aip.posted_flag = 'N')
253 OR (P_ACCOUNTED = 'BOTH'))
254 --Bug9252303: Commented below query that selects prepayment applications.
255 /* UNION ALL
256 SELECT 'A' transaction_type
257 ,SUM((NVL(aid.amount,0)* NVL(ai.exchange_rate,1))*-1) amount_applied
258 FROM ap_invoices ai
259 ,ap_invoice_distributions_all aid
260 ,ap_invoices aipre
261 ,ap_invoice_distributions_all aidpre
262 WHERE ai.invoice_id = aid.invoice_id
263 AND aid.prepay_distribution_id = aidpre.invoice_distribution_id
264 AND aipre.invoice_id = aidpre.invoice_id
265 AND aid.accounting_date < TO_DATE(P_FROM_GL_DATE,'RRRR/MM/DD HH24:MI:SS')
266 AND ai.vendor_id = p_in_vendor_id
267 AND ai.vendor_site_id = p_in_vendor_site_id
268 AND ai.invoice_currency_code = NVL2(P_CURRENCY,DECODE(P_CURRENCY,'ANY',ai.invoice_currency_code,P_CURRENCY),ai.invoice_currency_code)
269 AND ai.org_id = p_in_org_id
270 AND ai.wfapproval_status NOT IN ('CANCEL','CANCELLED')
271 AND AP_INVOICES_PKG.get_posting_status(ai.invoice_id)
272 = DECODE(P_ACCOUNTED,'ACCOUNTED','Y'
273 ,'UNACCOUNTED','N'
274 ,AP_INVOICES_PKG.get_posting_status(ai.invoice_id))
275 AND ((P_UNAPPROVED_TRX = 'N'
276 AND ai.wfapproval_status IN ('ACCEPT','APPROVED','MANUALLY APPROVED','NOT REQUIRED','WFAPPROVED','ACKNOWLEDGE'))
277 OR (P_UNAPPROVED_TRX = 'Y' ))*/
278 );
279 RETURN (NVL(ln_amount,0));
280 END balance_brought_forward;
281
282 END AP_TP_STMT_PKG;
[Home] [Help]