Thursday, February 28, 2013

File Types and Extensions in Oracle Apps

List of File Types and Extensions in Oracle Apps

.a Library files for C language code
.c  C lang source code
.ctl DataMerge control file (Sql loader)
.dat DataMerge import/export (Data file)
.Drv Driver (patch related files)
.env Unix environment
.exp DataMerge export
.fmb Binary forms
.fmx Execuatble forms
.h C lang header file
.jar java archive
.lc C lang source to be archived
.lct Data loader control
.ldt Data loader datafile
.log Concurrent request log
.lpc PRO*C  source to be archived
.msb Binary message
.msg Readable message
.o C lang object module
.odf Object description
.out Concurrent request output
.plb PL/SQL package body
.pll PL/SQL shared library (reports)
.pls PL/SQL package specs
.rdf Oracle D2k Reports
.req Oracle reports Executable
.sql SQL*Plus scripts

Benefits of forms personalization

Benifits:
  • Multiple users can develop forms personalization at any given point in time.
  • It is fairly easy to enable and disable forms personalizations.
  • A programmer is not required to do simple things such as hide/disable fields or buttons.
  • Provides more visibility on customization's to the screen.

Limitations of forms personalization
  • Can't create record group queries, hence can't implement LOV Query changes.
  • Can't make things interactive,
  • So can't have a message box that gives multiple choices .

Adding Multiple Request Groups to the Responsibility in Oracle Apps

