SQL Joins Concept

How to transfer assigned open PO to another buyer.

Using oracle build-in function : Mass Update of Buyer Name on Purchasing Documents 

1.      Reportà RunàOpen Purchase Orders Report(by Buyer) 

              * Can see   which  open  PO on  specific buyer.

2.      Reportà Runà Mass Update of Buyer Name on Purchasing Documents 

             *Assign  open PO from A buyer to B buyer.

LOV can't find exist supplier or customer

If the situation is supplier or customer is inactive.

System Administrator > Concurrent > Program > Define

check Request area's attribute "Allow Disabled Values"

How to use the SMTP package to send email in Oracle Database

Using UTL_SMTP

DECLARE
  c UTL_SMTP.CONNECTION;

  PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
  BEGIN
    UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
  END;

BEGIN
  c := UTL_SMTP.OPEN_CONNECTION('172.30.160.53');
  UTL_SMTP.HELO(c, 'google.com');
  UTL_SMTP.MAIL(c, 'Finch.Chen@Linde-lienhwa.com.tw'); --sender
  UTL_SMTP.RCPT(c, 'Finch.Chen@Linde-lienhwa.com.tw'); --recipient
  UTL_SMTP.OPEN_DATA(c);
  send_header('From',    '"Sender" <Finch.Chen@Linde-lienhwa.com.tw>');
  send_header('To',      '"Recipient" <Finch.Chen@Linde-lienhwa.com.tw>');
  send_header('Subject', 'Hello');
  UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'Hello, world!');
  UTL_SMTP.CLOSE_DATA(c);
  UTL_SMTP.QUIT(c);
EXCEPTION
  WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
    BEGIN
      UTL_SMTP.QUIT(c);
    EXCEPTION
      WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
        NULL; -- When the SMTP server is down or unavailable, we don't have
              -- a connection to the server. The QUIT call will raise an
              -- exception that we can ignore.
    END;
    raise_application_error(-20000,
      'Failed to send mail due to the following error: ' || sqlerrm);
END;

How to find out PO currency rate differ from Receipt currency conversion rate

PO currency rate in po_headers_all  &  Receipt currency conversion rate in rcv_transactions



select rt.currency_conversion_rate receipt_rate,pda.rate po_distribution_rate,pha.segment1, rsh.receipt_num

from rcv_transactions rt,
         po_distributions_all pda,
         po_headers_all pha,
         rcv_shipment_headers rsh

where rt.po_distribution_id = pda.po_distribution_id
  and rt.currency_conversion_rate <> pda.rate
  and pda.po_header_id = pha.po_header_id
  and rsh.shipment_header_id = rt.shipment_header_id
--and rsh.ship_to_org_id = 40
  order by pha.segment1 desc

How to check error log for AutoInvoice import.

How to check error log for AutoInvoice import.

Every workbook has three tabbed worksheets. You can use the tools available in the workbook to manage the review and update of information.

The workbook is populated with information from the AutoInvoice tables:

• RA_INTERFACE_LINES_ALL: Transaction header and line information.
• RA_INTERFACE_SALESCREDITS_ALL: Sales credit information for transactions.
• RA_INTERFACE_DISTRIBUTIONS_ALL: Distributions linked to the appropriate transaction lines in the ra_interface_lines table from the transaction flexfield.
• RA_INTERFACE_CONTS_ALL: Revenue contingencies that impact revenue recognition for imported transactions.
• RA_INTERFACE_ERRORS_ALL: All interface lines that failed validation and were not imported into Receivables tables.