Register Flagged Files Tool in R12

Whenever we implement Oracle Applications, the client often comes up with some businesses processes which cannot be completely implemented using the existing ERP processes. These are generally accommodated by customizations in the ERP. This gives rise to creation of custom objects/systems. Over a period of time when the actual use of system increases the number of customizations increases too.

During a course of time it may happen that the company needs to upgrade the environment or apply certain patches that are released by Oracle. It may happen that there is a strong impact on the customizations that are built. If the customizations are done by using ‘Customization By Modification’ approach there is a danger of the changes being completely washed off by the upgrade or patch application. So is there any way to avoid this? Or at least a way to minimize the impact?

Yes there is!!!

The way is to use ‘Register Flagged Files Tool’ in R12.

Register Flagged Files tool can be accessed through Oracle Applications Manager (OAM) responsibility in R12.

Flagged files is a concept within the Oracle eBusiness Suite (EBS) release 12, where you flag a standard deployment file, let's say a Forms file, a Package or a Java class file. When you run the patch analysis, the list of flagged files will be checked and in case one of these files gets patched, the analysis report will tell you.

You can flag as many files as you want, in whatever relationship they are with your customizations. In addition to the flag itself you can add a comment. You should use this comment to point to your customization reference (here XXAR_RPT_066 or XXAP_CUST_030). It is suggested to put the flagged files data file directly into your CEMLI patch. Herewith the flagged files registration will be executed right at the same time when the patch gets applied.

Process Steps:

Developer:

·         Builds CEMLI

·         Reviews code and identifies key standard objects referenced

·         Determines standard object files and flags them

·         Creates FNDLOAD file and adds the same to the CEMLI patch

DBA:

·         Executes for every new Oracle standard patch the patch analysis in a representative environment

·         Checks and retrieves the flagged files and comments

·         Sends flagged file list back to development team for analysis / retest

Developer:

·         Analyses / Updates / Retests effected CEMLIs

Prerequisite:

The patch analysis has to be executed in an environment where flagged files have been registered. (If you run the patch analysis in a vanilla or outdated environment (compared to your PROD), the analysis will not be so helpful!)

When to start with Flagged files?

Start right now utilizing this feature. It is an invest to improve the production stability and fulfill your SLA!

For 11i Users:

This functionality is also available in release 11, here it is implemented and known as "applcust.txt". You modify Oracle Application components only when you cannot meet a requirement using Oracle Application features and customization by extension (custom component development) is not an option. However, you should list each component that you modify in the file applcust.txt. This file, located in the $APPL_TOP/admin directory (or platform equivalent), provides a single location for a brief listing of customizations. Oracle Applications uses this file during patch processes (for Release 11.0 and later) to generate warning messages that customizations are being overwritten or may need to be replaced after the patch. Also, you can use the list to help determine the scope of changes that may be needed to customizations after an upgrade or patch. The applcust.txt file provides a place to list the original file name and location and a brief comment.

The above object listing process should also be followed for customization files that are copies of Oracle Applications files in custom top (customization by extension). For such customization you also include the destination file name and location (the customized file).

There is a format to be followed while registering your customization

# <Sprd> <Src Dir> <Src Fname> <Dprd> <Dest Dir> <Dest Fname>

It is easier to understand the format with an example.Consider that the file $AR_TOP/forms/US/ARXSUVAT.fmb has been modified in the method CBE as $XXAR_TOP/forms/US/XXARXSUVAT.fmb

is nothing but the Standard product abbreviation. In simple meaning the Sprd is taken using the format $_TOP.

  is always whatever after $_TOP, in our case it is forms/US

  is the exact file name.

  is your custom top where you have your customized/modified file.

  is the directory structure after $_TOP

  is the name of your custom file.

So in our example case, the format of the entry will be as follows

 # ar  forms/US    ARXSUVAT.fmb   xxar    forms/US    XXARXSUVAT.fmb

If you have customized your file in the CBM method, then you can leave the last three entries as blank (as below)

# ar  forms/US    ARXSUVAT.fmb

Steps in Detail to Register Flagged Files

Navigation: (R) System Administrator > (M) Oracle Applications Manager > Site Map > Maintenance > Register Flagged Files

 

Click on ‘Add’ button.

 

Enter the filter criteria to search for the object and click on ‘Go’ button.

Results will be displayed.

Select the appropriate object and click on ‘Add’ button.

Write comments in the provided field. You should use this comment to point to your customization reference.

Sample

 

Your DBA should now run the Patch Analysis every time he is going to apply a new patch.

