Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

query tuning

KPRMar 31 2011 — edited Mar 31 2011
Hi folks,

I have an original query. Please find below...I tried upto some extent to modify this query. but this is no change in execution time. Please help me..or advise me..!!

original query
**********
SELECT
NVL (NVL (DECODE (anz.account_name, NULL, order1.account_name,anz.account_name),
pmt.account_name
),Customers.account_name) as account_name,
NVL (NVL (DECODE (anz.account_number, NULL, order1.account_number,anz.account_number),
pmt.account_number
),Customers.account_number) as account_number,
NVL (NVL (DECODE (anz.overall_credit_limit,
NULL, order1.overall_credit_limit,anz.overall_credit_limit
),
pmt.overall_credit_limit
),Customers.overall_credit_limit) as overall_credit_limit,
order1.wip_release, order1.wip_hold, anz."Open AR - Not due" as Open_AR_Not_due,
anz."Open AR - 30 days" as Open_AR_30_days, anz."Open AR - 60 days" as Open_AR_60_days,
anz."Open AR - 90 days" as OpenAR_90_days, anz."Open AR - 120 days" AS Open_AR_120_days,
anz."Open AR - 180 days" AS Open_AR_180_days, anz."Open AR > 180 days" AS Open_AR_GREATER_180_days,
pmt.payment_unapplied, pmt.payment_applied, pmt.on_account,
( case when anz.account_name is not null and order1.account_name is not null and pmt.account_name is not null
then Customers.overall_credit_limit - anz.credit_limit_used - order1.credit_limit_used + pmt.payment_unapplied
when anz.account_name is not null and order1.account_name is not null and pmt.account_name is null
then Customers.overall_credit_limit - anz.credit_limit_used - order1.credit_limit_used
when anz.account_name is not null and order1.account_name is null and pmt.account_name is not null
then Customers.overall_credit_limit - anz.credit_limit_used + pmt.payment_unapplied
when anz.account_name is not null and order1.account_name is null and pmt.account_name is null
then Customers.overall_credit_limit - anz.credit_limit_used
when anz.account_name is null and order1.account_name is not null and pmt.account_name is not null
then Customers.overall_credit_limit- order1.credit_limit_used + pmt.payment_unapplied
when anz.account_name is null and order1.account_name is not null and pmt.account_name is null
then Customers.overall_credit_limit - order1.credit_limit_used
when anz.account_name is null and order1.account_name is null and pmt.account_name is not null
then Customers.overall_credit_limit + pmt.payment_unapplied
else Customers.overall_credit_limit
end
) as credit_limit_balance,

NVL (NVL (DECODE (anz."payment term", NULL, order1."payment term",anz."payment term"),
pmt."payment term"
),Customers."payment term") as payment_term,
NVL (NVL (DECODE (anz."sales channel", NULL, order1."sales channel",anz."sales channel"),
pmt."sales channel"
),Customers."sales channel") as sales_channel,
NVL (NVL (DECODE (anz."Credit Finance Group Code",
NULL, order1."Credit Finance Group Code",anz."Credit Finance Group Code"
),
pmt."Credit Finance Group Code"
),Customers."Credit Finance Group Code") as Credit_Finance_Group_Code,
NVL (NVL (DECODE (anz.orig_system_reference,
NULL, order1.orig_system_reference,anz.orig_system_reference
),
pmt.orig_system_reference
),Customers.orig_system_reference) AS smarts_number,
Customers."Status" as Status,Customers."Credit Hold" as Credit_hold,Customers.attribute2 as Country_code,Customers.tax_reference as Tax_Reg_No,Customers.attribute3 as Sub_Channel
FROM

