Oracle Cash Management Configuration

Oracle Cash Management

Oracle Cash Management is an open integrated solution for managing your company/enterprise-wide cash cycle. Oracle Cash Management is an enterprisewide solution for managing liquidity and controlling cash. Cash Management gives you direct access to expected cash flows from your operational systems. You can quickly analyze enterprisewide cash management, cash requirements and currency exposures, ensuring liquidity and optimal use of cash resources. Using Oracle Cash Management companies can project cash flows from Oracle General Ledger, Oracle Receivables, Oracle Payables, Oracle Payroll, Oracle Projects and Oracle Purchasing. Oracle Cash Management lets you automatically or manually record and reconcile bank statements, matching against system transactions using rules and tolerance levels. You can review and correct any import validation or reconciliation errors online. Oracle Cash Management can automatically reconcile correcting statement lines against error statement lines and provide an audit trail for verifying correction of bank errors. Oracle Cash Management is part of the Oracle Financials family of applications.

Configuration Steps

1. Define System Parameters

Use the System Parameters window to configure your Cash Management system to meet your business needs. System parameters determine, for example, which set of books Cash Management uses, the default options for manual reconciliation windows, and the control settings for the AutoReconciliation program. Navigation path: Cash Management>Setup>System>System Parameters

2. Run Security Wizard

Using the Cash Management Security wizard, an administrator can assign multiple legal entities to a role or roles to set up the following three securities:

• Bank Account Maintenance security – control bank account creation and updates
• Bank Account Use security – control bank account access
• Bank Account Fund Transfers security – control bank account transfers

For this purpose login through SYSADMIN default user and assign legal entity. Otherwise the user has to be given with relevant privileges to run this security wizard. User Name: SYSADMIN Navigation path: User Management>Roles & Role Inheritance

3. Bank Account Lookup Types

In Oracle Cash Management we can define lookup values for Bank Account Type. This lookup is extensible which can be defined current, saving, etc.Navigation path: Cash Management>Lookups

4. Define Bank(s)

The first step in the bank account creation is the bank definition. This means that which are the banks in which a company has accounts. This page allows you to search for existing banks, view and update them or create new banks.Navigation path: Cash Management>Setup>Banks>Banks

5. Define Branch(s)

Bank branch creation is the next step after the bank creation. Under a bank there must be branch(s) at different locations. This setup step allows you to search for existing bank branches, view and update them or create new bank branches. Navigation path:  Cash Management>Setup>Banks>Banks

6. Define Bank Account(s)

Once the bank and branch are created, you can proceed to the bank account setup. Select the bank branch you want to associate to your bank account. Assign the owner of the bank account. There are four areas associated to defining the account: general information, control of the account, security access to the account, and business unit assignment. If this is a Payable or Receivable account, the accounts are identified by business unit, and if a Payroll account, by legal entity. Navigation path: Cash Management>Setup>Banks>Bank Accounts

7. Define Check Book(s)

For each account Check Book is created in Oracle Cash Management. On payment automatic sequence number is assigned to the payment document.Navigation path: Cash Management>Setup>Banks>Bank Accounts After the configuration of these steps we are able to make entries in Oracle Cash Management.

oci_connect() ora-06413

To resolve oci_connect() ora-06413 error. Simple move your installation from Program Files (x86) folder to some where else. Because of () in the path connectivity issue arises.

Tags:
Oracle PHP Connection error, oci_connect() ora-06413

Deploy Oracle EBS OAF Page on Server

Deploy Oracle EBS OAF Page on Server

1: Copy your all OAF page files to the server on a path similar like: /u01/oracle/PROD/apps/apps_st/comn/java/classes/oracle/apps/fnd

2 Execute Import Command (For Windows) through Command prompt similar like:
D:\OAF\jdevbin\oaext\bin>import D:\OAF\jdevhome\jdev\myprojects\oracle\apps\fnd\
sample_pic\webui\samplePicPg.xml -username apps -password appsSu990rt -rootdir
D:\OAF\jdevhome\jdev\myprojects -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=t
cp)(HOST=192.168.20.101)(PORT=1521))(CONNECT_DATA=(SID=prod)))"


