SQL & PL/SQL

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