General Queries In Oracle Apps

1.Query to find the Flexfields and valuesets

     SELECT   A.ID_FLEX_STRUCTURE_CODE,
                   B.ID_FLEX_CODE,
                   E.LANGUAGE,
                   D.FLEX_VALUE,
                   E.DESCRIPTION,
                   E.FLEX_VALUE_MEANING,
                   B.FLEX_VALUE_SET_ID,
                   B.APPLICATION_COLUMN_NAME,
                   B.SEGMENT_NAME,
                   C.FLEX_VALUE_SET_NAME,
                   D.FLEX_VALUE_ID                
        FROM     APPS.fnd_id_flex_structures A,
                   APPS.fnd_id_flex_segments B,
                   APPS.fnd_flex_value_sets C,
                   APPS.fnd_flex_values D,     
                   APPS.fnd_flex_values_tl E      
        WHERE  A.ID_FLEX_NUM=B.ID_FLEX_NUM
         --AND   A.ID_FLEX_CODE='GL#'
     --AND   B.ID_FLEX_CODE='GL#'
         AND   B.FLEX_VALUE_SET_ID=C.FLEX_VALUE_SET_ID
         AND   C.FLEX_VALUE_SET_ID=D.FLEX_VALUE_SET_ID
         AND   D.FLEX_VALUE_ID=E.FLEX_VALUE_ID
         AND   A.APPLICATION_ID IN (101,201)
         AND   E.LANGUAGE='US'
         ORDER BY A.ID_FLEX_STRUCTURE_CODE
       
2.Query to find the Responsibilites

  SELECT B.RESPONSIBILITY_ID,
                 A.RESPONSIBILITY_NAME,
                 A.LANGUAGE,
                 B.RESPONSIBILITY_KEY ,
                 B.APPLICATION_ID
  FROM APPS.FND_RESPONSIBILITY_TL A,
       APPS.FND_RESPONSIBILITY B
  WHERE A.RESPONSIBILITY_ID(+)=B.RESPONSIBILITY_ID
  AND A.LANGUAGE = USERENV('LANG')
  ORDER BY A.RESPONSIBILITY_NAME

3. Query to find the Descriptive Field information for PO and GL

select flex_vl.title,flex.descriptive_flexfield_name,
 context_vl.descriptive_flex_context_code,context_vl.description,
 col_usage_vl.application_column_name, col_usage_vl.end_user_column_name
from apps.FND_DESCRIPTIVE_FLEXS flex,
apps.FND_DESCRIPTIVE_FLEXS_vl flex_vl
,apps.FND_DESCR_FLEX_CONTEXTS_VL context_vl
,apps.FND_DESCR_FLEX_COL_USAGE_VL col_usage_vl
where flex_vl.application_id in (201,101)
and flex_vl.title = 'Approved Supplier List'
and  flex.descriptive_flexfield_name = flex_vl.descriptive_flexfield_name

--'PO_APPROVED_SUPPLIER_LIST'
and  flex.descriptive_flexfield_name = context_vl.descriptive_flexfield_name
and  flex.descriptive_flexfield_name = col_usage_vl.descriptive_flexfield_name
and  col_usage_vl.descriptive_flex_context_code = context_vl.descriptive_flex_context_code

--'2346'
order by col_usage_vl.descriptive_flex_context_code,col_usage_vl.application_column_name

4.Query to find the Menus
 
SELECT
  B.ROWID ROW_ID,
  B.MENU_ID,
  B.MENU_NAME,
  B.TYPE,
  B.LAST_UPDATE_DATE,
  B.LAST_UPDATED_BY ,
  B.LAST_UPDATE_LOGIN ,
  B.CREATION_DATE ,
  B.CREATED_BY ,
  T.USER_MENU_NAME ,
  T.DESCRIPTION,
  T.LANGUAGE,
  C.SUB_MENU_ID
 FROM
  apps.FND_MENUS_TL T,
  apps.FND_MENUS B,
  apps.FND_MENU_ENTRIES C
 -- (SELECT USER_MENU_NAME,MENU_ID FROM FND_MENUS_TL)SUB
