Tuesday, 16 April 2013

Oracle DBA Queries

-- Database Details
SELECT * FROM v$database

-- Instance Details
SELECT * FROM v$instance

-- License Details 

SELECT * FROM v$license

-- Version Details
SELECT * FROM v$version

--Release Details
SELECT * FROM apps.fnd_product_groups

-- Patch Details
SELECT * FROM ad_applied_patches
SELECT * FROM ad_bugs


-- Concurrent Manager

SELECT concurrent_queue_name,
       user_concurrent_queue_name,
       description,
       enabled_flag
  FROM apps.fnd_concurrent_queues_vl fcq
 WHERE user_concurrent_queue_name LIKE 'AA%'



-- Partitioning Installed
SELECT DECODE (COUNT (*), 0, 'No', 'Yes') partitioning
FROM (SELECT 1
FROM dba_part_tables
WHERE owner NOT IN ('SYSMAN', 'SH', 'SYS', 'SYSTEM') AND ROWNUM = 1);

-- Spatial Installed
SELECT DECODE (COUNT (*), 0, 'No', 'Yes') spatial
FROM (SELECT 1
FROM all_sdo_geom_metadata
WHERE ROWNUM = 1);

-- RAC Installed
SELECT DECODE (COUNT (*), 0, 'No', 'Yes') rac
FROM (SELECT 1
FROM v$active_instances
WHERE ROWNUM = 1);

-- Unix Product Top Value
SELECT variable_name, value
FROM apps.fnd_env_context
WHERE variable_name = 'AP_TOP'
AND concurrent_process_id = 
(SELECT MAX (concurrent_process_id) FROM apps.fnd_env_context);

-- Command to Kill Session for Releasing Lock 
ALTER SYSTEM KILL SESSION '(sid, serial#)';


Monday, 15 April 2013

APPROVED SUPPLIER LIST Conversion in Oracle Apps r12


APPROVED SUPPLIER LIST CONVERSION

ASL is very helpful and purpose of ASL is to automate the Identification of the Supplier
to supply specific goods Or we cab say with ASL we are trying to automate the Source of Supply.
                or 
             
 Approved Supplier Lists in Oracle is the term used to describe a list of items and commodities
 that have approved sources from a list of suppliers.
             
Note - In Approve Supplier List (ASL)we define all the Supplier that are Approved to Supply a particular Item.



In ASL we store info. Like:
Ship- To
Ship – From
Also we store the status of the Supplier.

Status of Supplier in ASL are:
 Approve
 New
 Debarred
Along with the Statues , for each Supplier/Item combination in ASL we define the Rules
to indicate what action should allowed and Prevent

4 Action that we can assign to ASL are:
PO Allowed
Sourcing
Schedule Confirmation
Manufacture Link

Attribute that we define in ASL are:
Item
Business type
Status
Review By
Global etc

Navigation for Approved Supplier List:

PO Super User -> Supply base -> Approved Supplier List

Select the organization, then define the supplier for category or Item for which
you want to define, and status to approved.


API's used to insert the Approved Supplier List's are as follows:

po_asl_ths.insert_row
po_asl_attributes_ths.insert_row

base tables:
po_asl_attributes,po_approved_supplier_list

used columns:

         ORGANIZATION_NAME          ,
         VENDOR_BUSINESS_TYPE       ,
         STATUS                     ,
         ITEM                       ,
         VENDOR_NAME                ,
         VENDOR_SITE_CODE           ,
         COMMENTS                   ,
         CATEGORY_NAME              ,
         PRIMARY_VENDOR_ITEM        ,
         MANUFACTURERS_NAME         ,
         attribute1 to attribute 12 if required,
         COUNTRY_OF_ORIGIN_CODE     ,
         RELEASE_GENERATION_METHOD  ,
         PURCHASING_UNIT_OF_MEASURE ,
         SUPPLIER_CALENDAR

mandatory columns:      

ORGANIZATION_NAME
VENDOR_BUSINESS_TYPE
STATUS                     
ITEM                       
VENDOR_NAME                
VENDOR_SITE_CODE           
SUPPLIER_CALENDAR


validations:
.organization validation from org_organization_definitions
.item validation based on org from mtl_system_items_b
.supplir/vendor validation from ap_suppliers
.vendor site code validation based on vendor number from ap_supplier_sites_all
.calendar validation from BOM_CALENDARS

we can use api/direct base table..
using api:

