ECC Order Management, Credit Check Hold Details

select
x.*
from
(
SELECT * FROM
                                                    (
                                                        SELECT
                                                            oech.header_id|| ' - '|| oech.line_id ecc_spec_id,
                                                            oech.header_id,
                                                            oech.line_id,
                                                            oech.credit_check_rule_id,
                                                            oech.hold_id,
                                                            oech.hold_comment,
                                                            oech.party_id,
                                                            oech.bill_to_customer_id,
                                                            oech.bill_to_site_id,
                                                            oech.operating_unit,
                                                            oech.cc_released_flag,
                                                            oech.cc_global_exposure_flag,
                                                            oech.order_currency,
                                                            curr.name credit_limit_currency,
                                                            oech.order_limit,
                                                            oech.credit_currency_order_amount,
                                                            (
                                                                    SELECT
                                                                        st.meaning
                                                                    FROM
                                                                        fnd_lookup_values st
                                                                    WHERE
                                                                            st.lookup_code = oech.overdue_invoice_exists
                                                                        AND st.language = curr.language
                                                                        AND st.lookup_type = 'YES_NO'
                                                                        AND st.view_application_id = 660
                                                                        AND st.security_group_id = 0
                                                                ) overdue_invoice_exists,
                                                            oech.order_number,
                                                            oech.order_amount,
                                                            oech.org_id,
                                                            oech.external_exposure,
                                                            oech.credit_limit_level,
                                                            oech.credit_limit_level_id,
                                                            oech.credit_limit_level_name,
                                                            curr.language     language_code,
							    decode(oech.overdue_invoice_exists,'Y','false','true') HIDE_OVERDUE_INVOICE_ALERT,  -- Bug 36326419
							    CASE
							    	when oech.order_limit IS NULL then 'true'
								when nvl(oech.credit_currency_order_amount,0)>nvl(oech.order_limit,0) then 'false'
								else 'true'
							    END HIDE_ORDER_LIMIT_ALERT  -- Bug 36326419
                                                        FROM
                                                            oe_ecc_credit_hold_details   oech,
                                                            fnd_currencies_tl curr
                                                        WHERE
                                                                oech.cc_support_flag = 'Y'
                                                            AND oech.credit_limit_currency = curr.currency_code AND 1=1  AND curr.language IN ( 'US' )
                                                    ) PIVOT (
                                                        MAX ( credit_limit_currency ) AS credit_limit_currency,
                                                        MAX ( overdue_invoice_exists ) AS overdue_invoice_exists
                                                        FOR language_code IN ( 'US' "US"))
) x
where
2=2