Thursday, 7 March 2013

Important Profile Options in Oracle Purchasing:

post signature

Important Profile Options in Oracle Purchasing:

PO: AutoCreate GL Date Option
Indicates the date used on purchase orders generated by AutoCreate: The autocreate date is used as the purchase order date. The GL date on the requisition distribution is used as the
Purchase order date.
PO: Automatic Document Sourcing
Yes means that Purchasing automatically defaults source document and pricing information for an item from the most recently created blanket purchase agreement or quotation. No means that this source document information comes from the Approved Supplier List window, where you must specify which source documents to use. Note that if an item on a requisition is associated with both a blanket purchase agreement and a quotation, Purchasing uses the blanket purchase agreement even if the quotation was created more recently.
PO: Display the Autocreated Document
Yes or No indicates whether Purchasing opens the appropriate transaction window (Purchase
Orders window, RFQs window, or Sourcing negotiation page) and displays the created line(s) when you autocreate a document.
PO: Enable Sql Trace for Receiving Processor
Yes means that when you run the Receiving Transaction Processor to import data from another system using the Receiving Open Interface, the View Log screen displays the receiving transaction pre–processor’s actions, including errors, as it processed the receipt data from start to finish. (The profile option RCV: Processing Mode must also be set to Immediate or Batch for the Yes option to work.) Yes also generates a database trace file; if you need help with an error that occurs while the Receiving Transaction Processor runs, Oracle Support Services may ask you for this trace file.
This profile option should be set to Yes only while debugging the Receiving Open Interface or for generating a trace file.The Receiving Open Interface validates receipt transactions from other systems and uses the Receiving Transaction Processor to import the validated data into Purchasing.

PO: Release During ReqImport
Yes or No indicates whether Purchasing can automatically create releases during the
Requisition Import process.

PO: Restrict Requisition line modify to quantity split
Yes or No indicates whether Purchasing restricts requisition line modify in AutoCreate to only splitting the quantity of a line. No means that the standard AutoCreate requisition line modifies logic applies.
PO: Write Server Output to File
Yes or No indicates whether log details are written to a flat file rather than to the standard concurrent manager details log viewable through the View Log button in the Submit Request window when running the Purchasing Documents Open Interface program. Yes means log details are written to a flat file. No means log details are written to the concurrent manager log screen, which can cause overflow problems for large catalogs. Leaving this profile option blank means log details are not written at all, which improves performance.

RCV: Processing Mode
Indicates the processing mode used after you save your work for receiving transactions:
Batch

The transaction goes to the interface table, where it will be picked up the next time the Receiving Transaction Immediate
The Receiving Transaction Processor is called directly.
RCV: Allow routing override
Yes or No indicates whether the destination type assigned during requisition or purchase order entry can be overridden at receipt time.
RCV: Debug Mode
If set to Yes, and RCV: Processing Mode set to Immediate or Batch, debug messages will be printed to printed to the concurrent log file.
RCV: Default Include Closed PO Option
If it is set to yes, a search in the Enter Receipts window and the Receiving Transactions
Window automatically selects the Include Closed POs checkbox. Your search results will then include closed orders. The Receiving Open Interface (including ASN) will allow a receipt against orders with the status of Closed for Receiving if this profile is set to Yes. Any setting other than Yes prevents receiving against orders using the Receiving Open Interface with the status of Closed for Receiving.


Friday, 14 December 2012

XML report publisher concurrent program from backend.

XML report publisher

At times you might need to take the xml output of an existing program and apply an XML Publisher / BI Publisher Template to it. The standard use case is if the output is generated by pro*c code/ a spawned or host concurrent program. The XML Report Publisher concurrent program can help achieve this.

The Report takes the Concurrent request id, template application id, template name, template locale, template type and output type as parameters.

A sample piece of code is shown below.

DECLARE
  l_req_id NUMBER;
