How to add Attachments (pdf, word, excel, img etc) in Workflow Notifications.

Lets suppose we want to send the "Terms & Conditions" pdf file to the approver that is attached at PO_HEADERS block of Quotations. You need to do the following:

1 - Create a Document type attribute with Source as Send in the message of approval notification as following

Value field must be like that

 

plsqlblob:package_name.procedure_name/&document_id

 

In this case the document is a binary file so plsqlblob specifier would be used and  document_id should be the value of Po_headers_all.header_id which is stored in P_HEADER_ID attribute hence the Value field is:

 

=>  plsqlblob:cust_qoutations_wf.get_attachment/&P_HEADER_ID 

 

Click Apply button after entering all the detail. 

 

Save the workflow definition in the DB. 

  

 Next you need to create the package procedure as the below one in the apps schema which will be called by the workflow engine to get the attachment.

 

 

Create or Replace PACKAGE BODY CUST_QOUTATIONS_WF AS

--

 

  PROCEDURE get_attachment(document_id   in varchar2,

                                                    display_type  in varchar2,
                                                    document      in out blob,
                                                    document_type in out NOCOPY varchar2)
  
     is
  
      lv_file_name           VARCHAR2 (100) := NULL;
      lv_file_content_type   VARCHAR2 (100) := NULL;
      lv_doc_in            BLOB;
      lv_document_id po_headers_all.PO_HEADER_ID%TYPE;
      lv_length Integer;

  begin

    lv_document_id := document_id;

 

      select fl.file_name, fl.file_content_type, fl.file_data
       INTO lv_file_name, lv_file_content_type, lv_doc_in
      from APPLSYS.FND_ATTACHED_DOCUMENTS fad,
           APPLSYS.FND_DOCUMENTS fd ,
           APPLSYS.FND_LOBS fl
      where fad.ENTITY_NAME = 'PO_HEADERS'
        and fad.DOCUMENT_ID = fd.DOCUMENT_ID
        and fd.MEDIA_ID = fl.FILE_ID
        and fad.PK1_VALUE = lv_document_id ;

 

      document_type := lv_file_content_type || ';name=' || lv_file_name;

     lv_length := DBMS_LOB.getlength(lv_doc_in);

     
       DBMS_LOB.COPY (dest_lob => document,
                                           src_lob  => lv_doc_in,
                                           amount   => lv_length);

    commit;

  
  exception
    when others then
      wf_core.context('CUST_QOUTATIONS_WF',
                      'get_attachment',
                       document_id);
    raise;
   
 end;

 

 --

 

end CUST_QOUTATIONS_WF;

 

 

 

Hope that will help.

Thanks

 

 

 

 

 

Custom Workflow For Oracle Qoutations

-- Package Spec
create or replace PACKAGE XXCUS_QOUTATIONS_WF AS

  procedure VERIFY_QOUTE_DETAILS(itemtype  IN VARCHAR2,
                                 itemkey   IN VARCHAR2,
                                 actid     IN NUMBER,
                                 funcmode  IN VARCHAR2,
                                 resultout IN OUT NOCOPY VARCHAR2);
  procedure SET_PROCESS_DTL(itemtype  IN VARCHAR2,
                            itemkey   IN VARCHAR2,
                            actid     IN NUMBER,
                            funcmode  IN VARCHAR2,
                            resultout IN OUT NOCOPY VARCHAR2);
  PROCEDURE get_qoute_line_details(document_id   in varchar2,
                                   display_type  in varchar2,
                                   document      in out NOCOPY varchar2,
                                   document_type in out NOCOPY varchar2);
  PROCEDURE get_attachment(document_id   in varchar2,
                                   display_type  in varchar2,
                                   document      in out blob,
                                   document_type in out NOCOPY varchar2);
  PROCEDURE LAUNCH_WORKFLOW(P_QOUTE_ID in number, P_USER_ID in number);
  Procedure Upd_TO_Approved(itemtype in varchar2,
                            itemkey  in varchar2,
                            actid    in number,
                            funcmode in varchar2,
                            result   out nocopy varchar2);
  Procedure Upd_TO_Part_Approved(itemtype in varchar2,
                                 itemkey  in varchar2,
                                 actid    in number,
                                 funcmode in varchar2,
                                 result   out nocopy varchar2);
  Procedure Upd_TO_Rejected(itemtype in varchar2,
                            itemkey  in varchar2,
                            actid    in number,
                            funcmode in varchar2,
                            result   out nocopy varchar2);
  Procedure Upd_TO_Pending(itemtype in varchar2,
                           itemkey  in varchar2,
                           actid    in number,
                           funcmode in varchar2,
                           result   out nocopy varchar2);
  Procedure get_forward_from(itemtype in varchar2,
                             itemkey  in varchar2,
                             actid    in number,
                             funcmode in varchar2,
                             result   out nocopy varchar2);

