Query to get PO and Receipts details in fusion

SELECT pha.segment1 PO_NUMBER,
  PHA.START_DATE,
  pha.creation_date po_creation_date,
  pha.end_date,
  pha.APPROVED_FLAG,
  PersonName.FULL_NAME buyer_name,
  ps.vendor_name,
  pss.vendor_site_code site_name,
  PLA.line_num,
  pla.UNIT_PRICE,
  pla.QUANTITY,
  pla.UOM_CODE po_line_uom,
  pla.UNIT_PRICE* pla.QUANTITY lineValue,
  'Receipt details Start' receipt_details,
  RSH.RECEIPT_NUM,
  rsl.LINE_NUM receipt_line_number,
  rsh.RECEIPT_SOURCE_CODE,
  rsh.SHIPPED_DATE,
  rsh.EXPECTED_RECEIPT_DATE,
  rsl.QUANTITY_SHIPPED,
  rsl.QUANTITY_RECEIVED,
  rsl.QUANTITY_DELIVERED,
  rsl.QUANTITY_RETURNED,
  rsl.QUANTITY_ACCEPTED,
  rsl.QUANTITY_REJECTED,
  rsl.UOM_CODE,
  rsl.ITEM_DESCRIPTION,
  rsl.SHIPMENT_LINE_STATUS_CODE,
  rsl.COMMENTS,
  rct.TRANSACTION_TYPE,
  (SELECT meaning
  FROM FND_LOOKUP_VALUES_TL
  WHERE LOOKUP_CODE =rct.TRANSACTION_TYPE
  AND lookup_type   = 'RCV_TRANSACTION_TYPE'
  and language='US'
  ) TRANSACTION_TYPE_meaning,
  rct.TRANSACTION_DATE
FROM RCV_SHIPMENT_HEADERS RSH,
  RCV_SHIPMENT_LINES RSL,
  PO_HEADERS_ALL PHA,
  PER_PERSON_NAMES_F_V PersonName,
  PO_LINES_ALL pla,
  PO_LINE_TYPES_B,
  POZ_SUPPLIERS_V ps,
  POZ_SUPPLIER_SITES_V pss,
  RCV_TRANSACTIONS rct
WHERE RSH.SHIPMENT_HEADER_ID    =RSL.SHIPMENT_HEADER_ID
AND RSL.po_header_id            =PHA.po_header_id
AND PersonName.PERSON_ID        =PhA.agent_id
AND pla.po_header_id            =PHA.po_header_id
AND PO_LINE_TYPES_B.line_type_id=pla.LINE_TYPE_ID
AND pla.PO_LINE_ID              =rsl.PO_LINE_ID
AND pha.VENDOR_ID               =ps.vendor_id
AND pss.VENDOR_ID               =ps.vendor_id
AND rct.SHIPMENT_HEADER_ID      = RSH.SHIPMENT_HEADER_ID
AND rsl.SHIPMENT_LINE_ID        =rct.SHIPMENT_LINE_ID

Date formatting in OAF


Convert the String object to java.sql.Date object using stringToDate() function from oracle.apps.fnd.framework.OANLSServices class like below.

java.sql.Date getClientSysdate(){
OADBTransaction trx = 
(OADBTransaction)getApplicationModule().getTransaction();
OANLSServices nls = trx.getOANLSServices();
oracle.jbo.domain.Date serverDate = trx.getCurrentDBDate();

java.util.Date javaClientDate = nls.getUserDate(serverDate);
long longDate = javaClientDate.getTime();
return new java.sql.Date(longDate);
}


if (gRow.getAttribute("PromisedDate")!=null)
  {
   ReferenceId= gRow.getAttribute("PromisedDate").toString();//.substring(0,19);
      pageContext.writeDiagnostics(this, " Promisedate :"+ReferenceId, 1);
     // Calendar calendar = Calendar.getInstance();
    //SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
      //String today = dateFormat.format(ReferenceId);     
    //pageContext.writeDiagnostics(this, " Promised DATE WITh FORMAT "+today,1);
        if(result==0)
        {
           pageContext.writeDiagnostics(this, "Setting PO date to DFF",1);
   SimpleDateFormat dt = new SimpleDateFormat("yyyy/mm/dd hh:mm:ss"); 
          
       gRow.setAttribute("Attribute1",dt.format(ReferenceId));
        // gRow.setAttribute("Attribute1","2015/02/15 12:01:10");
       gRow.setAttribute("AttributeCategory","28385");
       }

  }

Fusion Related stuff: How to find the role of a user in fusion