BEGIN
  fnd_global.apps_initialize(6087,
                             20420,
                             1,
                             0);
  l_req_id := fnd_request.submit_request('XDO',
                                         'XDOREPPB',
                                         NULL,
                                         NULL,
                                         FALSE,  
                                         FND_GLOBAL.CONC_REQUEST_ID,
                                         1919318,
                                         20003, -- Receivables
                                         'XXGILGMDWOPICKLIST', -- Statement Generate
                                         'en-US', -- English
                                         'N',
                                         'RTF',
                                         'PDF');
  dbms_output.put_line(l_req_id);
     commit;
END;

--
Best Regards,
Boge Prasanth kumar Reddy.

Enabling Audit Trail in Oracle Apps Release 12

Enabling Audit Trail in Oracle Apps Release 12

1. Setting the AuditTrail Profile Value:


2. Creating the Audit group:

3.Creating the Audit Columns for the Audit Table:

4. Enabling audit for APPS User. Similarly query the AR user and enable Audit for the AR user.

5. Running the AuditTrail Update Tables Request:

5. Changing the status of the account number XXXXX to Invalid on Receivables Customer page:


6. Query the audit table hz_cust_accounts_a. One can see the record in the audit table and the status field shows it as Inactive.

Note:This materail is copied from applikast.net
--
Best Regards,
Boge Prasanth kumar Reddy.

Following SQL query can be used to find out the exact version of the oracle applications you are currently working on.

Following SQL query can be used to find out the exact version of the oracle applications you are currently working on.

SELECT substr(a.application_short_name, 1, 5) code,
       substr(t.application_name, 1, 50) application_name,
       p.product_version version
  FROM fnd_application a,
       fnd_application_tl t,
       fnd_product_installations p
 WHERE a.application_id = p.application_id
   AND a.application_id = t.application_id
   AND t.language = USERENV('LANG')

--


Following query gives Operating Unit Information and corresponding Inventory Orgs related information as well.


Following query gives Operating Unit Information and corresponding Inventory Orgs related information as well.

SELECT hou.NAME operating_unit_name,
hou.short_code,
hou.organization_id operating_unit_id, 
hou.set_of_books_id,
hou.business_group_id,
ood.organization_name inventory_organization_name,
ood.organization_code Inv_organization_code,
ood.organization_id Inv_organization_id, 
ood.chart_of_accounts_id
FROM hr_operating_units hou, 
org_organization_definitions ood
WHERE 1 = 1 
AND hou.organization_id = ood.operating_unit
ORDER BY hou.organization_id ASC
--


Monday, 17 September 2012

Sample script to insert secondary price lists to the primary price list

Sample script to insert secondary price lists to the primary price list


Sample script to insert secondary price lists to the primary price list with
 mandatory columns to be populated in the following bulk loader interface tables:
 QP_INTERFACE_LIST_HEADERS
 QP_INTERFACE_QUALIFIERS

 This script assumes that both primary price list and the secondary price lists
 have been created already and exist. This script populates
 * QP_INTERFACE_LIST_HEADERS table with ORIG_SYS_HEADER_REF of the secondary price list.
 * QP_INTERFACE_QUALIFIERS table with primary price list Name in
      QUALIFIER_ATTR_VALUE_CODE. Alternatively populate primary price list header id in
      QUALIFIER_ATTRIBUTE_CODE.




/* Attach two secondary price lists to the Sample_BLK_PL price list.*/
/* Attach Secondary Price List 1 */
INSERT INTO QP_INTERFACE_LIST_HEADERS (
ORIG_SYS_HEADER_REF,
LIST_TYPE_CODE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
INTERFACE_ACTION_CODE,
PROCESS_FLAG,
PROCESS_STATUS_FLAG,
REQUEST_ID,
PROCESS_ID
) VALUES (
'SAMPLE_HEADER11',        /* ORIG_SYS_HEADER_REF of the secondary price list to be attached.*/
'PRL',                    /* List Type Code */
1318,                     /* Last Updated By */
sysdate,                  /* Last Update Date */
'UPDATE',                 /* Interface Action Code. The possible values are INSERT/UPDATE/DELETE.*/
'Y',                      /* Process Flag */
'P',                      /* Process Status Flag */
NULL,                     /* Request Id */
999                       /* Process Id */
);

