Thursday, August 1, 2013

Organization Query in Oracle Apps R12



select * from HR_LOCATIONS where trunc(sysdate) = trunc(creation_date) and location_id = 30982

select * from HR_ORGANIZATION_UNITS where trunc(sysdate) = trunc(creation_date)

select * from HR_ALL_ORGANIZATION_UNITS where trunc(sysdate) = trunc(creation_date)

select ORGANIZATION_ID,
 NAME,
 BUSINESS_GROUP_ID,
 LOCATION_ID,
 DATE_FROM,
 INTERNAL_EXTERNAL_FLAG,
 INTERNAL_ADDRESS_LINE,
 TYPE from HR_ALL_ORGANIZATION_UNITS where trunc(sysdate) = trunc(creation_date)

select * from HR_ORGANIZATION_INFORMATION where trunc(sysdate) = trunc(creation_date)

select ORG_INFORMATION_ID,
 ORGANIZATION_ID,
 ORG_INFORMATION_CONTEXT,
 ORG_INFORMATION1  from HR_ORGANIZATION_INFORMATION where trunc(sysdate) = trunc(creation_date)


All organizations are maintained in HR_ALL_ORGANIZATION_UNITS table and organization classifications like Legal entity or operating
unit information are stored in HR_ORGANIZATION_INFORMATION table. There are 3 views provided based on these 2 tables to easily find out
the Legal entity, Operating unit and Inventory organization viz:


select ORGANIZATION_ID,               
BUSINESS_GROUP_ID ,
NAME    ,                      
DATE_FROM   ,                  
DATE_TO                       
 from apps.HR_LEGAL_ENTITIES


select ORGANIZATION_ID,       
BUSINESS_GROUP_ID,
NAME,                  
DATE_FROM,             
DATE_TO,                       
SET_OF_BOOKS_ID  from HR_OPERATING_UNITS


select ORGANIZATION_ID,               
BUSINESS_GROUP_ID,             
USER_DEFINITION_ENABLE_DATE,
DISABLE_DATE,                  
ORGANIZATION_CODE,             
ORGANIZATION_NAME,             
SET_OF_BOOKS_ID,               
CHART_OF_ACCOUNTS_ID,          
INVENTORY_ENABLED_FLAG,        
OPERATING_UNIT,                
LEGAL_ENTITY  from ORG_ORGANIZATION_DEFINITIONS

Query to find few business groups set up in the instance :

select
   business_group_id,name
from per_business_groups
where lower(name) like '%vision corporation%'
order by name

Query to find SOBs set up in the instance :

select
   set_of_books_id,
   name sob_name,
   chart_of_accounts_id,
   chart_of_accounts_name,
   period_set_name calendar_period,
   accounted_period_type,
   user_period_type,
   currency_code
from gl_sets_of_books_v
where set_of_books_id=1

A very important query to find out  inventory organizations for an operating unit :

select
   organization_id,
   organization_code,
   organization_name,
   (select location_id from hr_all_organization_units ou
     where od.organization_id=ou.organization_id) location_id,
   user_definition_enable_date,
   disable_date,
   chart_of_accounts_id,
   inventory_enabled_flag,
   operating_unit,
   legal_entity,
   set_of_books_id,
   business_group_id
from org_organization_definitions od
where operating_unit=204 and ORGANIZATION_ID = 9073
order by organization_code


select * from org_organization_definitions where ORGANIZATION_NAME like 'M12%'--trunc(sysdate) = trunc(creation_date)

select *from mtl_parameters where ORGANIZATION_ID = 9073

select * from MTL_SECONDARY_INVENTORIES_FK_V where trunc(sysdate) = trunc(creation_date)

select * from MTL_SECONDARY_INVENTORIES where trunc(sysdate) = trunc(creation_date)

select * from MTL_UOM_CLASSES where trunc(sysdate) = trunc(creation_date)

select * from MTL_ITEM_LOCATIONS  where trunc(sysdate) = trunc(creation_date)


Sunday, March 24, 2013

Vendor contact Update API


DECLARE
   l_flag varchar2(10) default 'Y';
   --
   lv_return_status        VARCHAR2 (1);
   lv_msg_count            NUMBER;
   lv_msg_data             VARCHAR2 (2000);
--
   lv_vendor_CONTACT_id       ap_supplier_CONTACTS.vendor_CONTACT_id%TYPE;
   lv_vendor_site_id       ap_supplier_sites.vendor_site_id%TYPE;
   lv_vendor_id            ap_suppliers.vendor_id%TYPE;
  -- lv_vendor_site_rec      ap_vendor_pub_pkg.r_vendor_site_rec_type;
  -- lv_vendor_rec           ap_vendor_pub_pkg.r_vendor_rec_type;
   lv_vendor_contact_rec   ap_vendor_pub_pkg.r_vendor_contact_rec_type;
 
 
   ln_vendor_id            NUMBER;
   ln_vendor_site_id       NUMBER;
   ln_vendor_CONTACT_id       NUMBER;