WHERE
  B.MENU_ID = T.MENU_ID
  AND T.LANGUAGE = USERENV('LANG')
  --AND T.USER_MENU_NAME = 'Activity Based Management'
  AND B.MENU_ID=C.MENU_ID


5.Query to find the Responsibility,Attached Menu and Application to the
particular responsibility

SELECT   distinct
                 B.RESPONSIBILITY_ID,
                 A.RESPONSIBILITY_NAME,
                 A.LANGUAGE,
                 B.RESPONSIBILITY_KEY ,
                 B.APPLICATION_ID,
                 C.USER_MENU_NAME,
                 E.APPLICATION_NAME             
  FROM APPS.FND_RESPONSIBILITY_TL A,
       APPS.FND_RESPONSIBILITY B,
           APPS.FND_MENUS_TL C,
       APPS.FND_MENUS D,
       apps.FND_APPLICATION_TL E,
           apps.FND_APPLICATION F
  WHERE A.RESPONSIBILITY_ID(+)=B.RESPONSIBILITY_ID
  AND   B.MENU_ID=C.MENU_ID
  AND   B.MENU_ID=D.MENU_ID
  AND   E.APPLICATION_ID=F.APPLICATION_ID
  AND   F.APPLICATION_ID=B.APPLICATION_ID
  AND   A.LANGUAGE='US'

6.Vendor Extraction Query

select
  d.vendor_name
, d.segment1 GSL_NUmber
, e.vendor_site_code
, e.address_line1
, e.address_line2
, e.address_line3
, e.city
, e.state
, e.zip
, e.province
, e.country
, substr(e.attribute14,1,3)
, d.last_update_date
, d.last_updated_by
, d.creation_date
, d.created_by
, d.set_of_books_id
,e.vendor_site_code
,e.vendor_site_code_alt
,e.purchasing_site_flag
,e.pay_site_flag
,e.address_line1
,e.address_lineS_alt
,e.address_line2
,e.address_line3
,e.city
,e.state
,e.zip
,e.province
,e.country
,e.phone
,e.customer_num
,e.ship_to_location_id
,e.bill_to_location_id
,e.ship_via_lookup_code
,e.freight_terms_lookup_code
,e.payment_method_lookup_code
,e.terms_date_basis
,e.accts_pay_code_combination_id
,e.prepay_code_combination_id
,e.payment_priority
,e.termS_id
,e.invoice_amount_limit
,e.pay_date_basis_lookup_code
,e.invoice_currency_code
,e.payment_currency_code
,e.hold_unmatched_invoices_flag
,e.ap_tax_rounding_rule
,e.auto_tax_calc_flag
,e.auto_tax_calc_override
,e.address_line4
,e.county
,e.match_option
FROM PO.po_vendors D
    ,PO.PO_VENDOR_SITES_ALL E
WHERE E.vendor_id = D.vendor_id
AND org_id in (2265)

 -- Initializing the default values from System Parameters

    -- Get default values from Payable Options                                                   

                                                                                                 

                                                     
    BEGIN
      SELECT auto_tax_calc_flag,
             auto_tax_calc_override,
             amount_includes_tax_flag,
             pay_date_basis_lookup_code,
             hold_unmatched_invoices_flag 
      FROM   ap_system_parameters_all
      WHERE  set_of_books_id = l_sob_id
      AND    org_id = l_org_id;     

           -- Get default values from Financial Options (DFT02) 
    
     BEGIN
       SELECT match_option,
              ship_to_location_id,
              bill_to_location_id,
              ship_via_lookup_code,
              freight_terms_lookup_code,
              tax_rounding_rule
       FROM financials_system_params_all
       WHERE set_of_books_id = l_sob_id
       AND  org_id =  l_org_id;  
          
          
            SELECT term_id
     FROM   ap_terms
    WHERE  name = vendor_record.terms_name
    AND ENABLED_FLAG='Y'
    AND (END_DATE_ACTIVE > SYSDATE OR END_DATE_ACTIVE IS NULL);