3) Create Function 
Type: Sswa jsp function

HTML path should be something like: OA.jsp?page=/oracle/apps/ak/emp_pic/webui/empPicPg

4) Bounce Apps Node only

5) Check your Oracle OAF Deployed page.

Tags:
Oracle EBS, OAF, Deploy OAF page

Error: JSP files must reside in the server root directory or a subdirectory beneath it

Oracle OAF E Business Suite Errors

Error: JSP files must reside in the server root directory or a subdirectory beneath it 

Tags:

Error: JSP files must reside in the server root directory or a subdirectory beneath it 

Oracle EBusiness Suite script commands

DB Node Commands
adautocfg.sh  
addbctl.sh   
adexecsql.pl  
adpreclone.pl  
adstrtdb.sql
adchknls.pl   
addlnctl.sh  
adlsnodes.sh  
adstopdb.sql

Apps Node Commands

adstpall.sh
To Down the apps node

adstrtal.sh
To start the apps node

adalnctl.sh   
adforms-c4wsctl.sh  
adopmnctl.sh             
mwactlwrpr.sh
adapcctl.sh   
adformsctl.sh       
adpreclone.pl  
java.sh      
adautocfg.sh  
adformsrvctl.sh     
jtffmctl.sh
adcmctl.sh    
adoacorectl.sh      
adexecsql.pl  
adoafmctl.sh        
gsmstart.sh    
mwactl.sh

Work Flow Builder Error Messages

Work Flow Builder Error Messages

Error:
3146: Commit happened in activity/function error in my workflow.
Resolution:


Error:
Error in Workflow NEW_TYPE/100_242 ORA-04061: existing state of has been invalidated ORA-04061: existing state of package 
Resolution:





Tags: Work Flow Builder Error Message

Oracle EBS Create Order API

Oracle EBS Release 12 create Order API from Order Management Responsibility sample procedure.




PROCEDURE cust_create_order (inq_id NUMBER)
   IS
      l_api_version_number           NUMBER                              := 1;
      l_return_status                VARCHAR2 (2000);
      l_msg_count                    NUMBER;
      l_msg_data                     VARCHAR2 (2000);