BEGIN

     po_asl_ths.insert_row
                  (x_row_id                           => l_row_id,
                   x_asl_id                           => l_asl_id,
                   x_item_id                          => REC_po_asl_valid.inventory_item_id,
                   x_vendor_id                        => REC_po_asl_valid.vendor_id,
                   x_vendor_site_id                   => REC_po_asl_valid.vendor_site_id,
                   x_vendor_business_type             => REC_po_asl_valid.vendor_business_type,
                   x_asl_status_id                    => 1,
                   x_created_by                       => l_user_id,
                   x_creation_date                    => SYSDATE,
                   x_last_updated_by                  => l_user_id,
                   x_last_update_login                => NULL,
                   x_last_update_date                 => SYSDATE,
                   x_using_organization_id            => -1 ,
                   x_owning_organization_id           => REC_po_asl_valid.organization_id,
                   x_manufacturer_id                  => NULL,
                   x_category_id                      => NULL,
                   x_primary_vendor_item              => REC_po_asl_valid.primary_vendor_item,
                   x_manufacturer_asl_id              => NULL,
                   x_comments                         => NULL,
                   x_review_by_date                   => NULL,
                   x_attribute_category               => NULL,
                   x_attribute1                       => REC_po_asl_valid.attribute1,
                   x_attribute2                       => REC_po_asl_valid.attribute2,
                   x_attribute3                       => NULL,
                   x_attribute4                       => NULL,
                   x_attribute5                       => REC_po_asl_valid.attribute5,
                   x_attribute6                       => REC_po_asl_valid.attribute6,
                   x_attribute7                       => REC_po_asl_valid.attribute7,
                   x_attribute8                       => REC_po_asl_valid.attribute8,
                   x_attribute9                       => REC_po_asl_valid.attribute9,
                   x_attribute10                      => REC_po_asl_valid.attribute10,
                   x_attribute11                      => REC_po_asl_valid.attribute11,
                   x_attribute12                      => REC_po_asl_valid.attribute12,
                   x_attribute13                      => NULL,
                   x_attribute14                      => NULL,
                   x_attribute15                      => NULL,
                   x_disable_flag                     => NULL
                  );
 --write exception
end;      

using base table directly:

here we have 2tables to insert i.e

begin
 BEGIN
   SELECT PO_APPROVED_SUPPLIER_LIST_S.nextval
   INTO v_appr_supp_list
   FROM DUAL;
 EXCEPTION
   WHEN OTHERS THEN
     FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in generating the sequence number');
 END;

insert into po_asl_attributes
      (
        asl_id,
        using_organization_id,
        document_sourcing_method,
        release_generation_method,
        enable_plan_schedule_flag,
        enable_ship_schedule_flag,
        enable_autoschedule_flag,
        enable_authorizations_flag,
        enable_vmi_flag,
        vendor_id,
        vendor_site_id,
        item_id,
        country_of_origin_code,
        delivery_calendar,
        last_update_date,
        last_updated_by,
        creation_date,
        created_by,
        last_update_login
      )
      values
      (v_appr_supp_list,
        -1,
        'ASL',
        'CREATE_AND_APPROVE',
        'N',
        'N',
        'N',
        'N',
        'N',
        v_vendor_id,--vendor id
        v_vendor_site_id,--vendor site id
        v_inv_item,--inventory item
        REC_po_asl_valid.country_of_origin_code,
        REC_po_asl_valid.supplier_calendar ,
        sysdate,
        FND_GLOBAL.USER_ID,
        sysdate,
        FND_GLOBAL.USER_ID,
        FND_GLOBAL.LOGIN_ID
      );

insert into po_approved_supplier_list
      (
        asl_id,
        using_organization_id,
        owning_organization_id,
        vendor_business_type,
        asl_status_id,
        vendor_id,
        vendor_site_id,
        primary_vendor_item,
        item_id,
        attribute1,
        attribute2,
        attribute5,
        attribute6 ,
        attribute7 ,
        attribute8,
        attribute9 ,
        attribute10,
        attribute11,
        attribute12,
       last_update_date,
       last_updated_by,
      creation_date,
      created_by,
      last_update_login
      )
      values
      (
        PO_APPROVED_SUPPLIER_LIST_S.currval,
        -1,--DECODE(trim(cal.inventory_org), 'AMS', 85, 'LVD', 86, 'SSO', 88),   --- Prayas on 04-Dec-2005 -- -1,
        v_using_org_id,--org id
        'DIRECT',
        2,  --2-Approved
        v_vendor_id,--vendor id
        v_vendor_site_id,--vendor site id
        REC_po_asl_valid.primary_vendor_item,--REC_po_asl_valid.item, -- changed by sanjay 
        v_inv_item,--inventory item
        REC_po_asl_valid.attribute1,
        REC_po_asl_valid.attribute2,
        REC_po_asl_valid.attribute5,
        REC_po_asl_valid.attribute6 ,
        REC_po_asl_valid.attribute7 ,
        REC_po_asl_valid.attribute8,
        REC_po_asl_valid.attribute9 ,
        REC_po_asl_valid.attribute10,
        REC_po_asl_valid.attribute11,
        REC_po_asl_valid.attribute12,
        sysdate,
        FND_GLOBAL.USER_ID,
        sysdate,
        FND_GLOBAL.USER_ID,
        FND_GLOBAL.LOGIN_ID
      ); 

