PO Printed RFQ
Description
- Categories
- BI Publisher
- Application
- Purchasing
- Source
- Printed RFQ Report (XML)
- Short Name
- POXPRRFP_XML
- DB package
- PO_POXPRRFP_XMLP_PKG
Imported from BI Publisher
Printed RFQ Report(Portrait)
Printed RFQ Report(Portrait)
SQL 374 lines
SELECT decode(:P_orderby,'RFQ NUMBER',null,poh.buyer_last_name) ,decode(:P_orderby,'RFQ NUMBER',null,poh.buyer_first_name) ,decode(:rfq_num_type,'NUMERIC',null,poh.rfq_num) ,decode(:rfq_num_type,'NUMERIC',decode(rtrim(poh.rfq_num,'0123456789'),NULL,to_number(poh.rfq_num),-1),null) ,poh.rfq_num poh_rfq_num ,poh.revision_num poh_revision_num ,poh.creation_date poh_creation_date ,poh.printed_date poh_printed_date ,poh.revised_date poh_revised_date ,poh.close_date poh_close_date ,poh.reply_by_date poh_reply_by_date ,poh.effective_date poh_effective_date ,poh.expiration_date poh_expiration_date ,poh.note_to_vendor poh_note_to_vendor ,substr(poh.buyer_first_name,1,1) ||' '||poh.buyer_last_name poh_buyer ,poh.ship_via poh_ship_via ,poh.fob poh_fob ,poh.freight_terms poh_freight_terms ,poh.reply_via poh_reply_via ,poh.payment_terms poh_payment_terms ,poh.customer_num poh_customer_num ,poh.vendor_num poh_vendor_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 ||', '||poh.vendor_state ||' '|| poh.vendor_postal_code poh_vendor_adr_info ,poh.vendor_country poh_vendor_country ,poh.vendor_phone poh_vendor_phone ,poh.vendor_contact_first_name ||' '|| poh.vendor_contact_last_name poh_vendor_contact_name ,poh.vendor_contact_phone poh_vendor_contact_phone ,poh.ship_address_line1 poh_ship_address_line1 ,poh.ship_address_line2 poh_ship_address_line2 ,poh.ship_address_line3 poh_ship_address_line3 ,poh.ship_city ||', '||poh.ship_state_province||' '|| poh.ship_postal_code poh_ship_adr_info ,poh.ship_country poh_ship_country ,poh.bill_address_line1 poh_bill_address_line1 ,poh.bill_address_line2 poh_bill_address_line2 ,poh.bill_address_line3 poh_bill_address_line3 ,poh.bill_city ||', '||poh.bill_state_province||' '|| poh.bill_postal_code poh_bill_adr_info ,poh.bill_city poh_bill_city ,poh.bill_state_province poh_bill_state_province ,poh.bill_postal_code poh_bill_postal_code ,poh.bill_country poh_bill_country ,poh.bill_to_location_id poh_bill_to_location_id ,poh.ship_to_location_id poh_ship_to_location_id ,poh.agent_id poh_agent_id ,poh.po_header_id poh_po_header_id ,poh.agent_id poh_agent_id ,poh.currency_code c_header_currency ,poh.sequence_num poh_sequence_num, PO_POXPRRFP_XMLP_PKG.c_fax_trailerformula(:poh_no_of_lines,:C_last_sum,:CS_poh_vendor_name,:CS_poh_rfq_num,:CS_poh_buyer,:CS_poh_agent_id) C_fax_trailer, PO_POXPRRFP_XMLP_PKG.c_fax_headerformula(:C_first_page) C_fax_header FROM po_rfq_headers_print poh WHERE poh.agent_id=nvl(:P_agent_id,poh.agent_id) AND :P_single_rfq_print=1 AND poh.rfq_num=:P_rfq_num_from &where_clause &p_language_where UNION ALL SELECT decode(:P_orderby,'RFQ NUMBER',null,poh.buyer_last_name) ,decode(:P_orderby,'RFQ NUMBER',null,poh.buyer_first_name) ,decode(:rfq_num_type,'NUMERIC',null,poh.rfq_num) ,decode(:rfq_num_type,'NUMERIC',decode(rtrim(poh.rfq_num,'0123456789'),NULL,to_number(poh.rfq_num),-1),null) ,poh.rfq_num poh_rfq_num ,poh.revision_num poh_revision_num ,poh.creation_date poh_creation_date ,poh.printed_date poh_printed_date ,poh.revised_date poh_revised_date ,poh.close_date poh_close_date ,poh.reply_by_date poh_reply_by_date ,poh.effective_date poh_effective_date ,poh.expiration_date poh_expiration_date ,poh.note_to_vendor poh_note_to_vendor ,substr(poh.buyer_first_name,1,1) ||' '||poh.buyer_last_name poh_buyer ,poh.ship_via poh_ship_via ,poh.fob poh_fob ,poh.freight_terms poh_freight_terms ,poh.reply_via poh_reply_via ,poh.payment_terms poh_payment_terms ,poh.customer_num poh_customer_num ,poh.vendor_num poh_vendor_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 ||', '||poh.vendor_state ||' '|| poh.vendor_postal_code poh_vendor_adr_info ,poh.vendor_country poh_vendor_country ,poh.vendor_phone poh_vendor_phone ,poh.vendor_contact_first_name ||' '|| poh.vendor_contact_last_name poh_vendor_contact_name ,poh.vendor_contact_phone poh_vendor_contact_phone ,poh.ship_address_line1 poh_ship_address_line1 ,poh.ship_address_line2 poh_ship_address_line2 ,poh.ship_address_line3 poh_ship_address_line3 ,poh.ship_city ||', '||poh.ship_state_province||' '|| poh.ship_postal_code poh_ship_adr_info ,poh.ship_country poh_ship_country ,poh.bill_address_line1 poh_bill_address_line1 ,poh.bill_address_line2 poh_bill_address_line2 ,poh.bill_address_line3 poh_bill_address_line3 ,poh.bill_city ||', '||poh.bill_state_province||' '|| poh.bill_postal_code poh_bill_adr_info ,poh.bill_city poh_bill_city ,poh.bill_state_province poh_bill_state_province ,poh.bill_postal_code poh_bill_postal_code ,poh.bill_country poh_bill_country ,poh.bill_to_location_id poh_bill_to_location_id ,poh.ship_to_location_id poh_ship_to_location_id ,poh.agent_id poh_agent_id ,poh.po_header_id poh_po_header_id ,poh.agent_id poh_agent_id ,poh.currency_code c_header_currency ,poh.sequence_num poh_sequence_num, PO_POXPRRFP_XMLP_PKG.c_fax_trailerformula(:poh_no_of_lines,:C_last_sum,:CS_poh_vendor_name,:CS_poh_rfq_num,:CS_poh_buyer,:CS_poh_agent_id) C_fax_trailer, PO_POXPRRFP_XMLP_PKG.c_fax_headerformula(:C_first_page) C_fax_header FROM po_rfq_headers_print poh WHERE poh.agent_id=nvl(:P_agent_id,poh.agent_id) AND :P_single_rfq_print!=1 AND :rfq_num_type='NUMERIC' AND decode(rtrim(poh.rfq_num,'0123456789'),NULL,to_number(poh.rfq_num),-1) BETWEEN decode(rtrim(nvl(:P_rfq_num_from,poh.rfq_num),'0123456789'),NULL, to_number(nvl(:P_rfq_num_from,poh.rfq_num)),-1) AND decode(rtrim(nvl(:P_rfq_num_to,poh.rfq_num),'0123456789'),NULL, to_number(nvl(:P_rfq_num_to,poh.rfq_num)),-1) &where_clause &p_language_where UNION ALL SELECT decode(:P_orderby,'RFQ NUMBER',null,poh.buyer_last_name) ,decode(:P_orderby,'RFQ NUMBER',null,poh.buyer_first_name) ,decode(:rfq_num_type,'NUMERIC',null,poh.rfq_num) ,decode(:rfq_num_type,'NUMERIC',decode(rtrim(poh.rfq_num,'0123456789'),NULL,to_number(poh.rfq_num),-1),null) ,poh.rfq_num poh_rfq_num ,poh.revision_num poh_revision_num ,poh.creation_date poh_creation_date ,poh.printed_date poh_printed_date ,poh.revised_date poh_revised_date ,poh.close_date poh_close_date ,poh.reply_by_date poh_reply_by_date ,poh.effective_date poh_effective_date ,poh.expiration_date poh_expiration_date ,poh.note_to_vendor poh_note_to_vendor ,substr(poh.buyer_first_name,1,1) ||' '||poh.buyer_last_name poh_buyer ,poh.ship_via poh_ship_via ,poh.fob poh_fob ,poh.freight_terms poh_freight_terms ,poh.reply_via poh_reply_via ,poh.payment_terms poh_payment_terms ,poh.customer_num poh_customer_num ,poh.vendor_num poh_vendor_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 ||', '||poh.vendor_state ||' '|| poh.vendor_postal_code poh_vendor_adr_info ,poh.vendor_country poh_vendor_country ,poh.vendor_phone poh_vendor_phone ,poh.vendor_contact_first_name ||' '|| poh.vendor_contact_last_name poh_vendor_contact_name ,poh.vendor_contact_phone poh_vendor_contact_phone ,poh.ship_address_line1 poh_ship_address_line1 ,poh.ship_address_line2 poh_ship_address_line2 ,poh.ship_address_line3 poh_ship_address_line3 ,poh.ship_city ||', '||poh.ship_state_province||' '|| poh.ship_postal_code poh_ship_adr_info ,poh.ship_country poh_ship_country ,poh.bill_address_line1 poh_bill_address_line1 ,poh.bill_address_line2 poh_bill_address_line2 ,poh.bill_address_line3 poh_bill_address_line3 ,poh.bill_city ||', '||poh.bill_state_province||' '|| poh.bill_postal_code poh_bill_adr_info ,poh.bill_city poh_bill_city ,poh.bill_state_province poh_bill_state_province ,poh.bill_postal_code poh_bill_postal_code ,poh.bill_country poh_bill_country ,poh.bill_to_location_id poh_bill_to_location_id ,poh.ship_to_location_id poh_ship_to_location_id ,poh.agent_id poh_agent_id ,poh.po_header_id poh_po_header_id ,poh.agent_id poh_agent_id ,poh.currency_code c_header_currency ,poh.sequence_num poh_sequence_num, PO_POXPRRFP_XMLP_PKG.c_fax_trailerformula(:poh_no_of_lines,:C_last_sum,:CS_poh_vendor_name,:CS_poh_rfq_num,:CS_poh_buyer,:CS_poh_agent_id) C_fax_trailer, PO_POXPRRFP_XMLP_PKG.c_fax_headerformula(:C_first_page) C_fax_header FROM po_rfq_headers_print poh WHERE poh.agent_id=nvl(:P_agent_id,poh.agent_id) AND :P_single_rfq_print!=1 AND :rfq_num_type='ALPHANUMERIC' AND :P_rfq_num_from IS NOT NULL AND :P_rfq_num_to IS NOT NULL AND poh.rfq_num>=:P_rfq_num_from AND poh.rfq_num<=:P_rfq_num_to &where_clause &p_language_where UNION ALL SELECT decode(:P_orderby,'RFQ NUMBER',null,poh.buyer_last_name) ,decode(:P_orderby,'RFQ NUMBER',null,poh.buyer_first_name) ,decode(:rfq_num_type,'NUMERIC',null,poh.rfq_num) ,decode(:rfq_num_type,'NUMERIC',decode(rtrim(poh.rfq_num,'0123456789'),NULL,to_number(poh.rfq_num),-1),null) ,poh.rfq_num poh_rfq_num ,poh.revision_num poh_revision_num ,poh.creation_date poh_creation_date ,poh.printed_date poh_printed_date ,poh.revised_date poh_revised_date ,poh.close_date poh_close_date ,poh.reply_by_date poh_reply_by_date ,poh.effective_date poh_effective_date ,poh.expiration_date poh_expiration_date ,poh.note_to_vendor poh_note_to_vendor ,substr(poh.buyer_first_name,1,1) ||' '||poh.buyer_last_name poh_buyer ,poh.ship_via poh_ship_via ,poh.fob poh_fob ,poh.freight_terms poh_freight_terms ,poh.reply_via poh_reply_via ,poh.payment_terms poh_payment_terms ,poh.customer_num poh_customer_num ,poh.vendor_num poh_vendor_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 ||', '||poh.vendor_state ||' '|| poh.vendor_postal_code poh_vendor_adr_info ,poh.vendor_country poh_vendor_country ,poh.vendor_phone poh_vendor_phone ,poh.vendor_contact_first_name ||' '|| poh.vendor_contact_last_name poh_vendor_contact_name ,poh.vendor_contact_phone poh_vendor_contact_phone ,poh.ship_address_line1 poh_ship_address_line1 ,poh.ship_address_line2 poh_ship_address_line2 ,poh.ship_address_line3 poh_ship_address_line3 ,poh.ship_city ||', '||poh.ship_state_province||' '|| poh.ship_postal_code poh_ship_adr_info ,poh.ship_country poh_ship_country ,poh.bill_address_line1 poh_bill_address_line1 ,poh.bill_address_line2 poh_bill_address_line2 ,poh.bill_address_line3 poh_bill_address_line3 ,poh.bill_city ||', '||poh.bill_state_province||' '|| poh.bill_postal_code poh_bill_adr_info ,poh.bill_city poh_bill_city ,poh.bill_state_province poh_bill_state_province ,poh.bill_postal_code poh_bill_postal_code ,poh.bill_country poh_bill_country ,poh.bill_to_location_id poh_bill_to_location_id ,poh.ship_to_location_id poh_ship_to_location_id ,poh.agent_id poh_agent_id ,poh.po_header_id poh_po_header_id ,poh.agent_id poh_agent_id ,poh.currency_code c_header_currency ,poh.sequence_num poh_sequence_num, PO_POXPRRFP_XMLP_PKG.c_fax_trailerformula(:poh_no_of_lines,:C_last_sum,:CS_poh_vendor_name,:CS_poh_rfq_num,:CS_poh_buyer,:CS_poh_agent_id) C_fax_trailer, PO_POXPRRFP_XMLP_PKG.c_fax_headerformula(:C_first_page) C_fax_header FROM po_rfq_headers_print poh WHERE poh.agent_id=nvl(:P_agent_id,poh.agent_id) AND :P_single_rfq_print!=1 AND :rfq_num_type='ALPHANUMERIC' AND :P_rfq_num_from IS NOT NULL AND :P_rfq_num_to IS NULL AND poh.rfq_num>=:P_rfq_num_from &where_clause &p_language_where UNION ALL SELECT decode(:P_orderby,'RFQ NUMBER',null,poh.buyer_last_name) ,decode(:P_orderby,'RFQ NUMBER',null,poh.buyer_first_name) ,decode(:rfq_num_type,'NUMERIC',null,poh.rfq_num) ,decode(:rfq_num_type,'NUMERIC',decode(rtrim(poh.rfq_num,'0123456789'),NULL,to_number(poh.rfq_num),-1),null) ,poh.rfq_num poh_rfq_num ,poh.revision_num poh_revision_num ,poh.creation_date poh_creation_date ,poh.printed_date poh_printed_date ,poh.revised_date poh_revised_date ,poh.close_date poh_close_date ,poh.reply_by_date poh_reply_by_date ,poh.effective_date poh_effective_date ,poh.expiration_date poh_expiration_date ,poh.note_to_vendor poh_note_to_vendor ,substr(poh.buyer_first_name,1,1) ||' '||poh.buyer_last_name poh_buyer ,poh.ship_via poh_ship_via ,poh.fob poh_fob ,poh.freight_terms poh_freight_terms ,poh.reply_via poh_reply_via ,poh.payment_terms poh_payment_terms ,poh.customer_num poh_customer_num ,poh.vendor_num poh_vendor_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 ||', '||poh.vendor_state ||' '|| poh.vendor_postal_code poh_vendor_adr_info ,poh.vendor_country poh_vendor_country ,poh.vendor_phone poh_vendor_phone ,poh.vendor_contact_first_name ||' '|| poh.vendor_contact_last_name poh_vendor_contact_name ,poh.vendor_contact_phone poh_vendor_contact_phone ,poh.ship_address_line1 poh_ship_address_line1 ,poh.ship_address_line2 poh_ship_address_line2 ,poh.ship_address_line3 poh_ship_address_line3 ,poh.ship_city ||', '||poh.ship_state_province||' '|| poh.ship_postal_code poh_ship_adr_info ,poh.ship_country poh_ship_country ,poh.bill_address_line1 poh_bill_address_line1 ,poh.bill_address_line2 poh_bill_address_line2 ,poh.bill_address_line3 poh_bill_address_line3 ,poh.bill_city ||', '||poh.bill_state_province||' '|| poh.bill_postal_code poh_bill_adr_info ,poh.bill_city poh_bill_city ,poh.bill_state_province poh_bill_state_province ,poh.bill_postal_code poh_bill_postal_code ,poh.bill_country poh_bill_country ,poh.bill_to_location_id poh_bill_to_location_id ,poh.ship_to_location_id poh_ship_to_location_id ,poh.agent_id poh_agent_id ,poh.po_header_id poh_po_header_id ,poh.agent_id poh_agent_id ,poh.currency_code c_header_currency ,poh.sequence_num poh_sequence_num, PO_POXPRRFP_XMLP_PKG.c_fax_trailerformula(:poh_no_of_lines,:C_last_sum,:CS_poh_vendor_name,:CS_poh_rfq_num,:CS_poh_buyer,:CS_poh_agent_id) C_fax_trailer, PO_POXPRRFP_XMLP_PKG.c_fax_headerformula(:C_first_page) C_fax_header FROM po_rfq_headers_print poh WHERE poh.agent_id=nvl(:P_agent_id,poh.agent_id) AND :P_single_rfq_print!=1 AND :rfq_num_type='ALPHANUMERIC' AND :P_rfq_num_from IS NULL AND :P_rfq_num_to IS NOT NULL AND poh.rfq_num<=:P_rfq_num_to &where_clause &p_language_where UNION ALL SELECT decode(:P_orderby,'RFQ NUMBER',null,poh.buyer_last_name) ,decode(:P_orderby,'RFQ NUMBER',null,poh.buyer_first_name) ,decode(:rfq_num_type,'NUMERIC',null,poh.rfq_num) ,decode(:rfq_num_type,'NUMERIC',decode(rtrim(poh.rfq_num,'0123456789'),NULL,to_number(poh.rfq_num),-1),null) ,poh.rfq_num poh_rfq_num ,poh.revision_num poh_revision_num ,poh.creation_date poh_creation_date ,poh.printed_date poh_printed_date ,poh.revised_date poh_revised_date ,poh.close_date poh_close_date ,poh.reply_by_date poh_reply_by_date ,poh.effective_date poh_effective_date ,poh.expiration_date poh_expiration_date ,poh.note_to_vendor poh_note_to_vendor ,substr(poh.buyer_first_name,1,1) ||' '||poh.buyer_last_name poh_buyer ,poh.ship_via poh_ship_via ,poh.fob poh_fob ,poh.freight_terms poh_freight_terms ,poh.reply_via poh_reply_via ,poh.payment_terms poh_payment_terms ,poh.customer_num poh_customer_num ,poh.vendor_num poh_vendor_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 ||', '||poh.vendor_state ||' '|| poh.vendor_postal_code poh_vendor_adr_info ,poh.vendor_country poh_vendor_country ,poh.vendor_phone poh_vendor_phone ,poh.vendor_contact_first_name ||' '|| poh.vendor_contact_last_name poh_vendor_contact_name ,poh.vendor_contact_phone poh_vendor_contact_phone ,poh.ship_address_line1 poh_ship_address_line1 ,poh.ship_address_line2 poh_ship_address_line2 ,poh.ship_address_line3 poh_ship_address_line3 ,poh.ship_city ||', '||poh.ship_state_province||' '|| poh.ship_postal_code poh_ship_adr_info ,poh.ship_country poh_ship_country ,poh.bill_address_line1 poh_bill_address_line1 ,poh.bill_address_line2 poh_bill_address_line2 ,poh.bill_address_line3 poh_bill_address_line3 ,poh.bill_city ||', '||poh.bill_state_province||' '|| poh.bill_postal_code poh_bill_adr_info ,poh.bill_city poh_bill_city ,poh.bill_state_province poh_bill_state_province ,poh.bill_postal_code poh_bill_postal_code ,poh.bill_country poh_bill_country ,poh.bill_to_location_id poh_bill_to_location_id ,poh.ship_to_location_id poh_ship_to_location_id ,poh.agent_id poh_agent_id ,poh.po_header_id poh_po_header_id ,poh.agent_id poh_agent_id ,poh.currency_code c_header_currency ,poh.sequence_num poh_sequence_num, PO_POXPRRFP_XMLP_PKG.c_fax_trailerformula(:poh_no_of_lines,:C_last_sum,:CS_poh_vendor_name,:CS_poh_rfq_num,:CS_poh_buyer,:CS_poh_agent_id) C_fax_trailer, PO_POXPRRFP_XMLP_PKG.c_fax_headerformula(:C_first_page) C_fax_header FROM po_rfq_headers_print poh WHERE poh.agent_id=nvl(:P_agent_id,poh.agent_id) AND :P_single_rfq_print!=1 AND :rfq_num_type='ALPHANUMERIC' AND :P_rfq_num_from IS NULL AND :P_rfq_num_to IS NULL &where_clause &p_language_where ORDER BY 1,2,3,4 |
Parameters 9
| Parameter | Type | List of values SQL |
|---|---|---|
| Operating Unit | LOV | |
| Report Type | LOV Oracle | |
| Buyer Name, Employee Number | LOV Oracle | |
| RFQ Numbers From | LOV Oracle | |
| To | LOV Oracle | |
| Test | LOV Oracle | |
| Sort By | LOV Oracle | |
| Fax Enable | LOV Oracle | |
| Fax Number | Char |