INSERT INTO QP_INTERFACE_QUALIFIERS (
ORIG_SYS_QUALIFIER_REF,
ORIG_SYS_HEADER_REF,
QUALIFIER_GROUPING_NO,
QUALIFIER_CONTEXT,
QUALIFIER_ATTRIBUTE_CODE,
QUALIFIER_ATTR_VALUE_CODE,
COMPARISON_OPERATOR_CODE,
QUALIFIER_PRECEDENCE,
INTERFACE_ACTION_CODE,
PROCESS_FLAG,
PROCESS_STATUS_FLAG,
PROCESS_ID
) VALUES (
'SAMPLE_QUAL11',          /* ORIG_SYS_QUALIFIER_REF */
'SAMPLE_HEADER11',        /* ORIG_SYS_HEADER_REF of the secondary price list.*/
-1,                       /* Qualifier Grouping No */
'MODLIST',                /* Qualifier Context */
'PRICE_LIST',             /* Qualifier Attribute Code */
'Sample_BLK_PL',          /* Qualifier Attribute Value Code. Primary Price List Name.*/
'=',                      /* Comparison Operator Code */
100,                      /* Qualifier Precedence */
'INSERT',                 /* Interface Action Code. The possible values are INSERT/UPDATE/DELETE.*/
'Y',                      /* Process Flag */
'P',                      /* Process Status Flag */
999                       /* Process Id */
);

/* Attach Secondary Price List 2.*/
INSERT INTO QP_INTERFACE_LIST_HEADERS (
ORIG_SYS_HEADER_REF,
LIST_TYPE_CODE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
INTERFACE_ACTION_CODE,
PROCESS_FLAG,
PROCESS_STATUS_FLAG,
REQUEST_ID,
PROCESS_ID
) VALUES (
'SAMPLE_HEADER2012',        /* ORIG_SYS_HEADER_REF of the secondary price list.*/
'PRL',                    /* List Type Code */
1318,                     /* Last Updated By */
sysdate,                  /* Last Update Date */
'UPDATE',                 /* Interface Action Code. The possible values are INSERT/UPDATE/DELETE.*/
'Y',                      /* Process Flag */
'P',                      /* Process Status Flag */
NULL,                     /* Request Id */
999                       /* Process Id */
);

INSERT INTO QP_INTERFACE_QUALIFIERS (
ORIG_SYS_QUALIFIER_REF,
ORIG_SYS_HEADER_REF,
QUALIFIER_GROUPING_NO,
QUALIFIER_CONTEXT,
QUALIFIER_ATTRIBUTE_CODE,
QUALIFIER_ATTR_VALUE_CODE,
COMPARISON_OPERATOR_CODE,
QUALIFIER_PRECEDENCE,
INTERFACE_ACTION_CODE,
PROCESS_FLAG,
PROCESS_STATUS_FLAG,
PROCESS_ID
) VALUES (
'SAMPLE_QUALIFIER',          /* ORIG_SYS_QUALIFIER_REF */
'SAMPLE_HEADER2012',        /* ORIG_SYS_HEADER_REF of the secondary price list.*/
-1,                       /* Qualifier Grouping No */
'MODLIST',                /* Qualifier Context */
'PRICE_LIST',             /* Qualifier Attribute Code */
'Sample_BLK_PL',          /* Qualifier Attribute Value Code. Primary Price List Name.*/
'=',                      /* Comparison Operator Code */
100,                      /* Qualifier Precedence */
'INSERT',                 /* Interface Action Code. The possible values are INSERT/UPDATE/DELETE.*/
'Y',                      /* Process Flag */
'P',                      /* Process Status Flag */
999                       /* Process Id */                      
);

