View SQL Statement in OAF page

Below are the detailed steps:

1) Update the following Profile Option Values to Yes at User level.
• FND: Diagnostics
• Personalize Self-Service Defn

2) Go to the Java OAF Page where the SQL Query executes

3) Click on the link About this Page in the bottom Left Hand Side of the page

4) Navigate to the Page Tab on the Top Left Hand Side of the page

5) In the Business Component References Details Section (You may need to expand this
section), Go to the View Objects Sub Section.

6) Find the Object that describes the piece of information that you want to find the query for

7) Click on the Link

8) The full Query used can be cut and pasted into a SQL Editor and the query run.
Note: You will have to find the Bind Variables passed to the query in order to do this!!

Example of $FLEX$ Syntax

Here is an example of using :$FLEX$.Value_Set_Name to set up value sets where one segment depends on a prior segment that itself depends on a prior segment ("cascading dependencies"). Assume you have a three-segment flexfield where the first segment is car manufacturer, the second segment is car model, and the third segment is car color. You could limit your third segment's values to only include car colors that are available for the car specified in the first two segments. Your three value sets might be defined as follows:

Segment Name         Manufacturer 
Value Set Name       Car_Maker_Name_Value_Set 
Validation Table     CAR_MAKERS 
Value Column         MANUFACTURER_NAME 
Description Column       MANUFACTURER_DESCRIPTION 
Hidden ID Column         MANUFACTURER_ID 
SQL Where Clause     (none) 

Segment Name         Model 
Value Set Name       Car_Model_Name_Value_Set 
Validation Table     CAR_MODELS 
Value Column         MODEL_NAME 
Description Column       MODEL_DESCRIPTION 
Hidden ID Column         MODEL_ID 
SQL Where Clause     WHERE MANUFACTURER_ID = 
                     :$FLEX$.Car_Maker_Name_Value_Set 

Segment Name         Color 
Value Set Name       Car_Color_Name_Value_Set 
Validation Table     CAR_COLORS 
Value Column         COLOR_NAME 
Description Column       COLOR_DESCRIPTION 
Hidden ID Column         COLOR_ID 
SQL Where Clause     WHERE MANUFACTURER_ID = 
                     :$FLEX$.Car_Maker_Name_Value_Set
                     AND MODEL_ID = 
                     :$FLEX$.Car_Model_Name_Value_Set 

In this example, MANUFACTURER_ID is the hidden ID column and MANUFACTURER_NAME is the value column of the Car_Maker_Name_Value_Set value set. The Model segment uses the hidden ID column of the previous value set, Car_Maker_Name_Value_Set, to compare against its WHERE clause. The end user never sees the hidden ID value for this example.

Script to get Order Header details

SELECT ra.customer_number,
        hl.address1,
        hl.address2,
        hl.city,
        hl.state,
        hl.postal_code,
        hl.country,
        hl.province,
        hl.county
  FROM oe_order_headers_all ooh,
       hz_cust_site_uses_all hcsu,
       hz_cust_acct_sites_all hcas,
       hz_party_sites hps,
       hz_locations hl,
       ra_customers ra
 WHERE ooh.invoice_to_org_id = hcsu.site_use_id
   AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
   AND hcas.party_site_id = hps.party_site_id
   AND hps.location_id = hl.location_id
   AND hcas.cust_account_id = ra.customer_id
   AND order_number = :p_order_num

Accounts Payables Flow

Acount Payables -> Account Payables Flow:
PO -> Receipt -> AP -> GL

Invoice -> Payments-> Move Transactions from AP to GL. In AP there are 2 Thumb rules.

• Without supplier there is no invoice.

• Without invoice there is no payment.

Invoice Types:

1. Standard: We will make all the payments based on the standard invoice. It will have the information of Invoice Number, Invoice Date, Invoice Amount, and Currency.

2. Credit Memo: We will create credit memo invoice whenever supplier is giving the discount and it will be adjusted in standard invoice. It is always negative amount.

3. Debit Memo: Sometimes Company will deduct some amount from the invoice amount. This will be adjusted in standard invoice. It is always negative amount.

4. With-Holding TAX: This type of invoice will be created to make the invoice tax to the Govt. on behalf of supplier.

5. Pre-Payment: If we want to make some payments to the supplier in advance then we create the Pre-Payment invoice.

6. PO Default: If we want to make the invoice as per the PO then we create PO default. We will give Po Number. System will pick up the complete PO information.

7. Mixed: Includes both positive and negative amount. We can match this invoice with PO’s and other invoices.

8. Expense Report: This will be applicable for the employees who are working in the Company where payables and internet expense and project account. Expense will be included.

9. Recurring invoice: We can enter invoice for periodic expense for which we may not receive. Invoice from supplier. To create a Recurring invoice first we will take template.

As per that we will create the invoice.Once the invoice is successfully completed, we can go for payments. It is of 3 types.

