Thursday, January 7, 2016

How to find all cancel Requisitions


SELECT prha.*
  FROM po_Requisition_headers_all prha, po_action_history pah
 WHERE     1 = 1
       AND pah.object_id = prha.requisition_header_id
       AND action_code = 'CANCEL'
       AND pah.object_type_code = 'REQUISITION';


Relation Between Requisition and PO


SELECT prha.segment1 "REQ NUM", pha.segment1 "PO NUM"
  FROM po_headers_all pha,
       po_distributions_all pda,
       po_req_distributions_all prda,
       po_requisition_lines_all prla,
       po_requisition_headers_all prha
 WHERE     pha.po_header_id = pda.po_header_id
       AND pda.req_distribution_id = prda.distribution_id
       AND prda.requisition_line_id = prla.requisition_line_id
       AND prla.requisition_header_id prha.requisition_header_id


Monday, November 2, 2015

BACK ORDERS QUERY in Oracle Apps



SELECT *
  FROM oe_order_headers_all ooha,
       wsh_delivery_details wdd,
       oe_order_lines_all oola
 WHERE     ooha.header_id = oola.header_id
       AND ooha.header_id = wdd.source_header_id
       AND wdd.released_status = 'B'
--       AND ooha.ordered_date > SYSDATE - 1
--       AND oola.ordered_item = 'NBR'                                 -- SKU
       AND NOT EXISTS
                  (SELECT 1
                     FROM wsh_delivery_details wdd1
                    WHERE     wdd1.released_status != 'B'
                          AND wdd1.source_header_id = ooha.header_id)

Friday, September 4, 2015

How to Check Header/Line WORKFLOW status from Back end?

We can check the workflow status from back end also for Header or Line with below query.

    SELECT wias.item_key,
         wpa.activity_name,
         wias.activity_status,
         wias.activity_result_code,
         wias.assigned_user,
         wias.begin_date,
         wias.end_date
    FROM WF_ITEM_ACTIVITY_STATUSES wias, WF_PROCESS_ACTIVITIES wpa
   WHERE     wias.process_activity = wpa.instance_id(+)
         AND wpa.process_item_type = wias.item_type
         AND wias.ACTIVITY_STATUS = 'ERROR'
         AND wias.end_date IS NULL
         AND wias.item_key = TO_CHAR (12345)           -- Header_id or Line_id
ORDER BY begin_date DESC


Friday, January 30, 2015

How To Add new columns into Standard Work Order Report in eAM



  1. Download the report (rdf & rtf) from server. 
  2.  Rename the report by prefixing xx or anything you wish. 
  3.  Add the required columns in rdf by changing the query.
  4. Add the columns in rtf and give the xml tags.
  5. Register the concurrent program as custom name, attach to request group, add to responsibility and check the report. 
      
       For More Information:
      
                 https://docs.oracle.com/cd/E18727_01/doc.121/e13670/T259967T259972.htm

Tuesday, April 29, 2014

API to create Item Category

The item categories can be created using item import when Items are created.
They can also be created using APIs discussed below
Following are the steps to Check and/or Create Item Categories
1) Create Category Segment combinations
2) If the enforce list is checked for category then insert category combinations in the enforce list
3) Create/Update/Delete Category Assignments on Item

Create Category Segment combinations
Following APIs can be used to create/update/delete category combinations.

INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY
(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_REC  IN    INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE,
  X_CATEGORY_ID  OUT    NUMBER
)

INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY
(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_REC  IN    INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE
)

INV_ITEM_CATEGORY_PUB.DELETE_CATEGORY
(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_ID  IN    NUMBER
)


Use following API for assigning a category to a category set. A category will be available in the list of valid categories for a category set only if it is assigned to the category set. This is a required step if for categories enforce list is checked on.

INV_ITEM_CATEGORY_PUB.CREATE_VALID_CATEGORY
(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  P_CATEGORY_SET_ID  IN    NUMBER,
  P_CATEGORY_ID  IN    NUMBER,
  P_PARENT_CATEGORY_ID  IN    NUMBER,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2
)


Following APIs can be used to create/update/delete Item category assignments.

INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY_ASSIGNMENT
(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_ID  IN    NUMBER,
  P_CATEGORY_SET_ID  IN    NUMBER,
  P_INVENTORY_ITEM_ID  IN    NUMBER,
  P_ORGANIZATION_ID  IN    NUMBER
)

INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY_DESCRIPTION
(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_ID  IN    NUMBER,
  P_DESCRIPTION  IN    VARCHAR2
)

INV_ITEM_CATEGORY_PUB.DELETE_CATEGORY_ASSIGNMENT
(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_ID  IN    NUMBER,
  P_CATEGORY_SET_ID  IN    NUMBER,
  P_INVENTORY_ITEM_ID  IN    NUMBER,
  P_ORGANIZATION_ID  IN    NUMBER
)