7. Extraction Query for Blanket Purchase Agreement:


SELECT pha.segment1 po_number, pha.type_lookup_code "PO TYPE", pha.revision_num,
       pha.creation_date "PO DATE", pha.vendor_id,pv.vendor_name,
       pha.vendor_site_id,pvs.vendor_site_code,
       bill_hrl.location_code "Bill To",
       ship_hrl.location_code "Ship To",
       pha.authorization_status,
      -- pav.agent_name "Buyer",
       ppf.full_name "Buyer",
       pha.currency_code,pla.line_num,
       pla.item_id,
       (SELECT DISTINCT segment1 FROM apps.mtl_system_items
        WHERE  inventory_item_id=pla.item_id ) item_name,
       pla.item_description,
       pla.category_id,
       (SELECT DISTINCT segment1||'.'||segment2
        FROM apps.mtl_categories
        WHERE  category_id=pla.category_id) "Category"       ,                
       pla.line_type_id,
       (SELECT DISTINCT line_type
        FROM apps.po_line_types
        WHERE line_type_id = pla.line_type_id) line_type,
       pla.unit_meas_lookup_code,pla.unit_price, 
           pla.tax_name
           --ats.name TAX_CODE                   
FROM
       apps.po_headers_all pha,
       apps.po_lines_all pla,
       apps.po_vendors pv,
       apps.po_vendor_sites_all pvs,
       apps.po_agents_v pav,
       apps.hr_locations bill_hrl,
       apps.hr_locations ship_hrl,
--         apps.po_agents_v pav,
       apps.per_all_people_f ppf
           --apps.ap_tax_codes ats    
