SQLize | PHPize | SQLtest

SELECT TRIM ( REPLACE ( REGEXP_REPLACE (x.c_sort_vendor_name, '[[:space:]]', ' '), '|', ' ')) c_sort_vendor_name, TRIM ( REPLACE ( REGEXP_REPLACE (x.c_master_vendor_name, '[[:space:]]', ' '), '|', ' ')) c_master_vendor_name, x.c_vendor_id c_vendor_id, x.dist_code_combination_id dist_code_combination_id, TRIM ( REPLACE (REGEXP_REPLACE (x.description, '[[:space:]]', ' '), '|', ' ')) description, x.c_matched_unit_price c_matched_unit_price, x.posted_flag posted_flag, x.c_original_dist_amount c_original_dist_amount, TRIM ( REPLACE ( REGEXP_REPLACE (x.c_hold_code_master, '[[:space:]]', ' '), '|', ' ')) c_hold_code_master, x.c_hold_date c_hold_date, TRIM ( REPLACE ( REGEXP_REPLACE (x.c_sort_nls_hold_code, '[[:space:]]', ' '), '|', ' ')) c_sort_nls_hold_code, TRIM ( REPLACE ( REGEXP_REPLACE (x.c_master_nls_hold_code, '[[:space:]]', ' '), '|', ' ')) c_master_nls_hold_code, TRIM ( REPLACE (REGEXP_REPLACE (x.c_hold_code, '[[:space:]]', ' '), '|', ' ')) c_hold_code, x.c_sort_month c_sort_month, x.c_month_name c_month_name, TRIM ( REPLACE ( REGEXP_REPLACE (x.c_m_sort_vendor_name, '[[:space:]]', ' '), '|', ' ')) c_m_sort_vendor_name, TRIM ( REPLACE ( REGEXP_REPLACE (x.c_detail_vendor_name, '[[:space:]]', ' '), '|', ' ')) c_detail_vendor_name, TRIM ( REPLACE ( REGEXP_REPLACE (x.c_detail_nls_hold_code, '[[:space:]]', ' '), '|', ' ')) c_detail_nls_hold_code, x.c_invoice_date c_invoice_date, TRIM ( REPLACE (REGEXP_REPLACE (x.c_batch_name, '[[:space:]]', ' '), '|', ' ')) c_batch_name, x.c_invoice_id c_invoice_id, TRIM ( REPLACE (REGEXP_REPLACE (x.c_invoice_num, '[[:space:]]', ' '), '|', ' ')) c_invoice_num, x.c_original_amount c_original_amount, x.c_amount_remaining c_amount_remaining, TRIM ( REPLACE (REGEXP_REPLACE (x.c_description, '[[:space:]]', ' '), '|', ' ')) c_description, x.creation_date creation_date, x.c_po_distribution_id c_po_distribution_id, x.c_po_line_location_id c_po_line_location_id, x.c_po_header_id c_po_header_id, TRIM ( REPLACE (REGEXP_REPLACE (x.c_po_number, '[[:space:]]', ' '), '|', ' ')) c_po_number, x.c_release_num c_release_num, x.c_po_line c_po_line, x.c_po_line_price c_po_line_price, x.c_po_shipment_no c_po_shipment_no, x.c_buyer c_buyer, x.c_requestor c_requestor, x.c_requestor_email_alias, x.c_buyer_email_alias, x.c_image_number c_image_number, TRIM ( REPLACE (REGEXP_REPLACE (x.c_paygroup, '[[:space:]]', ' '), '|', ' ')) c_paygroup, TRIM ( REPLACE (REGEXP_REPLACE (x.c_terms, '[[:space:]]', ' '), '|', ' ')) c_terms, TRIM ( REPLACE (REGEXP_REPLACE (x.c_pmt_method, '[[:space:]]', ' '), '|', ' ')) c_pmt_method, x.c_currency c_currency, x.c_due_date c_due_date, TRIM ( REPLACE (REGEXP_REPLACE (x.c_hold_reason, '[[:space:]]', ' '), '|', ' ')) c_hold_reason, TRIM ( REPLACE (REGEXP_REPLACE (x.c_org_name, '[[:space:]]', ' '), '|', ' ')) c_org_name, x.c_organization_id c_organization_id FROM ( SELECT DECODE ( :p_order_by, 'Hold Name', 'Do not sort by vendor name', DECODE ( :sort_by_alternate, 'Y', UPPER (aihd.vendor_name_alt), UPPER (aihd.vendor_name))) c_sort_vendor_name, aihd.vendor_name c_master_vendor_name, aihd.vendor_id c_vendor_id, aid.dist_code_combination_id dist_code_combination_id, aid.description description, aid.unit_price c_matched_unit_price, NVL (aid.posted_flag, 'N') posted_flag, DECODE (aihd.invoice_currency_code, :c_base_currency_code, aid.amount, aid.base_amount) c_original_dist_amount, aihd.hold_lookup_code c_hold_code_master, TRUNC (aihd.hold_date) c_hold_date, DECODE ( :p_order_by, 'Vendor Name', 'Do not sort by Hold Name', UPPER ( DECODE (aihd.hold_lookup_code, NULL, :c_nls_na, alc.displayed_field))) c_sort_nls_hold_code, DECODE (aihd.hold_lookup_code, NULL, :c_nls_na, alc.displayed_field) c_master_nls_hold_code, DECODE (aihd.hold_lookup_code, NULL, :c_nls_na, aihd.hold_lookup_code) c_hold_code, TO_CHAR (aihd.invoice_date, 'YYYYMM') c_sort_month, TO_CHAR (aihd.invoice_date, 'fmMonth YYYY') c_month_name, DECODE ( :p_order_by, 'Vendor Name', 'Do not sort by vendor name', DECODE ( :sort_by_alternate, 'Y', UPPER (aihd.vendor_name_alt), UPPER (aihd.vendor_name))) c_m_sort_vendor_name, aihd.vendor_name c_detail_vendor_name, DECODE (aihd.hold_lookup_code, NULL, :c_nls_na, alc.displayed_field) c_detail_nls_hold_code, aihd.invoice_date c_invoice_date, aihd.batch_name c_batch_name, aihd.invoice_id c_invoice_id, aihd.invoice_num c_invoice_num, DECODE (aihd.invoice_currency_code, :c_base_currency_code, aihd.invoice_amount, aihd.base_amount) c_original_amount, DECODE (aihd.invoice_currency_code, :c_base_currency_code, aihd.invoice_amount, aihd.base_amount) - DECODE ( aihd.invoice_currency_code, :c_base_currency_code, NVL (aihd.amount_paid, 0) + NVL ( aihd.discount_amount_taken, 0), DECODE ( f.minimum_accountable_unit, NULL, ROUND ( aihd.exchange_rate * NVL (aihd.amount_paid, 0), f.precision), ROUND ( ( aihd.exchange_rate * NVL (aihd.amount_paid, 0)) / f.minimum_accountable_unit) * f.minimum_accountable_unit) + DECODE ( f.minimum_accountable_unit, NULL, ROUND ( aihd.exchange_rate * NVL (aihd.discount_amount_taken, 0), f.precision), ROUND ( ( aihd.exchange_rate * NVL (aihd.discount_amount_taken, 0)) / f.minimum_accountable_unit) * f.minimum_accountable_unit)) c_amount_remaining, aihd.description c_description, aihd.creation_date creation_date, aid.po_distribution_id c_po_distribution_id, pod.line_location_id c_po_line_location_id, pod.po_header_id c_po_header_id, poh.segment1 c_po_number, por.release_num c_release_num, pol.line_num c_po_line, pol.unit_price c_po_line_price, poll.shipment_num c_po_shipment_no, pp1.full_name c_buyer, pp2.full_name c_requestor, SUBSTR (pp2.email_address, 1, INSTR (pp2.email_address, '@') - 1) c_requestor_email_alias, SUBSTR (pp1.email_address, 1, INSTR (pp1.email_address, '@') - 1) c_buyer_email_alias, aihd.attribute6 c_image_number, aihd.pay_group_lookup_code c_paygroup, t.name c_terms, aihd.payment_method_lookup_code c_pmt_method, aihd.invoice_currency_code c_currency, NVL (aihd.discount_date, aihd.due_date) c_due_date, aihd.hold_reason c_hold_reason, org.name c_org_name, org.organization_id c_organization_id FROM intg_user.amz_ap_invoice_hold_detail_gtt aihd, ap_holds_all aha, ap_invoice_lines_all aia,-- SHOULDN’T THIS BE AP_INVOICES_ALL ap_invoice_distributions_all aid, ap_lookup_codes alc, fnd_currencies_vl f, po_distributions_all pod, po_line_locations_all poll, po_lines_all pol, po_headers_all poh, po_releases_all por, per_all_people_f pp1, per_all_people_f pp2, ap_terms t, apps.hr_organization_units org WHERE aia.invoice_id(+) = aihd.invoice_id AND aHA.invoice_id = aihd.invoice_id AND aihd.invoice_id IN – IS THIS CONDITION CORRECT? (SELECT invoice_id FROM ap_invoice_lines_all) AND ( aha.line_location_id IS NULL OR aia.po_line_location_id = aha.line_location_id) AND aha.release_lookup_code IS NULL AND aid.invoice_line_number(+) = aia.line_number AND aid.invoice_id(+) = aia.invoice_id AND (aia.cancelled_flag IS NULL OR aia.cancelled_flag = 'N') AND (aia.discarded_flag IS NULL OR aia.discarded_flag = 'N') AND pod.po_distribution_id(+) = aid.po_distribution_id AND pod.line_location_id = poll.line_location_id(+) AND pod.po_line_id = pol.po_line_id(+) AND pod.po_header_id = poh.po_header_id(+) AND poll.po_release_id = por.po_release_id(+) AND poh.agent_id = pp1.person_id(+) AND pod.deliver_to_person_id = pp2.person_id(+) AND aihd.terms_id = t.term_id AND aihd.org_id = org.organization_id AND ( aihd.exchange_rate IS NOT NULL OR aihd.invoice_currency_code = :c_base_currency_code) AND NVL (pp1.effective_end_date, TO_DATE ('12/31/2047', 'MM/DD/YYYY')) > SYSDATE AND NVL (pp2.effective_end_date, TO_DATE ('12/31/2047', 'MM/DD/YYYY')) > SYSDATE AND UPPER (aihd.hold_lookup_code) NOT IN (SELECT UPPER (lookup_code) FROM ap_lookup_codes WHERE lookup_type = 'AMZ_HOLDS_NOT_INCLUDED' AND enabled_flag = 'Y' AND NVL (inactive_date, (SYSDATE + 1)) > SYSDATE) AND f.currency_code = :c_base_currency_code AND alc.lookup_type = 'HOLD CODE' AND alc.lookup_code = aihd.hold_lookup_code AND NVL (aid.posted_flag, 'N') = DECODE ( :p_posted_flag, 'Y', NVL (aid.posted_flag, 'N'), 'N') GROUP BY aihd.hold_lookup_code, TRUNC (aihd.hold_date), alc.displayed_field, aihd.vendor_name, DECODE ( :sort_by_alternate, 'Y', UPPER (aihd.vendor_name_alt), UPPER (aihd.vendor_name)), aihd.vendor_id, aihd.invoice_date, aihd.invoice_id, aihd.batch_name, aihd.invoice_num, DECODE (aihd.invoice_currency_code, :c_base_currency_code, aihd.invoice_amount, aihd.base_amount), DECODE (aihd.invoice_currency_code, :c_base_currency_code, aihd.invoice_amount, aihd.base_amount) - DECODE ( aihd.invoice_currency_code, :c_base_currency_code, NVL (aihd.amount_paid, 0) + NVL ( aihd.discount_amount_taken, 0), DECODE ( f.minimum_accountable_unit, NULL, ROUND ( aihd.exchange_rate * NVL (aihd.amount_paid, 0), f.precision), ROUND ( ( aihd.exchange_rate * NVL (aihd.amount_paid, 0)) / f.minimum_accountable_unit) * f.minimum_accountable_unit) + DECODE ( f.minimum_accountable_unit, NULL, ROUND ( aihd.exchange_rate * NVL (aihd.discount_amount_taken, 0), f.precision), ROUND ( ( aihd.exchange_rate * NVL (aihd.discount_amount_taken, 0)) / f.minimum_accountable_unit) * f.minimum_accountable_unit)), aihd.description, aihd.creation_date, aid.dist_code_combination_id, aid.description, aid.unit_price, aid.posted_flag, DECODE (aihd.invoice_currency_code, :c_base_currency_code, aid.amount, aid.base_amount), aid.po_distribution_id, pod.line_location_id, pod.po_header_id, poh.segment1, por.release_num, pol.line_num, pol.unit_price, poll.shipment_num, pp1.full_name, pp2.full_name, SUBSTR (pp2.email_address, 1, INSTR (pp2.email_address, '@') - 1), SUBSTR (pp1.email_address, 1, INSTR (pp1.email_address, '@') - 1), aihd.attribute6, aihd.pay_group_lookup_code, t.name, aihd.payment_method_lookup_code, aihd.invoice_currency_code, NVL (aihd.discount_date, aihd.due_date), aihd.hold_reason, org.name, org.organization_id UNION SELECT DECODE ( :p_order_by, 'Hold Name', 'Do not sort by vendor name', DECODE ( :sort_by_alternate, 'Y', UPPER (aihd.vendor_name_alt), UPPER (aihd.vendor_name))) c_sort_vendor_name, aihd.vendor_name c_master_vendor_name, aihd.vendor_id c_vendor_id, NULL dist_code_combination_id, NULL description, NULL c_matched_unit_price, NULL posted_flag, NULL c_original_dist_amount, aihd.hold_lookup_code c_hold_code_master, TRUNC (aihd.hold_date) c_hold_date, DECODE ( :p_order_by, 'Vendor Name', 'Do not sort by Hold Name', UPPER ( DECODE (aihd.hold_lookup_code, NULL, :c_nls_na, alc.displayed_field))) c_sort_nls_hold_code, DECODE (aihd.hold_lookup_code, NULL, :c_nls_na, alc.displayed_field) c_master_nls_hold_code, DECODE (aihd.hold_lookup_code, NULL, :c_nls_na, aihd.hold_lookup_code) c_hold_code, TO_CHAR (aihd.invoice_date, 'YYYYMM') c_sort_month, TO_CHAR (aihd.invoice_date, 'fmMonth YYYY') c_month_name, DECODE ( :p_order_by, 'Vendor Name', 'Do not sort by vendor name', DECODE ( :sort_by_alternate, 'Y', UPPER (aihd.vendor_name_alt), UPPER (aihd.vendor_name))) c_m_sort_vendor_name, aihd.vendor_name c_detail_vendor_name, DECODE (aihd.hold_lookup_code, NULL, :c_nls_na, alc.displayed_field) c_detail_nls_hold_code, aihd.invoice_date c_invoice_date, aihd.batch_name c_batch_name, aihd.invoice_id c_invoice_id, aihd.invoice_num c_invoice_num, DECODE (aihd.invoice_currency_code, :c_base_currency_code, aihd.invoice_amount, aihd.base_amount) c_original_amount, DECODE (aihd.invoice_currency_code, :c_base_currency_code, aihd.invoice_amount, aihd.base_amount) - DECODE ( aihd.invoice_currency_code, :c_base_currency_code, NVL (aihd.amount_paid, 0) + NVL ( aihd.discount_amount_taken, 0), DECODE ( f.minimum_accountable_unit, NULL, ROUND ( aihd.exchange_rate * NVL (aihd.amount_paid, 0), f.precision), ROUND ( ( aihd.exchange_rate * NVL (aihd.amount_paid, 0)) / f.minimum_accountable_unit) * f.minimum_accountable_unit) + DECODE ( f.minimum_accountable_unit, NULL, ROUND ( aihd.exchange_rate * NVL (aihd.discount_amount_taken, 0), f.precision), ROUND ( ( aihd.exchange_rate * NVL (aihd.discount_amount_taken, 0)) / f.minimum_accountable_unit) * f.minimum_accountable_unit)) c_amount_remaining, aihd.description c_description, aihd.creation_date creation_date, NULL c_po_distribution_id, pod.line_location_id c_po_line_location_id, pod.po_header_id c_po_header_id, poh.segment1 c_po_number, por.release_num c_release_num, pol.line_num c_po_line, pol.unit_price c_po_line_price, poll.shipment_num c_po_shipment_no, pp1.full_name c_buyer, pp2.full_name c_requestor, SUBSTR (pp2.email_address, 1, INSTR (pp2.email_address, '@') - 1) c_requestor_email_alias, SUBSTR (pp1.email_address, 1, INSTR (pp1.email_address, '@') - 1) c_buyer_email_alias, aihd.attribute6 c_image_number, aihd.pay_group_lookup_code c_paygroup, t.name c_terms, aihd.payment_method_lookup_code c_pmt_method, aihd.invoice_currency_code c_currency, NVL (aihd.discount_date, aihd.due_date) c_due_date, aihd.hold_reason c_hold_reason, org.name c_org_name, org.organization_id c_organization_id FROM intg_user.amz_ap_invoice_hold_detail_gtt aihd, ap_invoices_all aia, ap_holds_all aha, ap_lookup_codes alc, fnd_currencies_vl f, po_distributions_all pod, po_line_locations_all poll, po_lines_all pol, po_headers_all poh, po_releases_all por, per_all_people_f pp1, per_all_people_f pp2, ap_terms t, apps.hr_organization_units org WHERE aia.invoice_id = aihd.invoice_id AND ( aia.invoice_id NOT IN (SELECT invoice_id FROM apps.ap_invoice_lines_all) OR aia.invoice_id IN (SELECT aii.invoice_id FROM apps.ap_invoice_lines_all AII, ap_invoice_distributions_all AID WHERE ( aii.DISCARDED_FLAG = 'Y' OR aii.cancelled_flag = 'Y') AND (AID.INVOICE_ID(+) = AII.INVOICE_ID) AND NVL (aid.posted_flag, 'N') = DECODE ( :p_posted_flag, 'Y', NVL (aid.posted_flag, 'N'), 'N'))) AND AIA.QUICK_PO_HEADER_ID = POH.po_header_id(+) AND aia.invoice_id = aha.invoice_id AND aha.line_location_id IS NULL AND aha.release_lookup_code IS NULL AND pod.po_header_id(+) = poh.po_HEADER_id AND pod.po_line_id = pol.po_line_id(+) AND (pol.line_num <= 1 OR POL.LINE_NUM IS NULL) AND pod.line_location_id = poll.line_location_id(+) AND poll.po_release_id = por.po_release_id(+) AND poh.agent_id = pp1.person_id(+) AND pod.deliver_to_person_id = pp2.person_id(+) AND aihd.terms_id = t.term_id AND aihd.org_id = org.organization_id AND ( aihd.exchange_rate IS NOT NULL OR aihd.invoice_currency_code = :c_base_currency_code) AND NVL (pp1.effective_end_date, (SYSDATE + 1)) > SYSDATE AND NVL (pp2.effective_end_date, (SYSDATE + 1)) > SYSDATE AND UPPER (aihd.hold_lookup_code) NOT IN (SELECT UPPER (lookup_code) FROM ap_lookup_codes WHERE lookup_type = 'AMZ_HOLDS_NOT_INCLUDED' AND enabled_flag = 'Y' AND NVL (inactive_date, (SYSDATE + 1)) > SYSDATE) AND f.currency_code = :c_base_currency_code AND alc.lookup_type = 'HOLD CODE' AND alc.lookup_code = aihd.hold_lookup_code GROUP BY aihd.hold_lookup_code, TRUNC (aihd.hold_date), alc.displayed_field, aihd.vendor_name, DECODE ( :sort_by_alternate, 'Y', UPPER (aihd.vendor_name_alt), UPPER (aihd.vendor_name)), aihd.vendor_id, aihd.invoice_date, aihd.invoice_id, aihd.batch_name, aihd.invoice_num, DECODE (aihd.invoice_currency_code, :c_base_currency_code, aihd.invoice_amount, aihd.base_amount), DECODE (aihd.invoice_currency_code, :c_base_currency_code, aihd.invoice_amount, aihd.base_amount) - DECODE ( aihd.invoice_currency_code, :c_base_currency_code, NVL (aihd.amount_paid, 0) + NVL ( aihd.discount_amount_taken, 0), DECODE ( f.minimum_accountable_unit, NULL, ROUND ( aihd.exchange_rate * NVL (aihd.amount_paid, 0), f.precision), ROUND ( ( aihd.exchange_rate * NVL (aihd.amount_paid, 0)) / f.minimum_accountable_unit) * f.minimum_accountable_unit) + DECODE ( f.minimum_accountable_unit, NULL, ROUND ( aihd.exchange_rate * NVL (aihd.discount_amount_taken, 0), f.precision), ROUND ( ( aihd.exchange_rate * NVL (aihd.discount_amount_taken, 0)) / f.minimum_accountable_unit) * f.minimum_accountable_unit)), aihd.description, aihd.creation_date, NULL, pod.line_location_id, pod.po_header_id, poh.segment1, por.release_num, pol.line_num, pol.unit_price, poll.shipment_num, pp1.full_name, pp2.full_name, SUBSTR (pp2.email_address, 1, INSTR (pp2.email_address, '@') - 1), SUBSTR (pp1.email_address, 1, INSTR (pp1.email_address, '@') - 1), aihd.attribute6, aihd.pay_group_lookup_code, t.name, aihd.payment_method_lookup_code, aihd.invoice_currency_code, NVL (aihd.discount_date, aihd.due_date), aihd.hold_reason, org.name, org.organization_id) x WHERE ( :C_ORG_ID = x.c_organization_id) ORDER BY C_MASTER_NLS_HOLD_CODE, C_SORT_VENDOR_NAME, C_INVOICE_DATE, UPPER (C_BATCH_NAME), C_VENDOR_ID, C_INVOICE_NUM, C_ORIGINAL_AMOUNT DESC