Manual Payment: Here we will be mentioning the Invoice Number, Bank Account, and Document Number, Payment Date and Currency.

Refund payment: This is used for Employee expenses and for adjusting the Supplier account,

Quick payment: In this payment, system will automatically generate checks. To print Checks there will be a concurrent program for each check format.Once the payment was done, we will move all the transactions to GL.

Suppliers:

Set up suppliers in the Suppliers window to record information about individuals and companies from whom you purchase goods and services. You can also enter employees whom you reimburse for expense reports. When you enter a supplier that does business from multiple locations, you store supplier information only once, and enter supplier sites for each location. You can designate supplier sites as pay sites, purchasing sites, RFQ only sites, or procurement card sites. For example, for a single supplier, you can buy from several different sites and send payments to several different sites. Most supplier information automatically defaults to all supplier sites to facilitate supplier site entry. However, you can override these defaults and have unique information for each site. The system uses information you enter for suppliers and supplier sites to enter default values when you later enter transactions for a supplier site. Most information you enter in the Suppliers window is used only to enter defaults in the Supplier Sites window. When the system enters that information in a later transaction, it only uses supplier site information as a default, even if the supplier site value is null and the supplier has a value. If you update information at the supplier level, existing supplier sites are not updated. When you enter a supplier, you can also record information for your own reference, such as names of contacts or the customer number your supplier has assigned to you.

Invoices:

Invoice Type (LOV): The type of invoice. Standard and Credit are the only invoice types you can enter in this window. If you do not enter a value for this field then a value will be assigned during import based on the amount of the invoice.

Standard: A trade invoice you receive from a supplier. The amount of a Standard invoice must be zero or greater.

Credit: Credit Memo. A negative amount invoice you receive from a supplier representing a credit for goods or services purchased. Note that in the Invoice Gateway you can match a credit memo to a purchase order to perform a price correction, but you cannot match a credit memo to an invoice. If you want to match to an invoice, then use the Invoice Workbench.

Debit Memo: Negative amount invoice created by you and sent to a supplier to notify the supplier of a credit you are recording. Usually send with a note explaining the debit memo. Purchase Order Matched Invoices: You can match Payables invoices to purchase orders to ensure that you pay only for the goods that you have ordered, or you can match to purchase order receipts to ensure that you pay only for goods that you have received. Purchase order matched invoices are invoices that you match to any of the following:

• Purchase order shipments

• Purchase order receipts

• Purchase order receipt lines

• Purchase order distributions

Foreign Currency Invoices: When you enter an invoice in a currency other than your functional currency, Payables uses an exchange rate to convert the invoice and invoice distributions into your functional currency for creating journal entries. You define your functional currency during setup for your set of books.

Mixed Invoices: Mixed Invoices are invoices or credit/debit memos for which you can perform both positive and negative matching to purchase orders and to other invoices. For example, you can enter an invoice for –$100 with Invoice Type Mixed. You can match to an invoice for $–200, and match to a purchase order for $100.

Prepayments:A prepayment is a type of invoice you enter to make an advance payment to a supplier or employee. For example, you need to pay a deposit on a lease, or pay an employee an advance for travel expenses. You can later apply the prepayment to one or more invoices or expense reports you receive from the supplier or employee to offset the amount paid to them. The supplier might send an invoice that references a prepayment. The supplier has reduced the invoice amount by the amount of the prepayment and associated tax. You can use the Prepayment on Invoice feature to enter the invoice.

You can enter two types of prepayments:

Temporary prepayments can be applied to invoices or expense reports you receive. For example, you use a Temporary prepayment to pay a hotel a catering deposit. When the hotel’s invoice arrives, apply the prepayment to the invoice to reduce the invoice amount you pay.

Permanent prepayments cannot be applied to invoices. For example you use a Permanent prepayment to pay a lease deposit for which you do not expect to be invoiced.

2–way matching: The process of verifying that purchase order and invoice information matches within accepted tolerance levels. Payables use the following criteria to verify two–way matching: Invoice price <- Order price Quantity billed <- Quantity ordered

3–way matching: The process of verifying that purchase order, invoice, and receiving information matches within accepted tolerance levels. Payables use the following criteria to verify three–way matching: Invoice price <- Purchase Order price, Quantity billed <- Quantity ordered, Quantity billed <- Quantity received.

4–way matching: The process of verifying that purchase order, invoice, and receiving information matches within accepted tolerance levels. Payables use the following criteria to verify four–way matching:

Invoice price <- Order price

Quantity billed <- Quantity ordered

Quantity billed <- Quantity received

Quantity billed <- Quantity accepted

FOB (Free On Board): The point or location where the ownership title of goods is transferred from the seller to the buyer. This indicates that delivery of a shipment will be made on board or into a carrier by the shipper without charge, and is usually followed by a shipping point or destination (e.g. ’FOB Our warehouse in New York’). The FOB code is currently available only for reference purposes. Revenue and cost recognition is not currently determined by the value entered in this field. (Receivables Lookup)

