Friday 17 August 2012

Period Status Validation Query

Period status in oracle application is very important, since if you want to post or execute a transaction you need to have open/future period depending on the module (application). One company may use many applications like General Ledger, Payables, Receivables, Inventory etc.. . If you want to check which are the periods are open/closed status in a each module from by going to each application, its waste of time. Alternative is, you may run the following query by giving your set of books id and require application details.

SELECT gs.period_name,
fa.application_name,
gs.closing_status,
DECODE (gs.closing_status,
'C',
'Closed',
'O',
'Open',
'F',
'Future',
'W',
'Closed Pending',
'N',
'Never Opened',
'P',
'Permanently Closed'
)
"PERIOD_CLOSING_STATUS"
FROM gl.gl_period_statuses gs, apps.fnd_application_tl fa
WHERE fa.application_id = gs.application_id
AND fa.application_name IN
('Payables', 'Receivables', 'General Ledger')
AND gs.set_of_books_id = :set_of_books_d -- your set of books id
AND gs.period_name in -- Period Name/s for which you want to check
and closing_status in ('O','W','C')
group by Period_name,application_name, gs.closing_status
order by Period_name desc

How to set org context in Oracle apps R12 and 11i

Set org context in R12
  1. The SQL command to set the ORG_ID prior to running a script is:
    SQL> exec mo_global.init('AR');
    exec mo_global.set_policy_context('S','&org_id');
  2. Enter the org_id when prompted.
The procedure - mo_global.set_policy_context has two parameters
p_access_mode & p_org_id



p_access_mode
Description
S
In case you want your current session to work against Single ORG_ID
M
In case you want your current session to work against multiple ORG_IDs

          p_org_id: Only applicable if p_access_mode is passed value of "S"

If using Toad
 
Begin
mo_global.set_policy_context(‘S’, &org_id);
End;

Set org context in 11i:
  1. The SQL command to set the ORG_ID prior to running a script is:
    • SQL> execute dbms_application_info.set_client_info(&org_id);
  1. Enter the org_id when prompted.
If using Toad
 
Begin
fnd_client_info.set_org_context(&org_id);
     End;

Thursday 9 August 2012

XDOLoader Utility to upload XML Templates -- Basic Stapes

The parameters are described below:
  • UPLOAD (Mandatory): The first parameter: UPLOAD will be implemented in the feature.
  • DB_USERNAME (Mandatory): Database user name (example: apps).
  • DB_PASSWORD (Mandatory): Database user password (example: manager).
  • JDBC_CONNECTION (Mandatory): JDBC database connection string
    (example: ap000sun:1234:ebs).
  • LOB_TYPE (Mandatory):  XDO LOB type. Valid values are: ‘TEMPLATE’, ‘XML_SCHEMA’ and ‘XML_SAMPLE’
  • APPS_SHORT_NAME (Mandatory):  Application short name (example: AR).
  • LOB_CODE (Mandatory):  XDO LOB code. Enter either the Template Code or the Data Definition Code.
  • LANGUAGE (Optional): ISO two-letter language code (example: en).
  • TERRITORY (Optional): ISO two-letter territory code (example: US), default is ’00’.
  • XDO_FILE_TYPE (Mandatory):  Enter the XDO file type, valid values are: PDF, RTF, XLS, XSL-FO, XSL-HTML, XSL-XML, XSLTEXT, XSD, XML, RTF-ETEXT
  • NLS_LANG (Optional): Enter the NLS_LANG environment variable.
  • FILE_CONTENT_TYPE (Optional): Content type of the file (example: text/html, application/pdf)
  • FILE_NAME (Mandatory): Name of the file you want to upload. You can give full path name of the file.
  • OWNER (Optional): Owner of the template. Default is “ORACLE”.
  • CUSTOM_MODE (Optional): Whether to force update. Valid values are FORCE and NOFORCE (default).
  • LOG_FILE (Optional): Enter a file name for the output log file (default: xdotmpl.log).
 ===================================================================

 java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \
    -DB_USERNAME <db_username> \
    -DB_PASSWORD <db_password> \
    -JDBC_CONNECTION <jdbc_con_string> \
    -LOB_TYPE <lob_type> \
    -APPS_SHORT_NAME <application_short_name> \
    -LOB_CODE <lob_code> \
    -LANGUAGE  \
    -TERRITORY  \
    -XDO_FILE_TYPE <xdo_file_type> \
    -NLS_LANG <NLS_LANG> \
    -FILE_CONTENT_TYPE <file_content_type> \
    -FILE_NAME <file_name> \
    -OWNER  \
    -CUSTOM_MODE [FORCE|NOFORCE] \
    -LOG_FILE <log file>