COMMIT;
EXIT;

Thursday, 13 September 2012

OM faqs

OM Questions  1) What are the Base Tables and Interface Tables for Order Management?  Interface Tables : OE_HEADERS_IFACE_ALL, OE_LINES_IFACE_ALL  OE_PRICE_ADJS_IFACE_ALL, OE_ACTIONS_IFACE_ALL  OE_CREDITS_IFACE_ALL (Order holds like credit check holds etc)  Base Tables : OE_ORDER_HEADERS_ALL: Order Header Information  OE_ORDER_LINES_ALL: Items Information  OE_PRICE_ADJUSTMENTS: Discounts Information  OE_SALES_CREDITS: Sales Representative Credits.  Shipping Tables :WSH_NEW_DELIVERIES, WSH_DELIVERY_DETAILS, WSH_DELIVERY_ASSIGNMENTS, WSH_DELIVERIES.    2) What are the Base Tables and Interface Tables for Order Management?  Interface Tables : OE_HEADERS_IFACE_ALL, OE_LINES_IFACE_ALL  OE_PRICE_ADJS_IFACE_ALL, OE_ACTIONS_IFACE_ALL  OE_CREDITS_IFACE_ALL (Order holds like credit check holds etc)  Base Tables : OE_ORDER_HEADERS_ALL: Order Header Information  OE_ORDER_LINES_ALL: Items Information  OE_PRICE_ADJUSTMENTS: Discounts Information  OE_SALES_CREDITS: Sales Representative Credits.  Shipping Tables :WSH_NEW_DELIVERIES, WSH_DELIVERY_DETAILS, WSH_DELIVERY_ASSIGNMENTS, WSH_DELIVERIES.    3) What is Order Import and What are the Setup's involved in Order Import?  A) Order Import is an open interface that consists of open interface tables and a set of API's. It imports New, updated, or changed sales orders from other applications such as Legacy systems. Order Import features include validations, Defaulting, Processing Constraints checks, Applying and releasing of order holds, scheduling of shipments, then ultimately inserting, updating or deleting orders from the OM base tables. Order management checks all the data during the import process to ensure its validity with OM. Valid Transactions are then converted into orders with lines, reservations ,price adjustments, and sales credits in the OM base tables.  B) Setups:  · Setup every aspect of order management that we want to use with imported orders, including customers, pricing, items, and bills.  · Define and enable the order import sources using the order import source window.    4) Explain the Order Cycle?  i) Enter the Sales Order  ii) Book the Sales Order(SO will not be processed until booked(Inventory confirmation))  iii) Release sales order(Pickslip Report is generated and Deliveries are created)  (Deliveries – details about the delivery. Belongs to shipping module (wsh_deliveries, wsh_new_deliveries, wsh_delivery_assignments etc) they explain how many items are being shipped and such details.  iv) Transaction Move Order (creates reservations determines the source and transfers the inventory into the staging areas)  v) Launch Pick Release (  vi) Ship Confirm (Shipping Documents(Pickslip report, Performa Invoice, Shipping Lables))  vii) Auto invoice and closed    5) Explain the Order to Cash Flow?  I. Enter the Sales Order  II. Book the Sales Order(SO will not be processed until booked(Inventory confirmation))  III. Release sales order(Pickslip Report is generated and Deliveries are created)  (Deliveries – details about the delivery. Belongs to shipping module (wsh_deliveries, wsh_new_deliveries, wsh_delivery_assignments etc) they explain how many items are being shipped and such details.  IV. Transaction Move Order (Selects the serial number of the product which has to be moved/ shipped)  V. Launch Pick Release  VI. Ship Confirm (Shipping Documents(Pickslip report, Performa Invoice, Shipping Lables))  VII. AutoInvoice (Creation of Invoice in Accounts Receivable Module)  VIII.Autolockbox ( Appling Receipts to Invoices In AR)  IX. Transfer to General Ledger ( Populates GL interface tables)  X. Journal Import ( Populates GL base tables)  XI. Posting ( Account Balances Updated).    5. What are the Process Constraints?  A. Process Constraints prevent users from adding updating, deleting, splitting lines and canceling order or return information beyond certain points in the order cycle. Oracle has provided certain process constraints which prevent data integrity violations.  Process constraints are defined for entities and attributes. Entities include regions on the sales order window such as order, line, order price adjustments, line price adjustments, order sales credits and line sales credits. Attributes include individual fields (of a particular entity) such as warehouse, shit to location, or agreement.    6. What are Validation Templates?  A) Validation Templates are used to define the validation conditions in process constraints. A validation template names a conditions and defines the semantic of how to validate that condition. These are used in processing constraints framework to specify the constraining conditions for a given constraint. These conditions are based on  1 Where the entity is in its work flow.  2 The state of attributes on an entity.  3 Any other validation condition that cannot be modeled using the above condition.    7. What are different types of Holds?  1 GSA(General Services Administration) Violation Hold(Ensures that specific customers always get better pricing for example Govt. Customers)  2 Credit Checking Hold( Used for credit checking feature Ex: Credit Limit)  3 Configurator Validation Hold ( Cause: If we invalidate a configuration after booking)    8. What is Document Sequence?  A) Document sequence is defined to automatically generate numbers for your orders or returns as you enter them. Single / multiple document sequences can be defined for different order types.  Document sequences can be defined as three types Automatic (Does not ensure that the numbers are contiguous), Gapless (Ensures that the numbering is contiguous), Manual Numbering. Order Management validates that the number specified is unique for order type.    9. What are Defaulting Rules?  A) A defaulting rule is a value that OM automatically places in an order field of the sales order window. Defaulting rules reduce the amount of information one must enter. A defaulting rule is a collection of defaulting sources for objects and their attributes.  It involves the following steps  1 Defaulting Conditions - Conditions for Defaulting  2 Sequence – Priority for search  3 Source – Entity ,Attribute, Value  4 Defaulting source/Value    10. When an order cannot be cancelled?  A) An order cannot be cancelled if,  1 It has been closed  2 It has already been cancelled  3 A work order is open for an ATO line  4 Any part of the line has been shipped or invoiced  5 Any return line has been returned or credited.    11. When an order cannot be deleted?  A) you cannot delete an order line until there is a need for recording reason.    12. What is order type?  A) An order type is the classification of order. It controls the order work flow activity, order number sequence, credit check point and transaction type. Order Type is associated to a work flow process which drives the processing of the order.    13. What are primary and secondary price lists?  A) Every order is associated to a price list as each item on the order ought to have a price. A price list is contains basic list information and one or more pricing lines, pricing attributes, qualifiers, and secondary price lists. The price list that is primarily associated to an order is termed as Primary price list.  The pricing engine uses a Secondary Price list if it cannot determine the price of the item ordered in the Primary price list.    14. What is pick slip? Types?  A) It is an internal shipping document that pickers use to locate items to ship for an order.  1 Standard Pick Slip – Each order will have its own pick slip with in each picking batch.  2 Consolidated Pickslip – Pick slip will have all the orders released in the each picking batch.    15. What is packing slip?  A) It is an external shipping document that accompanies the shipment itemizing the contents of the shipment.    16. What are picking rules?  A) Picking rules define the sources and prioritization of sub inventories, lots, revisions and locators when the item is pick released by order management. They are user defined set of rules to define the priorities order management must use when picking items from finished goods inventory to ship to a customer.    17. Where do you find the order status column?  A) In the base tables, Order Status is maintained both at the header and line level. The field that maintains the Order status is FLOW_STATUS_CODE. This field is available in both the OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL.    18. When the order import program is run it validates and the errors occurred can be seen in?A) Responsibility: Order Management Super User  Navigation: Order, Returns > Import Orders > Corrections

--