Purge : An Oracle Receivables Process, where you identify a group of records for Receivables to delete from the database. Receivables purge each record and its related records. Receivables maintain summary data for each record it purges.

SAP Vs Oracle

What Is ROI?Return on investment is calculated by dividing the average annual net benefits over a 3 year period by the initial costs:
(Y1+Y2+Y3)/3/initial cost
Benefits include both direct cost savings and productivity and other indirect benefits; costs include software, hardware, personnel, consulting, and training.

ROI AND PAYBACK> 65 percent of Oracle E-Business Suite customers achieved a positive return on investment after an average deployment time of 3.1 years.
> 43 percent of SAP customers had achieved a positive return on investment after an average deployment time of 2.8 years.

COSTS
1) Oracle’s average costs were 48 percent lower than SAP’s.
2) Oracle’s median costs were 29 percent lower than SAP’s.
3) Oracle’s average 3-year TCO was 48% lower than SAP’s.
4) Oracle’s median 3-year TCO was 34 percent lower than SAP’s.

PERSONNEL REQUIREMENTS
1) It takes fewer internal personnel to deploy Oracle: an average of 81 man-months of internal personnel were needed for Oracle, compared to an average of 372 for SAP.
2) It takes less training to deploy Oracle: an average of 27 hours of training for Oracle, compared to an average of 49 hours for SAP

CONCLUSION1) SAP projects are larger in scale and scope and thus take a longer time to reach positive ROI than Oracle.
2) More Oracle customers than SAP customers achieve a positive ROI. Ninety percent of Oracle customers can expect a positive ROI in fewer than 5 years.
3) SAP customers often face customization and integration challenges that slow deployments and increase consulting and personnel costs.
4) Oracle has a lower overall TCO than SAP

Disable New Button(+) on AP Invoice Screen

Requirement : How to disable new invoice creation on AP invoice creation screen.

Solution:
Please follow the steps as per below screen shots.

  Once this is done save and open the form again.

You can do persanalization based at Site, Application, Responsibility or User level

R12 User Guide Link

R12 User Guide Link:

R12.1.1 http://download.oracle.com/docs/cd/B53825_03/curre nt/html/homeset.html

R12.1.2 http://download.oracle.com/docs/cd/B53825_06/curre nt/html/docset.html

Table Registration in Oracle Apps

We can register custom application tables using a PL/SQL routine in the AD_DD package. Below are the Procedures in the AD_DD Package

Procedure register_table
(p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_tab_type in varchar2,
p_next_extent in number default 512,
p_pct_free in number default 10,
p_pct_used in number default 70);

Procedure register_column
(p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_col_name in varchar2,
p_col_seq in number,
p_col_type in varchar2,
p_col_width in number,
p_nullable in varchar2,
p_translate in varchar2,
p_precision in number default null,
p_scale in number default null);

Procedure delete_table
(p_appl_short_name in varchar2,
p_tab_name in varchar2);

Procedure delete_column
(p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_col_name in varchar2);

Parameter Description
p_appl_short_name:
The application short name of the application that owns the table (usually your custom application).
p_tab_name:
The name of the table (in uppercase letters).
p_tab_type:
Use 'T' if it is a transaction table (almost all application tables), or 'S' for a "seed data" table (used only by Oracle Applications products).
p_pct_free:
The percentage of space in each of the table's blocks reserved for future updates to the table (1-99). The sum of p_pct_free and p_pct_used must be less than 100.
p_pct_used:
Minimum percentage of used space in each data block of the table (1-99). The sum of p_pct_free and p_pct_used must be less than 100.
p_col_name:
The name of the column (in uppercase letters).
p_col_seq:
The sequence number of the column in the table (the order in which the column appears in the table definition).
p_col_type:
The column type ('NUMBER', 'VARCHAR2', 'DATE', etc.).
p_col_width:
The column size (a number). Use 9 for DATE columns, 38 for NUMBER columns (unless it has a specific width).
p_nullable:
Use 'N' if the column is mandatory or 'Y' if the column allows null values.
p_translate:
Use 'Y' if the column values will be translated for an Oracle Applications product release (used only by Oracle Applications products) or 'N' if the values are not translated (most application columns).
p_next_extent:
The next extent size, in kilobytes. Do not include the 'K'.
p_precision:
The total number of digits in a number.
p_scale:
The number of digits to the right of the decimal point in a number

Restrict LOV using Form Persanalization

Purpose
The Purpose of this form persanalization is to restrict the list of responsibilities that a user can select

The standard List of Values (LoV) before the form persanalization looks like this:

The List of Values (LoV) after the form persanalization will show less responsibilities.
Restrict the List of Values of the Responsibilities
Before to start with the form persanalization , the following profile options should be set to the user making the form persanalization :
o ‘FND: Diagnostics’ set to ‘Yes’
o ‘Hide Diagnostics menu entry’ set to ‘No’
o ‘Utilities:Diagnostics’ set to ‘Yes’

From the horizontal menu select: Help -> Diagnostics -> Custom Code -> Personalize
In order to restrict the list of responsibilities to be showed in the LoV, we will need to tune the query that generate that LoV modifying the conditions specified in the WHERE clause.

Argument:
SELECT R.RESPONSIBILITY_NAME, A.APPLICATION_NAME, R.RESPONSIBILITY_ID, R.APPLICATION_ID FROM FND_APPLICATION_VL A, FND_RESPONSIBILITY_VL R WHERE ( R.APPLICATION_ID = A.APPLICATION_ID AND (R.VERSION = '4' OR R.VERSION = 'W' OR R.VERSION= 'M' OR R.VERSION = 'H') AND (R.END_DATE IS NULL OR (TRUNC(SYSDATE) BETWEEN R.START_DATE AND R.END_DATE)) ) AND (R.RESPONSIBILITY_NAME LIKE '%GB') ORDER BY RESPONSIBILITY_NAME
To customize the list of responsibilities in the LoV, the WHERE clause that should be modified is
AND (R.RESPONSIBILITY_NAME LIKE '%GB')

Close the form. Open the form again and test it. To get the query running behind the LOV download the form from the server and open it in forms builder and check the Record group of the LOV to get query. Do this persanalization at responsibility level.This needs to be done in the first screen on the form persanalization below processing mode.Please let me know if you face issue.

Custom Document Categories

Que :We are in 11i and in PO Header DFF one segment validation type is table and in Value set the table it takes values from fnd_document_categories_tl.
The specified table category_id= 1000410 and Name CUSTOM1000410. How can I add new value in this name.
Please guide me.

Soln:
Please find the below screen shot
Navigation:
Application Developer -> Attachments -> Document Categories

Need to delete fhe record from the MTL_MATERIAL_TRANSACTIONS_TEMP

Scenario : I had many old record in the mtl_material_transactions_temp

I checked with Inventory team that there is no need for this transaction
to Pushed to mtl_material_transaction as

Inventory and Adjustment done. Can records from be deleted ?

RESOLUTION FOR THIS:

Step 1: Backup
CREATE TABLE BACKUP. MMTT_2009 AS
SELECT * FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE TRANSACTION_SOURCE_TYPE_ID = **
AND ACCT_PERIOD_ID = ****
Step 2: Clear old records
DELETE FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE TRANSACTION_SOURCE_TYPE_ID = **
AND ACCT_PERIOD_ID = ****

Importing Sales Orders in Oracle Order Management

This code was developed on Oracle Applications 11.5.10.2. For R12, may be you need to do some modifications.

CREATE OR REPLACE PROCEDURE xx_parts_sales_order_api (
   p_parts_header_id   IN       NUMBER,
   p_order_type_id     IN       NUMBER,
   p_order_type        IN       VARCHAR2,
   p_msg               OUT      VARCHAR2
)
AS
/**************************************************************************
* Copyright (c)  200           **** ****
* All rights reserved
**************************************************************************
* HEADER
*   $Id: G.prc v 1.1
*
* PROGRAM NAME
*   Parts_Sales_Order_Api.prc
*
* DESCRIPTION
*  Procedure to create SO from Dealer Orders
*
* USAGE
*  Procedure to create SO from Dealer Orders
*
* PARAMETERS
* ==========
* NAME              DESCRIPTION
* ----------------- ------------------------------------------------------
** DEPENDENCIES
*  No
** CALLED BY
*   DCreate SO Button
*
* HISTORY
* =======*
* VERSION DATE          AUTHOR(S)         DESCRIPTION
* ------- ----------- --------------- ------------------------------------
* 1.1     24-Aug-2008   XXX                       XXXX
*************************************************************************/
   l_api_version_number           NUMBER                                 := 1;
   l_return_status                VARCHAR2 (2000);
   l_msg_count                    NUMBER;
   l_msg_data                     VARCHAR2 (2000);
/*****************PARAMETERS****************************************************/
   l_debug_level                  NUMBER                                 := 0;
                                                    -- OM DEBUG LEVEL (MAX 5)
   l_org                          NUMBER      := fnd_profile.VALUE ('ORG_ID');
                                                            -- OPERATING UNIT
   l_no_orders                    NUMBER                                 := 1;
         -- NO OF ORDERS It will be always one as it is created from the form
   l_user                         NUMBER     := fnd_profile.VALUE ('USER_ID');
                                                                      -- USER
   l_resp                         NUMBER     := fnd_profile.VALUE ('RESP_ID');
                                                            -- RESPONSIBLILTY
   l_appl                         NUMBER
                                        := fnd_profile.VALUE ('RESP_APPL_ID');
                                                          -- ORDER MANAGEMENT