--
     v_user_id NUMBER;
    v_resp_id NUMBER;
    v_resp_appl_id NUMBER;
 
    ----
   cursor cur_vendor is SELECT *
               FROM apps.XX_SUPPLIER_SITES_CONTACT WHERE SUPPLIER_CONTACT_ID = 294 ;

begin
   
 

   
      fnd_global.apps_initialize (1110,50622,200);
      mo_global.init ('S');

    --  lv_vendor_site_id := 4959;
     -- lv_vendor_id := 32652;
    --  lv_vendor_CONTACT_id := 294;
   
    for i in cur_vendor loop
   
      SELECT   vendor_id
               INTO ln_vendor_id
               FROM ap_suppliers
              WHERE vendor_id = i.SUPPLIER_id;
     
      SELECT vendor_site_id
              INTO ln_vendor_site_id
             FROM ap_supplier_sites_all
                 WHERE vendor_id = ln_vendor_id
            AND vendor_site_ID= i.SUPPLIER_site_ID;
   
      SELECT VENDOR_CONTACT_ID
      INTO ln_vendor_contact_id
      FROM ap_supplier_Contacts WHERE
       VENDOR_CONTACT_ID = I.SUPPLIER_CONTACT_ID;
   
       lv_vendor_site_id := ln_vendor_site_id;
      lv_vendor_id := ln_vendor_id;
      lv_vendor_CONTACT_id := ln_vendor_contact_id;
   
     SELECT  VENDOR_CONTACT_ID        ,
         VENDOR_SITE_ID            ,
        VENDOR_ID            
 
     ,  PER_PARTY_ID            ,
     RELATIONSHIP_ID            ,
     REL_PARTY_ID            ,
     PARTY_SITE_ID            ,
     ORG_CONTACT_ID            ,
        ORG_PARTY_SITE_ID
     INTO
     lv_vendor_contact_rec.vendor_contact_id,
     lv_vendor_contact_rec.VENDOR_SITE_ID,
      lv_vendor_contact_rec.VENDOR_ID,
        lv_vendor_contact_rec.PER_PARTY_ID,
        lv_vendor_contact_rec.RELATIONSHIP_ID,
        lv_vendor_contact_rec.REL_PARTY_ID,
        lv_vendor_contact_rec.PARTY_SITE_ID,
        lv_vendor_contact_rec.ORG_CONTACT_ID,
        lv_vendor_contact_rec.ORG_PARTY_SITE_ID
   
         FROM  po_vendor_contacts
          where vendor_contact_id= lv_vendor_CONTACT_id AND VENDOR_SITE_ID = lv_vendor_site_id AND VENDOR_ID = lv_vendor_id ;
            lv_vendor_contact_rec.inactive_date := trunc(sysdate-5); --fnd_api.g_miss_date;
           --lV_vendor_contact_rec.person_middle_name := 'Sekhar';
         
        ap_vendor_pub_pkg.update_vendor_contact
                           (p_api_version           => 1.0,
                            p_init_msg_list         => fnd_api.g_FALSE,
                            p_commit                => fnd_api.g_false,
                            p_validation_level      => fnd_api.g_valid_level_full,
                            p_vendor_contact_rec    => lv_vendor_contact_rec,
                            x_return_status         => lv_return_status,
                            x_msg_count             => lv_msg_count,
                            x_msg_data              => lv_msg_data
                           );
                           COMMIT;
   
     end loop;
   
     dbms_output.put_line('return_status: '||lV_return_status);
    dbms_output.put_line('msg_data: '||lV_msg_data);
    dbms_output.put_line('msg_count: '||lV_msg_count);
  IF lV_return_status ='U'
    THEN
    lV_msg_data := SUBSTR (lv_msg_data || SQLERRM, 1, 4000);
    END IF;

    DBMS_OUTPUT.put_line ('l_msg_data: ' || lV_msg_data);
   -- END;

    FOR I IN 1..lv_msg_count
    LOOP
    lv_msg_data := lv_msg_count||SUBSTR(FND_MSG_PUB.GET(p_encoded=>'T'),1,255);

 
    fnd_file.put_line(fnd_file.log,'Error occured while updating'||lv_msg_data);
    END LOOP ;

exception
when others then
    fnd_file.put_line(fnd_file.log,sqlcode||','||sqlerrm);
end;

Saturday, March 23, 2013

Loyality Extraction Anonymous procedure to fetch the loyality data in a particular location in RESA (Region wise *.CSV file has been created at a particular location using UTL Package)