======================================================================
Sample script to run the XDOLoader utility to upload a xml template:



echo  "----------------------------------------------------------------------"
    echo  "Please enter the following details needed for the XML XDOLoad process"
    echo  "This details can be found in the TNS entry"
    echo  "----------------------------------------------------------------------"
     
    echo "Enter Apps password  =>"
    read apps_pw
     
    echo "Enter Host TNS Database Server name  =>"
    read v_host
     
    echo "Enter Host Database TNS Port Number =>"
    read v_port
     
    echo "Enter Host Database SID name =>"
    read v_sid
     
    echo "Uploading rtf(XXFIN_SAMPLE_TEMPLATE.rtf) for the XML Template Definition"
     
    java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \
    -DB_USERNAME apps \
    -DB_PASSWORD $apps_pw \
    -JDBC_CONNECTION $v_host:$v_port:$v_sid \
    -LOB_TYPE TEMPLATE \
    -APPS_SHORT_NAME "IBY" \
    -LOB_CODE "XXFIN_TEMPLATE_CODE" \
    -LANGUAGE en \
    -XDO_FILE_TYPE RTF-ETEXT \
    -FILE_CONTENT_TYPE 'text/html' \
    -FILE_NAME "$XXFIN_TOP/templates/XXFIN_SAMPLE_TEMPLATE.rtf" \
    -CUSTOM_MODE FORCE
     
    echo  "Finish uploading rtf(XXFIN_SAMPLE_TEMPLATE.rtf) for the XML Template Definition"

Final JAVA Script for RTF templates

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \
-DB_USERNAME apps \
-DB_PASSWORD apps \
-JDBC_CONNECTION mtzdev.mastec.com:1521:MTZDEV \
-LOB_TYPE TEMPLATE_SOURCE \
-APPS_SHORT_NAME XXMTZ \
-LOB_CODE XXMTZ_GL_PERIODCLOSE \
-LANGUAGE en \
-TERRITORY US \
-XDO_FILE_TYPE RTF \
-FILE_CONTENT_TYPE 'application/rtf' \
-FILE_NAME XXMTZ_GL_PERIODCLOSE.rtf \
-NLS_LANG ENGLISH_UNITED STATES.WE8ISO8859P1

Tuesday 7 August 2012