/*****************PARAMETERS****************************************/
      l_debug_level                  NUMBER                              := 1;
      -- OM DEBUG LEVEL (MAX 5)
      l_org                          NUMBER                            := 101;
      -- OPERATING UNIT
      l_no_orders                    NUMBER                              := 1;
      -- NO OF ORDERS
      l_user                         NUMBER                           := 1110;
      -- USER
      l_resp                         NUMBER                          := 51330;
      -- RESPONSIBLILTY
      l_appl                         NUMBER                            := 660;
                              -- ORDER MANAGEMENT
      /*************************INPUT VARIABLES FOR PROCESS_ORDER API****************************/
      l_header_rec                   oe_order_pub.header_rec_type;
      l_line_tbl                     oe_order_pub.line_tbl_type;
      l_action_request_tbl           oe_order_pub.request_tbl_type;
      l_line_adj_tbl                 oe_order_pub.line_adj_tbl_type;
      /****************************OUT VARIABLES FOR PROCESS_ORDER API**************************/
      l_header_rec_out               oe_order_pub.header_rec_type;
      l_header_val_rec_out           oe_order_pub.header_val_rec_type;
      l_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;
      l_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;
      l_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;
      l_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;
      l_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;
      l_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;
      l_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;
      l_line_tbl_out                 oe_order_pub.line_tbl_type;
      l_line_val_tbl_out             oe_order_pub.line_val_tbl_type;
      l_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;
      l_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;
      l_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;
      l_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;
      l_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;
      l_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;
      l_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;
      l_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;
      l_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;
      l_action_request_tbl_out       oe_order_pub.request_tbl_type;
      l_msg_index                    NUMBER;
      l_data                         VARCHAR2 (2000);
      l_loop_count                   NUMBER;
      l_debug_file                   VARCHAR2 (200);
      b_return_status                VARCHAR2 (200);
      b_msg_count                    NUMBER;
      b_msg_data                     VARCHAR2 (2000);
      l_etd_date                     DATE;
      l_qty                          NUMBER;
      l_line_no                      NUMBER;
      l_line_tbl_index               NUMBER;
      order_typ                      NUMBER;
      cust_id                        NUMBER;
      poc_num                        NUMBER;
      org_id                         NUMBER;
      mfg_org_id                     NUMBER;
      pay_term_id                    NUMBER;
      from_size                      VARCHAR2 (20);
      to_size                        VARCHAR2 (20);
      header_id                      NUMBER;
      order_num                      NUMBER;
      Line_num                       NUMBER;
   BEGIN
      DBMS_APPLICATION_INFO.set_client_info (l_org);
      mo_global.set_policy_context ('S', l_org);
      mo_global.init ('ONT');

      /**************************INITIALIZE DEBUG INFO**********************************/
      IF (l_debug_level > 0)
      THEN
         l_debug_file := oe_debug_pub.set_debug_mode ('FILE');
         oe_debug_pub.initialize;
         oe_debug_pub.setdebuglevel (l_debug_level);
         oe_msg_pub.initialize;
      END IF;

      /****************************INITIALIZE ENVIRONMENT*******************************/
      fnd_global.apps_initialize (l_user, l_resp, l_appl);
                     -- pass in user_id, responsibility_id, and application_id
      /*****************INITIALIZE HEADER RECORD*************************/
      l_header_rec := oe_order_pub.g_miss_header_rec;

      /***********POPULATE REQUIRED ATTRIBUTES ************************/
      SELECT cuoi.customer_id, cuoi.inquiry_number, cuoi.org_id,
             cuoi.payment_term_id, cuoi.from_size, cuoi.to_size
        INTO cust_id, poc_num, org_id,
             pay_term_id, from_size, to_size
        FROM cust_usg_ont_inquiry cuoi
       WHERE cuoi.inquiry_id = inq_id;

      SELECT ood.organization_id
        INTO mfg_org_id
        FROM org_organization_definitions ood
       WHERE ood.operating_unit = org_id AND ood.organization_code <> 'IMO';

      SELECT hcsu.order_type_id
        INTO order_typ
        FROM hz_parties hp,
             hz_party_sites hps,
             hz_locations hl,
             hz_cust_accounts_all hca,
             hz_cust_acct_sites_all hcsa,
             hz_cust_site_uses_all hcsu
       WHERE hp.party_id = hps.party_id
         AND hps.location_id = hl.location_id
         AND hp.party_id = hca.party_id
         AND hcsa.party_site_id = hps.party_site_id
         AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
         AND hca.cust_account_id = hcsa.cust_account_id
         AND hca.cust_account_id = cust_id
         and hcsa.ORG_ID = l_org
         AND hcsu.site_use_code = 'BILL_TO';

      l_header_rec.order_type_id := order_typ;
      l_header_rec.sold_to_org_id := cust_id;
--      l_header_rec.ship_to_org_id := 1457;
      l_header_rec.ship_from_org_id := mfg_org_id;
      l_header_rec.orig_sys_document_ref := poc_num;
      l_header_rec.payment_term_id := pay_term_id;
      l_header_rec.global_attribute6 := inq_id;
      l_header_rec.global_attribute7 := from_size;
      l_header_rec.global_attribute8 := to_size;
      l_header_rec.global_attribute9 := poc_num;
      l_header_rec.ORDER_SOURCE_ID := 1001;
      
      /**********The statement indicates to the process order API that a new header has to be created**********/
      l_header_rec.operation := oe_globals.g_opr_create;
      l_line_tbl_index := 0;

      