SELECT r.*
FROM fusion.per_users u,
fusion.per_roles_dn_tl r,
fusion.per_user_roles ur,
fusion.per_all_people_f f
JOIN fusion.per_email_addresses e
ON e.person_id = f.person_id
AND e.email_address_id = f.primary_email_id
AND e.email_type = 'W1'
WHERE TRUNC(SYSDATE) BETWEEN f.effective_start_date AND f.effective_end_date
AND u.person_id = f.person_id
AND u.active_flag = 'Y'
AND r.role_id = ur.role_id
AND ur.active_flag = 'Y'
AND ur.terminated_flag != 'Y'

Attribute Level validation in AM and CO:

exceptions.add(new OAAttrValException(111, "XxdhPaContValueWorkDoneVO1", row.getKey(), "Description", row.getAttribute("Description"), "XXDH", "XXDH_PO_CLAIM_AMOUNT_MSG"));

How to get the data from different environments:


 
    public void getDatafromApps(){
        ResultSet resultset=null;
     
     
        try {
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
            Connection conn = DriverManager.getConnection("jdbc:oracle:thin:user_name/password@//Host:port:SID");
            Statement statement = conn.createStatement();
            String sql="select segment1,type_lookup_code from apps.po_headers_all where po_header_id=117";
             resultset = statement.executeQuery(sql);
            if(resultset.next())
                           {
                                System.out.println("-----------------------");
                                System.out.println(resultset.getString(1));
                                Segment1=resultset.getString(1);
                                System.out.println(resultset.getString(2));
                                System.out.println("-----------------------");
                             
                           }
        }
        catch (SQLException e) {
            System.out.println(e.getMessage());
        }
 
    }
 
 
    //get the SOA details
 
 
    public void getSOAData(){
 
            char quotes ='"';
            ResultSet resultset=null;
            try {
                DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
                Connection conn = DriverManager.getConnection("jdbc:oracle:thin:user_name/password@//Host:port/serviceName");
                Statement statement = conn.createStatement();


                   String sql=    "select count(*) countOfrec    from SOA."+quotes+"_SAPTable"+quotes;
                   System.out.println(sql);
                   resultset = statement.executeQuery(sql);
                if(resultset.next())
                               {
                                    System.out.println("-----------------------");
                                    System.out.println(resultset.getString(1));
                                    countrec=resultset.getString(1);
                               
                                 
                                    System.out.println("-----------------------");
                                 
                               }
            }
         
            catch (SQLException e) {
                System.out.println(e.getMessage());
            }
                 
        }

How to get the data from different environments:


    //get the prod details
    public void getDatafromApps(){
        ResultSet resultset=null;
     
     
        try {
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
            Connection conn = DriverManager.getConnection("jdbc:oracle:thin:user_name/password@//Host:port:SID");
            Statement statement = conn.createStatement();
            String sql="select segment1,type_lookup_code from apps.po_headers_all where po_header_id=117";
             resultset = statement.executeQuery(sql);
            if(resultset.next())
                           {
                                System.out.println("-----------------------");
                                System.out.println(resultset.getString(1));
                                Segment1=resultset.getString(1);
                                System.out.println(resultset.getString(2));
                                System.out.println("-----------------------");
                             
                           }
        }
        catch (SQLException e) {
            System.out.println(e.getMessage());
        }
 
    }
 
 
    //get the SOA details
 
 
    public void getSOAData(){
 
            char quotes ='"';
            ResultSet resultset=null;
            try {
                DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
                Connection conn = DriverManager.getConnection("jdbc:oracle:thin:user_name/password@//Host:port/serviceName");
                Statement statement = conn.createStatement();


                   String sql=    "select count(*) countOfrec    from SOA."+quotes+"_SAPTable"+quotes;
                   System.out.println(sql);
                   resultset = statement.executeQuery(sql);
                if(resultset.next())
                               {
                                    System.out.println("-----------------------");
                                    System.out.println(resultset.getString(1));
                                    countrec=resultset.getString(1);
                               
                                 
                                    System.out.println("-----------------------");
                                 
                               }
            }
         
            catch (SQLException e) {
                System.out.println(e.getMessage());
            }
                 
        }

 to add fire action with param from controller:

Hashtable params = new Hashtable (1);
             params.put ("param1", pageName);
             Hashtable paramsWithBinds = new Hashtable(3);
           
             paramsWithBinds.put ("param2", new OADataBoundValueFireActionURL(bandamActualFlag, "{$PoHeaderId}"));
             paramsWithBinds.put ("param3", new OADataBoundValueFireActionURL(bandamActualFlag, "{$LINE_LOCATION_ID}"));
           
             paramsWithBinds.put ("param4", new OADataBoundValueFireActionURL(bandamActualFlag, "{$bandamActualFlag}"));
             FireAction fireAction = OAWebBeanUtils.getFireActionForSubmit (bandamMHDDate, "bandamMHDDateEvent", params, paramsWithBinds,false, false);
               bandamActualFlag.setFireActionForSubmit("bandamActualFlagEvent",params, paramsWithBinds,true,true);
               bandamMHDDate.setPrimaryClientAction(fireAction);