rendered paste bodySELECT decode(:P_sortby, 'PO NUMBER', null, poh.document_buyer_last_name)
, decode(:P_sortby, 'PO NUMBER', null, poh.document_buyer_first_name)
, decode(:po_num_type,'NUMERIC', null, poh.po_num)
, decode(:po_num_type,'NUMERIC', decode(rtrim(poh.po_num,'0123456789'),NULL,to_number(poh.po_num),-1), null)
, poh.po_type poh_po_type
, por.release_type poh_release_type
, poh.po_header_id ID_POH_ent
, poh.po_num || decode(poh.po_type,'RELEASE','-'|| por.release_num||', '||poh.revision_num,', '||poh.revision_num) poh_po_num
, poh.po_num || decode(poh.po_type,'RELEASE','-'|| por.release_num,null) poh_po_num_de
, poh.po_num security_poh_po_num
, por.release_num security_por_po_num
, poh.revision_num poh_revision_num
, poh.vendor_name poh_vendor_name
, poh.vendor_address_line1 poh_vendor_address_line1
, poh.vendor_address_line2 poh_vendor_address_line2
, poh.vendor_address_line3 poh_vendor_address_line3
, poh.vendor_city vendor_city
, poh.vendor_state vendor_state
, poh.VENDOR_SITE_ID VENDOR_SITE_ID
, decode(poh.vendor_city,null, poh.vendor_state ,poh.vendor_city||', '||poh.vendor_state ||', '|| poh.vendor_postal_code) poh_vendor_adr_info
, poh.vendor_postal_code ||' '|| poh.vendor_city poh_vendor_adr_info_FR
, poh.vendor_postal_code ||' '|| poh.vendor_city poh_vendor_adr_info_GE -- OFE 22012004
, poh.vendor_country poh_vendor_country
, poh.customer_num poh_customer
, poh.vendor_num poh_vendor_num
, poh.creation_date poh_creation_date
, poh.revised_date poh_revised_date
, substr(poh.document_buyer_first_name,1,1) ||' '||poh.document_buyer_last_name poh_buyer
, poh.document_buyer_last_name || ', ' || poh.document_buyer_first_name poh_buyer_ES
, substr(poh.archive_buyer_first_name,1,1) ||decode(poh.archive_buyer_last_name,null,null,',')||poh.archive_buyer_last_name poh_archive_buyer
, poh.document_buyer_agent_id poh_document_buyer_agent_id
, poh.payment_terms poh_payment_terms
, poh.ship_via poh_ship_via
, poh.fob poh_fob
, poh.freight_terms poh_freight_terms
, poh.vendor_contact_first_name||' '||poh.vendor_contact_last_name poh_vendor_contact_name,
poh.vendor_contact_last_name poh_vendor_contact_last_name
, poh.vendor_phone poh_vendor_phone
, poh.vendor_contact_phone poh_vendor_contact_phone
, poh.note_to_vendor poh_note_to_vendor
, poh.printed_date poh_printed_date
, poh.amount_agreed poh_amount_agreed
, poh.cancel_flag poh_cancel_flag
, poh.confirming_order_flag poh_confirming_order_flag
, poh.acceptance_required_flag poh_acceptance_req_flag
, poh.acceptance_due_date poh_acceptance_due_date
, poh.currency_code poh_currency_code
, poh.currency_code C_CURRENCY
, poh.currency_name poh_currency_name
, poh.currency_conversion_rate poh_currency_conversion_rate
, poh.bill_to_location_id poh_bill_to_location
, poh.ship_to_location_id poh_ship_to_location
, poh.po_header_id poh_po_header_id
, poh.po_release_id poh_po_release_id
, poh.po_type poh_po_type
, poh.print_count poh_print_count
, poh.approved_flag poh_approved_flag
, poh.effective_date poh_effective_date
, poh.expiration_date poh_expiration_date
, nvl(poh.po_release_id,-1) poh_join_release_id
, poh.vendor_id poh_vendor_id
-- Comptable
, poh.attribute4 comptable_id
-- Origine des prix
, poh.attribute3 Origine
-- Archive
, poh.ARCHIVE_BUYER_AGENT_ID Archive_id
-- Vendor contact id
, pohe.VENDOR_CONTACT_ID poh_VENDOR_CONTACT_ID
-- Chef de projet
, poh.attribute5 Project_Manager
/*
-- Taux TVA
, ap_tax.tax_rate tax_rate
*/
FROM po_headers_print poh
, po_releases por
-- Vendor contact id
, po_headers pohe
/*
-- Taux TVA
,PO_TAX_CODES_SUMMARY_V po_tax
,AP_TAX_CODES_ALL ap_tax
*/
WHERE
-- Vendor contact id
pohe.po_header_id(+)= poh.po_header_id
/*
-- Taux TVA
po_tax.object_type(+)='PO'
AND po_tax.header_id(+)= poh.po_header_id
AND ap_tax.tax_id(+)=po_tax.tax_code
AND
*/
AND poh.po_release_id = por.po_release_id (+)
AND ( nvl(por.release_num,-1)
BETWEEN nvl(:P_release_num_from,nvl(por.release_num,-1))
AND nvl(:P_release_num_to,nvl(por.release_num,-1))
OR poh.po_type != 'RELEASE')
AND poh.document_buyer_agent_id = nvl(:P_agent_id,poh.document_buyer_agent_id)
AND ( poh.release_date is NULL
OR trunc(poh.release_date) between nvl(:P_date_from, trunc(poh.release_date))
and nvl(:P_date_to, trunc(poh.release_date + 1)) )
AND nvl(poh.approved_flag,'N') = nvl(:P_approved_flag, nvl(poh.approved_flag,'N'))
AND ((nvl(:P_print_releases,'Y') = 'Y') or (nvl(:P_print_releases,'Y') = 'N' and poh.po_type != 'RELEASE'))
&where_performance
&where_clause
&p_language_where
&C_ec_where_clause
ORDER BY 1,2,3,4