/**************************************CALLTO PROCESS ORDER API**********************************************/
      oe_order_pub.process_order
                    (p_api_version_number          => l_api_version_number,
                     p_header_rec                  => l_header_rec,
                     p_line_tbl                    => l_line_tbl,
                     p_action_request_tbl          => l_action_request_tbl,
                     p_line_adj_tbl                => l_line_adj_tbl
/*******************************************OUT variables****************************************************/
      ,
                     x_header_rec                  => l_header_rec_out,
                     x_header_val_rec              => l_header_val_rec_out,
                     x_header_adj_tbl              => l_header_adj_tbl_out,
                     x_header_adj_val_tbl          => l_header_adj_val_tbl_out,
                     x_header_price_att_tbl        => l_header_price_att_tbl_out,
                     x_header_adj_att_tbl          => l_header_adj_att_tbl_out,
                     x_header_adj_assoc_tbl        => l_header_adj_assoc_tbl_out,
                     x_header_scredit_tbl          => l_header_scredit_tbl_out,
                     x_header_scredit_val_tbl      => l_header_scredit_val_tbl_out,
                     x_line_tbl                    => l_line_tbl_out,
                     x_line_val_tbl                => l_line_val_tbl_out,
                     x_line_adj_tbl                => l_line_adj_tbl_out,
                     x_line_adj_val_tbl            => l_line_adj_val_tbl_out,
                     x_line_price_att_tbl          => l_line_price_att_tbl_out,
                     x_line_adj_att_tbl            => l_line_adj_att_tbl_out,
                     x_line_adj_assoc_tbl          => l_line_adj_assoc_tbl_out,
                     x_line_scredit_tbl            => l_line_scredit_tbl_out,
                     x_line_scredit_val_tbl        => l_line_scredit_val_tbl_out,
                     x_lot_serial_tbl              => l_lot_serial_tbl_out,
                     x_lot_serial_val_tbl          => l_lot_serial_val_tbl_out,
                     x_action_request_tbl          => l_action_request_tbl_out,
                     x_return_status               => l_return_status,
                     x_msg_count                   => l_msg_count,
                     x_msg_data                    => l_msg_data
                    );

   

    header_id := l_header_rec_out.header_id;
    order_num := l_header_rec_out.order_number;
    line_num := 0;
    l_header_rec.operation := oe_globals.g_opr_update;
    l_header_rec.header_id := header_id;



    FOR i IN (SELECT nvl(cope.quantity,cuoi.INQUIRY_QTY) qty, cuop.final_offer price,
                       cuoi.inquiry_number inqnum , nvl(cope.ETD, cuoi.EXPECT_SHIP_DATE ) etd
                  FROM cust_ont_poc_etd cope,
                       cust_usg_ont_inquiry cuoi,
                       cust_usg_ont_pcosting cuop
                 WHERE cope.inquiry_id = cuoi.inquiry_id
                   AND cuoi.inquiry_id = cuop.inquiry_id
                   AND cope.inquiry_id = inq_id)
      LOOP
         line_num := line_num+1;
         l_line_tbl_index := l_line_tbl_index + 1;
         l_line_tbl (l_line_tbl_index) := oe_order_pub.g_miss_line_rec;