/*****************INPUT VARIABLES FOR PROCESS_ORDER API*************************/
   l_header_rec                   oe_order_pub.header_rec_type;
   l_line_tbl                     oe_order_pub.line_tbl_type;
   l_action_request_tbl           oe_order_pub.request_tbl_type;
/*****************OUT VARIABLES FOR PROCESS_ORDER API***************************/
   l_header_rec_out               oe_order_pub.header_rec_type;
   l_header_val_rec_out           oe_order_pub.header_val_rec_type;
   l_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;
   l_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;
   l_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;
   l_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;
   l_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;
   l_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;
   l_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;
   l_line_tbl_out                 oe_order_pub.line_tbl_type;
   l_line_val_tbl_out             oe_order_pub.line_val_tbl_type;
   l_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;
   l_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;
   l_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;
   l_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;
   l_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;
   l_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;
   l_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;
   l_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;
   l_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;
   l_action_request_tbl_out       oe_order_pub.request_tbl_type;

/**************************Cursor to fetch data from the Dealer Order Entry Form********************************/
   CURSOR cur_parts_so
   IS
      SELECT inventory_item_id, item_name, quantity, unit_selling_price,
             line_discount, line_number, parts_header_id, uom
        FROM xx_dealparts_lines_v gdl
       WHERE gdl.parts_header_id = p_parts_header_id;

   cur_parts_rec                  cur_parts_so%ROWTYPE;

/*****************************CURSOR TO FETCH LINES FOR RESERVATIONS*******************************************/
   CURSOR cur_reserve_line
   IS
      SELECT header_id, line_id, line_type_id, order_quantity_uom,
             ship_from_org_id, inventory_item_id, item_revision,
             NULL subinventory_code, ' ' demand_source_name,
             2 demand_source_type_id, ordered_quantity
        FROM oe_order_lines_v
       WHERE attribute15 = p_parts_header_id;

   cur_reserve_line_rec           cur_reserve_line%ROWTYPE;
   ln_reservation_id              NUMBER;
   ln_reservation_status          VARCHAR2 (1);
   ln_reserved_quantity           NUMBER;
/***************************************************************************************************************/
   l_msg_index                    NUMBER;
   l_data                         VARCHAR2 (2000);
   l_loop_count                   NUMBER;
   l_debug_file                   VARCHAR2 (200);
   ln_sold_to_org_id              NUMBER;
   ln_price_list_id               NUMBER;
   lv_sell_price_curr             VARCHAR2 (3);
   ln_line_no                     NUMBER;
-- book API vars
   b_return_status                VARCHAR2 (200);
   b_msg_count                    NUMBER;
   b_msg_data                     VARCHAR2 (2000);
BEGIN
   DBMS_APPLICATION_INFO.set_client_info (l_org);

/*****************INITIALIZE DEBUG INFO*************************************/
   IF (l_debug_level > 0)
   THEN
      l_debug_file := oe_debug_pub.set_debug_mode ('FILE');
      oe_debug_pub.initialize;
      oe_debug_pub.setdebuglevel (l_debug_level);
      oe_msg_pub.initialize;
   END IF;

   DBMS_OUTPUT.put_line ('Step 1');
/*****************INITIALIZE ENVIRONMENT*************************************/
   fnd_global.apps_initialize (l_user, l_resp, l_appl);
                     -- pass IN user_id, responsibility_id, AND application_id
/*****************INITIALIZE HEADER RECORD******************************/
   l_header_rec := oe_order_pub.g_miss_header_rec;

/*********Write validations for getting variable values**************************/
   BEGIN
      SELECT dealer_id
        INTO ln_sold_to_org_id
        FROM xx_dealparts_headers_v gdh
       WHERE gdh.parts_header_id = p_parts_header_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         p_msg := 'Check customer setup Bill To Location';
         DBMS_OUTPUT.put_line ('Check customer setup Bill To Location');
   END;

   BEGIN
      SELECT DISTINCT price_list_id
                 INTO ln_price_list_id
                 FROM oe_order_types_v
                WHERE order_type_id = p_order_type_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         p_msg :=
                 'Check Price List against the Order Type - ' || p_order_type;
         DBMS_OUTPUT.put_line
                             (   'Check Price List against the Order Type - '
                              || p_order_type
                             );
   END;

   BEGIN
      SELECT currency_code
        INTO lv_sell_price_curr
        FROM qp_secu_list_headers_v
       WHERE list_header_id = ln_price_list_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         p_msg := 'Check selling price currency in parts price list';
         DBMS_OUTPUT.put_line
                          ('Check selling price currency in parts price list');
   END;

