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"));