Navigation: (R) System Administrator > (M) Oracle Applications Manager > Patch Wizard > Task "Recommend/Analyze Patches"

The screen shot above shows the impact summary. For this blog entry the number "2" titled "Flagged Files Changed" is in our focus. When you click the "2" you will get a similar screen like the first in this blog, showing you exactly the files which will get patched if you continue and apply this patch in this environment right now.

Item Orderability in Order Management R12.1

Item Orderability is one of the new features that come with Order Management Release 12.1. With this release, Oracle provides an easy way to define orderable products, based on exceptions defined in the item orderability rules. It would be possible to apply orderable products business rules to Items or Item Categories defined for an organization.

Based on the rules defined in the Orderability window, the user would be able to restrict the Items that can be ordered from the sales order and quick sales orders windows.

The purpose of Orderability Rule is to restrict the items that can be ordered from the sales order and quick sales orders windows.

In Release 12.1 a new form has been introduced: Item Orderability form

(OEXITORD.fmb) and can be accessed from the following:

Order Management > Setup> Rules > Item Orderability

Interface Table in Oracle Apps

General Ledger
GL_INTERFACE
GL_BUDGET_INTERFACE
GL_DAILY_RATES_INTERFACE_V
GL_IEA_INTERFACE
GL_INTERFACE_CONTROL

Payables
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE
AP_INTERFACE_CONTROLS
AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT
AP_SUPPLIER_INT_REJECTIONS

Receivables
RA_CUSTOMERS_INTERFACE
RA_CUSTOMER_PROFILES_INTERFACE
RA_CONTACT_PHONES_INTERFACE
RA_CUSTOMER_BANKS_INTERFACE
RA_CUST_PAY_METHOD_INTERFACE 
 RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_LINES_ALL
RA_INTERFACE_SALESCREDITS_ALL
RA_INTERFACE_ERRORS_ALL
--------------------------------------
AR_PAYMENTS_INTERFACE_ALL
AR_TAX_INTERFACE

Cash Management 
CE_STATEMENT_HEADERS_INT
CE_STATEMENT_LINES_INTERFACE

Purchasing 
PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERFACE
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL
PO_RESCHEDULE_INTERFACE
PO_INTERFACE_ERRORS

Inventory
MTL_REPLENISH_HEADERS_INT
MTL_REPLENISH_LINES_INT
MTL_SERIAL_NUMBERS_INTERFACE
MTL_SYSTEM_ITEMS_INTERFACE
MTL_TRANSACTION_LOTS_INTERFACE
MTL_TRANSACTIONS_INTERFACE

Order Entry
SO_HEADERS_INTERFACE_ALL
SO_HEADER_ATTRIBUTES_INTERFACE
SO_LINES_INTERFACE_ALL
SO_LINE_ATTRIBUTES_INTERFACE
SO_LINE_DETAILS_INTERFACE
SO_PRICE_ADJUSTMENTS_INTERFACE
SO_SALES_CREDITS_INTERFACE

Base Tables for Data Extraction

GL Journals
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
GL_CODE_COMBINATIONS

AP SuppliersPO_VENDORS
PO_VENDOR_SITES_ALL
PO_VENDOR_CONTACTS
AP_SUPPLIERS
AP_SUPPLIER_SITES_ALL
AP_SUPPLIER_CONTACTS

AP InvoicesAP_INVOICES_ALL
AP_INVOICE_LINES_ALL
AP_INVOICE_DISTRIBUTIONS_ALL
AP_TERMS
AP_HOLDS_ALL
PA_PROJECTS_ALL
PA_TASKS
PO_VENDORS

AR Customers

HZ_CUST_ACCOUNTS
HZ_PARTIES
HZ_CUST_ACCT_SITES_ALL
HZ_LOCATIONS
HZ_PARTY_SITES
HZ_CUST_SITE_USES_ALL

AR Customer Profiles
HZ_CUSTOMER_PROFILES
HZ_CUST_PROFILE_CLASSES
HZ_CUST_PROFILE_AMTS
AR_COLLECTORS
RA_TERMS
RA_GROUPING_RULES
AR_STATEMENT_CYCLES

AR Customer Contacts

RA_CONTACTS
RA_PHONES

AR Transactions
AR_PAYMENT_SCHEDULES_ALL
RA_CUSTOMER_TRX_ALL
RA_CUST_TRX_TYPES_ALL
RA_CUSTOMER_TRX_LINES_ALL
RA_CUST_TRX_LINE_GL_DIST_ALL
RA_TERMS
HR_OPERATING_UNITS