/*****************POPULATE REQUIRED ATTRIBUTES **********************************/
   DBMS_OUTPUT.put_line ('Step 2');
   l_header_rec.operation := oe_globals.g_opr_create;
   l_header_rec.order_type_id := p_order_type_id;
   l_header_rec.sold_to_org_id := ln_sold_to_org_id;
   l_header_rec.price_list_id := ln_price_list_id;
   l_header_rec.pricing_date := SYSDATE;
   l_header_rec.transactional_curr_code := lv_sell_price_curr;
   l_header_rec.flow_status_code := 'ENTERED';
   l_header_rec.cust_po_number := NULL;
   l_header_rec.attribute14 := p_parts_header_id;
   DBMS_OUTPUT.put_line ('Step 3');
/*****************INITIALIZE ACTION REQUEST RECORD*************************************/
   l_action_request_tbl (1) := oe_order_pub.g_miss_request_rec;

/*****************INITIALIZE LINE RECORD********************************/
   OPEN cur_parts_so;

   LOOP
      FETCH cur_parts_so
       INTO cur_parts_rec;

      EXIT WHEN cur_parts_so%NOTFOUND;
      ln_line_no := NVL (ln_line_no, 0) + 1;
      l_line_tbl (ln_line_no) := oe_order_pub.g_miss_line_rec;
      l_line_tbl (ln_line_no).operation := oe_globals.g_opr_create;
      l_line_tbl (ln_line_no).inventory_item_id :=
                                              cur_parts_rec.inventory_item_id;
                                                            --32039;--201775;
      l_line_tbl (ln_line_no).ordered_quantity := cur_parts_rec.quantity;
      l_line_tbl (ln_line_no).calculate_price_flag := 'N';
      l_line_tbl (ln_line_no).tax_exempt_flag := 'S';
      l_line_tbl (ln_line_no).attribute14 := cur_parts_rec.line_number;
      l_line_tbl (ln_line_no).attribute15 := cur_parts_rec.parts_header_id;
      l_line_tbl (ln_line_no).unit_list_price :=
           (  (  NVL (cur_parts_rec.quantity, 0)
               * NVL (cur_parts_rec.unit_selling_price, 0)
              )
            - (  (  NVL (cur_parts_rec.quantity, 0)
                  * NVL (cur_parts_rec.unit_selling_price, 0)
                 )
               * (NVL (cur_parts_rec.line_discount, 0) / 100)
              )
           )
         / cur_parts_rec.quantity;
      l_line_tbl (ln_line_no).unit_selling_price :=
           (  (  NVL (cur_parts_rec.quantity, 0)
               * NVL (cur_parts_rec.unit_selling_price, 0)
              )
            - (  (  NVL (cur_parts_rec.quantity, 0)
                  * NVL (cur_parts_rec.unit_selling_price, 0)
                 )
               * (NVL (cur_parts_rec.line_discount, 0) / 100)
              )
           )
         / cur_parts_rec.quantity;
      DBMS_OUTPUT.put_line ('Step 4');
   END LOOP;

   CLOSE cur_parts_so;

   FOR i IN 1 .. l_no_orders
   LOOP                                                          -- BEGIN LOOP
/*****************CALLTO PROCESS ORDER API*********************************/
      DBMS_OUTPUT.put_line ('Step 5');
      oe_order_pub.process_order
                   (p_api_version_number          => l_api_version_number,
                    p_header_rec                  => l_header_rec,
                    p_line_tbl                    => l_line_tbl,
                    p_action_request_tbl          => l_action_request_tbl,
-- OUT variables
                    x_header_rec                  => l_header_rec_out,
                    x_header_val_rec              => l_header_val_rec_out,
                    x_header_adj_tbl              => l_header_adj_tbl_out,
                    x_header_adj_val_tbl          => l_header_adj_val_tbl_out,
                    x_header_price_att_tbl        => l_header_price_att_tbl_out,
                    x_header_adj_att_tbl          => l_header_adj_att_tbl_out,
                    x_header_adj_assoc_tbl        => l_header_adj_assoc_tbl_out,
                    x_header_scredit_tbl          => l_header_scredit_tbl_out,
                    x_header_scredit_val_tbl      => l_header_scredit_val_tbl_out,
                    x_line_tbl                    => l_line_tbl_out,
                    x_line_val_tbl                => l_line_val_tbl_out,
                    x_line_adj_tbl                => l_line_adj_tbl_out,
                    x_line_adj_val_tbl            => l_line_adj_val_tbl_out,
                    x_line_price_att_tbl          => l_line_price_att_tbl_out,
                    x_line_adj_att_tbl            => l_line_adj_att_tbl_out,
                    x_line_adj_assoc_tbl          => l_line_adj_assoc_tbl_out,
                    x_line_scredit_tbl            => l_line_scredit_tbl_out,
                    x_line_scredit_val_tbl        => l_line_scredit_val_tbl_out,
                    x_lot_serial_tbl              => l_lot_serial_tbl_out,
                    x_lot_serial_val_tbl          => l_lot_serial_val_tbl_out,
                    x_action_request_tbl          => l_action_request_tbl_out,
                    x_return_status               => l_return_status,
                    x_msg_count                   => l_msg_count,
                    x_msg_data                    => l_msg_data
                   );
      DBMS_OUTPUT.put_line (   'Step 6 - '
                            || l_return_status
                            || ' - '
                            || l_debug_level
                            || ' - '
                            || SQLERRM
                            || ' - '
                            || fnd_api.g_ret_sts_success
                           );