/****************************************Line Attributes************************************************/
         l_line_tbl (l_line_tbl_index).inventory_item_id := 11927;
         l_line_tbl (l_line_tbl_index).ordered_quantity := i.qty;
         l_line_tbl (l_line_tbl_index).SCHEDULE_SHIP_DATE := i.etd ;
         l_line_tbl (l_line_tbl_index).orig_sys_document_ref := i.inqnum;
         l_line_tbl (l_line_tbl_index).user_item_description := order_num||'/'||line_num||'.1';         
         --l_line_tbl (l_line_tbl_index).attribute3 := 20;
         --l_line_tbl (l_line_tbl_index).attribute3 := 'Mohsin';
         l_line_tbl (l_line_tbl_index).unit_list_price := i.price;
         l_line_tbl (l_line_tbl_index).unit_selling_price := i.price;
         /**********************Indicates that this is a create operation for the line record********************/
         l_line_tbl (l_line_tbl_index).operation := oe_globals.g_opr_create;
         /*****************Indicates that this is an update operation for the line record****************************/

         --l_line_tbl(l_line_tbl_index).operation := OE_GLOBALS.G_OPR_UPDATE;

         /**********************Indicates that this is a delete operation for the line record*************************/

         --l_line_tbl(l_line_tbl_index).operation := OE_GLOBALS.G_OPR_DELETE;

         /************The below action request indicates to the process order that the order has to be booked*********/

         --l_action_request_tbl(1).request_type := oe_globals.g_book_order;
         l_action_request_tbl (l_line_tbl_index).entity_code :=
                                                   oe_globals.g_entity_header;
      END LOOP;
      
      oe_order_pub.process_order
                    (p_api_version_number          => l_api_version_number,
                     p_header_rec                  => l_header_rec,
                     p_line_tbl                    => l_line_tbl,
                     p_action_request_tbl          => l_action_request_tbl,
                     p_line_adj_tbl                => l_line_adj_tbl
/*******************************************OUT variables****************************************************/
      ,
                     x_header_rec                  => l_header_rec_out,
                     x_header_val_rec              => l_header_val_rec_out,
                     x_header_adj_tbl              => l_header_adj_tbl_out,
                     x_header_adj_val_tbl          => l_header_adj_val_tbl_out,
                     x_header_price_att_tbl        => l_header_price_att_tbl_out,
                     x_header_adj_att_tbl          => l_header_adj_att_tbl_out,
                     x_header_adj_assoc_tbl        => l_header_adj_assoc_tbl_out,
                     x_header_scredit_tbl          => l_header_scredit_tbl_out,
                     x_header_scredit_val_tbl      => l_header_scredit_val_tbl_out,
                     x_line_tbl                    => l_line_tbl_out,
                     x_line_val_tbl                => l_line_val_tbl_out,
                     x_line_adj_tbl                => l_line_adj_tbl_out,
                     x_line_adj_val_tbl            => l_line_adj_val_tbl_out,
                     x_line_price_att_tbl          => l_line_price_att_tbl_out,
                     x_line_adj_att_tbl            => l_line_adj_att_tbl_out,
                     x_line_adj_assoc_tbl          => l_line_adj_assoc_tbl_out,
                     x_line_scredit_tbl            => l_line_scredit_tbl_out,
                     x_line_scredit_val_tbl        => l_line_scredit_val_tbl_out,
                     x_lot_serial_tbl              => l_lot_serial_tbl_out,
                     x_lot_serial_val_tbl          => l_lot_serial_val_tbl_out,
                     x_action_request_tbl          => l_action_request_tbl_out,
                     x_return_status               => l_return_status,
                     x_msg_count                   => l_msg_count,
                     x_msg_data                    => l_msg_data
                    );
                    
      /**************************************CHECK RETURN STATUS**********************************************/
      IF l_return_status = fnd_api.g_ret_sts_success
      THEN
         DBMS_OUTPUT.put_line ('Order Processed Successfully');
         resultout := l_header_rec_out.order_number;
         COMMIT;
      ELSIF    l_return_status = fnd_api.g_ret_sts_error
            OR l_return_status = fnd_api.g_ret_sts_unexp_error
      THEN
         DBMS_OUTPUT.put_line ('Failed to create Order');
         resultout := 'Error ' || ':' || l_msg_data;
         ROLLBACK;
      END IF;
              

      /************************DISPLAY RETURN STATUS FLAGS***************************/
      IF (l_debug_level > 0)
      THEN
         DBMS_OUTPUT.put_line (   'process ORDER ret status IS: '
                               || l_return_status
                              );
         DBMS_OUTPUT.put_line ('process ORDER msg data IS: ' || l_msg_data);
         DBMS_OUTPUT.put_line ('process ORDER msg COUNT IS: ' || l_msg_count);
         DBMS_OUTPUT.put_line (   'header.order_number IS: '
                               || TO_CHAR (l_header_rec_out.order_number)
                              );
         /*DBMS_OUTPUT.put_line (   'adjustment.return_status IS: '
                               || l_line_adj_tbl_out (1).return_status
                              );*/
         DBMS_OUTPUT.put_line (   'header.header_id IS: '
                               || l_header_rec_out.header_id
                              );
         DBMS_OUTPUT.put_line (   'line.unit_selling_price IS: '
                               || l_line_tbl_out (l_line_tbl_index).unit_selling_price
                              );
      END IF;