FA Tables in R12

  • FA_ACE_BOOKS
  • FA_ADDITIONS_B
  • FA_ADDITIONS_TL
  • FA_ADDITIONS_V
  • FA_ADDITION_REP_ITF
  • FA_ADD_WARRANTIES
  • FA_ADJUSTMENTS
  • FA_ADJUST_REP_ITF
  • FA_AMORT_SCHEDULES
  • FA_ARCHIVE_HISTORY
  • FA_ASSET_HIERARCHY
  • FA_ASSET_HIERARCHY_PURPOSE
  • FA_ASSET_HIERARCHY_VALUES
  • FA_ASSET_HISTORY
  • FA_ASSET_INVOICES
  • FA_ASSET_KEYWORDS
  • FA_ASSET_LISTING_REP_ITF
  • FA_BALANCES_REPORT
  • FA_BALANCES_REP_ITF
  • FA_BONUS_RATES
  • FA_BONUS_RULES
  • FA_BOOKS
  • FA_BOOKS_GROUPS
  • FA_BOOKS_SUMMARY
  • FA_BOOK_CONTROLS
  • FA_BOOK_CONTROLS_HISTORY
  • FA_BUDGET_DISTRIBUTION
  • FA_BUDGET_INTERFACE
  • FA_CALENDAR_PERIODS
  • FA_CALENDAR_TYPES
  • FA_CAPITAL_BUDGET
  • FA_CAP_REP_ITF
  • FA_CATEGORIES_B
  • FA_CATEGORIES_TL
  • FA_CATEGORY_BOOKS
  • FA_CATEGORY_BOOK_DEFAULTS
  • FA_CEILINGS
  • FA_CEILING_TYPES
  • FA_CONVENTIONS
  • FA_CONVENTION_TYPES
  • FA_COSTCLEAR_REP_ITF
  • FA_DEFERRED_DEPRN
  • FA_DEFERRED_DEPRN_EVENTS
  • FA_DELETED_MASS_ADDITIONS
  • FA_DEPRN_BASIS_RULES
  • FA_DEPRN_DETAIL
  • FA_DEPRN_DETAIL_H
  • FA_DEPRN_EVENTS
  • FA_DEPRN_OVERRIDE
  • FA_DEPRN_PERIODS
  • FA_DEPRN_REP_ITF
  • FA_DEPRN_RULE_DETAILS
  • FA_DEPRN_SUMMARY
  • FA_DEPRN_SUMMARY_H
  • FA_DEPRN_TAX_REP_ITF
  • FA_DEPRN_TAX_REP_NBVS
  • FA_DISTRIBUTION_ACCOUNTS
  • FA_DISTRIBUTION_DEFAULTS
  • FA_DISTRIBUTION_HISTORY
  • FA_DISTRIBUTION_SETS
  • FA_EXCLUDE_HIERARCHY_LEVELS
  • FA_EXT_INV_RETIREMENTS
  • FA_FISCAL_YEAR
  • FA_FLAT_RATES
  • FA_FORMULAS
  • FA_GROUP_ASSETS
  • FA_GROUP_ASSET_DEFAULT
  • FA_GROUP_ASSET_RULES
  • FA_GROUP_DEPRN_DETAIL
  • FA_GROUP_DEPRN_RATES
  • FA_GROUP_DEPRN_SUMMARY
  • FA_GROUP_REP_ITF
  • FA_HIERARCHY_CONTROLS
  • FA_HIERARCHY_DISTRIBUTIONS
  • FA_HIERARCHY_RULE_DETAILS
  • FA_HIERARCHY_RULE_SET
  • FA_HR_RETIREMENT_DETAILS
  • FA_HR_RETIREMENT_HEADERS
  • FA_INS_LINES
  • FA_INS_MST_POLS
  • FA_INS_POLICIES
  • FA_INS_VALUES
  • FA_INVENTORY
  • FA_INVMISS_REP_ITF
  • FA_INVOICE_TRANSACTIONS
  • FA_INV_COMPARE_REP_ITF
  • FA_INV_INTERFACE
  • FA_ITC_RATES
  • FA_ITC_RECAPTURE_RATES
  • FA_JOURNAL_ENTRIES
  • FA_LEASES
  • FA_LEASE_PAYMENTS
  • FA_LEASE_PAYMENT_ITEMS
  • FA_LEASE_SCHEDULES
  • FA_LIFE_DERIVATION_INFO
  • FA_LOCATIONS
  • FA_LOOKUPS_B
  • FA_LOOKUPS_TL
  • FA_LOOKUP_TYPES_B
  • FA_LOOKUP_TYPES_TL
  • FA_MAINT_EVENTS
  • FA_MAINT_REP_ITF
  • FA_MAINT_SCHEDULE_DTL
  • FA_MAINT_SCHEDULE_HDR
  • FA_MASSADD_DISTRIBUTIONS
  • FA_MASSADD_REP_ITF
  • FA_MASS_ADDITIONS
  • FA_MASS_ADDITIONS_GT
  • FA_MASS_CHANGES
  • FA_MASS_CHANGES_ITF
  • FA_MASS_EXTERNAL_TRANSFERS
  • FA_MASS_EXT_RETIREMENTS
  • FA_MASS_EXT_RET_EXCEPTS
  • FA_MASS_RECLASS
  • FA_MASS_RECLASS_ITF
  • FA_MASS_RETIREMENTS
  • FA_MASS_RET_EXCEPTS
  • FA_MASS_REVALUATIONS
  • FA_MASS_REVALUATION_RULES
  • FA_MASS_REVAL_REP_ITF
  • FA_MASS_TAX_ADJUSTMENTS
  • FA_MASS_TAX_ADJ_REP_T
  • FA_MASS_TRANSFERS
  • FA_MASS_UPDATE_BATCH_DETAILS
  • FA_MASS_UPDATE_BATCH_HEADERS
  • FA_MC_ADJUSTMENTS
  • FA_MC_ASSET_INVOICES
  • FA_MC_BOOKS
  • FA_MC_BOOKS_GROUPS
  • FA_MC_BOOKS_RATES
  • FA_MC_BOOKS_SUMMARY
  • FA_MC_BOOK_CONTROLS
  • FA_MC_CONVERSION_HISTORY
  • FA_MC_CONVERSION_RATES
  • FA_MC_DEFERRED_DEPRN
  • FA_MC_DEPRN_DETAIL
  • FA_MC_DEPRN_DETAIL_H
  • FA_MC_DEPRN_PERIODS
  • FA_MC_DEPRN_SUMMARY
  • FA_MC_DEPRN_SUMMARY_H
  • FA_MC_GROUP_DEPRN_DETAIL
  • FA_MC_GROUP_DEPRN_SUMMARY
  • FA_MC_MASS_RATES
  • FA_MC_RETIREMENTS
  • FA_METHODS
  • FA_PARALLEL_WORKERS
  • FA_PERIODIC_PRODUCTION
  • FA_PERIOD_MAPS
  • FA_PLSQL_DEBUG
  • FA_PRICE_INDEXES
  • FA_PRICE_INDEX_VALUES
  • FA_PRODUCTION_INTERFACE
  • FA_PROJECT_REP_ITF
  • FA_PROJ_INTERIM
  • FA_PROPTAX_REP_ITF
  • FA_RATES
  • FA_RECLASS_REP_ITF
  • FA_RESERVE_LEDGER
  • FA_RETIREMENTS
  • FA_RETIREMENTS_V
  • FA_RETIRE_REP_ITF
  • FA_RX_ATTRSETS_B
  • FA_RX_ATTRSETS_TL
  • FA_RX_DYNAMIC_COLUMNS
  • FA_RX_LOV
  • FA_RX_MULTIFORMAT_REPS
  • FA_RX_REPORTS
  • FA_RX_REP_COLUMNS_B
  • FA_RX_REP_COLUMNS_TL
  • FA_RX_REP_PARAMETERS
  • FA_RX_SECURITY
  • FA_RX_SUMMARY_TL
  • FA_SHORT_TAX_RESERVES
  • FA_SUPER_GROUPS
  • FA_SUPER_GROUP_RULES
  • FA_SYSTEM_CONTROLS
  • FA_TAX_INTERFACE
  • FA_TAX_REPORT
  • FA_TRANSACTION_HEADERS
  • FA_TRANSACTION_INTERFACE
  • FA_TRANSFER_DETAILS
  • FA_TRANSFER_REP_ITF
  • FA_TRX_REFERENCES
  • FA_WARRANTIES
  • FA_WHATIF_ITF
  • FA_WORKER_JOBS