END XXCUS_QOUTATIONS_WF;

11i to R12 - A Brief Overview of Table Level Changes

Suppliers: 

AP_SUPPLIERS  replaces PO.PO_VENDORS

AP_SUPPLIER_SITES_ALL replaces  PO.PO_VENDOR_SITES_ALL

But the PO_VENDORS and PO_VENDOR_SITES_ALL are still available as views based on new tables for backward compatibility. 

Supplier linking with HZ (TCA):

HZ_PARTIES - Party data for the suppliers (AP_SUPPLIER.part_id).

HZ_PARTY_SITES - Party site data for the supplier sites (AP_SUPPLIER_SITES_ALL.party_site_id).

Invoices:

New table in R12:   

AP_INVOICE_LINES_ALL

Taxes:

Functionality provided by E-Business Tax new tables in R12

ZX_LINES - Detailed Tax lines for the invoice (trx_id = ap_invoices_all.invoice_id)

ZX_LINES_SUMMARY - Summary tax lines for the invoice (trx_id = ap_invoices_all .invoice_id)

ZX_REC_NREC_DIST  - Tax distributions for the invoice (trx_id = ap_invoices_all.invoice_id)

ZX_LINES_DET_FACTORS - Tax determination factors for the invoice (trx_id = ap_invoices_all.invoice_id)

Accounting:

Functionality moved to Centralized Sub Ledger Accounting (SLA) model

New tables in R12 :

XLA_EVENTS                  -  (Similar to AP_Accounting_Events_All table but it is not limited to Payables module only)

XLA_AE_HEADERS        - (Similar to AP_AE_HEADERS table but it is not limited to Payables module only)

XLA_AE_LINES                -  (Similar to AP_AE_LINES table but it is not limited to Payables module only)

XLA_TRANSACTION_ENTITIES –  It contains transaction’s header level key info like invoice_id or check_id or cash_receipt_id  in SOURCE_ID_INT_1 column and operating unit (org_id) in SECURITY_ID_INT_1 column.

XLA_DISTRIBUTION_LINKS -    It contains the distribution level info of transactions such as Invoice_Distribution_id and etc