/********************************DISPLAY ERROR MSGS*******************************/
      IF (l_debug_level > 0)
      THEN
         FOR i IN 1 .. l_msg_count
         LOOP
            oe_msg_pub.get (p_msg_index          => i,
                            p_encoded            => fnd_api.g_false,
                            p_data               => l_data,
                            p_msg_index_out      => l_msg_index
                           );
            DBMS_OUTPUT.put_line ('message is: ' || l_header_rec_out.header_id);
            DBMS_OUTPUT.put_line (   'message index is: '
                                  || l_header_rec_out.order_number
                                 );
         END LOOP;
      END IF;

      IF (l_debug_level > 0)
      THEN
         DBMS_OUTPUT.put_line ('Debug = ' || oe_debug_pub.g_debug);
         DBMS_OUTPUT.put_line (   'Debug Level = '
                               || TO_CHAR (oe_debug_pub.g_debug_level)
                              );
         DBMS_OUTPUT.put_line (   'Debug File = '
                               || oe_debug_pub.g_dir
                               || '/'
                               || oe_debug_pub.g_file
                              );
         DBMS_OUTPUT.put_line ('*******************************************');
         oe_debug_pub.debug_off;
      END IF;
   END cust_create_order;

Work Flow Builder Tutorial for beginners

Work Flow Builder Tutorial for beginners
Learn Work Flow builder usage with step by step instructions.

Pre Requisite:

• Work Flow Builder installed on your system.
• A customized form where you want to implement it.

Scenario:
We start with a simple scenario, we have a module where when a document is completed should go for approval to concern department head. We have to pass the primary key to workflow and then a notification will be emailed to concerned department head and then we will update the document flag based on the primary key passed to the workflow on the selection of Approval/Reject button.


Package Used:

CREATE OR REPLACE PACKAGE APPS.cust_sample_notify
IS
       
PROCEDURE run_cust_wf (sample_id IN NUMBER);
       
PROCEDURE appr_sam ( itemtype    IN       VARCHAR2,
                              itemkey     IN       VARCHAR2,
                              actid       IN       NUMBER,
                              funcmode    IN       VARCHAR2,
                              resultout   IN OUT   VARCHAR2);
        
PROCEDURE rej_sam ( itemtype    IN       VARCHAR2,
                              itemkey     IN       VARCHAR2,
                              actid       IN       NUMBER,
                              funcmode    IN       VARCHAR2,
                              resultout   IN OUT   VARCHAR2);
    
PROCEDURE get_sample_details (
      document_id     IN              VARCHAR2,
      display_type    IN              VARCHAR2,
      document        IN OUT NOCOPY   CLOB,
      document_type   IN OUT NOCOPY   VARCHAR2
   );
    
    END cust_sample_notify;
/