Friday 3 August 2012

PO MATCH IN ORACLE APPS

How many ways for matchingThey are mainly 3 ways:
  • 2-ways
  • 3-ways
  • 4-ways
How to explain
hand2-way matching
This verifies verifies that Purchase order and invoice information match within your tolerances as follows:
Invoice price <= Purchase order price
Quantity billed <= Quantity Ordered
hand3-way matching
This verifies that the receipt and invoice information match with the quantity tolerances defined:
Invoice price <= Purchase order price
Quantity billed <= Quantity Ordered
Quantity billed <= Quantity received
hand4-way matching
This verifies that acceptance documents and invoice information match within the quantity tolerances defined:
Invoice price <= Purchase order price
Quantity billed <= Quantity Ordered
Quantity billed <= Quantity received
Quantity billed <= Quantity accepted
How to relate between PO and invoice tables:
Here are the entity relationship diagram, explaining 2 way and 3 way matching condition.
2way
3way
In summary
compare

CUSTOM.PLL ORG SEPARATE CODING

PROCEDURE event (event_name VARCHAR2)
IS
   form_name        VARCHAR2 (30)  := NAME_IN ('system.current_form');
   block_name       VARCHAR2 (30)  := NAME_IN ('system.cursor_block');
   lc_attribute2    VARCHAR2 (60);
   lc_attribute12   VARCHAR2 (100);