---/rms-s9c/rms/d01/install_dump/oradb10g/dbadmin/rms_admin/utlfile/loylity declare CURSOR CUR_VAT_REGION IS SELECT vat_region FROM vat_region vr WHERE EXISTS ( SELECT 1 FROM store s WHERE EXISTS ( SELECT 1 FROM sa_exported WHERE sa_exported.store = s.store AND sa_exported.store BETWEEN 999 and 9999 AND TRUNC(sa_exported.exp_datetime) = '28-May-12' AND sa_exported.status = 'P' AND sa_exported.system_code = 'RMS' ) AND s.VAT_REGION = vr.vat_region ) ; ---This package is to get the region code to create the CSV store wise firstly CURSOR CUR_LOYALITY_DATA(p_vat_region number) IS SELECT sth.store, ---"Store No", sth.register, --"POS Terminal No", sth.ref_no2, --"Transaction No." , sth.tran_no, --"Receipt NO", to_char(sth.tran_datetime,'dd/mm/yyyy') tran_datetime, --"Transaction Date" , sth.ref_no4, --"Loyalty Scheme", sth.ref_no3, --"Loyalty Card No.", stt.TENDER_TYPE_ID, --"Tender Code ", stt.tender_amt, --"Tender Amount ", sth.value ---" Bill Amount " from sa_exported sexp , sa_tran_head sth, sa_tran_tender stt,store s where sexp.store_day_seq_no = sth.store_day_seq_no and sexp.tran_seq_no = sth.TRAN_SEQ_NO and sexp.store = sth.store and sexp.day = sth.day and sth.TRAN_SEQ_NO = stt.tran_seq_no and sth.store = stt.store and sth.day = stt.day and sexp.store = s.store and trunc(sexp.exp_datetime) = '28-May-12' and sth.status = 'P' and sth.tran_type in ('SALE','RETURN') and ref_no3 is not null and stt.tender_type_id != 7002 and SYSTEM_CODE = 'RMS' and sexp.status = 'P' and s.VAT_REGION = p_vat_region; l_id utl_file.file_type; v_time_stamp varchar2(40); BEGIN SELECT TO_CHAR(SYSDATE, 'DDMMYYYYHH12MISS') INTO v_time_stamp FROM DUAL; FOR i IN CUR_VAT_REGION LOOP l_id := utl_file.fopen('/rms-s9c/rms/d01/install_dump/oradb10g/dbadmin/rms_admin/utlfile',i.vat_region||'Loyalty_SM_VATREGION_'||v_time_stamp||'.csv','W'); FOR j IN CUR_LOYALITY_DATA(i.vat_region) LOOP utl_file.put_line(l_id, j.store||','|| j.register||','|| --j.tran_seq_no||','|| j.ref_no2||','|| j.tran_no||','|| j.tran_datetime||','|| j.ref_no4||','|| j.ref_no3||','|| j.TENDER_TYPE_ID||','|| j.tender_amt||','|| j.value||REPLACE(chr(13),'') ); END LOOP; --vNewLine := null; utl_file.fclose(l_id); END LOOP; EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20000, 'File location is invalid.'); WHEN UTL_FILE.INVALID_MODE THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20001, 'The open_mode parameter in FOPEN is invalid.'); WHEN UTL_FILE.INVALID_FILEHANDLE THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20002, 'File handle is invalid.'); WHEN UTL_FILE.INVALID_OPERATION THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20003, 'File could not be opened or operated on as requested.'); WHEN UTL_FILE.READ_ERROR THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20004, 'Operating system error occurred during the read operation.'); WHEN UTL_FILE.WRITE_ERROR THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20005, 'Operating system error occurred during the write operation.'); WHEN UTL_FILE.INTERNAL_ERROR THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20006, 'Unspecified PL/SQL error.'); WHEN UTL_FILE.CHARSETMISMATCH THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20007, 'A file is opened using FOPEN_NCHAR, but later I/O ' || 'operations use nonchar functions such as PUTF or GET_LINE.'); WHEN UTL_FILE.FILE_OPEN THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20008, 'The requested operation failed because the file is open.'); WHEN UTL_FILE.INVALID_MAXLINESIZE THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20009, 'The MAX_LINESIZE value for FOPEN() is invalid; it should ' || 'be within the range 1 to 32767.'); WHEN UTL_FILE.INVALID_FILENAME THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20010, 'The filename parameter is invalid.'); WHEN UTL_FILE.ACCESS_DENIED THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20011, 'Permission to access to the file location is denied.'); WHEN UTL_FILE.INVALID_OFFSET THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20012, 'The ABSOLUTE_OFFSET parameter for FSEEK() is invalid; ' || 'it should be greater than 0 and less than the total ' || 'number of bytes in the file.'); WHEN UTL_FILE.DELETE_FAILED THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20013, 'The requested file delete operation failed.'); WHEN UTL_FILE.RENAME_FAILED THEN UTL_FILE.FCLOSE(l_id); RAISE_APPLICATION_ERROR(-20014, 'The requested file rename operation failed.'); WHEN OTHERS THEN UTL_FILE.FCLOSE(l_id); RAISE; -- WHEN UTL_FILE.INVALID_PATH THEN -- utl_file.fclose(l_id); -- RAISE_APPLICATION_ERROR(-20000, 'gg File location is invalid.'); -- WHEN OTHERS THEN -- utl_file.fclose(l_id); -- RAISE; END; /