/************************************************************************/
 -- open cur_reserve_line;
      FOR cur_reserve_line_rec IN cur_reserve_line
      LOOP
         ln_reservation_id := NULL;
         ln_reservation_status := NULL;
         ln_reserved_quantity := NULL;
         cur_reserve_line_rec.header_id := NULL;
         cur_reserve_line_rec.header_id := NULL;
         cur_reserve_line_rec.order_quantity_uom := NULL;
         cur_reserve_line_rec.inventory_item_id := NULL;
         cur_reserve_line_rec.ship_from_org_id := NULL;
--  p_msg := cur_reserve_line_rec.header_id||' - '||cur_reserve_line_rec.line_id;
    --Fetch cur_reserve_line into cur_reserve_line_rec;
    --EXIT WHEN cur_reserve_line%NOTFOUND;
         xx_so_reservation_api.insert_reservations
            (p_reservation_date             => SYSDATE,
             p_ship_from_org_id             => cur_reserve_line_rec.ship_from_org_id,
             p_inventory_item_id            => cur_reserve_line_rec.inventory_item_id,
             p_demand_source_type_id        => cur_reserve_line_rec.demand_source_type_id,
             p_demand_source_name           => NULL
                                     --cur_reserve_line_rec.demand_source_name
                                                   ,
             p_demand_source_header_id      => cur_reserve_line_rec.header_id,
             p_demand_source_line_id        => cur_reserve_line_rec.line_id,
             p_uom_code                     => cur_reserve_line_rec.order_quantity_uom,
             p_quantity                     => cur_reserve_line_rec.ordered_quantity,
             p_revision                     => cur_reserve_line_rec.item_revision
                                                          -- Item revision\par
                                                                                 ,
             p_subinventory_code            => NULL
              --cur_reserve_line_rec.subinventory_code-- Subinventory Code\par
                                                   ,
             p_locator_id                   => NULL          -- Locator ID\par
                                                   ,
             p_lot_number                   => NULL          -- Lot Number\par
                                                   ,
             p_reservation_id               => ln_reservation_id
                                                         -- Reservation ID\par
                                                                ,
             p_status                       => ln_reservation_status
                                      -- Status 'S' - Success, 'E' - error\par
                                                                    ,
             p_reserved_qty                 => ln_reserved_quantity
            );
      END LOOP;

      --close cur_reserve_line;

      /*****************CHECK RETURN STATUS***********************************/
      IF l_return_status = fnd_api.g_ret_sts_success
      THEN
         IF (l_debug_level > 0)
         THEN
            DBMS_OUTPUT.put_line ('success');
         END IF;

         COMMIT;
         oe_order_book_util.complete_book_eligible
                                                  (1.0,
                                                   fnd_api.g_false,
                                                   l_header_rec_out.header_id,
                                                   b_return_status,
                                                   b_msg_count,
                                                   b_msg_data
                                                  );
         COMMIT;
      ELSE
         IF (l_debug_level > 0)
         THEN
            DBMS_OUTPUT.put_line ('failure');
         END IF;

         ROLLBACK;
      END IF;
   END LOOP;                                                       -- END LOOP

/*****************DISPLAY RETURN STATUS FLAGS******************************/
   IF (l_debug_level > 0)
   THEN
      DBMS_OUTPUT.put_line ('process ORDER ret status IS: ' || l_return_status
                           );
      DBMS_OUTPUT.put_line ('process ORDER msg DATA IS: ' || l_msg_data);
      DBMS_OUTPUT.put_line ('process ORDER msg COUNT IS: ' || l_msg_count);
      DBMS_OUTPUT.put_line (   'HEADER.order_number IS: '
                            || TO_CHAR (l_header_rec_out.order_number)
                           );
      DBMS_OUTPUT.put_line (   'HEADER.return_status IS: '
                            || l_header_rec_out.return_status
                           );
      DBMS_OUTPUT.put_line (   'HEADER.booked_flag IS: '
                            || l_header_rec_out.booked_flag
                           );
      DBMS_OUTPUT.put_line (   'HEADER.header_id IS: '
                            || l_header_rec_out.header_id
                           );
      DBMS_OUTPUT.put_line (   'HEADER.order_source_id IS: '
                            || l_header_rec_out.order_source_id
                           );
      DBMS_OUTPUT.put_line (   'HEADER.flow_status_code IS: '
                            || l_header_rec_out.flow_status_code
                           );
   END IF;