Adding Multiple Request Groups to the Responsibility in Oracle Apps

  • Create Request Group in System Administrator
  • Copy Request Group name application short name and Request group code.
  • Go to application developer create function for the form called "Run Report" and pass the following parameters in the parameter field
    • REQUEST_GORUP_CODE = "XYZABC_CODE"
    • REQUEST_GROUP_APPL_SHORT_NAME="PO"
    • TITLE = "XYZ ABC"

    Mapping between Inventory item categories and i procurement item categories

    Below query gives Mapping between inv item categories and iproc item categories using mapping table ICX_POR_CATEGORY_ORDER_MAP

    SELECT
    MC . SEGMENT1 ,
    MC . DESCRIPTION ,
    MCT . CATEGORY_ID ,
    ICCT . CATEGORY_NAME ,
    ICCT . RT_CATEGORY_ID
    FROM 
    MTL_CATEGORIES_TL MCT ,
    MTL_CATEGORIES_KFV MCK ,
    ICX_POR_CATEGORY_ORDER_MAP IPCO ,
    ICX_CAT_CATEGORIES_TL ICCT ,
    MTL_CATEGORIES MC ,
    MTL_CATEGORY_SET_VALID_CATS mcsv
    WHERE MCT . CATEGORY_ID = MCK . CATEGORY_ID
    AND MCT . CATEGORY_ID = MC . CATEGORY_ID
    AND mct . CATEGORY_ID = mcsv . CATEGORY_ID
    AND IPCO . EXTERNAL_SOURCE_KEY = MCK . CATEGORY_ID
    AND ICCT . RT_CATEGORY_ID = IPCO . RT_CATEGORY_ID

    how to insert the data into RA_INTERFACE_LINES_ALL table using back end script


    DECLARE
       l_return_status   VARCHAR2 (2000);
    BEGIN
       DBMS_APPLICATION_INFO.set_client_info ('1234');
       oe_invoice_wf.invoice_interface (
          'OEOL', -- Workflow type
          584156567, -- Line_id
          1234, -- org id
          'RUN',
          l_return_status
       );
    END;

    Wednesday, February 27, 2013

    Query To Fetch Customer Account/Site Contact Details in R12

    Queries to Fetch Customer Contact Details


    SELECT hr.relationship_id,
           hr.subject_id,
           hr.subject_type,
           hr.subject_table_name,
           hr.object_id,
           hr.object_type,
           hr.object_table_name,
           hr.party_id rel_party_id,
           hr.relationship_code,
           hr.comments rel_comments,
           hr.start_date,
           hr.end_date,
           hr.status rel_status,
           hr.relationship_type,
           hr.created_by_module rel_created_by_module,
           hr.percentage_ownership,
           hr.content_source_type rel_content_source_type,
           hr.actual_content_source rel_actual_content_source,
           rel_hp.party_number rel_party_number,
           hoc.org_contact_id,
           hoc.party_relationship_id,
           hoc.comments org_cont_comments,
           hoc.contact_number,
           hoc.department_code,
           hoc.department,
           hoc.title,
           hoc.job_title,
           hoc.mail_stop,
           hoc.decision_maker_flag,
           hoc.job_title_code,
           hoc.reference_use_flag,
           hoc.RANK,
           hcar.cust_account_role_id,
           hcar.party_id acct_role_party_id,
           hcar.cust_account_id acct_role_cust_account_id,
           hcar.cust_acct_site_id acct_role_cust_acct_site_id,
           hcar.primary_flag acct_role_primary_flag,
           hcar.role_type,
           hcar.source_code acct_role_source_code,
           hcar.status acct_role_status,
           hcar.created_by_module acct_role_created_by_module,
           hp.party_id,
           hp.party_number,
           hp.party_name,
           hp.party_type,
           hp.validated_flag,
           hp.salutation par_salutation,
           hp.status par_status,
           hpp.person_profile_id,
           hpp.party_id per_party_id,
           hpp.person_name,
           hpp.person_pre_name_adjunct,
           hpp.person_first_name,
           hpp.person_middle_name,
           hpp.person_last_name,
           hpp.person_name_suffix,
           hpp.person_title,
           hpp.person_academic_title,
           hpp.person_previous_last_name,
           hpp.person_initials,
           hpp.known_as per_known_as,
           hpp.person_name_phonetic,
           hpp.person_first_name_phonetic,
           hpp.person_last_name_phonetic,
           hpp.tax_reference per_tax_reference,
           hpp.jgzz_fiscal_code per_jgzz_fiscal_code,
           hpp.person_iden_type,
           hpp.person_identifier,
           hpp.date_of_birth,
           hpp.place_of_birth,
           hpp.date_of_death,
           hpp.gender,
           hpp.declared_ethnicity,
           hpp.marital_status,
           hpp.marital_status_effective_date,
           hpp.personal_income,
           hpp.head_of_household_flag,
           hpp.household_income,
           hpp.household_size,
           hpp.rent_own_ind per_rent_own_ind,
           hpp.last_known_gps,
           hpp.known_as2 per_known_as2,
           hpp.known_as3 per_known_as3,
           hpp.known_as4 per_known_as4,
           hpp.known_as5 per_known_as5,
           hpp.middle_name_phonetic,
           hpp.created_by_module per_created_by_module,
           hpp.actual_content_source per_actual_content_source,
           hpp.internal_flag internal_flag
      FROM hz_parties hp,
           hz_parties rel_hp,
           hz_person_profiles hpp,
           hz_relationships hr,
           hz_org_contacts hoc,
           hz_cust_account_roles hcar
     WHERE hoc.party_relationship_id = hr.relationship_id
       AND hr.subject_id             = hp.party_id
       AND rel_hp.party_id           = hr.party_id
       AND hp.party_id               = hpp.party_id(+)
       AND hpp.content_source_type(+) = user_entered
       AND hpp.effective_end_date IS NULL
       AND rel_hp.party_id           = hcar.party_id(+)
       AND hoc.party_relationship_id = hr.relationship_id
       AND hr.subject_table_name     = 'HZ_PARTIES'
       AND hr.subject_type           = 'PERSON'
       AND hr.relationship_code      = 'CONTACT_OF'
       AND hcar.cust_account_id      = vl_cust_account_id
       AND hcar.cust_acct_site_id    = vl_acct_site_id