Home » RDBMS Server » Performance Tuning » Query taking more time
| Query taking more time [message #603509] |
Tue, 17 December 2013 02:46  |
 |
nishant87
Messages: 53
Registered: September 2013
Location: india
|
Member |
|
|
SELECT /*+ INDEX_ASC ( aid AP_INVOICE_DISTRIBUTIONS_X7) */
aid.invoice_id, gcc.segment1, aid.line_type_lookup_code,
SUM (NVL (aid.base_amount, aid.amount)) amount
FROM ap_invoice_distributions aid, gl_code_combinations gcc
WHERE aid.org_id = '4719'
AND aid.line_type_lookup_code = 'ITEM'
AND NVL (aid.base_amount, aid.amount) != 0
AND aid.accounting_date BETWEEN '01-DEC-2012' AND '31-DEC-2012'
AND aid.accounting_date <= '22-NOV-2013'
AND gcc.code_combination_id = aid.dist_code_combination_id
AND aid.invoice_id IN (
SELECT ai.invoice_id
FROM ap_invoices ai
WHERE NVL (NULL, ai.vendor_id) = ai.vendor_id
AND NVL (NULL, ai.invoice_type_lookup_code) =
ai.invoice_type_lookup_code
AND ( ai.cancelled_date IS NULL
OR ( ai.cancelled_date IS NOT NULL
AND ai.cancelled_date > '22-NOV-2013'
)
))
GROUP BY aid.invoice_id, gcc.segment1, aid.line_type_lookup_code
HAVING SUM (NVL (aid.base_amount, aid.amount)) != 0
UNION ALL
SELECT /*+ INDEX_ASC ( aid AP_INVOICE_DISTRIBUTIONS_X7)
*/
aid.invoice_id, gcc.segment1, aid.line_type_lookup_code,
SUM (NVL (aid.base_amount, aid.amount)) amount
FROM ap_invoice_distributions aid, gl_code_combinations gcc
WHERE aid.org_id = '4719'
AND aid.line_type_lookup_code = 'TAX'
AND NVL (aid.base_amount, aid.amount) != 0
AND aid.accounting_date BETWEEN '01-DEC-2012' AND '31-DEC-2012'
AND aid.accounting_date <= '22-NOV-2013'
AND gcc.code_combination_id = aid.dist_code_combination_id
AND aid.invoice_id IN (
SELECT ai.invoice_id
FROM ap_invoices ai
WHERE NVL (NULL, ai.vendor_id) = ai.vendor_id
AND NVL (NULL, ai.invoice_type_lookup_code) =
ai.invoice_type_lookup_code
AND ( ai.cancelled_date IS NULL
OR ( ai.cancelled_date IS NOT NULL
AND ai.cancelled_date > '22-NOV-2013'
)
))
AND ( NOT EXISTS (
SELECT 1
FROM ap_invoice_distributions aid2
WHERE aid2.invoice_id = aid.invoice_id
AND aid2.line_type_lookup_code IN
('WTH', 'ITEM', 'PREPAY')
AND aid2.accounting_date <= '22-NOV-2013')
OR EXISTS (
SELECT SUM (NVL (aid8.base_amount, aid8.amount))
FROM ap_invoice_distributions aid8
WHERE aid8.invoice_id = aid.invoice_id
AND aid8.line_type_lookup_code = 'ITEM'
AND aid8.accounting_date <= '22-NOV-2013'
GROUP BY aid8.line_type_lookup_code
HAVING SUM (NVL (aid8.base_amount, aid8.amount)) = 0)
)
GROUP BY aid.invoice_id, gcc.segment1, aid.line_type_lookup_code
HAVING SUM (NVL (aid.base_amount, aid.amount)) != 0
UNION ALL
SELECT /*+ INDEX_ASC ( aid
AP_INVOICE_DISTRIBUTIONS_X7) */
aid.invoice_id, gcc.segment1, aid.line_type_lookup_code,
SUM (NVL (aid.base_amount, aid.amount)) amount
FROM ap_invoice_distributions aid, gl_code_combinations gcc
WHERE aid.org_id = '4719'
AND aid.line_type_lookup_code = 'FREIGHT'
AND NVL (aid.base_amount, aid.amount) != 0
AND aid.accounting_date BETWEEN '01-DEC-2012' AND '31-DEC-2012'
AND aid.accounting_date <= '22-NOV-2013'
AND gcc.code_combination_id = aid.dist_code_combination_id
AND aid.invoice_id IN (
SELECT ai.invoice_id
FROM ap_invoices ai
WHERE NVL (NULL, ai.vendor_id) = ai.vendor_id
AND NVL (NULL, ai.invoice_type_lookup_code) =
ai.invoice_type_lookup_code
AND ( ai.cancelled_date IS NULL
OR ( ai.cancelled_date IS NOT NULL
AND ai.cancelled_date > '22-NOV-2013'
)
))
AND ( NOT EXISTS (
SELECT 1
FROM ap_invoice_distributions aid2
WHERE aid2.invoice_id = aid.invoice_id
AND aid2.line_type_lookup_code IN
('WTH', 'ITEM', 'PREPAY')
AND aid2.accounting_date <= '22-NOV-2013')
OR EXISTS (
SELECT SUM (NVL (aid8.base_amount, aid8.amount))
FROM ap_invoice_distributions aid8
WHERE aid8.invoice_id = aid.invoice_id
AND aid8.line_type_lookup_code = 'ITEM'
AND aid8.accounting_date <= '22-NOV-2013'
GROUP BY aid8.line_type_lookup_code
HAVING SUM (NVL (aid8.base_amount, aid8.amount)) = 0)
)
GROUP BY aid.invoice_id, gcc.segment1, aid.line_type_lookup_code
HAVING SUM (NVL (aid.base_amount, aid.amount)) != 0
|
|
|
|
|
|
|
|
|
|
| Re: Query taking more time [message #603513 is a reply to message #603512] |
Tue, 17 December 2013 03:12  |
|
nishant87
Messages: 53
Registered: September 2013
Location: india
|
Member |
|
|
/* Formatted on 2013/12/17 14:42 (Formatter Plus v4.8. */
SELECT /*+ INDEX_ASC ( aid AP_INVOICE_DISTRIBUTIONS_X7) */
aid.invoice_id, gcc.segment1, aid.line_type_lookup_code,
SUM (NVL (aid.base_amount, aid.amount)) amount
FROM ap_invoice_distributions aid, gl_code_combinations gcc
WHERE aid.org_id = :b1
AND aid.line_type_lookup_code = 'ITEM'
AND NVL (aid.base_amount, aid.amount) != 0
AND aid.accounting_date BETWEEN :b2 AND :b3
AND aid.accounting_date <= :b4
AND gcc.code_combination_id = aid.dist_code_combination_id
AND aid.invoice_id IN (
SELECT ai.invoice_id
FROM ap_invoices ai
WHERE NVL (:b5, ai.vendor_id) = ai.vendor_id
AND NVL (:b6, ai.invoice_type_lookup_code) =
ai.invoice_type_lookup_code
AND ( ai.cancelled_date IS NULL
OR ( ai.cancelled_date IS NOT NULL
AND ai.cancelled_date > :b4
)
))
GROUP BY aid.invoice_id, gcc.segment1, aid.line_type_lookup_code
HAVING SUM (NVL (aid.base_amount, aid.amount)) != 0
UNION ALL
SELECT /*+ INDEX_ASC ( aid AP_INVOICE_DISTRIBUTIONS_X7)
*/
aid.invoice_id, gcc.segment1, aid.line_type_lookup_code,
SUM (NVL (aid.base_amount, aid.amount)) amount
FROM ap_invoice_distributions aid, gl_code_combinations gcc
WHERE aid.org_id = :b1
AND aid.line_type_lookup_code = 'TAX'
AND NVL (aid.base_amount, aid.amount) != 0
AND aid.accounting_date BETWEEN :b2 AND :b3
AND aid.accounting_date <= :b4
AND gcc.code_combination_id = aid.dist_code_combination_id
AND aid.invoice_id IN (
SELECT ai.invoice_id
FROM ap_invoices ai
WHERE NVL (:b5, ai.vendor_id) = ai.vendor_id
AND NVL (:b6, ai.invoice_type_lookup_code) =
ai.invoice_type_lookup_code
AND ( ai.cancelled_date IS NULL
OR ( ai.cancelled_date IS NOT NULL
AND ai.cancelled_date > :b4
)
))
AND ( NOT EXISTS (
SELECT 1
FROM ap_invoice_distributions aid2
WHERE aid2.invoice_id = aid.invoice_id
AND aid2.line_type_lookup_code IN
('WTH', 'ITEM', 'PREPAY')
AND aid2.accounting_date <= :b4)
OR EXISTS (
SELECT SUM (NVL (aid8.base_amount, aid8.amount))
FROM ap_invoice_distributions aid8
WHERE aid8.invoice_id = aid.invoice_id
AND aid8.line_type_lookup_code = 'ITEM'
AND aid8.accounting_date <= :b4
GROUP BY aid8.line_type_lookup_code
HAVING SUM (NVL (aid8.base_amount, aid8.amount)) = 0)
)
GROUP BY aid.invoice_id, gcc.segment1, aid.line_type_lookup_code
HAVING SUM (NVL (aid.base_amount, aid.amount)) != 0
UNION ALL
SELECT /*+ INDEX_ASC ( aid
AP_INVOICE_DISTRIBUTIONS_X7) */
aid.invoice_id, gcc.segment1, aid.line_type_lookup_code,
SUM (NVL (aid.base_amount, aid.amount)) amount
FROM ap_invoice_distributions aid, gl_code_combinations gcc
WHERE aid.org_id = :b1
AND aid.line_type_lookup_code = 'FREIGHT'
AND NVL (aid.base_amount, aid.amount) != 0
AND aid.accounting_date BETWEEN :b2 AND :b3
AND aid.accounting_date <= :b4
AND gcc.code_combination_id = aid.dist_code_combination_id
AND aid.invoice_id IN (
SELECT ai.invoice_id
FROM ap_invoices ai
WHERE NVL (:b5, ai.vendor_id) = ai.vendor_id
AND NVL (:b6, ai.invoice_type_lookup_code) =
ai.invoice_type_lookup_code
AND ( ai.cancelled_date IS NULL
OR ( ai.cancelled_date IS NOT NULL
AND ai.cancelled_date > :b4
)
))
AND ( NOT EXISTS (
SELECT 1
FROM ap_invoice_distributions aid2
WHERE aid2.invoice_id = aid.invoice_id
AND aid2.line_type_lookup_code IN
('WTH', 'ITEM', 'PREPAY')
AND aid2.accounting_date <= :b4)
OR EXISTS (
SELECT SUM (NVL (aid8.base_amount, aid8.amount))
FROM ap_invoice_distributions aid8
WHERE aid8.invoice_id = aid.invoice_id
AND aid8.line_type_lookup_code = 'ITEM'
AND aid8.accounting_date <= :b4
GROUP BY aid8.line_type_lookup_code
HAVING SUM (NVL (aid8.base_amount, aid8.amount)) = 0)
)
GROUP BY aid.invoice_id, gcc.segment1, aid.line_type_lookup_code
HAVING SUM (NVL (aid.base_amount, aid.amount)) != 0
|
|
|
|
|
|
| Re: Query taking more time [message #603516 is a reply to message #603514] |
Tue, 17 December 2013 03:24 |
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
|
Senior Member |
|
|
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 0 0 0 0
Execute 330 0.15 0.17 0 0 0 0
Fetch 529837 111.40 1876.08 151301 2384537 0 529507
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 530168 111.57 1876.27 151301 2384537 0 529507
As requested...please use code-tags, like I have done above as an example.
In this way we can actually read what is your post instead of guess.
Also please explain what is exactly your problem.
I guess your query is to slow (?)
Does that mean slower then before. Or slow in general.
Please supply ddl
Please supply explain plan in the post. I (but I'm speaking for me) will not just open a attachement in a forum.
Are you statistics up to date
Think about what information we need for helping you. (and then supply that information)
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Query taking more time [message #603530 is a reply to message #603519] |
Tue, 17 December 2013 04:01 |
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
|
Senior Member |
|
|
nishant87 wrote on Tue, 17 December 2013 10:34Above is not right.Please ignore
Not to worry, but have a very cloase look at : this page. Using code tags is explained there very nicely.
|
|
|
|
|
|
|
|
| Re: Query taking more time [message #603534 is a reply to message #603533] |
Tue, 17 December 2013 04:28 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
they are in the execution plan, it does both in one go at the top.
A better question is why does the select contain union all, the differences between the three selects aren't great enough to justify it.
@nishant87 - try rewriting it without the union alls.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Query taking more time [message #603671 is a reply to message #603668] |
Wed, 18 December 2013 05:45 |
|
nishant87
Messages: 53
Registered: September 2013
Location: india
|
Member |
|
|
SQL> EXPLAIN PLAN FOR
2 SELECT ai.invoice_id
3 FROM ap_invoices ai
4 WHERE NVL (NULL, ai.vendor_id) = ai.vendor_id
5 AND NVL (NULL, ai.invoice_type_lookup_code) =
6 ai.invoice_type_lookup_code
7 AND ( ai.cancelled_date IS NULL
8 OR ( ai.cancelled_date IS NOT NULL
9 AND ai.cancelled_date > '22-NOV-2013'
)); 10
Explained.
BETSYU1(Dev)-SQL> SET LINESIZE 130
BETSYU1(Dev)-SQL> SET PAGESIZE 0
BETSYU1(Dev)-SQL> SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY); 2
Plan hash value: 2198335773
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106K| 2804K| 143K (7)| 00:28:42 |
|* 1 | TABLE ACCESS FULL| AP_INVOICES_ALL | 106K| 2804K| 143K (7)| 00:28:42 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("CANCELLED_DATE" IS NULL OR "CANCELLED_DATE">TO_DATE('
2013-11-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) AND
NVL("ORG_ID",NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),'
',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),(-99)))=NVL(TO_NUMBER(DECODE(SU
BSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,1
0))),(-99)) AND "INVOICE_TYPE_LOOKUP_CODE" IS NOT NULL)
18 rows selected.
BETSYU1(Dev)-SQL>
|
|
|
|
| Re: Query taking more time [message #603672 is a reply to message #603671] |
Wed, 18 December 2013 05:48 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Your predicates there don't really leave it many choices without getting remarkably creative.
You're testing for is null, that really limits your options.
|
|
|
|
| Re: Query taking more time [message #603686 is a reply to message #603669] |
Wed, 18 December 2013 07:58 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
As Roachcoach says, you don't really have any options for speeding that bit up (expect for maybe using parallel).
So your best bet is to rewrite the query so that that slow bit isn't done three times for every execution.
Therefore:
cookiemonster wrote on Wed, 18 December 2013 11:37cookiemonster wrote on Tue, 17 December 2013 10:28
@nishant87 - try rewriting it without the union alls.
|
|
|
|
| Re: Query taking more time [message #603690 is a reply to message #603686] |
Wed, 18 December 2013 08:14 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Depending on the version and data skew you could get creative with indexing: be that function based index or virtual column and index but those are rather situational and would require varying degrees of code rewriting/view&synonym creation which may not sit well with stuff outside of this single query.
So you have a few options but truth be told, cookiemonsters idea is the best and least obtrusive starter, if it isnt enough, research some of the things I mention here. I'm not going to go into detail because if you go this route then you really need to understand what you're doing and not just lift it from a forum.
|
|
|
|
| Re: Query taking more time [message #603715 is a reply to message #603690] |
Wed, 18 December 2013 11:49 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Even I would go with Cookiemonster's solution.
Another thing, In 11g a virtual column as a function over a column is same as a function based index.
|
|
|
|
|
|
|
|
| Re: Query taking more time [message #603782 is a reply to message #603780] |
Thu, 19 December 2013 06:22 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Well the difference between the 2nd and 3rd part is:
2nd:
AND aid.line_type_lookup_code = 'TAX'
3rd:
AND aid.line_type_lookup_code = 'FREIGHT'
So changing that line in the 2nd to:
AND aid.line_type_lookup_code IN ('TAX', 'FREIGHT')
will allow you to get rid of the 3rd.
Do that then compare the 1st section to the 2nd and see if you can reconcile the differences - you've got a subquery or two that will need to be OR'd in the where clause. It's not difficult, give it a try. If you get stuck post what you attempted and we'll point out where the issue is.
|
|
|
|
| Re: Query taking more time [message #603808 is a reply to message #603782] |
Thu, 19 December 2013 09:04 |
|
nishant87
Messages: 53
Registered: September 2013
Location: india
|
Member |
|
|
SELECT /*+ INDEX_ASC ( aid AP_INVOICE_DISTRIBUTIONS_X7) */
aid.invoice_id, gcc.segment1, aid.line_type_lookup_code,
SUM (NVL (aid.base_amount, aid.amount)) amount
FROM ap_invoice_distributions aid, gl_code_combinations gcc
WHERE aid.org_id = '4719'
AND aid.line_type_lookup_code = 'ITEM'
AND NVL (aid.base_amount, aid.amount) != 0
AND aid.accounting_date BETWEEN '01-Dec-2012' AND '31-Dec-2012'
AND aid.accounting_date <= '22-Nov-2013'
AND gcc.code_combination_id = aid.dist_code_combination_id
AND aid.invoice_id IN (
SELECT ai.invoice_id
FROM ap_invoices ai
WHERE NVL (NULL, ai.vendor_id) = ai.vendor_id
AND NVL (NULL, ai.invoice_type_lookup_code) =
ai.invoice_type_lookup_code
AND ( ai.cancelled_date IS NULL
OR ( ai.cancelled_date IS NOT NULL
AND ai.cancelled_date > '22-Nov-2013'
)
))
GROUP BY aid.invoice_id, gcc.segment1, aid.line_type_lookup_code
HAVING SUM (NVL (aid.base_amount, aid.amount)) != 0
UNION ALL
SELECT /*+ INDEX_ASC ( aid AP_INVOICE_DISTRIBUTIONS_X7)
*/
aid.invoice_id, gcc.segment1, aid.line_type_lookup_code,
SUM (NVL (aid.base_amount, aid.amount)) amount
FROM ap_invoice_distributions aid, gl_code_combinations gcc
WHERE aid.org_id = '4719'
AND aid.line_type_lookup_code IN ('TAX','FREIGHT')
AND NVL (aid.base_amount, aid.amount) != 0
AND aid.accounting_date BETWEEN '01-Dec-2012' AND '31-Dec-2012'
AND aid.accounting_date <= '22-Nov-2013'
AND gcc.code_combination_id = aid.dist_code_combination_id
AND aid.invoice_id IN (
SELECT ai.invoice_id
FROM ap_invoices ai
WHERE NVL (NULL, ai.vendor_id) = ai.vendor_id
AND NVL (NULL, ai.invoice_type_lookup_code) =
ai.invoice_type_lookup_code
AND ( ai.cancelled_date IS NULL
OR ( ai.cancelled_date IS NOT NULL
AND ai.cancelled_date > '22-Nov-2013'
)
))
AND ( NOT EXISTS (
SELECT 1
FROM ap_invoice_distributions aid2
WHERE aid2.invoice_id = aid.invoice_id
AND aid2.line_type_lookup_code IN
('WTH', 'ITEM', 'PREPAY')
AND aid2.accounting_date <= '22-Nov-2013')
OR EXISTS (
SELECT SUM (NVL (aid8.base_amount, aid8.amount))
FROM ap_invoice_distributions aid8
WHERE aid8.invoice_id = aid.invoice_id
-- AND aid8.line_type_lookup_code = 'ITEM'
AND aid8.accounting_date <= '22-Nov-2013'
GROUP BY aid8.line_type_lookup_code
HAVING SUM (NVL (aid8.base_amount, aid8.amount)) = 0)
)
GROUP BY aid.invoice_id, gcc.segment1, aid.line_type_lookup_code
HAVING SUM (NVL (aid.base_amount, aid.amount)) != 0
i am on 11i
|
|
|
|
| Re: Query taking more time [message #603810 is a reply to message #603808] |
Thu, 19 December 2013 09:12 |
|
nishant87
Messages: 53
Registered: September 2013
Location: india
|
Member |
|
|
/* Formatted on 2013/12/19 20:42 (Formatter Plus v4.8. */
PROCEDURE create_tem_table_invoices
IS
v_found NUMBER;
v_exch_rate_reexp NUMBER;
v_value_set_id NUMBER;
v_amount NUMBER;
v_valid_inv NUMBER;
v_amount_remaining NUMBER;
v_sum_dist_amount NUMBER;
v_source xxap_invoice_aging_tmp.SOURCE%TYPE;
v_dist_split NUMBER;
v_segment_desc VARCHAR2 (240);
v_group_desc VARCHAR2 (240);
v_due_date DATE;
v_parent_flex_value VARCHAR2 (240);
-- Added by Quanam SR 10-04-2013 RFC#1140830
v_chr_parent_from VARCHAR2 (240);
v_chr_parent_to VARCHAR2 (240);
--
CURSOR c_dates
IS
SELECT /*+ INDEX ( gp GL_PERIODS_U2) */
gp.start_date, gp.end_date
FROM gl_periods gp
WHERE gp.period_set_name = :c_calendar_name
AND ( (gp.end_date <= TRUNC (:p_fecha_hasta))
OR (TRUNC (:p_fecha_hasta) BETWEEN gp.start_date AND gp.end_date
)
);
-- and gp.end_date <= trunc(:p_fecha_hasta);
CURSOR c_distributions (
p_start_date IN DATE,
p_end_date IN DATE,
p_value_set_id IN NUMBER
)
IS
SELECT /*+ INDEX_ASC ( aid AP_INVOICE_DISTRIBUTIONS_X7) */
aid.invoice_id, gcc.segment1
-- ,ffvl.description
-- ,ffv.parent_flex_value
--,ffvl2.description nombre_grupo
, aid.line_type_lookup_code,
SUM (NVL (aid.base_amount, aid.amount)) amount
FROM ap_invoice_distributions aid, gl_code_combinations gcc --,
--fnd_flex_value_norm_hierarchy ffv--,
-- fnd_flex_values_vl ffvl
WHERE aid.org_id = :cp_org_id
-- Commented by Quanam SR 02-05-2013 and aid.posted_flag ='Y'
-- and aid.posted_flag ='Y'
--
AND aid.line_type_lookup_code = 'ITEM'
AND NVL (aid.base_amount, aid.amount) <> 0
AND aid.accounting_date BETWEEN p_start_date AND p_end_date
AND aid.accounting_date <= :p_fecha_hasta
AND gcc.code_combination_id = aid.dist_code_combination_id
-- Added by Quanam SR 10-04-2013 RFC#1140830
AND aid.invoice_id IN (
SELECT ai.invoice_id
FROM ap_invoices ai
WHERE NVL (:p_vendor_id, ai.vendor_id) = ai.vendor_id
AND NVL (:p_invoice_type, ai.invoice_type_lookup_code) =
ai.invoice_type_lookup_code
AND ( ai.cancelled_date IS NULL
OR ( ai.cancelled_date IS NOT NULL
AND ai.cancelled_date > :p_fecha_hasta
)
))
--
-- and ffv.flex_value_set_id = p_value_set_id
-- and gcc.segment1 between ffv.child_flex_value_low and ffv.child_flex_value_high
-- and ( ( :p_num_currency_func = 'MXN' and ffv.parent_flex_value Like '2%'
-- and ffv.parent_flex_value not like '2ZZ%' )
-- or (ffv.parent_flex_value like '03%' and :p_num_currency_func= 'ARS')
-- )
/*and aid.invoice_id in (select ps.invoice_id
from ap_payment_schedules ps
where ps.invoice_id = aid.invoice_id
and (((nvl (ps.amount_remaining, 0) != 0)
or ( nvl (ps.amount_remaining, 0) = 0
and --((i.cancelled_date is not null and i.cancelled_date > :p_fecha_hasta )
exists ( select 1
from ap_invoice_payments aip, ap_checks ac
where aip.check_id = ac.check_id
and aip.invoice_id = aid.invoice_id
and aip.accounting_date > :p_fecha_hasta
and decode (ac.status_lookup_code, 'VOIDED', 'Y', 'N') = 'N'
)
)
or exists ( select 1
from ap_invoice_distributions
where line_type_lookup_code = 'PREPAY'
and accounting_date > :p_fecha_hasta
and invoice_id = aid.invoice_id
)
)
))*/
-- and ffvl.flex_value_set_id = p_value_set_id
-- and ffvl.flex_value = gcc.segment1
-- and ffvl.summary_flag = 'N'
-- and ffvl.enabled_flag = 'Y'
--and aid.invoice_id =369219
-- and gcc.segment1 = '0308'
GROUP BY aid.invoice_id, gcc.segment1
--,ffvl.description
-- ,ffv.parent_flex_value
, aid.line_type_lookup_code
-- Added by Quanam SR 30-APR-2013 RFC#1140830 To discard some lines with item and its reversal
HAVING SUM (NVL (aid.base_amount, aid.amount)) <> 0
--
UNION ALL
SELECT /*+ INDEX_ASC ( aid AP_INVOICE_DISTRIBUTIONS_X7) */
aid.invoice_id, gcc.segment1
--,ffvl.description
-- ,ffv.parent_flex_value
, aid.line_type_lookup_code,
SUM (NVL (aid.base_amount, aid.amount)) amount
FROM ap_invoice_distributions aid, gl_code_combinations gcc --,
--fnd_flex_value_norm_hierarchy ffv--,
--fnd_flex_values_vl ffvl
WHERE aid.org_id = :cp_org_id
-- Commented by Quanam SR 02-05-2013 and aid.posted_flag ='Y'
-- and aid.posted_flag ='Y'
--
AND aid.line_type_lookup_code = 'TAX'
AND NVL (aid.base_amount, aid.amount) <> 0
AND aid.accounting_date BETWEEN p_start_date AND p_end_date
AND aid.accounting_date <= :p_fecha_hasta
AND gcc.code_combination_id = aid.dist_code_combination_id
-- Commented by Quanam SR 10-04-2013 RFC#1140830
/*
and NOT EXISTS(select 1
from ap_invoice_distributions aid2
where aid2.invoice_id = aid.invoice_id
and aid2.line_type_lookup_code in ('WTH','ITEM','PREPAY'))
*/
-- Added by Quanam SR 10-04-2013 RFC#1140830
AND aid.invoice_id IN (
SELECT ai.invoice_id
FROM ap_invoices ai
WHERE NVL (:p_vendor_id, ai.vendor_id) = ai.vendor_id
AND NVL (:p_invoice_type, ai.invoice_type_lookup_code) =
ai.invoice_type_lookup_code
AND ( ai.cancelled_date IS NULL
OR ( ai.cancelled_date IS NOT NULL
AND ai.cancelled_date > :p_fecha_hasta
)
))
-- Added by Quanam SR 10-04-2013 RFC#1140830
AND ( NOT EXISTS (
SELECT 1
FROM ap_invoice_distributions aid2
WHERE aid2.invoice_id = aid.invoice_id
AND aid2.line_type_lookup_code IN
('WTH', 'ITEM', 'PREPAY')
AND aid2.accounting_date <= :p_fecha_hasta)
OR EXISTS (
SELECT SUM (NVL (aid8.base_amount, aid8.amount))
FROM ap_invoice_distributions aid8
WHERE aid8.invoice_id = aid.invoice_id
AND aid8.line_type_lookup_code = 'ITEM'
AND aid8.accounting_date <= :p_fecha_hasta
--AND NVL(aid8.base_amount,aid8.amount) <> 0
GROUP BY aid8.line_type_lookup_code
HAVING SUM (NVL (aid8.base_amount, aid8.amount)) = 0)
)
--
--
--
--and ffv.flex_value_set_id = p_value_set_id
--and gcc.segment1 between ffv.child_flex_value_low and ffv.child_flex_value_high
--and ( ( :p_num_currency_func = 'MXN' and ffv.parent_flex_value Like '2%'
-- and ffv.parent_flex_value not like '2ZZ%' )
-- or (ffv.parent_flex_value like '03%' and :p_num_currency_func= 'ARS')
-- )
/*and aid.invoice_id in (select ps.invoice_id
from ap_payment_schedules ps
where ps.invoice_id = aid.invoice_id
and (((nvl (ps.amount_remaining, 0) != 0)
or ( nvl (ps.amount_remaining, 0) = 0
and --((i.cancelled_date is not null and i.cancelled_date > :p_fecha_hasta )
exists ( select 1
from ap_invoice_payments aip, ap_checks ac
where aip.check_id = ac.check_id
and aip.invoice_id = aid.invoice_id
and aip.accounting_date > :p_fecha_hasta
and decode (ac.status_lookup_code, 'VOIDED', 'Y', 'N') = 'N'
)
)
or exists ( select 1
from ap_invoice_distributions
where line_type_lookup_code = 'PREPAY'
and accounting_date > :p_fecha_hasta
and invoice_id = aid.invoice_id
)
)
))
--and ffvl.flex_value_set_id = p_value_set_id
--and ffvl.flex_value = gcc.segment1
--and ffvl.summary_flag = 'N'
--and ffvl.enabled_flag = 'Y'
--and aid.invoice_id =369219
--and gcc.segment1 = '0308'*/
GROUP BY aid.invoice_id, gcc.segment1
--,ffvl.description
--,ffv.parent_flex_value
, aid.line_type_lookup_code
-- Added by Quanam SR 30-APR-2013 RFC#1140830 To discard some lines with item and its reversal
HAVING SUM (NVL (aid.base_amount, aid.amount)) <> 0
--
-- Added by Quanam SR 30-APR-2013 RFC#1140830
UNION ALL
SELECT /*+ INDEX_ASC ( aid AP_INVOICE_DISTRIBUTIONS_X7) */
aid.invoice_id, gcc.segment1
--,ffvl.description
-- ,ffv.parent_flex_value
, aid.line_type_lookup_code,
SUM (NVL (aid.base_amount, aid.amount)) amount
FROM ap_invoice_distributions aid, gl_code_combinations gcc --,
--fnd_flex_value_norm_hierarchy ffv--,
--fnd_flex_values_vl ffvl
WHERE aid.org_id = :cp_org_id
AND aid.line_type_lookup_code = 'FREIGHT'
AND NVL (aid.base_amount, aid.amount) <> 0
AND aid.accounting_date BETWEEN p_start_date AND p_end_date
AND aid.accounting_date <= :p_fecha_hasta
AND gcc.code_combination_id = aid.dist_code_combination_id
-- Commented by Quanam SR 10-04-2013 RFC#1140830
/*
and NOT EXISTS(select 1
from ap_invoice_distributions aid2
where aid2.invoice_id = aid.invoice_id
and aid2.line_type_lookup_code in ('WTH','ITEM','PREPAY'))
*/
-- Added by Quanam SR 10-04-2013 RFC#1140830
AND aid.invoice_id IN (
SELECT ai.invoice_id
FROM ap_invoices ai
WHERE NVL (:p_vendor_id, ai.vendor_id) = ai.vendor_id
AND NVL (:p_invoice_type, ai.invoice_type_lookup_code) =
ai.invoice_type_lookup_code
AND ( ai.cancelled_date IS NULL
OR ( ai.cancelled_date IS NOT NULL
AND ai.cancelled_date > :p_fecha_hasta
)
))
-- Added by Quanam SR 10-04-2013 RFC#1140830
AND ( NOT EXISTS (
SELECT 1
FROM ap_invoice_distributions aid2
WHERE aid2.invoice_id = aid.invoice_id
AND aid2.line_type_lookup_code IN
('WTH', 'ITEM', 'PREPAY')
AND aid2.accounting_date <= :p_fecha_hasta)
OR EXISTS (
SELECT SUM (NVL (aid8.base_amount, aid8.amount))
FROM ap_invoice_distributions aid8
WHERE aid8.invoice_id = aid.invoice_id
AND aid8.line_type_lookup_code = 'ITEM'
AND aid8.accounting_date <= :p_fecha_hasta
--AND NVL(aid8.base_amount,aid8.amount) <> 0
GROUP BY aid8.line_type_lookup_code
HAVING SUM (NVL (aid8.base_amount, aid8.amount)) = 0)
)
--
--
--
--and ffv.flex_value_set_id = p_value_set_id
--and gcc.segment1 between ffv.child_flex_value_low and ffv.child_flex_value_high
--and ( ( :p_num_currency_func = 'MXN' and ffv.parent_flex_value Like '2%'
-- and ffv.parent_flex_value not like '2ZZ%' )
-- or (ffv.parent_flex_value like '03%' and :p_num_currency_func= 'ARS')
-- )
--and ffvl.flex_value_set_id = p_value_set_id
--and ffvl.flex_value = gcc.segment1
--and ffvl.summary_flag = 'N'
--and ffvl.enabled_flag = 'Y'
--and aid.invoice_id =369219
--and gcc.segment1 = '0308'*/
GROUP BY aid.invoice_id, gcc.segment1
--,ffvl.description
--,ffv.parent_flex_value
, aid.line_type_lookup_code
-- Added by Quanam SR 30-APR-2013 RFC#1140830 To discard some lines with item and its reversal
HAVING SUM (NVL (aid.base_amount, aid.amount)) <> 0;
--
CURSOR c_payment (p_invoice_id IN NUMBER)
IS
SELECT ps.due_date, ps.amount_remaining
FROM ap_payment_schedules ps
WHERE ps.invoice_id = p_invoice_id;
CURSOR c_invoices (p_invoice_id IN NUMBER)
IS
SELECT i.invoice_id, i.vendor_id, i.invoice_num,
i.invoice_currency_code, i.payment_currency_code,
i.payment_cross_rate, i.vendor_site_id, i.amount_paid,
i.invoice_type_lookup_code, i.accts_pay_code_combination_id,
i.invoice_date, i.exchange_rate, i.cancelled_date, i.org_id,
i.gl_date, i.payment_status_flag, i.terms_id --,
--ps.due_date
FROM ap_invoices i --,
-- ap_payment_schedules ps
WHERE i.invoice_id = p_invoice_id
AND NVL (:p_vendor_id, i.vendor_id) = i.vendor_id
AND NVL (:p_invoice_type, i.invoice_type_lookup_code) =
i.invoice_type_lookup_code
AND ( i.cancelled_date IS NULL
OR ( i.cancelled_date IS NOT NULL
AND i.cancelled_date > :p_fecha_hasta
)
);
/* and i.invoice_id in (select ps.invoice_id
from ap_payment_schedules ps
where ps.invoice_id = p_invoice_id
--and (nvl(i.cancelled_date, next_day(trunc(:p_fecha_hasta),1)) > trunc(:p_fecha_hasta))
and ps.invoice_id = i.invoice_id
and (((nvl (ps.amount_remaining, 0) * nvl (i.exchange_rate, 1) != 0)
or ( nvl (ps.amount_remaining, 0) * nvl (i.exchange_rate, 1) = 0
and ((i.cancelled_date is not null and i.cancelled_date > :p_fecha_hasta )
or exists ( select 1
from ap_invoice_payments aip, ap_checks ac
where aip.check_id = ac.check_id
and aip.invoice_id = i.invoice_id
and aip.accounting_date > :p_fecha_hasta
and decode (ac.status_lookup_code, 'VOIDED', 'Y', 'N') = 'N'
)
)
or exists ( select 1
from ap_invoice_distributions
where line_type_lookup_code = 'PREPAY'
and accounting_date > :p_fecha_hasta
and invoice_id = i.invoice_id
)
)
)
and (i.cancelled_date is null or (i.cancelled_date is not null and i.cancelled_date > :p_fecha_hasta))
));
/* and ((NVL(ps.amount_remaining, 0)*NVL(i.exchange_rate, 1)!= 0)
or (NVL(ps.amount_remaining, 0)*NVL(i.exchange_rate, 1)=0
and ( exists (select 1
from ap_invoice_payments aip
, ap_checks ac
where aip.check_id = ac.check_id
and aip.invoice_id = i.invoice_id
and aip.accounting_date > trunc(:p_fecha_hasta)
and DECODE (ac.status_lookup_code,'VOIDED', 'Y','N') = 'N')
or exists (select 1
from ap_invoice_distributions aid
where aid.line_type_lookup_code = 'PREPAY'
and aid.accounting_date > trunc(:p_fecha_hasta)
and aid.invoice_id = i.invoice_id)
)
)
or (nvl(i.cancelled_date, next_day(trunc(:p_fecha_hasta),1)) > trunc(:p_fecha_hasta))
);*/--and i.vendor_id=16702 ;
CURSOR c_valid_inv (p_invoice_id IN NUMBER)
IS
SELECT COUNT (1)
FROM ap_invoice_payments aip, ap_checks ac
WHERE aip.check_id = ac.check_id
AND aip.invoice_id = p_invoice_id
AND aip.accounting_date > :p_fecha_hasta
AND DECODE (ac.status_lookup_code, 'VOIDED', 'Y', 'N') = 'N'
AND ROWNUM = 1;
CURSOR c_valid_inv2 (p_invoice_id IN NUMBER)
IS
SELECT COUNT (1)
FROM ap_invoice_distributions
WHERE invoice_id = p_invoice_id
AND line_type_lookup_code = 'PREPAY'
AND accounting_date > :p_fecha_hasta
AND ROWNUM = 1;
CURSOR c_dist_split (
p_invoice_id IN NUMBER,
p_line_type_lookup_code IN VARCHAR2
)
IS
SELECT /*+ INDEX ( xxap_invoice_aging_all XXAP_INVOICE_AGING_ALL_N1) */
segment1, sum_dist_amount
FROM xxap_invoice_aging_all
WHERE invoice_id = p_invoice_id
AND line_type_lookup_code = p_line_type_lookup_code
AND request_id = :cp_concurrent_id
AND org_id = :cp_org_id;
CURSOR c_dist_split2 (
p_invoice_id IN NUMBER,
p_line_type_lookup_code IN VARCHAR2,
p_segment1 IN VARCHAR2
)
IS
SELECT /*+ INDEX ( xxap_invoice_aging_all XXAP_INVOICE_AGING_ALL_N1) */
SUM (DECODE (segment1, p_segment1, 1, 0)),
SUM (DECODE (segment1, p_segment1, 0, sum_dist_amount))
FROM xxap_invoice_aging_all
WHERE invoice_id = p_invoice_id
AND line_type_lookup_code = p_line_type_lookup_code
AND request_id = :cp_concurrent_id
AND org_id = :cp_org_id;
CURSOR c_segment_description (
p_value IN VARCHAR2,
p_value_set_id IN NUMBER
-- Added by Quanam SR 10-04-2013 RFC#1140830
,
p_parent_from IN VARCHAR2,
p_parent_to IN VARCHAR2
--
)
IS
/*Select t.description
From fnd_flex_values_tl t,
fnd_flex_values b
Where b.flex_value_id = t.flex_value_id
And t.LANGUAGE = USERENV ('LANG')
And b.flex_value = p_value
And b.flex_value_set_id = p_value_set_id
And b.summary_flag = 'N'
And b.enabled_flag = 'Y';*/
SELECT t.description, ffv.parent_flex_value
FROM fnd_flex_values_tl t,
fnd_flex_values b,
fnd_flex_value_norm_hierarchy ffv
WHERE b.flex_value_id = t.flex_value_id
AND t.LANGUAGE = USERENV ('LANG')
AND b.flex_value = p_value
AND b.flex_value_set_id = p_value_set_id
AND ffv.flex_value_set_id = p_value_set_id
AND p_value BETWEEN ffv.child_flex_value_low
AND ffv.child_flex_value_high
-- Commented by Quanam SR 10-04-2013 RFC#1140830
-- and ffv.parent_flex_value between :P_PARENT_FROM and :P_PARENT_TO
-- and ffv.parent_flex_value like '03%'
-- Added by Quanam SR 10-04-2013 RFC#1140830
AND ffv.parent_flex_value IN (
SELECT lookup_code
FROM fnd_lookup_values_vl flv
WHERE ( NVL ('', territory_code) = territory_code
OR territory_code IS NULL
)
AND lookup_type = 'EMR AP PARENT FLEX VALUE LAM'
AND (lookup_type LIKE 'EMR AP PARENT FLEX VALUE LAM')
AND (view_application_id = 3)
AND (security_group_id = 0)
AND NVL (enabled_flag, 'Y') = 'Y'
AND flv.meaning BETWEEN p_parent_from AND p_parent_to)
--
AND b.summary_flag = 'N'
AND b.enabled_flag = 'Y';
CURSOR c_segment_description2 (p_value IN VARCHAR2, p_value_set_id IN NUMBER)
IS
SELECT t.description, ffv.parent_flex_value
FROM fnd_flex_values_tl t,
fnd_flex_values b,
fnd_flex_value_norm_hierarchy ffv
WHERE b.flex_value_id = t.flex_value_id
AND t.LANGUAGE = USERENV ('LANG')
AND b.flex_value = p_value
AND b.flex_value_set_id = p_value_set_id
AND ffv.flex_value_set_id = p_value_set_id
AND p_value BETWEEN ffv.child_flex_value_low
AND ffv.child_flex_value_high
--and ffv.parent_flex_value Like '2%'
AND ffv.parent_flex_value BETWEEN :p_parent_from AND :p_parent_to
AND ffv.parent_flex_value NOT LIKE '2ZZ%'
AND b.summary_flag = 'N'
AND b.enabled_flag = 'Y';
CURSOR c_group_description (p_value IN VARCHAR2, p_value_set_id IN NUMBER)
IS
SELECT t.description
FROM fnd_flex_values_tl t, fnd_flex_values b
WHERE b.flex_value_id = t.flex_value_id
AND t.LANGUAGE = USERENV ('LANG')
AND b.flex_value = p_value
AND b.flex_value_set_id = p_value_set_id;
BEGIN
BEGIN
/*--12-AUG-2011 LAM Support / AMalatesta v8.0 added group_by in the query to table fnd_id_flex_segments--*/
SELECT /*+ index_asc ( fifs fnd_id_flex_segments_u2) */
fifs.flex_value_set_id
INTO v_value_set_id
FROM fnd_id_flex_segments fifs
WHERE fifs.segment_name = 'XXGL_BUSINESS_UNIT'
AND fifs.id_flex_code = 'GL#'
GROUP BY fifs.flex_value_set_id;
EXCEPTION
WHEN OTHERS
THEN
srw.MESSAGE ('1',
'OTHERS for search in fnd_id_flex_segments: '
|| SUBSTR (SQLERRM, 1, 100)
);
END;
-- Added by Quanam SR 10-04-2013 RFC#1140830
BEGIN
SELECT meaning
INTO v_chr_parent_from
FROM fnd_lookup_values_vl
WHERE ( NVL ('', territory_code) = territory_code
OR territory_code IS NULL
)
AND lookup_type = 'EMR AP PARENT FLEX VALUE LAM'
AND (lookup_type LIKE 'EMR AP PARENT FLEX VALUE LAM')
AND (view_application_id = 3)
AND (security_group_id = 0)
AND NVL (enabled_flag, 'Y') = 'Y'
AND lookup_code = :p_parent_from;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
srw.MESSAGE
('18',
'NO_DATA_FOUND for search in PARENT FLEX VALUE FROM: '
|| SUBSTR (SQLERRM, 1, 100)
);
WHEN OTHERS
THEN
srw.MESSAGE ('19',
'OTHERS for search in PARENT FLEX VALUE FROM: '
|| SUBSTR (SQLERRM, 1, 100)
);
END;
BEGIN
SELECT meaning
INTO v_chr_parent_to
FROM fnd_lookup_values_vl
WHERE ( NVL ('', territory_code) = territory_code
OR territory_code IS NULL
)
AND lookup_type = 'EMR AP PARENT FLEX VALUE LAM'
AND (lookup_type LIKE 'EMR AP PARENT FLEX VALUE LAM')
AND (view_application_id = 3)
AND (security_group_id = 0)
AND NVL (enabled_flag, 'Y') = 'Y'
AND lookup_code = :p_parent_to;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
srw.MESSAGE ('19',
'NO_DATA_FOUND for search in PARENT FLEX VALUE TO: '
|| SUBSTR (SQLERRM, 1, 100)
);
WHEN OTHERS
THEN
srw.MESSAGE ('19',
'OTHERS for search in PARENT FLEX VALUE TO: '
|| SUBSTR (SQLERRM, 1, 100)
);
END;
srw.MESSAGE ('20', 'v_chr_parent_from : ' || v_chr_parent_from);
srw.MESSAGE ('20', 'v_chr_parent_to : ' || v_chr_parent_to);
--
FOR r_dates IN c_dates
LOOP
FOR r_distributions IN c_distributions (r_dates.start_date,
r_dates.end_date,
v_value_set_id
)
LOOP
v_found := 0;
v_dist_split := 0;
v_sum_dist_amount := 0;
v_valid_inv := 0;
v_amount_remaining := 0;
v_segment_desc := NULL;
v_group_desc := NULL;
/*
FOR r_dist_split in c_dist_split(r_distributions.invoice_id
,r_distributions.line_type_lookup_code)
LOOP
IF r_dist_split.segment1 = r_distributions.segment1
THEN
v_found:=1;
ELSIF r_dist_split.sum_dist_amount !=0 AND r_dist_split.segment1 != r_distributions.segment1
THEN
v_dist_split:=1;
END IF;
END LOOP;*/
BEGIN
OPEN c_dist_split2 (r_distributions.invoice_id,
r_distributions.line_type_lookup_code,
r_distributions.segment1
);
FETCH c_dist_split2
INTO v_found, v_sum_dist_amount;
CLOSE c_dist_split2;
EXCEPTION
WHEN OTHERS
THEN
v_found := 0;
v_sum_dist_amount := 0;
END;
IF NVL (v_sum_dist_amount, 0) != 0
THEN
v_dist_split := 1;
END IF;
IF NVL (v_found, 0) = 0
THEN
IF :p_num_currency_func = 'ARS'
THEN
BEGIN
OPEN c_segment_description (r_distributions.segment1,
v_value_set_id,
-- Added by Quanam SR 10-04-2013 RFC#1140830
v_chr_parent_from,
v_chr_parent_to
--
);
FETCH c_segment_description
INTO v_segment_desc, v_parent_flex_value;
CLOSE c_segment_description;
EXCEPTION
WHEN OTHERS
THEN
v_segment_desc := NULL;
v_parent_flex_value := NULL;
END;
ELSE
BEGIN
OPEN c_segment_description2 (r_distributions.segment1,
v_value_set_id
);
FETCH c_segment_description2
INTO v_segment_desc, v_parent_flex_value;
CLOSE c_segment_description2;
EXCEPTION
WHEN OTHERS
THEN
v_segment_desc := NULL;
v_parent_flex_value := NULL;
END;
END IF;
BEGIN
OPEN c_group_description
(v_parent_flex_value --r_distributions.parent_flex_value
,
v_value_set_id
);
FETCH c_group_description
INTO v_group_desc;
CLOSE c_group_description;
EXCEPTION
WHEN OTHERS
THEN
v_group_desc := NULL;
END;
IF v_segment_desc IS NOT NULL AND v_group_desc IS NOT NULL
THEN
FOR r_invoices IN c_invoices (r_distributions.invoice_id)
LOOP
v_found := 0;
v_valid_inv := 0;
BEGIN
OPEN c_payment (r_distributions.invoice_id);
FETCH c_payment
INTO v_due_date, v_amount_remaining;
CLOSE c_payment;
EXCEPTION
WHEN OTHERS
THEN
v_due_date := NULL;
v_amount_remaining := NULL;
END;
IF ( r_invoices.cancelled_date IS NULL
OR ( r_invoices.cancelled_date IS NOT NULL
AND r_invoices.cancelled_date > :p_fecha_hasta
)
)
THEN
IF NVL (v_amount_remaining, 0) != 0
THEN
v_valid_inv := 1;
END IF;
IF v_valid_inv !=
1
--and r_invoices.cancelled_date > :p_fecha_hasta
THEN
BEGIN
OPEN c_valid_inv (r_distributions.invoice_id);
FETCH c_valid_inv
INTO v_valid_inv;
CLOSE c_valid_inv;
EXCEPTION
WHEN OTHERS
THEN
v_valid_inv := 0;
END;
END IF;
IF NVL (v_valid_inv, 0) != 1
THEN
BEGIN
OPEN c_valid_inv2 (r_distributions.invoice_id);
FETCH c_valid_inv2
INTO v_valid_inv;
CLOSE c_valid_inv2;
EXCEPTION
WHEN OTHERS
THEN
v_valid_inv := 0;
END;
END IF;
END IF;
IF NVL (v_valid_inv, 0) = 1
THEN
IF r_invoices.invoice_currency_code <>
:p_num_currency_func
THEN
BEGIN
SELECT COUNT (1)
INTO v_found
FROM xxap_inv_currency_aging_tmp
WHERE invoice_currency_code =
r_invoices.invoice_currency_code;
EXCEPTION
WHEN OTHERS
THEN
srw.MESSAGE
('1',
'OTHERS for search in xxap_inv_currency_aging_all with invoice_currency_code: '
|| r_invoices.invoice_currency_code
|| ', '
|| SUBSTR (SQLERRM, 1, 100)
);
END;
IF v_found = 0
THEN
BEGIN
SELECT ROUND (DECODE (r.eop_rate,
0, 0,
(1 / r.eop_rate)
),
5
)
INTO v_exch_rate_reexp
FROM gl_period_statuses gp,
fnd_application fa,
gl_translation_rates r
WHERE gp.set_of_books_id = :p_sob_id
AND gp.application_id = fa.application_id
AND application_short_name = 'SQLGL'
AND r.set_of_books_id = gp.set_of_books_id
AND r.period_name = gp.period_name
AND r.to_currency_code =
r_invoices.invoice_currency_code
AND gp.end_date =
(SELECT MAX (gp.end_date)
FROM gl_period_statuses gp,
fnd_application fa,
gl_translation_rates r
WHERE gp.set_of_books_id = :p_sob_id
AND gp.application_id =
fa.application_id
AND application_short_name =
'SQLGL'
AND r.set_of_books_id =
gp.set_of_books_id
AND r.period_name = gp.period_name
AND r.to_currency_code =
r_invoices.invoice_currency_code
AND gp.end_date <= :p_fecha_hasta);
EXCEPTION
WHEN OTHERS
THEN
srw.MESSAGE
('1',
'OTHERS for search in gl_period_statuses with invoice_currency_code: '
|| r_invoices.invoice_currency_code
|| ', '
|| SUBSTR (SQLERRM, 1, 100)
);
END;
BEGIN
INSERT INTO xxap_inv_currency_aging_tmp
(invoice_currency_code,
rate
)
VALUES (r_invoices.invoice_currency_code,
v_exch_rate_reexp
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
srw.MESSAGE
('1',
'OTHERS for insert into xxap_inv_currency_aging_all with invoice_currency_code: '
|| r_invoices.invoice_currency_code
|| ', '
|| SUBSTR (SQLERRM, 1, 100)
);
END;
END IF;
END IF;
BEGIN
INSERT INTO xxap_invoice_aging_all
(invoice_id,
vendor_id,
invoice_num,
invoice_currency_code,
payment_currency_code,
payment_cross_rate,
vendor_site_id,
amount_paid,
invoice_date,
invoice_type_lookup_code,
accts_pay_code_combination_id,
exchange_rate,
cancelled_date,
org_id, gl_date,
line_type_lookup_code,
segment1,
segment_description, segment_group,
group_description, sum_dist_amount,
payment_status_flag,
distribution_split, terms_id,
due_date, request_id,
created_by, creation_date
)
VALUES (r_invoices.invoice_id,
r_invoices.vendor_id,
r_invoices.invoice_num,
r_invoices.invoice_currency_code,
r_invoices.payment_currency_code,
r_invoices.payment_cross_rate,
r_invoices.vendor_site_id,
r_invoices.amount_paid,
r_invoices.invoice_date,
r_invoices.invoice_type_lookup_code,
r_invoices.accts_pay_code_combination_id,
r_invoices.exchange_rate,
r_invoices.cancelled_date,
r_invoices.org_id, r_invoices.gl_date,
r_distributions.line_type_lookup_code,
r_distributions.segment1,
v_segment_desc, v_parent_flex_value,
--r_distributions.parent_flex_value,
v_group_desc, r_distributions.amount,
r_invoices.payment_status_flag,
v_dist_split, r_invoices.terms_id,
v_due_date, :cp_concurrent_id,
:cp_user_id, SYSDATE
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
srw.MESSAGE
('1',
'OTHERS for insert into xxap_invoice_aging_all with invoice_id: '
|| TO_CHAR (r_distributions.invoice_id)
|| ', '
|| SUBSTR (SQLERRM, 1, 100)
);
END;
IF v_dist_split != 0
THEN
BEGIN
UPDATE xxap_invoice_aging_all
SET distribution_split = v_dist_split,
last_update_date = SYSDATE,
last_updated_by = :cp_user_id
WHERE invoice_id = r_distributions.invoice_id
AND line_type_lookup_code =
r_distributions.line_type_lookup_code
AND segment1 != r_distributions.segment1
AND sum_dist_amount != 0
AND request_id = :cp_concurrent_id
AND org_id = :cp_org_id;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
srw.MESSAGE
('1',
'OTHERS for update xxap_invoice_aging_all with invoice_id: '
|| TO_CHAR (r_distributions.invoice_id)
|| ', '
|| SUBSTR (SQLERRM, 1, 100)
);
END;
END IF;
END IF;
END LOOP;
END IF;
ELSE
BEGIN
UPDATE xxap_invoice_aging_all
SET sum_dist_amount =
(sum_dist_amount + r_distributions.amount
),
distribution_split = v_dist_split,
last_update_date = SYSDATE,
last_updated_by = :cp_user_id
WHERE invoice_id = r_distributions.invoice_id
AND line_type_lookup_code =
r_distributions.line_type_lookup_code
AND segment1 = r_distributions.segment1
AND request_id = :cp_concurrent_id
AND org_id = :cp_org_id;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
srw.MESSAGE
('1',
'OTHERS for update xxap_invoice_aging_all with invoice_id: '
|| TO_CHAR (r_distributions.invoice_id)
|| ', '
|| SUBSTR (SQLERRM, 1, 100)
);
END;
BEGIN
UPDATE xxap_invoice_aging_all
SET distribution_split = v_dist_split,
last_update_date = SYSDATE,
last_updated_by = :cp_user_id
WHERE invoice_id = r_distributions.invoice_id
AND line_type_lookup_code =
r_distributions.line_type_lookup_code
AND segment1 != r_distributions.segment1
AND sum_dist_amount != 0
AND request_id = :cp_concurrent_id
AND org_id = :cp_org_id;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
srw.MESSAGE
('1',
'OTHERS for update xxap_invoice_aging_all with invoice_id: '
|| TO_CHAR (r_distributions.invoice_id)
|| ', '
|| SUBSTR (SQLERRM, 1, 100)
);
END;
END IF;
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
srw.MESSAGE (1,
'Error en CREATE_TEM_TABLE_INVOICES: '
|| SUBSTR (SQLERRM, 1, 100)
);
END;
|
|
|
|
|
|
|
|
|
|
| Re: Query taking more time [message #603816 is a reply to message #603813] |
Thu, 19 December 2013 09:23 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
nishant87 wrote on Thu, 19 December 2013 15:17Cookiemaster:
you mean to say that we need to remove the OR condition.As i don't see that if we remove OR then something else fit there
No you need to add a OR.
One of the selects has a combined exists/not exists clause, the other doesn't.
So to combine the two you need to add OR so that the exists/not exists is only applied when appropriate (i.e. depending on the sole other difference between the two).
|
|
|
|
| Re: Query taking more time [message #603818 is a reply to message #603815] |
Thu, 19 December 2013 09:24 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
nishant87 wrote on Thu, 19 December 2013 15:20Yesterday i have posted the explain plan and found that the subquery which is
SELECT ai.invoice_id
FROM ap_invoices ai
WHERE NVL (NULL, ai.vendor_id) = ai.vendor_id
AND NVL (NULL, ai.invoice_type_lookup_code) =
ai.invoice_type_lookup_code
AND ( ai.cancelled_date IS NULL
OR ( ai.cancelled_date IS NOT NULL
AND ai.cancelled_date > '22-Nov-2013'
)
))
is taking more time in retriving data when i ran seperately
And we commented on that at the time, so why bring it up again?
|
|
|
|
|
|
|
|
|
|
| Re: Query taking more time [message #603833 is a reply to message #603831] |
Thu, 19 December 2013 12:36 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Your refusal to format your code is downright insulting. Has it ever occurred to you that if you consistently annoy the people with whom you work you are unlikely to progress very far?
Goodbye.
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 20:10:41 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