/*****************DISPLAY ERROR MSGS*************************************/
   IF (l_debug_level > 0)
   THEN
      FOR i IN 1 .. l_msg_count
      LOOP
         oe_msg_pub.get (p_msg_index          => i,
                         p_encoded            => fnd_api.g_false,
                         p_data               => l_data,
                         p_msg_index_out      => l_msg_index
                        );
         DBMS_OUTPUT.put_line ('message IS: ' || l_data);
         DBMS_OUTPUT.put_line ('message INDEX IS: ' || l_msg_index);
      END LOOP;
   END IF;

   IF (l_debug_level > 0)
   THEN
      DBMS_OUTPUT.put_line ('DEBUG = ' || oe_debug_pub.g_debug);
      DBMS_OUTPUT.put_line (   'DEBUG LEVEL = '
                            || TO_CHAR (oe_debug_pub.g_debug_level)
                           );
      DBMS_OUTPUT.put_line (   'DEBUG FILE = '
                            || oe_debug_pub.g_dir
                            || '/'
                            || oe_debug_pub.g_file
                           );
      DBMS_OUTPUT.put_line
                       ('****************************************************');
      oe_debug_pub.debug_off;
   END IF;
END;

KFF Custom Form Window doesnot Pop Up

KFF does not pop up in the custom form - Form Triggers:

Solution:
Triggers in the KFF item should be modified as below:
WHEN-VALIDATE-ITEM
if ( :system.mode = 'NORMAL' ) then
fnd_flex.event( 'WHEN-VALIDATE-ITEM' );
end if;
WHEN-NEW-ITEM-INSTANCE
app_standard.event('WHEN-NEW-ITEM-INSTANCE');
fnd_flex.event('WHEN-NEW-ITEM-INSTANCE' );
POST-QUERY
--Loads the flexfields (in our case, it populates --the concatenated field on execute query).
FND_FLEX.EVENT('POST-QUERY');
PRE-QUERY
--If you don't do this, whatever query criteria you may enter in
-- the concatenated flex field, it is not taken into account.
FND_FLEX.EVENT('PRE-QUERY' );
KEY-LISTVAL
APP_STANDARD.EVENT('KEY-LISTVAL');
FND_FLEX.EVENT('KEY-LISTVAL' );

Date Format Conversion

How to convert Mon Nov 19 19:00:17 PST 2007 covert to "MM-DD-YYYY HH:MI:SS" FORMAT?



Solution:
select to_char(to_date(replace('Mon Nov 19 19:00:17 PST 2007','PST',''),'DY Mon DD HH24:MI:SS YYYY'),'MM-DD-YYYY HH:MI:SS') from dual;

PO Receipt Conversion

Requirement – Details about PO Receipt Creation

Solution:

INSERT INTO rcv_headers_interface
(
header_interface_id
, group_id
, processing_status_code
, receipt_source_code
, transaction_type
, last_update_date
, last_updated_by
, last_update_login
, vendor_id
, receipt_num
, expected_receipt_date
, validation_flag
)
SELECT
rcv_headers_interface_s.NEXTVAL
, 1--rcv_interface_groups_s.NEXTVAL
, 'PENDING'
, 'VENDOR'
, 'NEW'
, SYSDATE
, 1--ln_user_id
, 0
, ln_vendor_id
, cur_inv.invoice_num
, SYSDATE
, 'Y'
FROM dual;

FOR cursor1 IN cur_item_reciept(cur_inv.invoice_num)
LOOP
IF cursor1.closed_code IN ('APPROVED','OPEN')
AND cursor1.quantity_received < cursor1.quantity
AND NVL(cursor1.cancel_flag,'N') = 'N' THEN

INSERT INTO rcv_transactions_interface
(
interface_transaction_id
, group_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, transaction_type
, transaction_date
, processing_status_code
, processing_mode_code
, transaction_status_code
, po_line_id
, item_id
, quantity
, unit_of_measure
, po_line_location_id
, auto_transact_code
, receipt_source_code
, to_organization_code
, source_document_code
, document_num
, header_interface_id
, validation_flag
)
SELECT
rcv_transactions_interface_s.NEXTVAL
, 1--rcv_interface_groups_s.CURRVAL
, SYSDATE
, ln_user_id
, SYSDATE
, ln_user_id
, 0
, 'RECEIVE'
, SYSDATE
, 'PENDING'
, 'BATCH'
, 'PENDING'
, cursor1.po_line_id
, cursor1.item_id
, cursor1.quantity
, cursor1.unit_meas_lookup_code
, cursor1.line_location_id
, 'RECEIVE'
, 'VENDOR'
, cursor1.organization_code
, 'PO'
, cursor1.segment1
, rcv_headers_interface_s.CURRVAL
, 'Y'
FROM dual;
Then run Receiving Transaction Processor.
Go through Metalink Doc - 307166.1
Cheersss...