AR Receipts
AR_CASH_RECEIPTS_ALL
AR_CASH_RECEIPT_HISTORY_All
AR_RECEIVABLE_APPLICATIONS_ALL
AR_RECEIPT_METHODS
AR_RECEIPT_CLASSES
AR_PAYMENT_SCHEDULES_ALL

Purchase Order

PO_HEADERS_ALL
PO_LINES_V
PO_LINE_LOCATIONS_ALL
PO_DISTRIBUTIONS_V
PO_RELEASES_ALL
PO_REQUISITION_HEADERS_V
PO_REQUISITION_LINES_V

Project Data
PA_PROJECTS_ALL
PA_TASKS
PA_PROJECT_PARTIES
PA_PROJECT_ROLE_TYPES_B

Project Transaction Data
PA_EXPENDITURE_ITEMS_ALL
PA_COST_DISTRIBUTION_LINES_ALL
PA_EXPENDITURES_ALL
PA_EXPENDITURE_COMMENTS
PA_PROJECTS_ALL
PA_TASKS
INV Items
MTL_SYSTEM_ITEMS_B
ORG_ORGANIZATION_DEFINITIONS

INV Item Categories
MTL_CATEGORY_SETS_TL
MTL_CATEGORIES
MTL_ITEM_CATEGORIES
MTL_SYSTEM_ITEMS
ORG_ORGANIZATION_DEFINITIONS
MTL_ORGANIZATIONS

INV Item SubInventory

MTL_ITEM_SUB_INVENTORIES
ORG_ORGANIZATION_DEFINITIONS
MTL_SYSTEM_ITEMS_B
MTL_ITEM_LOCATIONS
MTL_SECONDARY_LOCATORS

Attachments
FND_DOCUMENTS_LONG_TEXT
FND_DOCUMENTS_SHORT_TEXT
FND_DOCUMENT_CATEGORIES_TL
FND_DOCUMENT_DATATYPES
FND_DOCUMENTS_TL
FND_DOCUMENTS
FND_ATTACHED_DOCUMENTS
FND_DOC_CATEGORY_USAGES

Bank Accounts- Supplier and Customer in R12

- Banks and their Branches are now each stored as Parties (HZ_PARTIES) in their own right. They are linked together through Relationships(HZ_RELATIONSHIP). There is a separate link for both Bank to Branch and also from Branch to Bank.

- The Bank Accounts themselves are now stored in the new Oracle Payments Application

- Below are the Key tables where the Bank Account information is stored
 IBY_EXTERNAL_PAYEES_ALL
 IBY_EXTERNAL_PAYERS_ALL
 IBY_EXT_BANK_ACCOUNTS
 IBY_PMT_INSTR_USES_ALL

- Below are the Key tables where the Bank Data of R12 TCA is stored
 HZ_PARTIES
 HZ_PARTY_SITES
 HZ_LOCATIONS
 HZ_ORGANIZATION_PROFILES
 HZ_CONTACT_POINTS
 HZ_ORG_CONTACT
 HZ_ORG_CONTACT_ROLES

•The following query gives you the links required for matching a Bank Account to its Supplier Site Record

SELECT party_supp.party_name supplier_name
 , aps.segment1 supplier_number
 , ass.vendor_site_code supplier_site
 , ieb.bank_account_num
 , ieb.bank_account_name
 , party_bank.party_name bank_name
 , branch_prof.bank_or_branch_number bank_number
 , party_branch.party_name branch_name
 , branch_prof.bank_or_branch_number branch_number
 FROM hz_parties party_supp
 , ap_suppliers aps
 , hz_party_sites site_supp
 , ap_supplier_sites_all ass
 , iby_external_payees_all iep
 , iby_pmt_instr_uses_all ipi
 , iby_ext_bank_accounts ieb
 , hz_parties party_bank
 , hz_parties party_branch
 , hz_organization_profiles bank_prof
 , hz_organization_profiles branch_prof
 WHERE party_supp.party_id = aps.party_id
 AND party_supp.party_id = site_supp.party_id
 AND site_supp.party_site_id = ass.party_site_id
 AND ass.vendor_id = aps.vendor_id
 AND iep.payee_party_id = party_supp.party_id
 AND iep.party_site_id = site_supp.party_site_id
 AND iep.supplier_site_id = ass.vendor_site_id
 AND iep.ext_payee_id = ipi.ext_pmt_party_id
 AND ipi.instrument_id = ieb.ext_bank_account_id
 AND ieb.bank_id = party_bank.party_id
 AND ieb.bank_id = party_branch.party_id
 AND party_branch.party_id = branch_prof.party_id
 AND party_bank.party_id = bank_prof.party_id
 ORDER BY party_supp.party_name
 , ass.vendor_site_code;