--exception
end;

     

Tuesday, 9 April 2013

How to Load the Data from FLAT FILE to TEMP Table Using SQL LOADER


Steps to Load Data from flat file to temp table using SQL Loader:

step1:data will given in flat file,it would be text file/table format etc
step2:to move the data from flat file to temp table,we use sql loader.
step3:save the flat file with name mytest.csv
step4:create contraol based on flat file data columns
   
i.e
open new notepad,
write below script i.e

loaddata
infile '/appldir/instancename/xxxxx/12.0.0/bin/mytest.csv'
infile '/appldir/instancename/xxxxx/12.0.0/bin/mytest.bad'
insert 
into table my_test_table fileds terminated by "," optionally enclosed by '"'
trailing nullcols
(company "trim(:company)"
,year "trim(:company)"
,consultant_num "trim(:consultant_num)"
,id "trim(:id)"
,promotion_code "trim(:promotion_code)"
)


save this script with mytest.ctl

step5:move this mytest.csv,mytest.ctl files to temp folder in server location using winscp and putty software
     
step6:open the putty software for running the commands
step7:run the pseudo code(depends upon the instances)
step8:cd /tmp
      in this only we have our files
step9:type this command "sqlldr"
      we get all the needful commands in sqlloader

step10:run this below command to load the data i.e

 sqlldr apps/apps1234 control='mytest.ctl' log='mytest.log' bad='mytest.bad' data='mytest.csv'

 apps/apps1234-->connecting to the instance with username and password

from the above command

mytest.log --this shows log files records
mytest.bad --this shows failed files records

step11:to check for the log or bad files of our file.write command as

ls -ltr.mytest*

it shows all the files with name as mytest.

step12:to see only log report use this command i.e

more mytest.log

This way we can load the data from flat file to temp tables using SQL Loader.

how to execute VIEW's and see the data in instance

begin
MO_GLOBAL.SET_ORG_CONTEXT('121',NULL,'SQLAP');
end;



--SQLAP-this is the application short name
--121-this is operating unit name

BOM Conversion issues with solutions in Oracle Apps r12


1)
Item CAR is a standard item.  Such items can only have Standard or ATO Standard items as components.  Please check these attributes for component TIER.
Solution:goto inventory-->items-->master items-->query with the "TIER"
goto order management tab,in this uncheck the check box of  pick components,assemblt to order  fileds.
now run import program,records will fetch from interface to base tables

2)
The item sequence number 5 you have entered for component TIER is not unique within the bill.
Solution: this will be data issue,go to bills of material -->bills-->bills
query for the PARENT COMPONENT of this child item(TIER)
After quering,next query child item under parent item,and see the item sequence number.in this you will find already number 5 is exist.
so,we can report it as date issue.


Tuesday, 2 April 2013

How to do buyer setup in R12 oracle apps?


goto
human resource responsibility-->
people-->
enter and maintain


last:Ram Manohar
first:Reddy
title:Mr.
gender:male
action:create employement
person type of action:Employee
identification:Employee and give <any number> besides it.
social security:<any number>
in personal tab-->birth date:<any date>
save this

click on assignment option
organization:<any>
job:<give any>
group:<give any>
status:active assignment
 in this agian go to supervisor tab
 name:<any name>
 save it.

 goto
 purchasing responsibility-->
 setup-->
 personal-->
 buyer-->
 click on add buyer and attatch our employee name i.e "Ram Manohar,Mr.Reddy"
 save it.

 now goto
 system administrator-->
 security-->
 user-->define
 query with our user name
 user details will be opened,in this attatche our employee name in person filed
 person:"Ram Manohar,Mr.Reddy"
 save it.

Monday, 1 April 2013

Supplier conversion issues and solution in Oracle Apps r12


1)issue:
we are getting below error while loading supplier sites using API.Hw to solve this issue?same issue you will get in the name of AP_INVALID_TCA_ERROR when you are running from interface.
in API you get error as:
Inconsistent data entered as compared to TCA records. For details, refer
to the Concurrent Program log.
in INTERFACE you get error as: AP_INVALID_TCA_ERROR
solution:The above issue is related to address validation.for this to solve,we need to set up profile,so that address related info will not be validated when u run through API /INTERFACE
Setting up Profile:
go to system administrator-->Profile-->system-->
application=Payables
profile :HZ: Address Validation Level for Application
press find,new screen opens,in this screen,under the application select 'no validation'
note:now duplicate site addresses will also taken care.