BEGIN
   IF fnd_profile.VALUE ('ORG_ID') = '82'
   THEN
      IF    event_name = 'WHEN-NEW-BLOCK-INSTANCE'
         OR event_name = 'WHEN-NEW-RECORD-INSTANCE'
      THEN
         IF form_name = 'APXINWKB' AND block_name = 'LINE_SUM_FOLDER'
         THEN
            lc_attribute2 := NAME_IN ('INV_SUM_FOLDER.DF');
            lc_attribute12 := NAME_IN ('LINE_SUM_FOLDER.LINE_DF');

            IF SUBSTR (lc_attribute2, INSTR (lc_attribute2, '.', 1, 1) + 1) IS NOT NULL
            THEN
               SET_ITEM_PROPERTY ('LINE_SUM_FOLDER.LINE_DF',
                                  enabled,
                                  property_false
                                 );
            ELSIF lc_attribute2 = '.' OR lc_attribute2 IS NULL
            THEN
               SET_ITEM_PROPERTY ('LINE_SUM_FOLDER.LINE_DF',
                                  enabled,
                                  property_true
                                 );
            END IF;
         END IF;
      END IF;
   END IF;
END event;

CUSTOM.PLL STEPS AND CODING

There are different ways to handle Forms Level Customizations.
1.Using Custom.PLL
2.Using Forms Personalizations
3.Copy the Standard form Object and Change the Code

First lets see Custom.pll how to use it?


What is Custom.PLL??
The CUSTOM.pll library is a standard Oracle Forms PL/SQL library that is supplied by Oracle with the Oracle
Applications. This is Oracle’s built-in feature that allows the customer to enhance the standard functionality of the
Applications by implementing site-specific business rules. Every Oracle Forms -based eBusiness screen, and any
custom form developed using the Oracle Application development standards, will access the CUSTOM library.
This makes an ideal point of creating business rules that effect the entire organization.

Where is this located?
Custom.pll is located in $AU_TOP/resource Directory.

How to add code to this ?
open this pll using the Form builder.make changes to the program units

How to compile this PLL ?
Once you make changes you need to compile the pll.use the F60gen to compile it
f60gen module=custom.pll userid=APPS/ output_file=$AU_TOP/resource/custom.plx module_type=library batch=no compile_all=special

What are Different Triggers that is supported?

WHEN-NEW-FORM-INSTANCE – initially entering a form

WHEN-NEW-BLOCK-INSTANCE – entering a zone (or block) within a form

WHEN-NEW-ITEM-INSTANCE – moving into a new field within the form

WHEN-NEW-RECORD-INSTANCE - creating a new record

WHEN-FORM-NAVIGATE – navigating thru a form using the mouse

WHEN-VALIDATE-RECORD – saving (committing) the information to the database

EXPORT – triggered by using the Export feature Some events are field specific

ZOOM – Pre -11 feature for moving to another form and querying up specific records

Some events are form specific

SPECIALn - (where n is a number between 1 and 45) used to generate entries in the ‘Special’ menu of the
tool bar and the code is triggered by selecting a menu choices from the ‘Special’ option on the toolbar

KEY-Fn – (where n is a number between 1 and 8) triggered by pressing the corresponding function key