•The following query gives you the links required for matching a Bank Account to its Customer Site Record:

SELECT cust.party_name customer_name
 , cust_acct.account_number
 , cust_uses.site_use_code
 , cust_loc.address1
 , cust_loc.address2
 , cust_loc.address3
 , cust_loc.address4
 , cust_loc.city
 , cust_loc.postal_code
 , bank.party_name bank_name
 , bank_prof.home_country
 , branch.party_name branch_name
 , branch_prof.bank_or_branch_number branch_number
 , account.bank_account_num
 , account.bank_account_name
 FROM hz_parties bank
 , hz_relationships rel
 , hz_parties branch
 , hz_organization_profiles bank_prof
 , hz_organization_profiles branch_prof
 , iby_ext_bank_accounts account
 , iby_account_owners acc_owner
 , iby_external_payers_all ext_payer
 , iby_pmt_instr_uses_all acc_instr
 , hz_parties cust
 , hz_cust_accounts cust_acct
 , hz_cust_acct_sites_all cust_site
 , hz_cust_site_uses_all cust_uses
 , hz_locations cust_loc
 WHERE 1=1
 AND bank.party_id = rel.object_id
 and bank.party_type = rel.object_type
 AND rel.object_table_name = 'HZ_PARTIES'
 AND rel.relationship_code = 'BRANCH_OF'
 AND rel.subject_id = branch.party_id
 AND rel.subject_type = branch.party_type
 AND rel.subject_table_name = 'HZ_PARTIES'
 AND bank.party_id = bank_prof.party_id
 AND branch.party_id = branch_prof.party_id
 AND bank.party_id = account.bank_id
 AND branch.party_id = account.branch_id
 AND account.ext_bank_account_id = acc_owner.ext_bank_account_id
 AND acc_owner.account_owner_party_id = cust.party_id
 AND account.ext_bank_account_id = acc_instr.instrument_id
 AND acc_instr.ext_pmt_party_id = ext_payer.ext_payer_id
 AND ext_payer.cust_account_id = cust_acct.cust_account_id
 AND cust_acct.cust_account_id = cust_site.cust_account_id
 AND cust_site.cust_acct_site_id = cust_uses.cust_acct_site_id
 AND cust_uses.site_use_id = ext_payer.acct_site_use_id
 AND cust_uses.location = cust_loc.location_id
 AND cust.party_id = cust_acct.party_id;

Amazing Query ! Try It

select decode 
        ( sign(floor(MaxWidth /2)-rownum) 
        , 1 
        , lpad( ' ', floor(MaxWidth /2)-(rownum-1)) || rpad( '*', 2*(rownum-1)+1, ' *') 
        , lpad( '* * *', floor(MaxWidth/2)+3)
        ) 
  from all_tables , (select 60 as MaxWidth from dual) Mx 
where rownum < floor(MaxWidth /2) + 6;

Queries to connect RA_CUSTOMER_TRX_ALL and OE_ORDER_HEADERS_ALL/LINES_ALL

SELECT rt.trx_number,rt.interface_header_attribute1,rt.interface_header_context,h.order_number from
RA_CUSTOMER_TRX_ALL rt , oe_order_headers_all h
where 1=1
and interface_header_context = 'ORDER ENTRY'
and interface_header_attribute1 = to_char(h.order_number)

SELECT rl.customer_trx_line_id,rl.customer_trx_id,rl.line_number Invoice_line_num,
rl.interface_line_attribute1,h.order_number, l.line_id, rl.sales_order_line
from
RA_CUSTOMER_TRX_LINES_ALL rl,
oe_order_lines_all l,
oe_order_headers_all h
where line_type = 'LINE'
and interface_line_context = 'ORDER ENTRY'
and h.header_id = l.header_id
and interface_line_attribute6 = to_char(l.line_id)
and interface_line_attribute1 = to_char(h.order_number)
and sales_order = h.order_number

SELECT rl.interface_status,rl.line_number Invoice_line_num,
h.order_number, l.line_id
from RA_INTERFACE_LINES_ALL rl,
oe_order_lines_all l,
oe_order_headers_all h
where line_type = 'LINE'
and interface_line_context = 'ORDER ENTRY'
and h.header_id = l.header_id
and interface_line_attribute6 = to_char(l.line_id)
and interface_line_attribute1 = to_char(h.order_number)
and sales_order_line IS NOT NULL;

Key Features of Interface Trip Stop Process in Order Management