(
SELECT flv.attribute2,hca.cust_account_id, hca.account_number, hca.account_name,hca.STATUS "Status", hcp.CREDIT_HOLD "Credit Hold",
hcpa.overall_credit_limit, rt.NAME "payment term",
hca.sales_channel_code "sales channel",
hcp.attribute15 "Credit Finance Group Code",
hca.orig_system_reference,
hcsu.tax_reference,
flv_sal.attribute3
FROM ar.hz_customer_profiles hcp,
ar.hz_cust_profile_amts hcpa,
ar.hz_cust_accounts hca,
ar.ra_terms_tl rt,
applsys.fnd_lookup_values flv,
ar.hz_cust_acct_sites_all hcas,
ar.hz_cust_site_uses_all hcsu,
applsys.fnd_lookup_values flv_sal
WHERE hca.cust_account_id = hcp.cust_account_id
AND hca.cust_account_id = hcpa.cust_account_id
AND hcpa.currency_code = SUBSTR (hcp.attribute5, 1, 3)
AND hcp.standard_terms(+) = rt.term_id
AND hcp.attribute15 IS NOT NULL
AND hcpa.site_use_id IS NULL
AND hcp.site_use_id IS NULL
AND substr(hca.account_number,1,2) = flv.meaning
AND flv.lookup_type = 'DELL_CUSTPREFIX_COUNTRYCODE'
AND flv.attribute2 In ('CN') --Enter the country code here
AND hca.cust_account_id = hcas.cust_account_id
and hcas.cust_acct_site_id = hcsu.cust_acct_site_id
and hcsu.site_use_code ='BILL_TO'
and hcsu.status ='A'
and hca.sales_channel_code = flv_sal.lookup_code
and flv_sal.lookup_type = 'SALES_CHANNEL'
) Customers
LEFT OUTER JOIN
(SELECT hca.account_name, hca.cust_account_id,
( ( SUM
(NVL ((CASE
WHEN aps.due_date > TRUNC(SYSDATE)
AND aps.CLASS <> 'PMT'
THEN amount_due_remaining
END
),
0
)
) +
SUM
(NVL
((CASE
WHEN aps.due_date BETWEEN TRUNC(SYSDATE - 30) AND TRUNC(SYSDATE)
AND aps.CLASS <> 'PMT'
THEN amount_due_remaining
END
),
0
)
) +
SUM
(NVL
((CASE
WHEN aps.due_date BETWEEN TRUNC(SYSDATE - 60)
AND TRUNC(SYSDATE - 31)
AND aps.CLASS <> 'PMT'
THEN amount_due_remaining
END
),
0
)
) +
SUM
(NVL
((CASE
WHEN aps.due_date BETWEEN TRUNC(SYSDATE - 90)
AND TRUNC(SYSDATE - 61)
AND aps.CLASS <> 'PMT'
THEN amount_due_remaining
END
),
0
)
) +
SUM
(NVL
((CASE
WHEN aps.due_date BETWEEN TRUNC(SYSDATE - 120)
AND TRUNC(SYSDATE - 91)
AND aps.CLASS <> 'PMT'
THEN amount_due_remaining
END
),
0
)
) +
SUM
(NVL
((CASE
WHEN aps.due_date BETWEEN TRUNC(SYSDATE - 180)
AND TRUNC(SYSDATE - 121)
AND aps.CLASS <> 'PMT'
THEN amount_due_remaining
END
),
0
)
) +
SUM
(NVL ((CASE
WHEN aps.due_date < TRUNC(SYSDATE - 180)
AND aps.CLASS <> 'PMT'
THEN amount_due_remaining
END
),
0
)
)
)
) credit_limit_used,
hcpa.overall_credit_limit,
SUM
(NVL ((CASE
WHEN aps.due_date > TRUNC(SYSDATE)
AND aps.CLASS <> 'PMT'
THEN amount_due_remaining
END
),
0
)
) "Open AR - Not due",
SUM
(NVL
((CASE
WHEN aps.due_date BETWEEN TRUNC(SYSDATE - 30) AND TRUNC(SYSDATE)
AND aps.CLASS <> 'PMT'
THEN amount_due_remaining
END
),
0
)
) "Open AR - 30 days",
SUM
(NVL
((CASE
WHEN aps.due_date BETWEEN TRUNC(SYSDATE - 60)
AND TRUNC(SYSDATE - 31)
AND aps.CLASS <> 'PMT'
THEN amount_due_remaining
END
),
0
)
) "Open AR - 60 days",
SUM
(NVL
((CASE
WHEN aps.due_date BETWEEN TRUNC(SYSDATE - 90)
AND TRUNC(SYSDATE - 61)
AND aps.CLASS <> 'PMT'
THEN amount_due_remaining
END
),
0
)
) "Open AR - 90 days",
SUM
(NVL
((CASE
WHEN aps.due_date BETWEEN TRUNC(SYSDATE - 120)
AND TRUNC(SYSDATE - 91)
AND aps.CLASS <> 'PMT'
THEN amount_due_remaining
END
),
0
)
) "Open AR - 120 days",
SUM
(NVL
((CASE
WHEN aps.due_date BETWEEN TRUNC(SYSDATE - 180)
AND TRUNC(SYSDATE - 121)
AND aps.CLASS <> 'PMT'
THEN amount_due_remaining
END
),
0
)
) "Open AR - 180 days",
SUM
(NVL ((CASE
WHEN aps.due_date < TRUNC(SYSDATE - 180)
AND aps.CLASS <> 'PMT'
THEN amount_due_remaining
END
),
0
)
) "Open AR > 180 days",
rt.NAME "payment term",
hca.sales_channel_code "sales channel",
hcp.attribute15 "Credit Finance Group Code",
hca.account_number, hca.orig_system_reference
FROM ar.ar_payment_schedules_all aps,
ar.hz_cust_accounts hca,
ar.hz_cust_profile_amts hcpa,
ar.hz_customer_profiles hcp,
ar.ra_terms_tl rt,
hr.hr_all_organization_units hao,
applsys.fnd_lookup_values flv
WHERE aps.customer_id = hca.cust_account_id
AND aps.org_id = hao.organization_id
AND aps.status = 'OP'
AND hca.cust_account_id = hcpa.cust_account_id
AND hcpa.currency_code = SUBSTR (hcp.attribute5, 1, 3)
AND hcp.standard_terms(+) = rt.term_id
AND hcp.cust_account_id = hca.cust_account_id
AND hcp.attribute15 IS NOT NULL
AND hcp.site_use_id IS NULL
AND hcpa.site_use_id IS NULL
AND hao.attribute2 = flv.attribute2
AND flv.lookup_type = 'DELL_CUSTPREFIX_COUNTRYCODE'
AND flv.attribute2 In ('CN') --Enter the country code here
GROUP BY hcpa.overall_credit_limit,
rt.NAME,
hca.sales_channel_code,
hcp.attribute15,
hca.account_number,
hca.cust_account_id,
hca.account_name,
hca.orig_system_reference) anz
ON CUSTOMERS.CUST_ACCOUNT_ID = ANZ.CUST_ACCOUNT_ID
LEFT OUTER JOIN
(SELECT abc.sold_to_org_id, abc.account_number, abc.account_name,
abc.overall_credit_limit, abc."payment term",
abc."sales channel", abc."Credit Finance Group Code",
abc.orig_system_reference,
SUM (abc.wip_release) AS wip_release,
SUM (abc.wip_hold) AS wip_hold,
( SUM (nvl(abc.wip_release,0))
+ SUM (nvl(abc.wip_hold,0))) credit_limit_used

FROM (SELECT ooh.sold_to_org_id, ooh.order_number,
hca.account_number, hca.account_name,
hcpa.overall_credit_limit, rt.NAME "payment term",
hca.sales_channel_code "sales channel",
hcp.attribute15 "Credit Finance Group Code",
hca.orig_system_reference,
(NVL (DECODE (NVL (hold.released_flag, 'Y'),
'Y', SUM ( ool.unit_selling_price
* ool.ordered_quantity
+ ool.tax_value
)
),
0
)
) wip_release,
(NVL (DECODE (NVL (hold.released_flag, 'Y'),
'N', SUM ( ool.unit_selling_price
* ool.ordered_quantity
+ ool.tax_value
)
),
0
)
) wip_hold
FROM ont.oe_order_headers_all ooh,
ont.oe_order_lines_all ool,
ont.oe_order_holds_all hold,
ar.hz_cust_accounts hca,
ar.hz_customer_profiles hcp,
ar.hz_cust_profile_amts hcpa,
ar.ra_terms_tl rt,
applsys.fnd_lookup_values flv
WHERE ooh.cancelled_flag = 'N'
AND ooh.open_flag = 'Y'
AND ooh.header_id = ool.header_id
AND ooh.header_id = hold.header_id(+)
AND ooh.sold_to_org_id = hca.cust_account_id
AND hca.cust_account_id = hcp.cust_account_id
AND substr(hca.account_number,1,2) = flv.meaning
AND flv.lookup_type = 'DELL_CUSTPREFIX_COUNTRYCODE'
AND flv.attribute2 In ('CN') --Enter the country code here
AND hcp.standard_terms(+) = rt.term_id
AND hca.cust_account_id = hcpa.cust_account_id
AND hcpa.currency_code = SUBSTR (hcp.attribute5, 1, 3)
AND hcp.site_use_id IS NULL
AND hcp.attribute15 IS NOT NULL
AND hcpa.site_use_id IS NULL
GROUP BY ooh.sold_to_org_id,
ooh.order_number,
hold.released_flag,
hca.account_number,
hca.account_name,
hcpa.overall_credit_limit,
rt.NAME,
hca.sales_channel_code,
hcp.attribute15,
hca.orig_system_reference) abc
GROUP BY abc.sold_to_org_id,
abc.account_number,
abc.account_name,
abc.overall_credit_limit,
abc."payment term",
abc."sales channel",
abc."Credit Finance Group Code",
abc.orig_system_reference) order1
ON CUSTOMERS.CUST_ACCOUNT_ID = order1.sold_to_org_id
LEFT OUTER JOIN
(SELECT aps.customer_id, hca.account_number, hca.account_name,
hcpa.overall_credit_limit, rt.NAME "payment term",
hca.sales_channel_code "sales channel",
hcp.attribute15 "Credit Finance Group Code",
hca.orig_system_reference,
SUM (NVL (DECODE (ara.status, 'UNAPP', ara.amount_applied),
0)
) as payment_unapplied,
SUM (NVL (DECODE (ara.status, 'APP', ara.amount_applied), 0)
) as payment_applied,
SUM (NVL (DECODE (ara.status, 'ACC', ara.amount_applied), 0)
) as on_account
FROM ar.ar_payment_schedules_all aps,
ar.ar_cash_receipts_all acr,
ar.ar_receivable_applications_all ara,
ar.hz_cust_accounts hca,
ar.hz_customer_profiles hcp,
ar.hz_cust_profile_amts hcpa,
ar.ra_terms_tl rt,
applsys.fnd_lookup_values flv,
hr.hr_all_organization_units hao
WHERE aps.CLASS = 'PMT'
AND aps.status = 'OP'
AND aps.org_id =hao.organization_id
AND aps.cash_receipt_id = acr.cash_receipt_id
AND acr.cash_receipt_id = ara.cash_receipt_id
AND aps.customer_id = hca.cust_account_id
AND hca.cust_account_id = hcp.cust_account_id
AND hca.cust_account_id = hcpa.cust_account_id
AND hcpa.currency_code = SUBSTR (hcp.attribute5, 1, 3)
AND hcp.standard_terms(+) = rt.term_id
AND hcp.attribute15 IS NOT NULL
AND hcpa.site_use_id IS NULL
AND hcp.site_use_id IS NULL
AND hao.attribute2 = flv.attribute2
AND flv.lookup_type = 'DELL_CUSTPREFIX_COUNTRYCODE'
AND flv.attribute2 In ('CN') --Enter the country code here

GROUP BY aps.customer_id,
hca.account_number,
hca.account_name,
hcpa.overall_credit_limit,
rt.NAME,
hca.sales_channel_code,
hcp.attribute15,
hca.orig_system_reference) pmt
ON Customers.cust_account_id = pmt.customer_id

I have made some changes in this query..will send you by another mail.because if I post both..getting message like "your messag exceeds the max.lendght of 30K chars. Please try to understand and give me positive reply.

regards
P
This post has been answered by Mahir M. Quluzade on Mar 31 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 28 2011
Added on Mar 31 2011
2 comments
763 views