Import command for importing an XML Page into the database.

import C:\Users\josyamsx\Downloads\p9879989_R12_GENERIC\jdevbin\jdev\myclasses\oracle\apps\xxoxyintf\webui\CRCPoetFinderPG.xml -username apps -password onecald2014 -dbconnection "(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=ckclora1-d.calnet.ads) (PORT=1571)) (CONNECT_DATA=(SID=ONECALD)))" -rootDir C:\Users\josyamsx\Downloads\p9879989_R12_GENERIC\jdevbin\jdev\myclasses -rootPackage /

Learn SOA

How to build an XSD?
Watch the below youtube video for learning how to build an xsd using JDeveloper (11g)

https://www.youtube.com/watch?v=250pkV5F400

https://www.youtube.com/watch?v=o62ST-82cM0

--Connect as System user

CONNECT <USER-NAME>/<PASSWORD>@<DATABASE NAME>;

 

--Create user query

CREATE USER <USER NAME> IDENTIFIED BY <PASSWORD>;

 

--Provide roles

GRANT CONNECT,RESOURCE,DBA TO <USER NAME>;

 

--Provide privileges

GRANT CREATE SESSION TO <USER NAME>;

 

GRANT UNLIMITED TABLESPACE TO <USER NAME>;

 

--Provide access to tables.

GRANT SELECT,UPDATE,INSERT ON <TABLE NAME> TO <USER NAME>;

Get OnHand Quantities using API

PROCEDURE get_onhand_quantity (p_item_id IN NUMBER, p_organization_id IN NUMBER, p_onhand_qty OUT NUMBER)
  IS
   x_return_status         VARCHAR2 (50);
   x_msg_count             VARCHAR2 (50);
   x_msg_data              VARCHAR2 (50);
   v_item_id               NUMBER;
   v_org_id                NUMBER;
   v_qoh                   NUMBER;
   v_rqoh                  NUMBER;
   v_atr                   NUMBER;
   v_att                   NUMBER;
   v_qr                    NUMBER;
   v_qs                    NUMBER;
   v_lot_control_code      BOOLEAN;
   v_serial_control_code   BOOLEAN;
BEGIN
   v_qoh := NULL;
   v_rqoh := NULL;
   v_atr := NULL;
   v_lot_control_code := FALSE;
   v_serial_control_code := FALSE;
   -- Set the org context
   --fnd_client_info.set_org_context (83);
   fnd_global.apps_initialize (17267, 20539, 401);
   -- Call API
   inv_quantity_tree_pub.query_quantities (
   p_api_version_number => 1.0,
    p_init_msg_lst             => 'F',
    x_return_status            => x_return_status,
    x_msg_count                => x_msg_count,
    x_msg_data                 => x_msg_data,
    p_organization_id          => p_organization_id,
    p_inventory_item_id        => p_item_id,
    p_tree_mode                => apps.inv_quantity_tree_pub.g_transaction_mode, -- or 3
    p_is_revision_control      => FALSE,
    p_is_lot_control           => v_lot_control_code,  -- is_lot_control,
    p_is_serial_control        => v_serial_control_code,
    p_revision                 => NULL,        -- p_revision,
    p_lot_number               => NULL,           -- p_lot_number,
    p_lot_expiration_date      => SYSDATE,
    p_subinventory_code        => NULL,    -- p_subinventory_code,
    p_locator_id               => NULL,           -- p_locator_id,
    -- p_cost_group_id            => NULL,       -- cg_id,
    p_onhand_source            => 3,
    x_qoh                      => v_qoh,      -- Quantity on-hand
    x_rqoh                     => v_rqoh,           --reservable quantity on-hand
    x_qr                       => v_qr,
    x_qs                       => v_qs,
    x_att                      => v_att,  -- available to transact
    x_atr                      => v_atr    -- available to reserve
   );
   dbms_output.put_line ('api return status is '||x_return_status);
   DBMS_OUTPUT.put_line ('On-Hand Quantity: ' || v_qoh);
   DBMS_OUTPUT.put_line ('Available to reserve: ' || v_atr);
   DBMS_OUTPUT.put_line ('Quantity Reserved: ' || v_qr);
   DBMS_OUTPUT.put_line ('Quantity Suggested: ' || v_qs);
   DBMS_OUTPUT.put_line ('Available to Transact: ' || v_att);
   DBMS_OUTPUT.put_line ('Available to Reserve: ' || v_atr);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM); 
  END get_onhand_quantity;

Email from PLSQL Procedure