In Oracle Order Management, Interface Trip Stop (ITS) will be trigger at the time of Shipping if check box “Defer Interface” is not check.
ITS can also be executed from concurrent request.

ITS has to main components.
Update the Order Management Data (oe_order_lines_all) .
Trigger the Inventory Interface (to Update the Inventory tables).

Inventory Interface will be trigger only if the first part, that means related to OM has successfully completed and flag OE_INTERFACED_FLAG = ‘Y’ on WSH_DELIVERY_DETAILS.If value of this flag is N/P then Inventory Interface will never be triggered. And even if you try to submit the Inventory Interface from SRS, delivery detail with oe_interfaced_flag =’N’ will never be picked up.

ITS update the following fields on oe_order_lines_all table
1.Shipped_quantity
2.shipping_quantity,
3.Actual_shipment_date.

Above is just simple input about basic process for ITS , apart from it other feature of OTS are
1.Line spliting .
2.Progress the Order line to FULFILL Deferr Status (depend on your workflow).

Drop Ship Cycle

Drop Shipment is a process where the customer places a purchase order on a company and this company instructs its supplier to directly ship the items to the customer.

Drop Shipment Process Steps

1. Create Sale Order

2. Book Sales Order (Status of order line is 'BOOKED')

3. Progress Sales Order

4. Release Purchase Order

5. Run the Workflow Background process if necessary.  (Now the staus of order line will changed to Awaiting receipt)

6. Launch Requisition Import (This concurrent program will also launch the standard concurrent program 'Create Relaease')

7. Link between sales order and purchase order (i.e. you can also view the Purchase Order and Receiving information in Additional line information of Order Line in Drop Ship Tab.)

8. Receive the material against purchase order

9. Run the Auto Invoice Program from AR responsibility.

NB: Please make sure that ASL have been set up for the item so that PO can be created otherwise only requisition will be created)

Data flow for Order Cycle

Enter the order:
---------------
oe-order-headers-all
oe-order-lines-all(flow_status_code = entered)
Book the order:
--------------
oe_order_headers_all
oe_order_lines_all(flow_status_code = booked)
wsh_new_deliveries(status_code =open)
wsh_delivery_details(releases_status = R 'ready to release)
Pick release:
---------------
which items on sales order we need to take out from inventory.
normally pick release SRS program run from backend once this over.
oe_order_lines_all(flow_status_code=picked)
wsh_delivery_detalis(release_status = S 'submitted for release)
Pick confirm:
-------------
items are transfered from salable to staging sub inventory.
mtl_material_transaction
mtl_transaction_accounts
wsh_delivery_details(released_status = Y 'released')
wsh_delivery_assignments
Ship confirm:
-------------
here ship confirm program run from backend and data removed from wsh_new_deliveries.
oe_order_lines_all ( flow_status_code =shipped)
wsh_delivery_details (released_status = shipped)
mtl_transaction_interface
mtl_material_transactions (linked through source_header_id)
data deleted from mtl_demand & mtl_reservations
item deducted from mtl_onhand_quantities.
Enter invoice:
--------------
this is also called recivables interface,that mean information moved to accounting area for invoicing
details.
invoicing workflow activity transfers shipped item info to recevables.
ra_interface_lines_all
then auto-invoice program imports data from this.
then effected tables : ra_customer_trx_all (trx_number is the invoice number)
ra_customer_trx_lines_all (line_attribute 1 & 6 ) linked to header_id
and lined_id for orders....
complete line:
-------------
order line level table get updated with flow status & open flag.
oe_ordr_lines_all ( flow_status_code = shipped & open flag = N)
close order:
------------
once we close the order oe_order_lines_all table get updated with flow_status_code as closed.

Script for adding responsibility to a user

DECLARE
CURSOR c1_cur IS SELECT u.user_name
u.user_id u.end_date u.start_date u.description
FROM fnd_user u
WHERE u.user_name = ('JAI')--Existing User
AND (u.end_date IS NULL OR u.end_date = TO_DATE('31-dec-4712' 'dd-mon-yyyy'))
ORDER BY u.user_name;
BEGIN
FOR c1 IN c1_cur LOOP
BEGIN
fnd_user_pkg.addresp(username => c1.user_name --varchar2
resp_app => 'SYSADMIN' --varchar2
resp_key => 'SYSTEM_ADMINISTRATOR' --varchar2
security_group => 'STANDARD' --varchar2
description => c1.description --varchar2
start_date => sysdate --date
end_date => c1.end_date --date
);
dbms_output.put_line('Inserted '||c1.user_name);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('ERROR '||c1.user_name|| SQLERRM);
END;
END LOOP;
COMMIT;
END;
/