To Create a Oracle Form based in View.
Step#1 – Create Data block based on the view.
Step#2 – Create On-Update /On –Insert Trigger to update/insert data of base tables (on which view has constructed). In case you are not interested in writing Form based triggers you can write database In-Stead of trigger on View.
Step#3 – Create ON – Lock Trigger on View based block and Enter following code in that BeginNULL;End;
Step #4 – There is no Step #4 – You are ready to go.
Tuesday, August 16, 2011
Unlock a table
List of Objects which are locked :-
select a.object_name, b.oracle_username, b.os_user_name,c.sid, c.serial#,c.terminal, d.sql_textfrom sys.dba_objects a,v$locked_object b,v$session c,v$sqltext dwhere a.object_id = b.object_idand c.sid = b.session_idand c.sql_hash_value = d.hash_value
To kill a particular session
alter system kill session 'c.sid, c.serial#'
select a.object_name, b.oracle_username, b.os_user_name,c.sid, c.serial#,c.terminal, d.sql_textfrom sys.dba_objects a,v$locked_object b,v$session c,v$sqltext dwhere a.object_id = b.object_idand c.sid = b.session_idand c.sql_hash_value = d.hash_value
To kill a particular session
alter system kill session 'c.sid, c.serial#'
Wednesday, July 13, 2011
OM_order_headers table
Order Headers are stored in OE_ORDER_HEADERS_ALL. http://www.shareoracleapps.com/2010/06/oeorderheadersall-description-and-its.html
Order Management uses workflow to track status.
Some core statuses are de-normalized onto the Header entity - OPEN_FLAG, BOOKED_FLAG.
The FLOW_STATUS columns store the Header Flow Summary Status, and its value changes as the Header progresses in its flow.
The API OE_HEADER_STATUS_PUB provides information about various functional statuses and when a header workflow activity was completed.
Order Entry used SVRID columns to manage defaulting attribute values and cascading attribute value changes.
Order Management uses the PL/SQL based Defaulting Framework to provide initial default values.
It does not retain an audit trail of how an attribute was defaulted.
The following table describes OE_ORDER_HEADERS_ALL in alphabetical order. Apart from the columns specified here the OE_ORDER_HEADERS_ALL table also has the regular Descriptive Flex, Global Descriptive, Trading Partner Descriptive Flex and Standard Who Columns
Column
Description
Used by
ACCOUNTING_RULE_ID
Foreign Key to RA_RULES
Invoicing
AGREEMENT_ID
Agreement
Pricing Contracts
BOOKED_DATE
Date when Order was Booked
Booking
BOOKED_FLAG
Indicates whether order is booked
Booking
CANCELLED_FLAG
Indicates whether entire order is canceled
Cancellations
CHANGE_SEQUENCE
Controls sequence in which updates are done
EDI Integration
CHECK_NUMBER
Number of Check used to make payment on Order
Invoicing
CONVERSION_RATE
Rate of currency conversion.
General
CONVERSION_RATE_DATE
Date for the rate used for currency conversion.
General
CONVERSION_TYPE_CODE
Type of conversion used for currency conversion
General
CREDIT_CARD_APPROVAL_CODE
Credit Card Approval Code
I-Payment Integration
CREDIT_CARD_APPROVAL_DATE
Credit Card Approval Date
I-Payment Integration
CREDIT_CARD_CODE
Credit Card Code
I-Payment Integration
CREDIT_CARD_HOLDER_NAME
Credit Card Holder’s name
I-Payment Integration
CREDIT_CARD_NUMBER
Credit Card Number
I-Payment Integration
CREDIT_CARD_EXPIRATION_DATE
Credit Card Expiration Date
I-Payment Integration
CUST_PO_NUMBER
PO Number in the customer system which the customer specifies when placing order
Pricing Contracts
CUSTOMER_PREFERENCE_SET_CODE
Determines default set: Arrival or Ship
Scheduling
DELIVER_TO_CONTACT_ID
Contact person for the organization that the product is finally delivered to.
DELIVER_TO_ORG_ID
Organization / individual that would be the end consumer of the goods. Also deliveryconfirmations may be done using the deliver_to.Defaults to the order line.
General
DEMAND_CLASS_CODE
Demand Class
Planning
EARLIEST_SCHEDULE_LIMIT
Inner limit of Schedule Date Window
EXPIRATION_DATE
Future Use
Future Use
FIRST_ACK_CODE
Acknowledgment Code that is sent to the EDI trading partner when the order is acknowledged the first time
EDI Integration
FIRST_ACK_DATE
The first day the order was acknowledged
EDI Integration
FLOW_STATUS_CODE
Order Header flow status summary
General
FOB_POINT_CODE
Point of ownership transfer
General
FREIGHT_CARRIER_CODE
Shipping Integration
FREIGHT_TERMS_CODE
Freight Term Code, could be Absorb, buyer Pays, Cost to Charge, Fixed Charge
Shipping Integration
HEADER_ID
Unique identifier for an order - system generated id
Process Order
INVOICE_TO_CONTACT_ID
Contact person for the organization that will foot the bill
INVOICE_TO_ORG_ID
Organization that should be invoiced for the order / who would pay for the order. Defaults to the order line.
Invoicing Integration
INVOICING_RULE_ID
Foreign Key to RA_RULES
Invoicing Integration
LAST_ACK_CODE
Acknowledgment Code that is sent to the EDI trading partner when the order was last acknowledged
EDI Integration
LAST_ACK_DATE
Last Date the order was acknowledged
EDI Integration
LATEST_SCHEDULE_LIMIT
Outer limit of Schedule Date window
Scheduling
LOCK_CONTROL
Internal Use
Process Order
OPEN_FLAG
Indicates whether order is open
General
ORDER_CATEGORY_CODE
Category of Order (ORDER, RETURN or MIXED). Defaults from Order Type.
General
ORDER_DATE_TYPE_CODE
Indicates whether Customer is ordering based on ship date or arrival date.
Scheduling
ORDER_NUMBER
User displayed order number.
Order Numbering
ORDER_SOURCE_ID
Foreign key to oe_order_sources. This field will be used by OI to point to the order source e.g. EDI, Service etc.
Order Import
ORDER_TYPE_ID
Foreign key to OE_Transaction_Types.
General
ORDERED_DATE
Date on which order was placed.
General
ORG_ID
Organization that is taking the order. (same assold_from_org_id).
General
sold_from_org_id).
ORIG_SYS_DOCUMENT_REF
Used by Order Import - this field will contain the document number from the legacy or external Order Entry system.
Order Import
PACKING_INSTRUCTIONS
Packing Instruction
Shipping Integration
PARTIAL_SHIPMENTS_ALL
Flag to indicate whether partial quantities may beshipped for lines belonging to the order (whenset to ‘Y’) or whether it should follow the all ornone rule. (when set to ‘N’).
Shipping Integration
PAYMENT_AMOUNT
Advance payment made by Customer
Invoicing Integration
PAYMENT_TERM_ID
Foreign Key to RA_TERMS
Invoicing Integration
PAYMENT_TYPE_CODE
Indicates type of Payment
Invoicing Integration
PRICE_LIST_ID
Foreign key to oe_price_lists. Indicates which price list should be used for the order.
Pricing
PRICING_DATE
Date on which order was priced.
Pricing
REQUEST_DATE
Request Shipping / Arrival date communicated by the customer.
Defaulting Source
RETURN_REASON_CODE
Defaulting source for Return Reason on the Line.
RMA
SALES_CHANNEL_CODE
Sales Channel that was the source for this Order.
Reporting
SALESREP_ID
Foreign key to RA_SALESREP
Sales Credits,TaxIntegration
SHIP_TO_CONTACT_ID
Contact person for the organization that the products have been shipped to.
Defaulting Source
SHIP_TO_ORG_ID
Organization to which the order items were sentand would take ownership of the product.Defaults to order lines.
Defaulting Source
SHIP_TOLERANCE_ABOVE
Upper tolerance level for line quantities(expressed as percent of the originally orderedquantity). When quantities are shipped within thistolerance level, this won’t be considered as overshipment.
Defaulting Source
SHIP_TOLERANCE_BELOW
Lower tolerance level for line quantities(expressed as percent of the originally orderedquantity). When quantities are shipped within thistolerance level, this won't be considered as undershipment.
Defaulting Source
SHIPMENT_PRIORITY_CODE
Shipment Priority
Defaulting Source
SHIPPING_METHOD_CODE
Freight Carrier + Service Level
Defaulting Source
SOLD_FROM_ORG_ID
Organization that took the order (same as org_id)
General
SOLD_TO_CONTACT_ID
Contact person for the organization thatproducts have been sold to. Defaults to the orderline.
General
SOLD_TO_ORG_ID
Contact person for the organization thatproducts have been sold to. Defaults to the orderline.
General
SOURCE_DOCUMENT_ID
Based on the source document type, this would be a foreign key to an Oracle application table.
CopyIntegration,Orders,Integration
SOURCE_DOCUMENT_TYPE_ID
Foreign key to oe_order_sources
CopyIntegration,Orders,Integration
TAX_EXEMPT_FLAG
Tax exempt indicator
Tax Integration
TAX_EXEMPT_NUMBER
Tax exemption certificate number
Tax Integration
TAX_EXEMPT_REASON_CODE
Tax exemption reason
Tax Integration
TAX_POINT_CODE
A point in the Order processing cycle at whichtax payable by the customer is recorded and fixedin your financial system
Tax Integration
TRANSACTIONAL_CURR_CODE
Currency code to be used for order transactions.
General
UPGRADED_FLAG
Flag to indicate whether this record was upgraded from OE
Upgrade
Order Management uses workflow to track status.
Some core statuses are de-normalized onto the Header entity - OPEN_FLAG, BOOKED_FLAG.
The FLOW_STATUS columns store the Header Flow Summary Status, and its value changes as the Header progresses in its flow.
The API OE_HEADER_STATUS_PUB provides information about various functional statuses and when a header workflow activity was completed.
Order Entry used SVRID columns to manage defaulting attribute values and cascading attribute value changes.
Order Management uses the PL/SQL based Defaulting Framework to provide initial default values.
It does not retain an audit trail of how an attribute was defaulted.
The following table describes OE_ORDER_HEADERS_ALL in alphabetical order. Apart from the columns specified here the OE_ORDER_HEADERS_ALL table also has the regular Descriptive Flex, Global Descriptive, Trading Partner Descriptive Flex and Standard Who Columns
Column
Description
Used by
ACCOUNTING_RULE_ID
Foreign Key to RA_RULES
Invoicing
AGREEMENT_ID
Agreement
Pricing Contracts
BOOKED_DATE
Date when Order was Booked
Booking
BOOKED_FLAG
Indicates whether order is booked
Booking
CANCELLED_FLAG
Indicates whether entire order is canceled
Cancellations
CHANGE_SEQUENCE
Controls sequence in which updates are done
EDI Integration
CHECK_NUMBER
Number of Check used to make payment on Order
Invoicing
CONVERSION_RATE
Rate of currency conversion.
General
CONVERSION_RATE_DATE
Date for the rate used for currency conversion.
General
CONVERSION_TYPE_CODE
Type of conversion used for currency conversion
General
CREDIT_CARD_APPROVAL_CODE
Credit Card Approval Code
I-Payment Integration
CREDIT_CARD_APPROVAL_DATE
Credit Card Approval Date
I-Payment Integration
CREDIT_CARD_CODE
Credit Card Code
I-Payment Integration
CREDIT_CARD_HOLDER_NAME
Credit Card Holder’s name
I-Payment Integration
CREDIT_CARD_NUMBER
Credit Card Number
I-Payment Integration
CREDIT_CARD_EXPIRATION_DATE
Credit Card Expiration Date
I-Payment Integration
CUST_PO_NUMBER
PO Number in the customer system which the customer specifies when placing order
Pricing Contracts
CUSTOMER_PREFERENCE_SET_CODE
Determines default set: Arrival or Ship
Scheduling
DELIVER_TO_CONTACT_ID
Contact person for the organization that the product is finally delivered to.
DELIVER_TO_ORG_ID
Organization / individual that would be the end consumer of the goods. Also deliveryconfirmations may be done using the deliver_to.Defaults to the order line.
General
DEMAND_CLASS_CODE
Demand Class
Planning
EARLIEST_SCHEDULE_LIMIT
Inner limit of Schedule Date Window
EXPIRATION_DATE
Future Use
Future Use
FIRST_ACK_CODE
Acknowledgment Code that is sent to the EDI trading partner when the order is acknowledged the first time
EDI Integration
FIRST_ACK_DATE
The first day the order was acknowledged
EDI Integration
FLOW_STATUS_CODE
Order Header flow status summary
General
FOB_POINT_CODE
Point of ownership transfer
General
FREIGHT_CARRIER_CODE
Shipping Integration
FREIGHT_TERMS_CODE
Freight Term Code, could be Absorb, buyer Pays, Cost to Charge, Fixed Charge
Shipping Integration
HEADER_ID
Unique identifier for an order - system generated id
Process Order
INVOICE_TO_CONTACT_ID
Contact person for the organization that will foot the bill
INVOICE_TO_ORG_ID
Organization that should be invoiced for the order / who would pay for the order. Defaults to the order line.
Invoicing Integration
INVOICING_RULE_ID
Foreign Key to RA_RULES
Invoicing Integration
LAST_ACK_CODE
Acknowledgment Code that is sent to the EDI trading partner when the order was last acknowledged
EDI Integration
LAST_ACK_DATE
Last Date the order was acknowledged
EDI Integration
LATEST_SCHEDULE_LIMIT
Outer limit of Schedule Date window
Scheduling
LOCK_CONTROL
Internal Use
Process Order
OPEN_FLAG
Indicates whether order is open
General
ORDER_CATEGORY_CODE
Category of Order (ORDER, RETURN or MIXED). Defaults from Order Type.
General
ORDER_DATE_TYPE_CODE
Indicates whether Customer is ordering based on ship date or arrival date.
Scheduling
ORDER_NUMBER
User displayed order number.
Order Numbering
ORDER_SOURCE_ID
Foreign key to oe_order_sources. This field will be used by OI to point to the order source e.g. EDI, Service etc.
Order Import
ORDER_TYPE_ID
Foreign key to OE_Transaction_Types.
General
ORDERED_DATE
Date on which order was placed.
General
ORG_ID
Organization that is taking the order. (same assold_from_org_id).
General
sold_from_org_id).
ORIG_SYS_DOCUMENT_REF
Used by Order Import - this field will contain the document number from the legacy or external Order Entry system.
Order Import
PACKING_INSTRUCTIONS
Packing Instruction
Shipping Integration
PARTIAL_SHIPMENTS_ALL
Flag to indicate whether partial quantities may beshipped for lines belonging to the order (whenset to ‘Y’) or whether it should follow the all ornone rule. (when set to ‘N’).
Shipping Integration
PAYMENT_AMOUNT
Advance payment made by Customer
Invoicing Integration
PAYMENT_TERM_ID
Foreign Key to RA_TERMS
Invoicing Integration
PAYMENT_TYPE_CODE
Indicates type of Payment
Invoicing Integration
PRICE_LIST_ID
Foreign key to oe_price_lists. Indicates which price list should be used for the order.
Pricing
PRICING_DATE
Date on which order was priced.
Pricing
REQUEST_DATE
Request Shipping / Arrival date communicated by the customer.
Defaulting Source
RETURN_REASON_CODE
Defaulting source for Return Reason on the Line.
RMA
SALES_CHANNEL_CODE
Sales Channel that was the source for this Order.
Reporting
SALESREP_ID
Foreign key to RA_SALESREP
Sales Credits,TaxIntegration
SHIP_TO_CONTACT_ID
Contact person for the organization that the products have been shipped to.
Defaulting Source
SHIP_TO_ORG_ID
Organization to which the order items were sentand would take ownership of the product.Defaults to order lines.
Defaulting Source
SHIP_TOLERANCE_ABOVE
Upper tolerance level for line quantities(expressed as percent of the originally orderedquantity). When quantities are shipped within thistolerance level, this won’t be considered as overshipment.
Defaulting Source
SHIP_TOLERANCE_BELOW
Lower tolerance level for line quantities(expressed as percent of the originally orderedquantity). When quantities are shipped within thistolerance level, this won't be considered as undershipment.
Defaulting Source
SHIPMENT_PRIORITY_CODE
Shipment Priority
Defaulting Source
SHIPPING_METHOD_CODE
Freight Carrier + Service Level
Defaulting Source
SOLD_FROM_ORG_ID
Organization that took the order (same as org_id)
General
SOLD_TO_CONTACT_ID
Contact person for the organization thatproducts have been sold to. Defaults to the orderline.
General
SOLD_TO_ORG_ID
Contact person for the organization thatproducts have been sold to. Defaults to the orderline.
General
SOURCE_DOCUMENT_ID
Based on the source document type, this would be a foreign key to an Oracle application table.
CopyIntegration,Orders,Integration
SOURCE_DOCUMENT_TYPE_ID
Foreign key to oe_order_sources
CopyIntegration,Orders,Integration
TAX_EXEMPT_FLAG
Tax exempt indicator
Tax Integration
TAX_EXEMPT_NUMBER
Tax exemption certificate number
Tax Integration
TAX_EXEMPT_REASON_CODE
Tax exemption reason
Tax Integration
TAX_POINT_CODE
A point in the Order processing cycle at whichtax payable by the customer is recorded and fixedin your financial system
Tax Integration
TRANSACTIONAL_CURR_CODE
Currency code to be used for order transactions.
General
UPGRADED_FLAG
Flag to indicate whether this record was upgraded from OE
Upgrade
Tuesday, July 12, 2011
O2C
Order to Cash cycle
Articles, General Add comments
Apr 142011
google_protectAndRun("render_ads.js::google_render_ad", google_handleError, google_render_ad);
In this article, we will go through the Order to Cash cycle. The below are the steps in short:
Enter the Sales Order
Book the Sales Order
Launch Pick Release
Ship Confirm
Create Invoice
Create the Receipts either manually or using Auto Lockbox ( In this article we will concentrate on Manual creation)
Transfer to General Ledger
Journal Import
Posting
Let’s get into the details of each step mentioned above.
Enter the Sales Order:
Navigation:
Order Management Super User Operations (USA)>Orders Returns >Sales Orders
Enter the Customer details (Ship to and Bill to address), Order type.
Click on Lines Tab. Enter the Item to be ordered and the quantity required.
Line is scheduled automatically when the Line Item is saved.
Scheduling/unscheduling can be done manually by selecting Schedule/Un schedule from the Actions Menu.
You can check if the item to be ordered is available in the Inventory by clicking on Availability Button.
Save the work.
Underlying Tables affected:
In Oracle, Order information is maintained at the header and line level.
The header information is stored in OE_ORDER_HEADERS_ALL and the line information in OE_ORDER_LINES_ALL when the order is entered. The column called FLOW_STATUS_CODE is available in both the headers and lines tables which tell us the status of the order at each stage.
At this stage, the FLOW_STATUS_CODE in OE_ORDER_HEADERS_ALL is ‘Entered’
Book the Sales Order:
Book the Order by clicking on the Book Order button.
Now that the Order is BOOKED, the status on the header is change accordingly.
Underlying tables affected:
At this stage:
The FLOW_STATUS_CODE in the table OE_ORDER_HEADERS_ALL would be
‘BOOKED’
The FLOW_STATUS_CODE in OE_ORDER_LINES_ALL will be
‘AWAITING_SHIPPING’.
Record(s) will be created in the table WSH_DELIVERY_DETAILS with
RELEASED_STATUS=’R’ (Ready to Release)
Also Record(s) will be inserted into WSH_DELIVERY_ASSIGNMENTS.
At the same time DEMAND INTERFACE PROGRAM runs in the background and inserts
into MTL_DEMAND
Launch Pick Release:
Navigation:
Shipping > Release Sales Order > Release Sales Orders.
Key in Based on Rule and Order Number
In the Shipping Tab key in the below:
Auto Create Delivery: Yes
Auto Pick Confirm: Yes
Auto Pack Delivery: Yes
In the Inventory Tab:
Auto Allocate: Yes
Enter the Warehouse
Click on Execute Now Button.
On successful completion, the below message would pop up as shown below.
Pick Release process in turn will kick off several other requests like Pick Slip Report,
Shipping Exception Report and Auto Pack Report
Underlying Tables affected:
If Autocreate Delivery is set to ‘Yes’ then a new record is created in the table WSH_NEW_DELIVERIES.
DELIVERY_ID is populated in the table WSH_DELIVERY_ASSIGNMENTS.
The RELEASED_STATUS in WSH_DELIVERY_DETAILS would be now set to ‘Y’ (Pick Confirmed) if Auto Pick Confirm is set to Yes otherwise RELEASED_STATUS is ‘S’ (Release to Warehouse).
Pick Confirm the Order:
IF Auto Pick Confirm in the above step is set to NO, then the following should be done.
Navigation:
Inventory Super User > Move Order> Transact Move Order
In the HEADER tab, enter the BATCH NUMBER (from the above step) of the order. Click FIND. Click on VIEW/UPDATE Allocation, then Click TRANSACT button. Then Transact button will be deactivated then just close it and go to next step.
Ship Confirm the Order:
Navigation:
Order Management Super User>Shipping >Transactions.
Query with the Order Number.
Click On Delivery Tab
Click on Ship Confirm.
The Status in Shipping Transaction screen will now be closed.
This will kick off concurrent programs like.INTERFACE TRIP Stop, Commercial Invoice, Packing Slip Report, Bill of Lading
Underlying tables affected:
RELEASED_STATUS in WSH_DELIVERY_DETAILS would be ‘C’ (Ship Confirmed)
FLOW_STATUS_CODE in OE_ORDER_HEADERS_ALL would be “BOOKED“
FLOW_STATUS_CODE in OE_ORDER_LINES_ALL would be “SHIPPED“
Create Invoice:
Run workflow background Process.
Navigation:
Order Management >view >Requests
Workflow Background Process inserts the records RA_INTERFACE_LINES_ALL with
INTERFACE_LINE_CONTEXT = ’ORDER ENTRY’
INTERFACE_LINE_ATTRIBUTE1= Order_number
INTERFACE_LINE_ATTRIBUTE3= Delivery_id
and spawns Auto invoice Master Program and Auto invoice import program which creates Invoice for that particular Order.
The Invoice created can be seen using the Receivables responsibility
Navigation:
Receivables Super User> Transactions> Transactions
Query with the Order Number as Reference.
Underlying tables:
RA_CUSTOMER_TRX_ALL will have the Invoice header information. The column INTERFACE_HEADER_ATTRIBUTE1 will have the Order Number.
RA_CUSTOMER_TRX_LINES_ALL will have the Invoice lines information. The column INTERFACE_LINE_ATTRIBUTE1 will have the Order Number.
Create receipt:
Navigation:
Receivables> Receipts> Receipts
Enter the information.
Click on Apply Button to apply it to the Invoice.
Underlying tables:
AR_CASH_RECEIPTS_ALL
Transfer to General Ledger:
To transfer the Receivables accounting information to general ledger, run General Ledger Transfer Program.
Navigation:
Receivables> View Requests
Parameters:
Give in the Start date and Post through date to specify the date range of the transactions to be transferred.
Specify the GL Posted Date, defaults to SYSDATE.
Post in summary: This controls how Receivables creates journal entries for your transactions in the interface table. If you select ‘No’, then the General Ledger Interface program creates at least one journal entry in the interface table for each transaction in your posting submission. If you select ‘Yes’, then the program creates one journal entry for each general ledger account.
If the Parameter Run Journal Import is set to ‘Yes’, the journal import program is kicked off automatically which transfers journal entries from the interface table to General Ledger, otherwise follow the topic Journal Import to import the journals to General Ledger manually.
.
Underlying tables:
This transfers data about your adjustments, chargeback, credit memos, commitments, debit memos, invoices, and receipts to the GL_INTERFACE table.
Journal Import:
To transfer the data from General Ledger Interface table to General Ledger, run the Journal Import program from Oracle General Ledger.
Navigation:
General Ledger > Journal> Import> Run
Parameters:
Select the appropriate Source.
Enter one of the following Selection Criteria:
No Group ID: To import all data for that source that has no group ID. Use this option if you specified a NULL group ID for this source.
All Group IDs: To import all data for that source that has a group ID. Use this option to import multiple journal batches for the same source with varying group IDs.
Specific Group ID: To import data for a specific source/group ID combination. Choose a specific group ID from the List of Values for the Specific Value field.
If you do not specify a Group ID, General Ledger imports all data from the specified journal entry source, where the Group_ID is null.
Define the Journal Import Run Options (optional)
Choose Post Errors to Suspense if you have suspense posting enabled for your set of books to post the difference resulting from any unbalanced journals to your suspense account.
Choose Create Summary Journals to have journal import create the following:
• one journal line for all transactions that share the same account, period, and currency and that has a debit balance
• one journal line for all transactions that share the same account, period, and currency and that has a credit balance.
Enter a Date Range to have General Ledger import only journals with accounting dates in that range. If you do not specify a date range, General Ledger imports all journals data.
Choose whether to Import Descriptive Flexfields, and whether to import them with validation.
Click on Import button.
Underlying tables:
GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES
Posting:
We have to Post journal batches that we have imported previously to update the account balances in General Ledger.
Navigation:
General Ledger> Journals > Enter
Query for the unposted journals for a specific period as shown below.
From the list of unposted journals displayed, select one journal at a time and click on Post button to post the journal.
If you know the batch name to be posted you can directly post using the Post window
Navigation:
General Ledger> Journals> Post
Underlying tables:
GL_BALANCES.
Articles, General Add comments
Apr 142011
google_protectAndRun("render_ads.js::google_render_ad", google_handleError, google_render_ad);
In this article, we will go through the Order to Cash cycle. The below are the steps in short:
Enter the Sales Order
Book the Sales Order
Launch Pick Release
Ship Confirm
Create Invoice
Create the Receipts either manually or using Auto Lockbox ( In this article we will concentrate on Manual creation)
Transfer to General Ledger
Journal Import
Posting
Let’s get into the details of each step mentioned above.
Enter the Sales Order:
Navigation:
Order Management Super User Operations (USA)>Orders Returns >Sales Orders
Enter the Customer details (Ship to and Bill to address), Order type.
Click on Lines Tab. Enter the Item to be ordered and the quantity required.
Line is scheduled automatically when the Line Item is saved.
Scheduling/unscheduling can be done manually by selecting Schedule/Un schedule from the Actions Menu.
You can check if the item to be ordered is available in the Inventory by clicking on Availability Button.
Save the work.
Underlying Tables affected:
In Oracle, Order information is maintained at the header and line level.
The header information is stored in OE_ORDER_HEADERS_ALL and the line information in OE_ORDER_LINES_ALL when the order is entered. The column called FLOW_STATUS_CODE is available in both the headers and lines tables which tell us the status of the order at each stage.
At this stage, the FLOW_STATUS_CODE in OE_ORDER_HEADERS_ALL is ‘Entered’
Book the Sales Order:
Book the Order by clicking on the Book Order button.
Now that the Order is BOOKED, the status on the header is change accordingly.
Underlying tables affected:
At this stage:
The FLOW_STATUS_CODE in the table OE_ORDER_HEADERS_ALL would be
‘BOOKED’
The FLOW_STATUS_CODE in OE_ORDER_LINES_ALL will be
‘AWAITING_SHIPPING’.
Record(s) will be created in the table WSH_DELIVERY_DETAILS with
RELEASED_STATUS=’R’ (Ready to Release)
Also Record(s) will be inserted into WSH_DELIVERY_ASSIGNMENTS.
At the same time DEMAND INTERFACE PROGRAM runs in the background and inserts
into MTL_DEMAND
Launch Pick Release:
Navigation:
Shipping > Release Sales Order > Release Sales Orders.
Key in Based on Rule and Order Number
In the Shipping Tab key in the below:
Auto Create Delivery: Yes
Auto Pick Confirm: Yes
Auto Pack Delivery: Yes
In the Inventory Tab:
Auto Allocate: Yes
Enter the Warehouse
Click on Execute Now Button.
On successful completion, the below message would pop up as shown below.
Pick Release process in turn will kick off several other requests like Pick Slip Report,
Shipping Exception Report and Auto Pack Report
Underlying Tables affected:
If Autocreate Delivery is set to ‘Yes’ then a new record is created in the table WSH_NEW_DELIVERIES.
DELIVERY_ID is populated in the table WSH_DELIVERY_ASSIGNMENTS.
The RELEASED_STATUS in WSH_DELIVERY_DETAILS would be now set to ‘Y’ (Pick Confirmed) if Auto Pick Confirm is set to Yes otherwise RELEASED_STATUS is ‘S’ (Release to Warehouse).
Pick Confirm the Order:
IF Auto Pick Confirm in the above step is set to NO, then the following should be done.
Navigation:
Inventory Super User > Move Order> Transact Move Order
In the HEADER tab, enter the BATCH NUMBER (from the above step) of the order. Click FIND. Click on VIEW/UPDATE Allocation, then Click TRANSACT button. Then Transact button will be deactivated then just close it and go to next step.
Ship Confirm the Order:
Navigation:
Order Management Super User>Shipping >Transactions.
Query with the Order Number.
Click On Delivery Tab
Click on Ship Confirm.
The Status in Shipping Transaction screen will now be closed.
This will kick off concurrent programs like.INTERFACE TRIP Stop, Commercial Invoice, Packing Slip Report, Bill of Lading
Underlying tables affected:
RELEASED_STATUS in WSH_DELIVERY_DETAILS would be ‘C’ (Ship Confirmed)
FLOW_STATUS_CODE in OE_ORDER_HEADERS_ALL would be “BOOKED“
FLOW_STATUS_CODE in OE_ORDER_LINES_ALL would be “SHIPPED“
Create Invoice:
Run workflow background Process.
Navigation:
Order Management >view >Requests
Workflow Background Process inserts the records RA_INTERFACE_LINES_ALL with
INTERFACE_LINE_CONTEXT = ’ORDER ENTRY’
INTERFACE_LINE_ATTRIBUTE1= Order_number
INTERFACE_LINE_ATTRIBUTE3= Delivery_id
and spawns Auto invoice Master Program and Auto invoice import program which creates Invoice for that particular Order.
The Invoice created can be seen using the Receivables responsibility
Navigation:
Receivables Super User> Transactions> Transactions
Query with the Order Number as Reference.
Underlying tables:
RA_CUSTOMER_TRX_ALL will have the Invoice header information. The column INTERFACE_HEADER_ATTRIBUTE1 will have the Order Number.
RA_CUSTOMER_TRX_LINES_ALL will have the Invoice lines information. The column INTERFACE_LINE_ATTRIBUTE1 will have the Order Number.
Create receipt:
Navigation:
Receivables> Receipts> Receipts
Enter the information.
Click on Apply Button to apply it to the Invoice.
Underlying tables:
AR_CASH_RECEIPTS_ALL
Transfer to General Ledger:
To transfer the Receivables accounting information to general ledger, run General Ledger Transfer Program.
Navigation:
Receivables> View Requests
Parameters:
Give in the Start date and Post through date to specify the date range of the transactions to be transferred.
Specify the GL Posted Date, defaults to SYSDATE.
Post in summary: This controls how Receivables creates journal entries for your transactions in the interface table. If you select ‘No’, then the General Ledger Interface program creates at least one journal entry in the interface table for each transaction in your posting submission. If you select ‘Yes’, then the program creates one journal entry for each general ledger account.
If the Parameter Run Journal Import is set to ‘Yes’, the journal import program is kicked off automatically which transfers journal entries from the interface table to General Ledger, otherwise follow the topic Journal Import to import the journals to General Ledger manually.
.
Underlying tables:
This transfers data about your adjustments, chargeback, credit memos, commitments, debit memos, invoices, and receipts to the GL_INTERFACE table.
Journal Import:
To transfer the data from General Ledger Interface table to General Ledger, run the Journal Import program from Oracle General Ledger.
Navigation:
General Ledger > Journal> Import> Run
Parameters:
Select the appropriate Source.
Enter one of the following Selection Criteria:
No Group ID: To import all data for that source that has no group ID. Use this option if you specified a NULL group ID for this source.
All Group IDs: To import all data for that source that has a group ID. Use this option to import multiple journal batches for the same source with varying group IDs.
Specific Group ID: To import data for a specific source/group ID combination. Choose a specific group ID from the List of Values for the Specific Value field.
If you do not specify a Group ID, General Ledger imports all data from the specified journal entry source, where the Group_ID is null.
Define the Journal Import Run Options (optional)
Choose Post Errors to Suspense if you have suspense posting enabled for your set of books to post the difference resulting from any unbalanced journals to your suspense account.
Choose Create Summary Journals to have journal import create the following:
• one journal line for all transactions that share the same account, period, and currency and that has a debit balance
• one journal line for all transactions that share the same account, period, and currency and that has a credit balance.
Enter a Date Range to have General Ledger import only journals with accounting dates in that range. If you do not specify a date range, General Ledger imports all journals data.
Choose whether to Import Descriptive Flexfields, and whether to import them with validation.
Click on Import button.
Underlying tables:
GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES
Posting:
We have to Post journal batches that we have imported previously to update the account balances in General Ledger.
Navigation:
General Ledger> Journals > Enter
Query for the unposted journals for a specific period as shown below.
From the list of unposted journals displayed, select one journal at a time and click on Post button to post the journal.
If you know the batch name to be posted you can directly post using the Post window
Navigation:
General Ledger> Journals> Post
Underlying tables:
GL_BALANCES.
Monday, July 11, 2011
Triggers in Oracle Forms
Triggers are blocks of PL/SQL code that are written to perform tasks when a specific event occurs within an application. In effect, an Oracle Forms trigger is an event-handler written in PL/SQL to augment (or occasionally replace) the default processing behavior. Every trigger has a name, and contains one or more PL/SQL statements. A trigger encapsulates PL/SQL code so that it can be associated with an event and executed and maintained as a distinct object.
Block Processing Triggers:
Block processing triggers fire in response to events related to record management in a block.
When-Create-Record Perform an action whenever Oracle Forms attempts to create a new record in a block.
When-Clear-Block Perform an action whenever Oracle Forms flushes the current block; that is, removes all records from the block.
When-Database-Record Perform an action whenever Oracle Forms changes a record’s status to Insert or Update, thus indicating that the record should be processed by the next COMMIT_FORM operation.
Interface Event Triggers:
Interface event triggers fire in response to events that occur in the form interface. Some of these triggers, such as When-Button-Pressed, fire only in response to operator input or manipulation. Others, like When-Window-Activated, can fire in response to both operator input and programmatic control.
When-Button-Pressed Initiate an action when an operator selects a button, either with the mouse or through keyboard selection.
When-Checkbox-Changed Initiate an action when the operator toggles the state of a check box, either with the mouse or through keyboard selection.
When-Image-Activated Initiate an action whenever the operator double-clicks an image item.
When-Image-Pressed Initiate an action whenever an operator clicks on an image item.
When-Radio-Changed Initiate an action when an operator changes the current radio button selected in a radio group item.
When-Window-Activated Initiate an action whenever an operator or the application activates a window.
When-Window-Closed Initiate an action whenever an operator closes a window with the window manager’s Close command.
When-Window-Deactivated Initiate an action whenever a window is deactivated as a result of another window becoming the active window.
Master/Detail Triggers:
Oracle Forms generates master/detail triggers automatically when a master/detail relation is defined between blocks. The default master/detail triggers enforce coordination between records in a detail block and the master record in a master block. Unless developing custom block-coordination schemes, you do not need to define these triggers.
On-Check-Delete-Master Fires when Oracle Forms attempts to delete a record in a block that is a master block in a master/detail relation.
On-Clear-Details Fires when Oracle Forms needs to clear records in a block that is a detail block in a master/detail relation because those records no longer correspond to the current record in the master block.
On-Populate-Details Fires when Oracle Forms needs to fetch records into a block that is the detail block in a master/detail relation so that detail records are synchronized with the current record in the master block.
Message-Handling Triggers:
Oracle Forms automatically issues appropriate error and informational messages in response to runtime events. Message handling triggers fire in response to these default messaging events.
On-Error Replace a default error message with a custom error message, or to trap and recover from an error.
On-Message To trap and respond to a message; for example, to replace a default message issued by Oracle Forms with a custom message.
Validation Triggers:
Validation triggers fire when Oracle Forms validates data in an item or record. Oracle Forms performs validation checks during navigation that occurs in response to operator input, programmatic control, or default processing, such as a Commit operation.
When-Validate-Item
When-Validate-Record
Navigational Triggers:
Navigational triggers fire in response to navigational events. Navigational triggers can be further sub-divided into two categories: Pre- and Post- triggers, and When-New-Instance triggers. Pre- and Post- Triggers fire as Oracle Forms navigates internally through different levels of the object hierarchy. When-New-Instance-Triggers fire at the end of a navigational sequence that places the input focus on a different item.
Pre-Form Perform an action just before Oracle Forms navigates to the form from “outside” the form, such as at form startup.
Pre-Block Perform an action before Oracle Forms navigates to the block level from the form level.
Pre-Record Perform an action before Oracle Forms navigates to the record level from the block level.
Pre-Text-Item Perform an action before Oracle Forms navigates to a text item from the record level.
Post-Text-Item Manipulate an item when Oracle Forms leaves a text item and navigates to the record level.
Post-Record Manipulate a record when Oracle Forms leaves a record and navigates to the block level.
Post-Block Manipulate the current record when Oracle Forms leaves a block and navigates to the form level.
Post-Form Perform an action before Oracle Forms navigates to “outside” the form, such as when exiting the form.
When-New-Form-Instance Perform an action at form start-up. (Occurs after the Pre-Form trigger fires).
When-New-Block-Instance Perform an action immediately after the input focus moves to an item in a block other than the block that previously had input focus.
When-New-Record-Instance Perform an action immediately after the input focus moves to an item in a different record.
When-New-Item-Instance Perform an action immediately after the input focus moves to a different item.
Transactional Triggers:
Transactional triggers fire in response to a wide variety of events that occur as a form interacts with the data source.
On-Delete
On-Insert
On-Update
On-Logon
On-Logout
Post-Database-Commit
Post-Delete
Post-Insert
Post-Update
Pre-Commit
Pre-Delete
Pre-Insert
Pre-Update
Query-Time Triggers:
Query-time triggers fire just before and just after the operator or the application executes a query in a block.
Pre-Query Validate the current query criteria or provide additional query criteria programmatically, just before sending the SELECT statement to the database.
Post-Query Perform an action after fetching a record, such as looking up values in other tables based on a value in the current record. Fires once for each record fetched into the block.
Block Processing Triggers:
Block processing triggers fire in response to events related to record management in a block.
When-Create-Record Perform an action whenever Oracle Forms attempts to create a new record in a block.
When-Clear-Block Perform an action whenever Oracle Forms flushes the current block; that is, removes all records from the block.
When-Database-Record Perform an action whenever Oracle Forms changes a record’s status to Insert or Update, thus indicating that the record should be processed by the next COMMIT_FORM operation.
Interface Event Triggers:
Interface event triggers fire in response to events that occur in the form interface. Some of these triggers, such as When-Button-Pressed, fire only in response to operator input or manipulation. Others, like When-Window-Activated, can fire in response to both operator input and programmatic control.
When-Button-Pressed Initiate an action when an operator selects a button, either with the mouse or through keyboard selection.
When-Checkbox-Changed Initiate an action when the operator toggles the state of a check box, either with the mouse or through keyboard selection.
When-Image-Activated Initiate an action whenever the operator double-clicks an image item.
When-Image-Pressed Initiate an action whenever an operator clicks on an image item.
When-Radio-Changed Initiate an action when an operator changes the current radio button selected in a radio group item.
When-Window-Activated Initiate an action whenever an operator or the application activates a window.
When-Window-Closed Initiate an action whenever an operator closes a window with the window manager’s Close command.
When-Window-Deactivated Initiate an action whenever a window is deactivated as a result of another window becoming the active window.
Master/Detail Triggers:
Oracle Forms generates master/detail triggers automatically when a master/detail relation is defined between blocks. The default master/detail triggers enforce coordination between records in a detail block and the master record in a master block. Unless developing custom block-coordination schemes, you do not need to define these triggers.
On-Check-Delete-Master Fires when Oracle Forms attempts to delete a record in a block that is a master block in a master/detail relation.
On-Clear-Details Fires when Oracle Forms needs to clear records in a block that is a detail block in a master/detail relation because those records no longer correspond to the current record in the master block.
On-Populate-Details Fires when Oracle Forms needs to fetch records into a block that is the detail block in a master/detail relation so that detail records are synchronized with the current record in the master block.
Message-Handling Triggers:
Oracle Forms automatically issues appropriate error and informational messages in response to runtime events. Message handling triggers fire in response to these default messaging events.
On-Error Replace a default error message with a custom error message, or to trap and recover from an error.
On-Message To trap and respond to a message; for example, to replace a default message issued by Oracle Forms with a custom message.
Validation Triggers:
Validation triggers fire when Oracle Forms validates data in an item or record. Oracle Forms performs validation checks during navigation that occurs in response to operator input, programmatic control, or default processing, such as a Commit operation.
When-Validate-Item
When-Validate-Record
Navigational Triggers:
Navigational triggers fire in response to navigational events. Navigational triggers can be further sub-divided into two categories: Pre- and Post- triggers, and When-New-Instance triggers. Pre- and Post- Triggers fire as Oracle Forms navigates internally through different levels of the object hierarchy. When-New-Instance-Triggers fire at the end of a navigational sequence that places the input focus on a different item.
Pre-Form Perform an action just before Oracle Forms navigates to the form from “outside” the form, such as at form startup.
Pre-Block Perform an action before Oracle Forms navigates to the block level from the form level.
Pre-Record Perform an action before Oracle Forms navigates to the record level from the block level.
Pre-Text-Item Perform an action before Oracle Forms navigates to a text item from the record level.
Post-Text-Item Manipulate an item when Oracle Forms leaves a text item and navigates to the record level.
Post-Record Manipulate a record when Oracle Forms leaves a record and navigates to the block level.
Post-Block Manipulate the current record when Oracle Forms leaves a block and navigates to the form level.
Post-Form Perform an action before Oracle Forms navigates to “outside” the form, such as when exiting the form.
When-New-Form-Instance Perform an action at form start-up. (Occurs after the Pre-Form trigger fires).
When-New-Block-Instance Perform an action immediately after the input focus moves to an item in a block other than the block that previously had input focus.
When-New-Record-Instance Perform an action immediately after the input focus moves to an item in a different record.
When-New-Item-Instance Perform an action immediately after the input focus moves to a different item.
Transactional Triggers:
Transactional triggers fire in response to a wide variety of events that occur as a form interacts with the data source.
On-Delete
On-Insert
On-Update
On-Logon
On-Logout
Post-Database-Commit
Post-Delete
Post-Insert
Post-Update
Pre-Commit
Pre-Delete
Pre-Insert
Pre-Update
Query-Time Triggers:
Query-time triggers fire just before and just after the operator or the application executes a query in a block.
Pre-Query Validate the current query criteria or provide additional query criteria programmatically, just before sending the SELECT statement to the database.
Post-Query Perform an action after fetching a record, such as looking up values in other tables based on a value in the current record. Fires once for each record fetched into the block.
PL/SQL
what is pragma autonomous transaction ?
Autonomous Transaction is a feature which maintains the state of its transactions and save it , to affect with the commit or rollback of the surrounding transactions.
Autonomous transaction is the transaction which acts independantly from the calling part and could commit the process done.
A autonomous transaction starts with the first sql statement of the pl/sql block and ends with a commit. It starts within the context of an another transaction called parent transaction and independent of it (parent transaction).To make a transaction autonomous u have to declare PRAGMA AUTONOMOUS_TRANSACTION at the beginning.
The main adv. of using PRAGMA AUTONOMOUS_TRANSATION is that weather the transaction made by the parent may be rolled back due to some error the autonomous transaction has no effect on it. Suppose if there is any error in autonomous transaction … then >>>> what happen? ? ? don’t worry***** It will save all the transactions just before the error occurred. Only the last transaction that has error will be rolled back only if there is no error handler.
ora816 SamSQL :> declare2 Procedure InsertInTest_Table_B3 is4 BEGIN5 INSERT into Test_Table_B(x) values (1);6 Commit;7 END ;8 BEGIN9 INSERT INTO Test_Table_A(x) values (123);10 InsertInTest_Table_B;11 Rollback;12 END;13 /
PL/SQL procedure successfully completed.
ora816 SamSQL :> Select * from Test_Table_A;X----------123ora816 SamSQL :> Select * from Test_Table_B;X----------1
Notice in above pl/sql COMMIT at line no 6 , commits the transaction at line-no 5 and line-no 9. The Rollback at line-no 11 actually did nothing. Commit/ROLLBACK at nested transactions will commit/rollback all other DML transaction before that. PRAGMA AUTONOMOUS_TRANSACTION override this behavior.
Let us the see the following example with PRAGMA AUTONOMOUS_TRANSACTION.
ora816 SamSQL :> declare2 Procedure InsertInTest_Table_B3 is4 PRAGMA AUTONOMOUS_TRANSACTION;5 BEGIN6 INSERT into Test_Table_B(x) values (1); 7 Commit;8 END ;9 BEGIN10 INSERT INTO Test_Table_A(x) values (123);11 InsertInTest_Table_B;12 Rollback;13 END;14 /
PL/SQL procedure successfully completed.
ora816 SamSQL :> Select * from Test_Table_A;
no rows selected
ora816 SamSQL :> Select * from Test_Table_B;
X----------1
What is mutating trigger? How do you resolve it?If a trigger body is trying to update dependent tables, will it get a mutating error?
Mutating trigger has similar concept to that of deadlock. When a table is in the half way of executing a transaction and is the owner of a trigger and if the same table is accessed by the trigger somewhere else in the same time then at that instance mutating trigger occurs.
Mutating trigger is trigger that is currently being modified by DML opertion. For eg. You created a trigger trigger1 on table1 it should fire after update for each row. And you wrote some update statement on the same table (table1) inside the trigger . When you execute the individual update stmt on table1 the trigger get fires and the trigger also is currently being updated the same rows in table1 which is called mutating error and mutating trigger.
Autonomous Transaction is a feature which maintains the state of its transactions and save it , to affect with the commit or rollback of the surrounding transactions.
Autonomous transaction is the transaction which acts independantly from the calling part and could commit the process done.
A autonomous transaction starts with the first sql statement of the pl/sql block and ends with a commit. It starts within the context of an another transaction called parent transaction and independent of it (parent transaction).To make a transaction autonomous u have to declare PRAGMA AUTONOMOUS_TRANSACTION at the beginning.
The main adv. of using PRAGMA AUTONOMOUS_TRANSATION is that weather the transaction made by the parent may be rolled back due to some error the autonomous transaction has no effect on it. Suppose if there is any error in autonomous transaction … then >>>> what happen? ? ? don’t worry***** It will save all the transactions just before the error occurred. Only the last transaction that has error will be rolled back only if there is no error handler.
ora816 SamSQL :> declare2 Procedure InsertInTest_Table_B3 is4 BEGIN5 INSERT into Test_Table_B(x) values (1);6 Commit;7 END ;8 BEGIN9 INSERT INTO Test_Table_A(x) values (123);10 InsertInTest_Table_B;11 Rollback;12 END;13 /
PL/SQL procedure successfully completed.
ora816 SamSQL :> Select * from Test_Table_A;X----------123ora816 SamSQL :> Select * from Test_Table_B;X----------1
Notice in above pl/sql COMMIT at line no 6 , commits the transaction at line-no 5 and line-no 9. The Rollback at line-no 11 actually did nothing. Commit/ROLLBACK at nested transactions will commit/rollback all other DML transaction before that. PRAGMA AUTONOMOUS_TRANSACTION override this behavior.
Let us the see the following example with PRAGMA AUTONOMOUS_TRANSACTION.
ora816 SamSQL :> declare2 Procedure InsertInTest_Table_B3 is4 PRAGMA AUTONOMOUS_TRANSACTION;5 BEGIN6 INSERT into Test_Table_B(x) values (1); 7 Commit;8 END ;9 BEGIN10 INSERT INTO Test_Table_A(x) values (123);11 InsertInTest_Table_B;12 Rollback;13 END;14 /
PL/SQL procedure successfully completed.
ora816 SamSQL :> Select * from Test_Table_A;
no rows selected
ora816 SamSQL :> Select * from Test_Table_B;
X----------1
What is mutating trigger? How do you resolve it?If a trigger body is trying to update dependent tables, will it get a mutating error?
Mutating trigger has similar concept to that of deadlock. When a table is in the half way of executing a transaction and is the owner of a trigger and if the same table is accessed by the trigger somewhere else in the same time then at that instance mutating trigger occurs.
Mutating trigger is trigger that is currently being modified by DML opertion. For eg. You created a trigger trigger1 on table1 it should fire after update for each row. And you wrote some update statement on the same table (table1) inside the trigger . When you execute the individual update stmt on table1 the trigger get fires and the trigger also is currently being updated the same rows in table1 which is called mutating error and mutating trigger.
AIM Methodology
What is a methodology..
"A methodology is a set of guidelines or principles that can be tailored and applied to a specific situation."
In a project environment, these guidelines might be a list of things to do. A methodology could also be a specific approach, templates, forms, and even checklists used over the project life cycle.
Therefore that was the driving factor for bringing AIM's as methodology which was initially used by Oracle consulting , which is now most acceptable methodology for oracle application roll out.
When it was first launched
Any Guess… The first version of AIM was initially released for use by Oracle Consulting staff in October 1994.
Tasks, steps and deliverables for project life cycle
Templates for many deliverables
AIM 2.0, a refined version of the method, was released in July 1997.
Oracle introduced AIM Advantage 3.0 in September 1999.
What is current version in market?
Oracle has launched AIM's 3.1 version, which is also called as AIM's Advantage, which is now a consider as web-deployed tool kit for planning, executing and controlling Oracle Applications implementation projects.
Application Implementation Method is a proven approach for all the activities required to implement oracle applications.
The scope of the AIM is focused on an enterprise as a whole.There are eleven processes of implementation.
1. Business Process Architecture [BP]
This phase outlines:
Existing business practices
Catalog change practices
Leading practices
Future practices
2. Business Requirement Definition[RD]
This phase explains about the initial baseline questionnaire and gathering of requirements.
3. Business Requirement Mapping[BR]
In this phase the requirements of business are matched with the standard functionality of the oracle applications.
BR.100 Define Applications Setup
BR.110 Define security Profiles
4. Application and Technical Architecture [TA]
This outlines the infrastructure requirements to implement oracle applications.
5. Build and Module Design [MD]
This phase emphasizes the development of new functionality (customization) required by the client. It mainly details how to design the required forms, database and reports.
MD.010 Define Application Extension Strategy
MD.020 Define and estimate application extensions
MD.030 Define design standards
MD.040 Define Build Standards
MD.050 Create Application extensions functional design
MD.060 Design Database extensions
MD.070 Create Application extensions technical design
MD.080 Review functional and Technical designs
MD.090 Prepare Development environment
MD.100 Create Database extensions
MD.110 Create Application extension modules
MD.120 Create Installation routines
6. Data Conversion [CV]
Data Conversion is the process of converting or transferring the data from legacy system to oracle applications.
Ex. Transferring customer records from the legacy to the Customer Master.
7. Documentation [DO]
Documentation prepared per module that includes user guides and implementation manuals.
8. Business System Testing [TE]
A process of validating the setup’s and functionality by QA(functional consultant) to certify status.
TE.010 Define testing requirements and strategy
TE.020 Develop unit test script
TE.030 Develop link test script
TE.040 Develop system test script
TE.050 Develop systems integration test script
TE.060 Prepare testing environments
TE.070 Perform unit test
TE.080 Perform link test
TE.090 perform installation test
TE.100 Prepare key users for testing
TE.110 Perform system test
TE.120 Perform systems integration test
TE.130 Perform Acceptance test
9. Performance Testing [PT]
Performance testing is the evaluation of transactions saving time, transaction retrieval times, workflow background process, database performance, etc…
10; Adoption and Learning [AP]
This phase explains the removal of the legacy system and oracle application roll out enterprise wide.
11. Production Migration [PM]
The process of “decommissioning” of legacy system and the usage(adoption) of oracle application system.
"A methodology is a set of guidelines or principles that can be tailored and applied to a specific situation."
In a project environment, these guidelines might be a list of things to do. A methodology could also be a specific approach, templates, forms, and even checklists used over the project life cycle.
Therefore that was the driving factor for bringing AIM's as methodology which was initially used by Oracle consulting , which is now most acceptable methodology for oracle application roll out.
When it was first launched
Any Guess… The first version of AIM was initially released for use by Oracle Consulting staff in October 1994.
Tasks, steps and deliverables for project life cycle
Templates for many deliverables
AIM 2.0, a refined version of the method, was released in July 1997.
Oracle introduced AIM Advantage 3.0 in September 1999.
What is current version in market?
Oracle has launched AIM's 3.1 version, which is also called as AIM's Advantage, which is now a consider as web-deployed tool kit for planning, executing and controlling Oracle Applications implementation projects.
Application Implementation Method is a proven approach for all the activities required to implement oracle applications.
The scope of the AIM is focused on an enterprise as a whole.There are eleven processes of implementation.
1. Business Process Architecture [BP]
This phase outlines:
Existing business practices
Catalog change practices
Leading practices
Future practices
2. Business Requirement Definition[RD]
This phase explains about the initial baseline questionnaire and gathering of requirements.
3. Business Requirement Mapping[BR]
In this phase the requirements of business are matched with the standard functionality of the oracle applications.
BR.100 Define Applications Setup
BR.110 Define security Profiles
4. Application and Technical Architecture [TA]
This outlines the infrastructure requirements to implement oracle applications.
5. Build and Module Design [MD]
This phase emphasizes the development of new functionality (customization) required by the client. It mainly details how to design the required forms, database and reports.
MD.010 Define Application Extension Strategy
MD.020 Define and estimate application extensions
MD.030 Define design standards
MD.040 Define Build Standards
MD.050 Create Application extensions functional design
MD.060 Design Database extensions
MD.070 Create Application extensions technical design
MD.080 Review functional and Technical designs
MD.090 Prepare Development environment
MD.100 Create Database extensions
MD.110 Create Application extension modules
MD.120 Create Installation routines
6. Data Conversion [CV]
Data Conversion is the process of converting or transferring the data from legacy system to oracle applications.
Ex. Transferring customer records from the legacy to the Customer Master.
7. Documentation [DO]
Documentation prepared per module that includes user guides and implementation manuals.
8. Business System Testing [TE]
A process of validating the setup’s and functionality by QA(functional consultant) to certify status.
TE.010 Define testing requirements and strategy
TE.020 Develop unit test script
TE.030 Develop link test script
TE.040 Develop system test script
TE.050 Develop systems integration test script
TE.060 Prepare testing environments
TE.070 Perform unit test
TE.080 Perform link test
TE.090 perform installation test
TE.100 Prepare key users for testing
TE.110 Perform system test
TE.120 Perform systems integration test
TE.130 Perform Acceptance test
9. Performance Testing [PT]
Performance testing is the evaluation of transactions saving time, transaction retrieval times, workflow background process, database performance, etc…
10; Adoption and Learning [AP]
This phase explains the removal of the legacy system and oracle application roll out enterprise wide.
11. Production Migration [PM]
The process of “decommissioning” of legacy system and the usage(adoption) of oracle application system.
Tuesday, July 5, 2011
Attachments
Oracle Applications provide a functionality to attach not-structured data, such as image, document, URLs and files, to a record/form/function. This functionality is already enabled for some standard forms, and can be enabled for other forms by defining meta-data. The attachment functionality is invoked by clicking on the Clip button as shown in the figure (1).
Using attachment functionality, it is possible to attach:
The attached document can be associated with a category. This helps organizing various documents attached to a particular record.
Whenever a record is queried, the corresponding attached document can be retrieved by clicking on the clip button.
Uses
Attachment functionality is useful for attaching non-structured data with a particular record. For example, one may want to associate a scanned copy of purchase order with the PO record, or a copy of physical invoice with the invoice record. In case of contracts, we may want to associate a scanned copy of the contract with the contract record.
How to Enable an attachment
Enabling an attachment consists of defining the following things:
We will explain the enabling of attachment by taking an example. Suppose, we want to enable attachment for User Definition Screen (System Administrator => Security => User => Define). The screen is shown below (Figure 2):
- Identify the name of the form. This can be done by clicking Help => About Oracle Applications. In this case, the form name is FNDSCAUS.
- Identify the name of the base table which stores the data. In our example, the base table name is FND_USER. This can be identified by doing a query (F11 => Ctrl F11) and then doing Help => Diagnostics => Examine, Block = System, field = Last_Query.
- Identify the primary key of the table. In our case, the primary key is USER_ID.
- Identify the block name where we want to associate the attachment. In the example, block name is USER.
- Navigate to Application Developer => Attachments => Document entities and enter the details as given below. Click on Save:
- Now go to Application Developer => Attachments => Document categories and define a new category as below. Click on SAVE:
- Go to Application Developer => Attachments => Attachment functions and enter the detail as below:
- Now click on Categories button, and enter the category we just defined. Click on Save.
- Click on Blocks button and enter the information as given below. Click on SAVE:
- Click on Entities button and enter the entity information as given below:
- Click on Primary Key fields tab and enter the primary key information as given below. Click on SAVE.
We are now done with the setup required for enable the attachment in the user definition screen. Now go to the user screen and see if the click button is enabled.
How to use the attachment feature
For attaching a document, first query a record in User screen and then click on the Clip button.
Enter sequence Number (any unique number), Category (which is ‘User Application’) and data type.
Technical Details of the Attachment Functionality
Attachment Setup Tables
Document categories defined using Application Developer => Attachment => Categories are stored in the table FND_DOCUMENT_CATEGORIES
Document entities defined using Application Developer => Attachment => Entities are stored in the table FND_DOCUMENT_ENTITIES.
Attachment functions are stored in the following tables:
· FND_ATTACHMENT_FUNCTIONS
· FND_ATTACHMENT_BLOCKS
Attachment Usage tables
FND_ATTACHED_DOCUMENTS – stores information on entity, key and the associated attachment information.
FND_DOCUMENT_SHORT_TEXT – stores the details of short text type of an attachment.
FND_DOCUMENT_LONG_TEXT – stores the details of long text type of an attachment.
FND_DOCUMENTS/FND_DOCUMENTS_TL – stores the details of attached documents.
FND_LOBS – stores the actual File for attached documents
Subscribe to:
Posts (Atom)