DECLARE
   c           UTL_SMTP.connection;
   v_subject   VARCHAR2 (180) := 'Monthly Interest Owners Reports';
   v_msg       VARCHAR2 (32000)
      := '   Please click on the following link to access the most recent Monthly Interest Owners Reports: \\ckcndsk1\OneCalP_WebMethod_Top\WebMethods\pos\data\outbound\Evergreen
   Please note the following differences and limitations of the reports:
1. The information contained in the reports is only current through the day before the reports are pulled. Any changes in owner information or status or any changes to decks or new decks created after the day before the reports are pulled will not be displayed until the new reports are pulled.
2. Area Report;
    a. Each Cost Center listed on this report, regardless of which county it falls under, will contain a complete list of the owners on the deck.
    b. Some Fields and Cost Centers may appear under multiple counties or the incorrect county. For the best results, search by Cost Center.
3. Pre-Checkwrite Value Reports;
    a. All owners, including suspended owners, are included on this report.
    b. Working interest owners; expenses have not been deducted from the listed revenue.
    c. The amount of funds listed in suspended owners; accounts includes any funds which were placed into the owners; accounts in previous months for those consecutive months the owners are held in suspense. This can result in the same funds being counted multiple times.
    d. Funds transferred from one owner;s account to another are counted on both owners; accounts.
4. Post Checkwrite Value Reports;
    a. Working interest owners; expenses have been deducted from the revenue for working interest owners with a ;N; Net Bill Indicator.
    b. Displays actual payments made by only pulling payments that owners have cleared.
    i. The most recent month;s payment is not included in this report, because Accounting updates ACH/check status the month after the payments are made to the owners.
    c. Funds transferred from one owner;s account to another are only counted once, on the second owner;s account.
    d. Owners in suspense are not included on this report. In order for an owner to be included on this report, they have to have cleared the payment they received from CRC.
    e. Working interest owners; expenses have not been deducted from the listed revenue for those working interest owners with a ;Y; Net Bill Indicator.';
   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 ('mail.crc.com');
   UTL_SMTP.helo (c, 'foo.com');
   UTL_SMTP.mail (c, 'sender@foo.com');
   UTL_SMTP.rcpt (c, 'sudhamsu.josyam@crc.com');
   UTL_SMTP.open_data (c);
   send_header ('From', '"System" <noreply@crc.com>');
   send_header ('To', '"Recipient" <sudhamsu.josyam@crc.com>');
   send_header ('Subject', v_subject);
   UTL_SMTP.write_data (c, UTL_TCP.CRLF || v_msg);
   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;
      END;
      raise_application_error (
         -20000,
         'Failed to send mail due to the following error: ' || SQLERRM);
END;
        
       

Checks and Vendor Information for a Payment Process Request.

select --idp.calling_app_doc_ref_number invoice_number

pv.vendor_name, idp.*

from apps.iby_pay_service_requests ipsr,

apps.iby_pay_instructions_all ipi,

apps.iby_payments_all ip,

apps.iby_docs_payable_all idp,

apps.po_vendors pv --, apps.po_vendor_sites_all pvs

where ipsr.payment_service_request_id = ipi.payment_service_request_id

and ipi.payment_instruction_id = ip.payment_instruction_id

and ip.payment_id = idp.payment_id

and pv.party_id = idp.payee_party_id

--and pvs.vendor_id = pv.vendor_id

and call_app_pay_service_req_code = 'PPR Name here';

How to call InitQuery in OAF Page

in Application Module Impl.java

    public void initQuery(String ProjectNumber, String TaskNumber) {
     System.out.println("Entered initQuery");
     CRCPoetFinderVOImpl vo = (CRCPoetFinderVOImpl)getCRCPoetFinderVO1();
     //vo.setWhereClause(null);
     System.out.println("Value passed to ProjectNumber is "+ProjectNumber);
     System.out.println("Value passed to TaskNumber is "+TaskNumber);
     //vo.setWhereClause("project_number = :1 and task_number = :2");
     vo.setWhereClause(" project_number = "+"'"+ProjectNumber+"' and task_number = '"+TaskNumber+"'");
     //vo.setWhereClauseParams(null);
     //vo.setWhereClauseParam(0, ProjectNumber);
     //vo.setWhereClauseParam(1, TaskNumber);
     vo.executeQuery();
    }

In CO

    OAApplicationModule am = pageContext.getApplicationModule(webBean);
    //ViewObject vo = am.findViewObject("CRCPoetFindVO1");
    String ProjectNum = pageContext.getParameter("ProjectNumber") ;
    String TaskNum = pageContext.getParameter("TaskNumber");
    Serializable[] parameters = {ProjectNum, TaskNum};
    System.out.println("AM Method is called");
    am.invokeMethod("initQuery", parameters);