Join examples:

      1 -     Xla_Events.Event_Id  =  Ap_Invoice_Distributions.Accounting_Event_Id   

 

             Alternatively  

                       Xla_Transaction_Entities.Source_Id_Int_1 = Ap_Invoices.Invoice_id

                and Xla_Transaction_Entities.Security_Id_Int_1 = Ap_Invoices.Org_id

                and Xla_Transaction_Entities.Entity_code = ‘AP_INVOICES’

     

      2 -     Xla_Events.Event_Id  =  Ra_Cust_Trx_Line_Gl_Dist_All.Event_Id

           

               Alternatively  

            

                       Xla_Transaction_Entities.Source_Id_Int_1 = Ra_Customer_Trx_all.Customer_Trx _id

                and Xla_Transaction_Entities.Security_Id_Int_1 = Ra_Customer_Trx_all.Org_id

                and Xla_Transaction_Entities.Entity_code = ‘TRANSACTIONS’
 

      3 -     XLA_EVENTS.event_id  =  AP_INVOICE_PAYMENTS.accounting_event_id (AP Payment)

                Alternatively  

                       Xla_Transaction_Entities.Source_Id_Int_1 =AP_Checks_all.Check _id

                and Xla_Transaction_Entities.Security_Id_Int_1 = AP_Checks_all.Org_id

                and Xla_Transaction_Entities.Entity_code = ‘AP_PAYMENTS’

             

      4-      XLA_EVENTS.event_id  =  AR_CASH_RECEIPTS_HISTORY.event_id  (AR Receipts)

              

              Alternatively

  

                       Xla_Transaction_Entities.Source_Id_Int_1 =AR_Cash_Receipts_all.Cash_Receipt _id

                and Xla_Transaction_Entities.Security_Id_Int_1 =AR_Cash_Receipts_all.Org_id

                and Xla_Transaction_Entities.Entity_code = ‘RECEIPTS’

etc

Bank Accounts:

Functionality moved From Payables to Cash Management.

CE_BANK_ACCOUNTS  replaces AP_BANK_ACCOUNTS_ALL

CE_BANK_ACCT_USES_ALL  replaces AP_BANK_ACCOUNT_USES_ALL

CE_PAYMENT_DOCUMENTS replaces AP_CHECK_STOCKS_ALL

The old AP tables will also remain in db for backward compatibility only. All new bank accounts info will be stored in cash management tabeles.

References

1 – AP_CHECKS_ALL

    12.2:  

             AP_CHECKS_ALL.Ce_Bank_Acct_Use_Id = CE_BANK_ACCT_USES_ALL.Bank_Acct_Use_Id

 

    11i :  

             AP_CHECKS_ALL.Bank_Account_Id = AP_BANK_ACCOUNTS_ALL.Bank_Account_Id 

2- AR_CASH_RECEIPTS_ALL

   12.2: 

             AR_CASH_RECEIPTS_ALL.Remit_Bank_Acct_Use_Id = CE_BANK_ACCT_USES_ALL.Bank_Acct_Use_Id

 

   11i:  

             AR_CASH_RECEIPTS_ALL.Remittance_Bank_Account_Id = AP_BANK_ACCOUNTS_ALL.Bank_Account_Id

Customers & Bill To/Ship to Addresses:

AR_CUSTOMERS replaces RA_CUSTOMERS

HZ_CUST_SITE_USES  replaces RA_SITE_USES_ALL

HZ_LOCATIONS replaces RA_ADDRESSES_ALL

Old tables will be truncated and data will only be available in new tables after upgrade.

Additional Tables Involved:

HZ_CUST_ACCT_SITES_ALL

HZ_PARTY_SITES

 Example query:

SELECT AC.CUSTOMER_NUMBER ,

       AC.CUSTOMER_NAME , 

       HCSUA.SITE_USE_CODE ,

       HL.ADDRESS1    ,

       HL.ADDRESS2    ,

       HL.ADDRESS3    ,

       HL.CITY        ,

       HL.POSTAL_CODE ,

       HL.STATE       ,

       HL.COUNTRY

  FROM AR_CUSTOMERS AC ,

       HZ_CUST_ACCT_SITES_ALL HCASA ,

       HZ_PARTY_SITES HPS,

       HZ_CUST_SITE_USES_ALL HCSUA,

       HZ_LOCATIONS HL

WHERE AC.CUSTOMER_ID = HCASA.CUST_ACCOUNT_ID

  AND HCASA.PARTY_SITE_ID = HPS.PARTY_SITE_ID

  AND HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID

  AND HCSUA.PRIMARY_FLAG = 'Y' -- Primary Addresses

  AND HCSUA.STATUS = 'A' -- Active

  AND HPS.LOCATION_ID = HL.LOCATION_ID