Some events are application specific:
Application Object Library

WHEN-LOGIN-CHANGED – when a user logs on as a different user
WHEN-RESPONSIBILITY-CHANGED – when a user changes responsibilities
WHEN-PASSWORD-CHANGED – when a user changes their password

How to make changes get affected?
Once you make the changes compile the pll and generate the PLX
Since the CUSTOM library is loaded once for a given session, a user must log out of the
application and sign-on again before any changes will become apparent.



Examples--Metalink:

1. Sample code to make all the responsibilities read only for a specific user.
BEGIN

IF event_name = 'WHEN-NEW-FORM-INSTANCE' THEN
IF FND_PROFILE.VALUE('USER_NAME')='' THEN

BEGIN
COPY('Entering app_form.query_only_mode.','global.frd_debug');
COPY('YES', 'PARAMETER.QUERY_ONLY');
APP_MENU2.SET_PROP('FILE.SAVE', ENABLED,PROPERTY_OFF);
APP_MENU2.SET_PROP('FILE.ACCEPT', ENABLED,PROPERTY_OFF);
formname := NAME_IN('system.current_form');
blockname := GET_FORM_PROPERY(formname, FIRST_BLOCK);

WHILE (blockname is not null) LOOP

IF (GET_BLOCK_PROPERTY(blockname, BASE_TABLE) is not NULL) THEN

SET_BLOCK_PROPERTY(blockname, INSERT_ALLOWED, PROPERTY_FALSE);
SET_BLOCK_PROPERTY(blockname, UPDATE_ALLOWED, PROPERTY_FALSE);
SET_BLOCK_PROPERTY(blockname, DELETE_ALLOWED, PROPERTY_FALSE);

END IF;

blockname := GET_BLOCK_PROPERTY(blockname, NEXTBLOCK);

END LOOP;


END query_only_mode;


END;

2.How does one restrict or reduce the LOV?
"The customer LOV can be overriden using the when-new-item-instance or when-new-form-instance event at the form level through CUSTOM.pll."
You will need to write custom code using that specific event in the custom.pll

Some sample code
if (event_name = 'WHEN-NEW-FORM-INSTANCE' and form_name = 'form name')then
r:=find_group('group name');
if not id_null(r) then
delete_group('group name');
end if;
v:='select colum1,column2
from table';

r:=create_group_from_query('group name',v);

set_lov_property('lov NAME',group_name,r);--lov

See that the column names should be same as the old query so that the mappings still holds good



3.How to make the attachment function in specific responsibilities to act as read-only mode so that users who log into these specific responsibilities can only view attachments, while for the rest of the responsibilities allow users to add, update and delete attachments?


// Source File Name: custom.pll
// Source File path: $AU_TOP/resource

form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
begin
if (event_name = 'WHEN-NEW-FORM-INSTANCE') then
if (form_name = 'FNDATTCH') then
if (FND_GLOBAL.RESP_NAME Like '') then --
Set_item_Property( SEQ_NUM, ENABLED,PROPERTY_FALSE);
Set_item_Property( CATEGORY_DESCRIPTION, ENABLED,PROPERTY_FALSE);
Set_item_Property( DOCUMENT_DESCRIPTION, ENABLED,PROPERTY_FALSE);
Set_item_Property( DATATYPE_NAME, ENABLED,PROPERTY_FALSE);
Set_item_Property( FILE_NAME_DISPLAY, ENABLED,PROPERTY_FALSE);
end if;
end if;
end if;

4. How to make the customisation CustomPO Number not less than PO 4 digits in sales order form?

procedure event(event_name varchar2) is
form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
item_name varchar2(30) := name_in('system.cursor_item');

Begin
if (form_name = 'OEXOEORD'and block_name = 'ORDER') then
if LENGTH(name_in('ORDER.CUST_PO_NUMBER')) > 3 then
fnd_message.set_name('FND','Cust PO Number should be less than 4 digits');
fnd_message.Error;
RAISE FORM_TRIGGER_FAILURE;
End if;
End if;
End Event;