------------------------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE BODY APPS.cust_sample_notify
    IS
    
       PROCEDURE run_cust_wf (sample_id IN NUMBER)
        IS
      itemtype      VARCHAR2 (100)  := 'NEW_TYPE';
      itemkey       VARCHAR2 (100)
                                  := sample_id || '_' || poc_wf_appr_seq.NEXTVAL;
      wfprocess     VARCHAR2 (100)  := 'NEW_PROCESS';
      
      p_user_id     NUMBER;
      p_requestor   VARCHAR2 (100);
      p_resp_id     NUMBER;
      p_appl_id     NUMBER;
      p_org_id      NUMBER;
      ERROR_CODE    VARCHAR2 (100);
      error_msg     VARCHAR2 (1000);
      vSampleDetails        CLOB;
      nl            VARCHAR2 (10)     := fnd_global.NEWLINE;
      
   BEGIN
      p_user_id := 1110;--fnd_profile.VALUE ('user_id');
      p_resp_id := 21623; --fnd_profile.VALUE ('resp_id');
      p_appl_id := 660; --fnd_profile.VALUE ('resp_appl_id');
      p_org_id := 81;--fnd_profile.VALUE ('org_id');

      
       SELECT user_name
        INTO p_requestor
        FROM fnd_user
       WHERE user_id = p_user_id;

      fnd_global.apps_initialize (p_user_id, p_resp_id, p_appl_id);
      wf_engine.createprocess (itemtype      => itemtype,
                               itemkey       => itemkey,
                               process       => wfprocess
                              );
      wf_engine.SETITEMATTRTEXT ( itemtype => itemtype,
                          itemkey => itemkey,
                          aname => 'REQUESTOR',
                          avalue =>  p_requestor); 
                          
      wf_engine.SETITEMATTRNUMBER ( itemtype => itemtype,
                          itemkey => itemkey,
                          aname => 'SAMPLEID',
                          avalue =>  sample_id);                                         
      
      
      vSampleDetails := 'PLSQL:CUST_SAMPLE_NOTIFY.GET_SAMPLE_DETAILS/' || sample_id;
      
      wf_engine.SETITEMATTRTEXT ( itemtype => itemtype,
                          itemkey   => itemkey,
                          aname     => 'MSG',
                          avalue    =>  vSampleDetails);
                          
      wf_engine.startprocess (itemtype => itemtype, itemkey => itemkey);

      COMMIT;

   EXCEPTION
      WHEN OTHERS
      THEN
         ERROR_CODE := SQLCODE;
         error_msg := SQLERRM (SQLCODE);
         fnd_file.put_line (fnd_file.output, ERROR_CODE);
         fnd_file.put_line (fnd_file.output, error_msg);
   END run_cust_wf;
    
    PROCEDURE appr_sam(
        itemtype    IN       VARCHAR2,
      itemkey     IN       VARCHAR2,
      actid       IN       NUMBER,
      funcmode    IN       VARCHAR2,
      resultout   IN OUT   VARCHAR2)
    IS
    BEGIN
    
        
        
    
    
        UPDATE custusg.cust_sample_temp
        SET flag = 'Y'
        where id = wf_engine.GETITEMATTRTEXT ( itemtype => itemtype,
                                      itemkey => itemkey,
                                      aname => 'SAMPLEID'
                                      );
        
        COMMIT;
        
    EXCEPTION
      WHEN OTHERS
      THEN
--         ERROR_CODE := SQLCODE;
--         error_msg := SQLERRM (SQLCODE);
         fnd_file.put_line (fnd_file.output, SQLCODE);
         fnd_file.put_line (fnd_file.output, SQLERRM (SQLCODE));
   END ;
    
    
    
   PROCEDURE rej_sam (
            itemtype    IN       VARCHAR2,
            itemkey     IN       VARCHAR2,
            actid       IN       NUMBER,
            funcmode    IN       VARCHAR2,
            resultout   IN OUT   VARCHAR2)
    IS
    BEGIN
    
        UPDATE custusg.cust_sample_temp
        SET flag = 'N'
        where id = wf_engine.GETITEMATTRTEXT ( itemtype => itemtype,
                                      itemkey => itemkey,
                                      aname => 'SAMPLEID'
                                      );    
        
        COMMIT;
    END;
    
        
  