WHERE
       pha.po_header_id = pla.po_header_id
   AND pha.vendor_id = pv.vendor_id
   AND pha.vendor_id = pvs.vendor_id
   AND pha.vendor_site_id = pvs.vendor_site_id(+)
   --AND pla.tax_code_id=ats.tax_id
   --AND pha.agent_id = pav.agent_id(+)
   AND pha.agent_id = ppf.person_id
   AND pha.type_lookup_code IN ('BLANKET','STANDARD')
   AND NVL (pha.closed_code, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED')
   AND NVL (pla.closed_code, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED')
   AND pha .org_id =2265
   AND bill_hrl.location_id = pha.bill_to_location_id
   AND ship_hrl.location_id = pha.ship_to_location_id
   ORDER BY 1 DESC

Link between Internal requisition and Internal sales order

1. OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_LINE_ID =

    po_requisition_lines_all.REQUISITION_LINE_ID

2.  OE_ORDER_LINES_ALL.ORIG_SYS_DOCUMENT_REF=        
      po_requisition_HEADERs_all.SEGMENT1(Requisition Number)
3.  OE_ORDER_HEADERS_ALL.ORIG_SYS_DOCUMENT_REF=          
      po_requisition_HEADERs_all.SEGMENT1(Requisition number)

Assigning random serial numbers and ship confirm thru API

 
For I in Delivery_id Loop
L_CHANGED_ATTRIBUTES (L_INDEX2).DELIVERY_DETAIL_ID := I.DELIVERY_DETAIL_ID;
L_CHANGED_ATTRIBUTES (L_INDEX2).SHIPPED_QUANTITY :=   I.SHIPPED_QTY;--
For J in serial_table loop
L_SERIAL_ATTRIBUTES (L_INDEX3).DELIVERY_DETAIL_ID := I.DELIVERY_DETAIL_ID;
L_SERIAL_ATTRIBUTES (L_INDEX3).FROM_SERIAL_NUMBER := J.SERIAL_NUMBER;
L_SERIAL_ATTRIBUTES (L_INDEX3).TO_SERIAL_NUMBER := J.SERIAL_NUMBER;
L_SERIAL_ATTRIBUTES (L_INDEX3).QUANTITY :=1;

WSH_DELIVERY_DETAILS_PUB.UPDATE_SHIPPING_ATTRIBUTES
(P_API_VERSION_NUMBER => 1.0,
P_INIT_MSG_LIST => INIT_MSG_LIST,
P_COMMIT => L_COMMIT,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA,
P_CHANGED_ATTRIBUTES => L_CHANGED_ATTRIBUTES,
P_SOURCE_CODE => L_SOURCE_CODE,
P_SERIAL_RANGE_TAB => L_SERIAL_ATTRIBUTES
);
End Loop --For I
End Loop -- For J 

Displaying current row and next row

SELECT    Hire_date,
                  Batch_id,
                  Status     Current_Status,
                  LEAD(Status)  Over(order by Hire_date) Previous_Status
FROM      HBC
WHERE    Batch_id=4; 

Output:

Hire_date               Batch_id            Current_Status  Previous_Status
07-Mar-2011          4                         2                            3
08-Mar-2011          4                         3                            4
09-Mar-2011          4                         4                           

Displaying current row and previous row

This query will display current row and its previous row.

For example:

Table->HBC

Hire_date               Batch_id            Status
07-Mar-2011          4                         2
08-Mar-2011          4                         3
09-Mar-2011          4                         4

Query:

SELECT    Hire_date,
                  Batch_id,
                  Status     Current_Status,
                  LAG(Status)  Over(order by Hire_date) Previous_Status
FROM      HBC
WHERE    Batch_id=4; 

Output:

Hire_date               Batch_id            Current_Status  Previous_Status
07-Mar-2011          4                         2                           
08-Mar-2011          4                         3                             2
09-Mar-2011          4                         4                             3

Item Conversion steps

Item Conversion

You can import items from any source into Oracle Inventory using the Item Interface

Following is the approach for Item conversion.

Step 1:

We will get the flat file from the customer.

Flat file types:  .csv, .txt, .xls, .xml

Step 2:

Create a staging table based on the flat file structure.

Create the error table.

Step 3:

Create the control file to transfer the data from flat file to staging table.

SQL * Loader:

Following are the file we will come across while working with SQL* Loader.

Control file .ctl             à to transfer the data from flat file to staging table.

Flat file .csv                 à raw data file which we will receive from legacy system

Log file .log                  à It will display the execution of the program.

Bad file .bad                à These records could have been rejected by SQL*Loader

Discard file .dis           à It will contain records that didn't meet the criteria.

Step 4 :

Run the control file.

Now the data has been transferred to staging table.

Step 5:

Create a PL/SQL validation program to validate the records and to transfer the records to the interface table.

The item interface table MTL_SYSTEM_ITEMS_INTERFACE contains every column in the Oracle Inventory item master table, MTL_SYSTEM_ITEMS_B. The columns in the item interface correspond directly to those in the item master table.

MTL_ITEM_CATEGORIES_INTERFACE

MTL_ITEM_REVISIONS_INTERFACE

Required Columns for MTL_SYSTEM_ITEMS_INTERFACE

ITEM_NUMBER

DESCRIPTION

ORGANIZATION_CODE

PROCESS_FLAG

TRANSACTION_TYPE

SET_PROCESS_ID

Validations for Item Import:

1)    transaction_type should be ‘create’

2)    ORGANIZATION CODE should be exist.

3)    Item number should not be exist.

4)    Description should be not null.

5)    Item template should be exist and valid.

6)    Item and organization combination should not exist.

7)     ccid of cogs account should be exist.

8)    PROCESS_FLAG should be 1.

9)    UOM should be exist.

Step 6:

If the record is validated then status_flag should be updated with ‘V’.

If the record is error out then status_flag should be updated with ‘E’ and the record should be inserted into custom error table.

Step 7:

PL/SQL program will pick the validate records and then inserted into the interface table.

Step 8:

Run the standard concurrent program to import items to the base table.

Base tables:

MTL_SYSTEM_ITEMS_B

Error table:

MTL_INTERFACE_ERRORS

Other URL

http://oracleappstechworld.blogspot.com/2009_05_01_archive.html