PROCEDURE get_sample_details (
      document_id     IN              VARCHAR2,
      display_type    IN              VARCHAR2,
      document        IN OUT NOCOPY   CLOB,
      document_type   IN OUT NOCOPY   VARCHAR2
   )
   IS
      l_body    clob;
      inq_id    NUMBER;
      nl        VARCHAR2 (1)     := fnd_global.NEWLINE;
      l_sr_no   NUMBER           := 0;
   --l_apprv      VARCHAR2 (100);
   BEGIN
      document_type := 'text/html';
      --
      l_body := nl || nl ||'<P><B><U>';
      l_body :=
            l_body
         || '<TABLE border=0 cellpadding=0 cellspacing=0 <width=100%><TR>
          <td align=LEFT valign=baseline ><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
         || '<u><b>'
         || 'POC Fabric Lines'
         || '</u></b>'
         || '</TD></TR></TABLE>'
         || nl;
      l_body := l_body || '</B>' 
      || nl 
      || '</P>';
      l_body :=
            l_body
         || '<table  cellspacing="2" ; cellpadding="5"; width=100%> <thead><tr>'
         || '<TH bgcolor="#d9e5ef"><font-family:Arial,Helvetica,Geneva,sans-serif; color:#000001><font size=2>'
         || 'SR No.'
         || '</TH>'
         || nl
         || '<TH bgcolor="#d9e5ef"><font-family:Arial,Helvetica,Geneva,sans-serif; color:#000001><font size=2>'
         || 'Item Description'
         || '</TH>'
         || nl
         || '<TH bgcolor="#d9e5ef"><font-family:Arial,Helvetica,Geneva,sans-serif; color:#000001><font size=2>'
         || 'UOM'
         || '</TH>'
         || nl
         || '<TH bgcolor="#d9e5ef"><font-family:Arial,Helvetica,Geneva,sans-serif; color:#000001><font size=2>'
         || 'Material Type'
         || '</TH>'
         || nl
         || '<TH bgcolor="#d9e5ef"><font-family:Arial,Helvetica,Geneva,sans-serif; color:#000001><font size=2>'
         || 'Quantity'
         || '</TH>'
         || nl
         || '<TH bgcolor="#d9e5ef"><font-family:Arial,Helvetica,Geneva,sans-serif; color:#000001><font size=2>'
         || 'Rate'
         || '</TH>'
         || nl
         || '<TH bgcolor="#d9e5ef"><font-family:Arial,Helvetica,Geneva,sans-serif; color:#000001><font size=2>'
         || 'Cost Amount'
         || '</TH>'
         || nl
         || '<TH bgcolor="#d9e5ef"><font-family:Arial,Helvetica,Geneva,sans-serif; color:#000001><font size=2>'
         || 'Amount (FCY)'
         || '</TH>'
         || nl
         || '<TH bgcolor="#d9e5ef"><font-family:Arial,Helvetica,Geneva,sans-serif; color:#000001><font size=2>'
         || 'Description / Remarks'
         || '</TH>'
         || nl
         || '</thead>
            </tr>
            <tbody>';

      FOR i IN (SELECT rawmaterial_id,measure_unit_id,remarks,quantity
                from cust_ont_sample_lines
                WHERE PERCOSTING_ID = 104
                order by percosting_id)
      LOOP
         BEGIN
            l_sr_no := l_sr_no + 1;
            l_body :=
                  l_body
               || '<tr> <td nowrap align=middle font color=#000000 face="Arial, Helvetica, Geneva, sans-serif" bgcolor="#eeeeee"><font size=2>'
               || l_sr_no
               || '</td> <td nowrap align=left font color=#000000 face="Arial, Helvetica, Geneva, sans-serif" bgcolor="#eeeeee"><font size=2>'
               || i.rawmaterial_id
               || '</td> <td nowrap align=middle font color=#000000 face="Arial, Helvetica, Geneva, sans-serif" bgcolor="#eeeeee"><font size=2>'
               || i.measure_unit_id
               || '</td> <td nowrap align=middle font color=#000000 face="Arial, Helvetica, Geneva, sans-serif" bgcolor="#eeeeee"><font size=2>'
               || i.remarks
               || '</td> <td nowrap align=middle font color=#000000 face="Arial, Helvetica, Geneva, sans-serif" bgcolor="#eeeeee"><font size=2>'
               || i.quantity
               || '</td> <td nowrap align=middle font color=#000000 face="Arial, Helvetica, Geneva, sans-serif" bgcolor="#eeeeee"><font size=2>'
               || NULL
               || '</td> <td nowrap align=middle font color=#000000 face="Arial, Helvetica, Geneva, sans-serif" bgcolor="#eeeeee"><font size=2>'
               || NULL
               || '</td> <td nowrap align=middle font color=#000000 face="Arial, Helvetica, Geneva, sans-serif" bgcolor="#eeeeee"><font size=2>'
               || NULL
               || '</td> <td nowrap align=left font color=#000000 face="Arial, Helvetica, Geneva, sans-serif" bgcolor="#eeeeee"><font size=2>'
               || i.remarks
               || '</td> </tr> </tbody> ';
         END;
      END LOOP;

      l_body := l_body || '</table>';
      document := l_body;
--
--Setting document type which is nothing but MIME type
--
      document_type := 'text/html';
   EXCEPTION
      WHEN OTHERS
      THEN
         document := '<H4>Error: ' || SQLERRM || '</H4>';
   END get_sample_details;


    
    
END  cust_sample_notify;
/

Tags: Oracle Work Flow Builder,