Search This Blog

Friday, December 30, 2011

Item Availability Information in iStore's Cart Page

To display Item Availability Information in iStore Cart: 
Disclaimer:
This customization is to be used at your own risk. We are not responsible for any sort of inconsistencies or problems due to that. Implement this customization in test instance and test all possible scenarios.
 

1. Download below files
  xx_ibeCScdItemAvailability.pdf
  xx_ibeCScdViewA.pdf

2. Rename those two files to xx_ibeCScdItemAvailability.jsp and xx_ibeCScdViewA.jsp

3. Port these files in $OA_HTML directory.

4. If automatic JSP compilation is already set in your environment then no need to compile these files explicitly(Meta Link #458338.1)
   Otherwise compile those two files using below commands..
   a) cd $FND_TOP/patch/115/bin
   b) perl ojspCompile.pl --compile -s xx_ibeCScdItemAvailability.jsp
   c) perl ojspCompile.pl --compile -s xx_ibeCScdViewA.jsp
 
5. Create a new template for xx_ibeCScdItemAvailability.jsp:
 5.1 Navigation: Login to oralce applications, iStore Administrator(resp) -> ibe Merchant: Advanced -> Template Manager
 5.2 Click on Add Template
     Enter Below details
      Name               : LSS_ITEM_AVAILABILITY
      Program Access Name:  LSS_ITEM_AVAILABILITY
      Description        :  Modified Cart page for only Active Carts
      Applicable to      : Shopping Cart
     Click on Apply
  5.3 Click on Add Source File
  Give Details of the file as below:
    Source File Name: xx_ibeCScdItemAvailability.jsp
    Description     : Customized Cart page to include Item Availability Information in Active Cart
    Site            : <Your Site Name>
    Language        : American English
 Click on Apply.

6. Mapping xx_ibeCScdViewA.jsp
 6.1 Search for the template with Programmatic Access Name as 'STORE_CART_MODIFY'
 6.2 Click on Update Icon.
 6.3 Click on Add Source File
    Enter details as below
         Source File Name: xx_ibeCScdViewA.jsp
         Description     : Customized Cart Modify Page for Starrett Site
         Site            : <Your Site Name>
         Language        : American English
    Click on Apply.

7. Modify url_fw.conf file to allow these custom pages in firewall
 7.1 Take backup of existing url_fw.conf file.
 7.2 Comment below line in url_fw.conf (Add # in front to comment the line)
       RewriteRule  ^/OA_HTML/ibeC.*\.jsp$  - [L]
 7.3 Add below lines to allow custom JSPs and Custom Login pages in firewall
       RewriteRule ^/OA_HTML/ibeC.*\.jsp(;jsessionid=[.a-z0-9A-Z]+){0,1}$ - [L]
       RewriteRule  ^/OA_HTML/xx.*\.jsp$  - [L]

8.  Creating Custom Error messages for Item Availability Information field in Cart Page
 8.1 Navigation: Application Developer(resp) → Application → Messages 
 8.2 Create message 'LSS_IBE_AVAIL_INFO_NEEDBY':
      Enter Details as below and save the page:
        Name                 : LSS_IBE_AVAIL_INFO_NEEDBY
    Language             : US
    Application          : iStore
    Type                 : 30% Expansion prompt
    Description          : Message for displaying earliest shipdate
    Current Message Text : Estimated Ship Date for Remaining Qty:
 8.3 Create message 'LSS_IBE_NOT_AVIL_INFO':
       Enter Details as below and save the page:
    Name                : LSS_IBE_NOT_AVIL_INFO
    Language            : US
    Application         : iStore
    Type                : 30% Expansion prompt
    Description        : Message for displaying Item Availability Information
    Current Message Text: Total Quantity is Not Available
 8.4 Create message 'LSS_IBE_AVAIL_INFO_QTY':
       Enter Details as below and save the page:
     Name                : LSS_IBE_AVAIL_INFO_QTY
     Language            : US
         Application         : iStore
         Type                : 30% Expansion prompt
         Description         : Message for displaying available qty on requested date
         Current Message Text: Quantity available:

9. Remove cache and Bounce oacore and apache services.

Centralized Vs Distributed Architecture of Oracle Applications


Centralized Installation - single database where the EBS Source applications for the OLTP transactions and setups used by VCP applications are all on the same database. Data Collections moves the data from these tables for INV, PO, BOM, WIP, OE, etc from these base tables to the MSC tables where this data can be used by our applications.

Distributed Installation - Two (or more) databases located on different machines. One is the EBS Source Instance where all OLTP transactions and setups are performed.The APS Destination is where Data Collections process is launched and uses database links to communicate and move the data to the MSC tables in the APS Destination.

Advantages of Distributed Architecture:

 1. The processing performed for Planning in ASCP, IO, DRP, etc.  can take huge amounts of memory and disk IO. This can slow down the OLTP processing while the plan processes are running. It is not unusual for large plans to consume anywhere from 5-20+ GB of memory while running and move millions of rows into and out of tables to complete the planning process.

 2. It is possible to have APS Destination on different release.
APS Destination could be 12.1 with EBS Source running 11.5.10 or 12.0.6
  
 3. It is possible to have more than one EBS Source connected to the APS Destination.

Wednesday, December 28, 2011

Item Supply Demand

Item Supply Demand Form: This is very popular form to supply/demand information in the near future. It shows the current on-hand quantity for any item queried and then displays the supply demand information sorted by supply/demand date along with projected on-hand information.

Basically It considers below supply/demand sources:
1. Sales Orders & Internal Sales Orders
2. Reserved Sales Orders
3. WIP Demand(Work Orders)
4. WIP Supply(Work Orders)
5. Purchase Orders

Navigation: Login to Oracle applications -> Inventory(resp) -> Onhand, Availability -> Item Supply/Demand

Below procedure calculates and displays all the above mentioned supply/demand sources for the given item and organization combination.

Create below procedure in your instance and execute as shown below:

set serverout on

begin
lss_isd_proc(<Item Name>,<Organzation_code>);
end;
/

Procedure:
*****************************************************************
*****************************************************************
create or replace procedure LSS_ISD_PROC(p_item IN VARCHAR2, p_org IN VARCHAR2)
as

cursor lss_isd_cur(l_item_id NUMBER, l_org_id NUMBER)
is
--Reserved Sales Orders
SELECT
  d.requirement_date Required_date ,
  ml.meaning Supply_Demand_Type,
  to_char(ooha.order_number) Identifier,
  -1 * ( d.primary_uom_quantity - GREATEST (NVL (d.reservation_quantity, 0),
  d.completed_quantity) ) quantity
FROM
  mtl_parameters p,
  mtl_system_items i,
  bom_calendar_dates c,
  mtl_demand d,
  mfg_lookups ml,
  (
    SELECT
      DECODE (demand_source_type, 2, DECODE (reservation_type, 1, 2, 3, DECODE
      (supply_source_type, 5, 23, 31), 9 ), 8, DECODE (reservation_type, 1, 21,
      22), demand_source_type) supply_demand_source_type,
      demand_id
    FROM
      mtl_demand
  )
  dx,
  oe_order_headers_all ooha,
  oe_order_lines_all oola
WHERE
  1                        =1
AND d.demand_source_line   = oola.line_id
AND ooha.header_id         = oola.header_id
AND d.organization_id      = l_org_id
AND d.demand_id            = dx.demand_id
AND ml.lookup_type         = 'MTL_SUPPLY_DEMAND_SOURCE_TYPE'
AND ml.lookup_code         = dx.supply_demand_source_type
AND d.primary_uom_quantity > GREATEST (NVL (d.reservation_quantity, 0),
  d.completed_quantity)
AND d.inventory_item_id   = l_item_id
AND d.available_to_atp    = 1
AND d.reservation_type   != -1
AND d.demand_source_type != 13
AND d.demand_source_type != -1
AND
  (
    d.subinventory  IS NULL
  OR d.subinventory IN
    (
      SELECT
        s.secondary_inventory_name
      FROM
        mtl_secondary_inventories s
      WHERE
        s.organization_id      = d.organization_id
      AND s.inventory_atp_code = 1
    )
  )
AND i.organization_id           = d.organization_id
AND i.inventory_item_id         = d.inventory_item_id
AND p.organization_id           = d.organization_id
AND p.calendar_code             = c.calendar_code
AND p.calendar_exception_set_id = c.exception_set_id
AND c.calendar_date             = TRUNC (d.requirement_date)
AND d.inventory_item_id         = DECODE (d.reservation_type, 1, DECODE (
  d.parent_demand_id, NULL, d.inventory_item_id, -1 ), 2, d.inventory_item_id,
  3, d.inventory_item_id,                        -1 )
UNION
-- Sales Orders and Internal Sales Orders
SELECT   d.requirement_date required_date, ml.meaning Supply_demand_Type,
to_char(ooha.order_number) Identifier,
NVL(  -1
       * (  d.primary_uom_quantity
          - d.total_reservation_quantity
          - d.completed_quantity
         ), 0) Quantity            
  FROM mtl_parameters p,
       mtl_system_items i,
       bom_calendar_dates c,
       mrp_demand_om_reservations_v d,
       oe_order_headers_all ooha,
       oe_order_lines_all oola,
       mfg_lookups ml,
       (select DECODE (demand_source_type,
               2, DECODE (reservation_type, 1, 2, 3, 23, 9),
               8, DECODE (reservation_type, 1, 21, 22),
               demand_source_type
              ) supply_demand_source_type, demand_id from  mrp_demand_om_reservations_v) dx
 WHERE d.open_flag = 'Y'
   AND ml.lookup_type = 'MTL_SUPPLY_DEMAND_SOURCE_TYPE'
   and ml.lookup_code = dx.supply_demand_source_type
   and d.demand_id = dx.demand_id
   AND ooha.header_id = oola.header_id
   and oola.line_id = d.demand_id
   AND d.reservation_type != 2
   AND d.organization_id = l_org_id
   AND d.primary_uom_quantity >
                        (d.total_reservation_quantity + d.completed_quantity
                        )
   AND d.inventory_item_id = l_item_id
   AND (   d.visible_demand_flag = 'Y'
        OR (    NVL (d.visible_demand_flag, 'N') = 'N'
            AND d.ato_line_id IS NOT NULL
            AND NOT EXISTS (
                   SELECT NULL
                     FROM oe_order_lines_all ool, mtl_demand md
                    WHERE TO_CHAR (ool.line_id) = md.demand_source_line
                      AND ool.ato_line_id = d.ato_line_id
                      AND ool.item_type_code = 'CONFIG'
                      AND md.reservation_type IN (2, 3))
           )
       )
   AND d.reservation_type != -1
   AND d.reservation_type != -1
   AND d.demand_source_type != -1
   AND d.demand_source_type != -1
   AND (d.subinventory IS NULL
        OR d.subinventory IN (
              SELECT s.secondary_inventory_name
                FROM mtl_secondary_inventories s
               WHERE s.organization_id = d.organization_id
                 AND s.inventory_atp_code = 1
                 AND s.attribute1 = 'FG')
                 )
   AND i.organization_id = d.organization_id
   AND i.inventory_item_id = d.inventory_item_id
   AND p.organization_id = d.organization_id
   AND p.calendar_code = c.calendar_code
   AND p.calendar_exception_set_id = c.exception_set_id
   AND c.calendar_date = TRUNC (d.requirement_date)
   AND d.inventory_item_id =
          DECODE (d.reservation_type,
                  1, DECODE (d.parent_demand_id,
                             NULL, d.inventory_item_id,
                             -1
                            ),
                  2, d.inventory_item_id,
                  3, d.inventory_item_id,
                  -1
                 )
UNION
--WIP DEMAND
  SELECT   o.date_required required_date,    ml.meaning Supply_Demand_Type, we.wip_entity_name Identifier,
        LEAST (-1 * (o.required_quantity - o.quantity_issued), 0) quantity
  FROM                                          
       mtl_parameters p,
       mfg_lookups ml,
       -- mtl_atp_rules r,
       mtl_system_items i,
       bom_calendar_dates c,
       wip_requirement_operations o,
       wip_discrete_jobs d,
       wip_entities we,
       (select DECODE (job_type, 1, 5, 7) supply_demand_source_type, wip_entity_id from wip_discrete_jobs) dx
 WHERE 1 = 1
 and we.wip_entity_id = d.wip_entity_id
   AND ml.lookup_type         = 'MRP_SUPPLY_DEMAND_SOURCE_TYPE'
  AND ml.lookup_code         = dx.supply_demand_source_type
  and d.wip_entity_id = dx.wip_entity_id
   AND o.organization_id = d.organization_id
   AND o.organization_id = l_org_id
   AND o.inventory_item_id = l_item_id
   AND o.wip_entity_id = d.wip_entity_id
   AND o.wip_supply_type NOT IN (5, 6)
   AND o.required_quantity > 0
   AND o.required_quantity <> (o.quantity_issued)
   AND o.operation_seq_num > 0
   AND o.date_required IS NOT NULL
   AND (   o.supply_subinventory IS NULL
        OR EXISTS (
              SELECT 'X'
                FROM mtl_secondary_inventories s
               WHERE s.organization_id = o.organization_id
                 AND o.supply_subinventory = s.secondary_inventory_name
                 AND s.inventory_atp_code = 1)
       )
   AND d.status_type IN (1, 3, 4, 6)
   AND p.organization_id = o.organization_id
   AND i.organization_id = o.organization_id
   AND i.inventory_item_id = o.inventory_item_id
   AND p.calendar_code = c.calendar_code
   AND p.calendar_exception_set_id = c.exception_set_id
   AND c.calendar_date = TRUNC (o.date_required)
UNION
--WIP Supply
   SELECT
  d.scheduled_completion_date required_date,
  ml.meaning Supply_Demand_Type,
  we.wip_entity_name Identifier,
  (d.start_quantity - d.quantity_completed - d.quantity_scrapped )Quantity
FROM
  wip_discrete_jobs d,
  bom_calendar_dates c,
  mtl_parameters p,
  mtl_system_items i,
  wip_entities we,
  (
    SELECT
      DECODE (job_type, 1, 5, 7) supply_demand_source_type,
      wip_entity_id
    FROM
      wip_discrete_jobs
  )
  dx,
  mfg_lookups ml
WHERE
  1                              =1
AND d.wip_entity_id              = dx.wip_entity_id
AND dx.supply_demand_source_type = ml.lookup_code
AND ml.lookup_type               = 'MRP_SUPPLY_DEMAND_SOURCE_TYPE'
AND d.wip_entity_id              = we.wip_entity_id
AND d.status_type               IN (1, 3, 4, 6)
AND
  (
    d.start_quantity - d.quantity_completed
  )
                      > 0
AND d.organization_id = l_org_id
AND d.primary_item_id = l_item_id
AND
  (
    d.completion_subinventory IS NULL
  OR EXISTS
    (
      SELECT
        'X'
      FROM
        mtl_secondary_inventories s
      WHERE
        s.organization_id           = d.organization_id
      AND d.completion_subinventory = s.secondary_inventory_name
      AND s.inventory_atp_code      = 1
    )
  )
AND p.organization_id           = d.organization_id
AND i.organization_id           = d.organization_id
AND i.inventory_item_id         = d.primary_item_id
AND p.calendar_code             = c.calendar_code
AND p.calendar_exception_set_id = c.exception_set_id
AND c.calendar_date             = TRUNC (d.scheduled_completion_date)
UNION ALL
SELECT
  d.scheduled_completion_date required_date,
  ml.meaning Supply_Demand_Type,
  we.wip_entity_name Identifier,
  (d.start_quantity - d.quantity_completed - d.quantity_scrapped ) Quantity
FROM
  mtl_parameters p,
  mtl_system_items i,
  bom_calendar_dates c,
  wip_requirement_operations o,
  wip_discrete_jobs d,
  wip_entities we,
  (
    SELECT
      DECODE (job_type, 1, 5, 7) supply_demand_source_type,
      wip_entity_id
    FROM
      wip_discrete_jobs
  )
  dx,
  mfg_lookups ml
WHERE
  1                             =1
AND d.wip_entity_id             = dx.wip_entity_id
AND dx.supply_demand_source_type= ml.lookup_code
AND ml.lookup_type              = 'MRP_SUPPLY_DEMAND_SOURCE_TYPE'
AND we.wip_entity_id            = d.wip_entity_id
AND o.organization_id           = d.organization_id
AND o.inventory_item_id         = l_item_id
AND o.wip_entity_id             = d.wip_entity_id
AND o.organization_id           = l_org_id
AND o.wip_supply_type NOT      IN (5, 6)
AND o.required_quantity         < 0
AND
  (
    o.required_quantity - o.quantity_issued
  )
                        < 0
AND o.operation_seq_num > 0
AND
  (
    d.completion_subinventory IS NULL
  OR EXISTS
    (
      SELECT
        'X'
      FROM
        mtl_secondary_inventories s
      WHERE
        s.organization_id           = d.organization_id
      AND d.completion_subinventory = s.secondary_inventory_name
      AND s.inventory_atp_code      = 1
    )
  )
AND
  (
    d.job_type  = 1
  OR d.job_type = 3
  )
AND d.status_type              IN (1, 3, 4, 6)
AND d.organization_id           = o.organization_id
AND p.organization_id           = o.organization_id
AND i.organization_id           = o.organization_id
AND i.inventory_item_id         = o.inventory_item_id
AND p.calendar_code             = c.calendar_code
AND p.calendar_exception_set_id = c.exception_set_id
AND c.calendar_date             = TRUNC (o.date_required)
UNION
--Purchase Orders:
SELECT c.next_date Required_Date,
  ml.meaning Supply_demand_Type,
  sx.identifier,
  DECODE (s.supply_type_code, 'SHIPMENT', s.to_org_primary_quantity,
  s.to_org_primary_quantity ) Quantity
FROM
  mtl_system_items i,
  mtl_parameters p,
  bom_calendar_dates c,
  mtl_supply s,
  mfg_lookups ml,
  (    SELECT
      DECODE (ms.po_header_id, NULL, DECODE (ms.supply_type_code, 'REQ', DECODE (
      ms.from_organization_id, NULL, 18, 20), 12 ), DECODE (ms.supply_type_code,
      'SHIPMENT', 35, 'RECEIVING', 36, 1) ) supply_demand_source_type,
      poh.segment1 Identifier,
      supply_source_id
    FROM
      mtl_supply ms,
      po_headers_all poh
    WHERE
      1=1
    AND poh.po_header_id = ms.po_header_id
  ) sx
WHERE
    1  = 1
AND s.supply_source_id = sx.supply_source_id
AND ml.lookup_type     = 'MRP_SUPPLY_DEMAND_SOURCE_TYPE'
AND ml.lookup_code     = sx.supply_demand_source_type
AND
  (
    (
      s.req_header_id  IS NULL
    AND s.po_header_id IS NULL
    )
  OR
    (
      s.req_header_id           = s.req_header_id
    AND s.from_organization_id IS NOT NULL
    )
  OR
    (
      s.supply_type_code        = 'REQ'
    AND s.from_organization_id IS NULL
    )
  OR s.po_header_id = s.po_header_id
  )
AND s.to_organization_id    = l_org_id
AND s.item_id               = l_item_id --v.inventory_item_id
AND s.destination_type_code = 'INVENTORY'
AND
  (
    s.to_subinventory IS NULL
  OR EXISTS
    (
      SELECT
        'X'
      FROM
        mtl_secondary_inventories s2
      WHERE
        s2.organization_id      = s.to_organization_id
      AND s.to_subinventory     = s2.secondary_inventory_name
      AND s2.inventory_atp_code = 1
      AND s2.availability_type  = s2.availability_type
    )
  )
AND i.organization_id           = s.to_organization_id
AND i.inventory_item_id         = s.item_id
AND p.organization_id           = s.to_organization_id
AND p.calendar_code             = c.calendar_code
AND p.calendar_exception_set_id = c.exception_set_id
AND NOT EXISTS
  (
    SELECT
      'X'
    FROM
      oe_drop_ship_sources odss
    WHERE
      DECODE (s.po_header_id, NULL, s.req_line_id, s.po_line_location_id ) =
      DECODE (s.po_header_id, NULL, odss.requisition_line_id,
      odss.line_location_id )
  )
AND c.calendar_date = TRUNC (s.expected_delivery_date);

l_ohq NUMBER := 0;
org_id NUMBER;
item_id NUMBER;

begin


BEGIN
  SELECT organization_id
  INTO org_id
  FROM mtl_parameters
  WHERE 1=1
  AND organization_code = p_org;
  SELECT inventory_item_id
  INTO item_id
  FROM mtl_system_items_b msib
  WHERE 1=1
  AND segment1 like p_item
  AND organization_id = org_id;
  EXCEPTION WHEN NO_DATA_FOUND
  THEN
   DBMS_OUTPUT.PUT_LINE('Invalid Item or Organization');
   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('Unknown Exception while retrieving Item Information');
END;

BEGIN 
        SELECT NVL (SUM (a.primary_transaction_quantity), 0) s
          INTO l_ohq
          FROM mtl_onhand_quantities_detail a
              , mtl_secondary_inventories b
         WHERE a.organization_id = org_id
           AND a.inventory_item_id = item_id
           AND a.subinventory_code = b.secondary_inventory_name
           AND a.organization_id = b.organization_id;
       EXCEPTION
           WHEN OTHERS THEN
              l_ohq :=0;
END;


DBMS_OUTPUT.PUT_LINE('********** Availability Information for ' || p_item ||' from the ORG: ' || p_org ||' *************');
DBMS_OUTPUT.PUT_LINE(CHR(13));

DBMS_OUTPUT.PUT_LINE(RPAD('Request Date',20,' ') ||  RPAD('|Supply/Demand Type',25,' ') || RPAD('|Identifier',20,' ') || RPAD('|Quantity',20,' '));

DBMS_OUTPUT.PUT_LINE(RPAD('_',75,'_'));

FOR lss_isd_rec in lss_isd_cur(item_id,org_id)
LOOP
  BEGIN
    DBMS_OUTPUT.PUT_LINE(                      RPAD(lss_isd_rec.required_date,20,' ')||
              RPAD(lss_isd_rec.supply_demand_type,25,' ')||
              RPAD(lss_isd_rec.identifier,20,' ')||
              RPAD(lss_isd_rec.quantity,20,' '));
    
 Exception when others then

 DBMS_OUTPUT.PUT_LINE('Exception while retrieving Item Supply Demand Info');

 END;
 END LOOP;

 EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unknown Exception in the Procedure lss_isd_proc');

END lss_isd_proc;
/

Tuesday, December 27, 2011

Pricing Engine

Pricing Engine Overview:
     The pricing engine is a software component of Oracle Advanced Pricing that is called by an application such as Oracle Order Management or iStore to apply pricing actions to transactions. Applications make calls to the pricing engine when transactions need pricing services or need to be priced.
       
     The advanced, flexible pricing engine performs pricing and benefit calculations for Oracle Order Management products and Oracle Customer Relationship Management products through open APIs.

The pricing engine performs the following functions:
1. Prepares the price request structure
2. Selects applicable price lists and modifier lists
3. Determines base list price
4. Calculates final selling price
5. Applies benefits and surcharges (from modifier lists) to list price to determine selling price 

Preparing the Price Request Structure : The calling applications submit price requests. This function configures a pricing request into a pricing request structure. The pricing request structure provides information about all the qualifiers and product pricing attributes.
Selecting the Price List or Modifier List : This function selects which price lists are eligible for the current pricing request. It uses the qualifiers and pricing attributes to select an eligible list of prices or modifiers it can apply to the pricing request lines according to the certain rules.
Determining List Price: This function takes the validated price list lines and applies them to the pricing request lines. You can specify the list price on a price list as unit price, percent price, or formula.
Applying Price and Modifier Adjustments: This function takes the validated modifier list lines and applies them to the pricing request lines.
    The modifier function provides price adjustments such as discounts, price breaks, surcharges, coupons, item and term substitutions, and other item discounts. Discount and surcharges modifiers affect the selling price; freight charge modifiers do not affect the selling price.

Oracle Advanced Pricing

Introduction:
                   Oracle Advanced Pricing supports e-business applications by providing a flexible pricing engine that executes pricing and promotional calculations for Oracle Order Management and other Oracle Applications. This application is licensed as Oracle Advanced Pricing.
                       Oracle Advanced Pricing is a rules-based application with an engine component to service the pricing requirements of Oracle applications to price customer transactions. Oracle Advanced Pricing enables you to set up pricing actions such as price lists, agreements, formulas, and modifiers that the pricing engine applies to transactions.
                  Oracle Advanced Pricing also enables you to define a set of pricing rules (and pricing controls that can be used in conjunction with the rules) to precisely govern how and when pricing actions are applied to transactions.

Pricing Components:

1. Qualifiers : Qualifiers determine who is eligible for a price or benefit. Qualifiers and qualifier groups can be linked to price lists and modifiers to define rules for who can receive a particular price, discount, promotion, or benefit. They can assign discounts and promotions to:
  • Specific customers
  • Customer groups
  • Order types
  • Order amount
  • Specific customer or group of customers

2 Qualifier Groups : Qualifier groups enable you to define multiple qualifiers relationships in preparation for association with either price lists or modifiers. You can save these qualifier groups and copy them to one or more price lists and modifiers.
3 Price Lists: Price lists relate a list price to a product. (The list price is the starting price before any related discounts and adjustments are applied.) Price lists can contain one or more price list lines, price breaks, pricing attributes, qualifiers, and secondary price lists.
4 Multiple Currency Price Lists : Multiple currency price lists enables businesses that have pricing strategies based on a single price for an item in a base currency to use exchange rates or formulas to convert that price into the ordering currency. At engine run time, the pricing engine will take the currency from the order and search for a price list or price lists with base or conversion currencies matching this currency. The pricing engine converts the price from the base currency and calculates the ordering currency based upon the established conversion rules.

5 Pricing Attributes : Pricing attributes control what is being priced or modified on a price list or modifier list.

6 Modifiers : Pricing modifiers control how the pricing engine can modify the pricing requests and pricing request lines. The modifiers are:
  • Discount: Reduces the price by a fixed amount or percentage or provides a new price.
  • Surcharge: Increases the price by a fixed amount or percentage or provides a new price.
  • Item upgrade: Replaces an ordered item with another item for the same price as the original item.
  • Other item discount: Gives a price adjustment or benefit to a specified item on an order when the customer orders one or more specified items on that same order.
  • Coupon: Issues a coupon as an offer of discount on other goods and services which is redeemable on a future pricing request.
  • Terms substitution: Upgrades payment, shipping, and freight terms.
  • Freight/Special charges: Charges for shipping and miscellaneous situations, for example, duty, handling charges, and insurance.
  • Promotional goods: Adds a new item to an order and gives this item a price adjustment or benefit when the customer orders one or more specific items on the same order.
  • Price break: Applies a variable discount or surcharge price adjustment to a pricing request based on the break type and condition met. You can use both point- and range-type breaks.
7 Formulas: Pricing formulas allow the pricing engine to determine item prices based on the following:
  • A combination of pricing attributes and factors based on the value of a pricing attribute
  • The list price on the price list line to which the formula is attached
  • The list price on any specific price list line
  • A numeric constant
  • A customizable function
  • You can also attach a formula to a modifier line for the pricing engine to use to calculate discounts.
  • You can use two types of formulas:
Static: You specify the formula and execute a concurrent process which calculates absolute price values.
Dynamic: You specify the formula and the pricing engine uses the formula in its calculations each time that someone orders the product.
8 Buckets: The pricing engine may calculate different selling prices depending on how you group your discounts into buckets. Plan your cascading discounts so that you can assign discounts to buckets based on the subtotal on which each discount needs to be applied.
9 Incompatibility groups and exclusivity: Determine the pricing bands to which the various discounts and promotions belong. Determine which discounts and promotions apply on top of one another, are incompatible, or are exclusive. Analyze all discount schemes and promotions to determine the impact.
10 Attribute Mapping: Attribute mapping is the process used to pass in data (from other applications or systems) that is not seeded in the delivered product into Oracle Advanced Pricing for use in price lists, modifiers, agreements, qualifiers, and formulas.
11 Agreements: Agreements enable you to define prices, payment terms, and freight terms that you negotiated with specific customers. You can
       1. Define your agreements using customer part numbers and inventory item numbers.
       2. Revise the original terms and maintain these changes and their reasons under separate  revision numbers.
       3. Attach an already existing price list to the agreement or define new prices.
      4. Assign optional price breaks by quantity.
      5. Set effective dates for agreement terms.
      6. Set payment terms including invoice rule and accounting rule.
      7. Set freight terms including the freight carrier.
      8. Apply agreement terms to sales orders by reference agreements.

12 GSA Pricing :GSA Pricing enables you to define a GSA price list for your GSA customers. The GSA Price List actually uses the modifiers window and uses the new price. You create a discount that adjusts the base price of the item to the GSA price.

 


Friday, May 27, 2011

Conditionally Enabling/Disabling Concurrent Program Parameters

Introduction : This post provides the guidance to the user with the necessary information for creating parameters for a concurrent program and making them conditionally enable/disable.
Steps to be followed :-
1.Create a procedure with two input parameters.
2.Create an executable with execution method as PL/SQL Procedure
  and execution file as the procedure created in step1.
3.Create a concurrent program with executable created in step2.
4.Assign the concurrent program to a request set to run the
  concurrent program from a responsibility.
5.Create two two three values sets.
6.Create three parameters for the concurrent program
  created in step 3 using value sets created in step 5.
7.Go to the responsibility to which the concurrent program assigned in step 4 to run the program..

Installation Steps

Step1 : Create procedure
CREATE OR REPLACE procedure test_proc(
errbuf OUT varchar2,
retcode out NUMBER,
p_One IN VARCHAR2,
p_two_dummy IN VARCHAR2,
p_two IN NUMBER)
is
begin
fnd_file.put_line (fnd_file.LOG,'Log File');
fnd_file.put_line (fnd_file.OUTPUT,'Out put File' || p_one || '   ' || p_two);
end test_proc;
/

Run above procedure in SQL * Plus / Toad.Step2:  Creating Executable:Navigation :- System Administrator Responsibility → Concurrent →  Program → Executable
Executable : TEST_PROC
Short Name: TEST_PROC
Application: Custom Development
Execution Method: PL/SQL Stored Procedure
Execution File Name : TEST_PROC

Save and close the Concurrent Program executable window.

Step 3: Creation of Values Sets
 3.1 Creating Value set
       Value Set Name  :LAMS_SRS_YES_NO_MAND
       Description     :Yes/No   
       List Type       : List Of Values
       Format          : Char
       Security Type   : No Security
       Validation Type : Table

       Edit Information:
            Table Name : FND_LOOKUPS
            Value      : MEANING         TYPE :VARCHAR2
            ID         : LOOKUP_CODE     TYPE :VARCHAR2
            Where/Order By : WHERE Lookup_type = 'YES_NO'    

Click on Test → Ok → Save and close Validation Table Information window and then value set window.

  3.2 Creating Value set for Dummy Parameter:

      Value Set Name: CST_SRS_MARGIN_ORDER_DUMMY2
      List Type     : List Of Values
      Format        : Char(Check Uppercase Only)
      Security Type : No Security
      Validation Type : None
 

  3.3 Value set for Sales Order Numbers:      Value Set Name : ONT_ORDER
      List Type   : List Of Values
      Format      : Number(Check Numbers Only)
      Security Type : No Security
      Validation Type : Table

      Edit Information:
        Table Name : OE_ORDER_HEADERS_ALL
        Value   : ORDER_NUMBER           TYPE :NUMBER
        Where/Order By : where :$FLEX$.XXLSS_ORDER_DUMMY = 'Y'     
 

Click on Test → Ok → Save and close Validation Table Information window and then value set window.
 Step 4 : Creating a concurrent Program

   Program          : TEST_PROC
   Short Name       : TEST_PROC
   Application      : Custom Development

   Executable  Name : TEST_PROC

Save and click on Parameters.
Step 5: Creation of Parameters
  5.1 Yes/No Parameter
         Seq          : 10
         Parameter    : p_one
         Value Set    : AMS_SRS_YES_NO_MAND
         Enabled      : Yes
         Required     : Yes
         Display      : Yes

  5.2Creating Dummy Parameter
         Seq : 15
         Parameter : p_two_dummy
         Value Set    : CST_SRS_MARGIN_ORDER_DUMMY2
         Default Type : SQL Statement
         Default Value: select decode        (:$FLEX$.XXONT_OSR_YES_NO,'Y','Y','N',NULL) from dual
         Enabled   : Yes
         Required  : No
         Display   : No

  5.3 Creating Conditional Parameter
          Seq : 20
          Parameter :  p_two
          Value Set : ONT_ORDER      
           Enabled   : Yes
           Required  : Yes
            Display   : Yes


Step 6 : Assigning the concurrent program a request group:Navigation:- System Administrator → Security → Responsibility → Request
Query for the Request group, 'OM Concurrent Programs'

Select Request Type and click on Add New
Type : Program
Name : TEST_PROC
Application : Custom Development

Save and close the Window.
Step7 : Running  the concurrent request:-
Navigation: Order Management Super User → Reports, Requests → Run Requests  →
  Give Program Name as 'TEST_PROC'
 When you select Yes for the first parameter then second parameter become mandatory.

FND_LOAD

Data Synchronization: Data Synchronization is a process in which some setup data would be synchronized, and this would be more important when you are working in oracle application
development/implementation project. The equally important that AOL data Migration takes place necessary to synchronize the data across databases instance during
• Installations (New desc. flex field creations etc)
• Upgrades (Apps upgrade etc)
• Maintenance (Value set changes etc)


FNDLOAD is one and only oracle solution. It can be defined as a concurrent program that can move Oracle Applications data between database and text file representations. Or this can be defined as
FNDLOAD can download data from an application entity into an editable text file, which can be uploaded to another database.

Working of FNDLOAD: Conversion between database format and text file format is specified by a configuration file. Oracle does provide configuation file and it is important to just pass the
configuration file name and then just call the loader and leave everything .


These are the extensive list which can be done through FNDLOAD
• Concurrent Programs, Executables
• Request Groups, Request Sets
• Profile Options
• Key and Descriptive Flexfields
• Menus and Responsibilities
• Forms and Form Functions
• Attachments
• Messages
• Value Sets and Values
• Lookup Types
• User Responsibilities
• Printer Definitions
• FND Dictionary
• Help Configuration
• Document Sequences
• Concurrent Manager Schedules


Advantages using FNDLOAD are :• Because downloaded data is stored in a text file, version      administration is possible
• No learning curve.
• Fully supported and recommended by Oracle
• Capture the migrations in a file and use it during installations, clones etc. to migrate in batch
• Pin-point when something happened and where (database) easily
• Your AOL data migration process is now simplified and streamlined.


Disadvantages:• Applications patching mechanisms use FNDLOAD heavily – possibility of negative impact is
not zero
• UPLOAD_MODE=REPLACE only for menus
• No validation against migrating database/instance sensitive data.


Syntax : To use FNDLOAD, the following syntax is needed.
FNDLOAD apps/appspwd 0 Y mode configfile datafile entity [parameter1…..]
• The mode is either DOWNLOAD or UPLOAD.
• The configfile is the file that Fndload needs to download on upload data.
• The data file is the output file, in which the downloaded data is written
• The entity is the entity you want to download,


Modes of Operation:Two modes - Upload and Download…
1. Example of download:
FNDLOADapps/pwd 0 Y DOWNLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt \
PROGRAM CONCURRENT_PROGRAM_NAME= concurrent_program_short_name>
APPLICATION_SHORT_NAME=

2. Example of Upload
FNDLOAD apps/pwd 0 Y UPLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt -
CUSTOM_MODE=FORCE undocumented parameter


Sample Script Code:

1 - Printer StylesFNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct
file_name.ldt STYLE PRINTER_STYLE_NAME=”printer style name”


2 - LookupsFNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct
file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=”prod”
LOOKUP_TYPE=”lookup name”


3 - Descriptive Flexfield with all of specific Contexts
FNDLOAD
apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct
file_name.ldt DESC_FLEX P_LEVEL=?COL_ALL:REF_ALL:CTX_ONE:SEG_ALL?
APPLICATION_SHORT_NAME=”prod” DESCRIPTIVE_FLEXFIELD_NAME=”desc flex name”
P_CONTEXT_CODE=”context name”


4 - Key Flexfield StructuresFNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct
file_name.ldt KEY_FLEX P_LEVEL=?
COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL?
APPLICATION_SHORT_NAME=”prod” ID_FLEX_CODE=”key flex code”
P_STRUCTURE_CODE=”structure name”


5 - Concurrent Programs
FNDLOAD
apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct
file_name.ldt PROGRAM APPLICATION_SHORT_NAME=”prod”
CONCURRENT_PROGRAM_NAME=”concurrent name”


6 - Value SetsFNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct
file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME=”value set name”


7 - Value Sets with values
FNDLOAD
apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct
file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME=”value set name”


8 - Profile OptionsFNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct
file_name.ldt PROFILE PROFILE_NAME=”profile option” APPLICATION_SHORT_NAME=”prod”


9 - Requset GroupFNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct
file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME=”request group”
APPLICATION_SHORT_NAME=”prod”


10 - Request SetsFNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct
file_name.ldt REQ_SET APPLICATION_SHORT_NAME=”prod” REQUEST_SET_NAME=”request
set”


11 - ResponsibilitiesFNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct
file_name.ldt FND_RESPONSIBILITY RESP_KEY=”responsibility


12 - MenusFNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct
file_name.ldt MENU MENU_NAME=”menu_name”


13 – Forms/FunctionsFNDLOAD apps/apps@seed115 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct
file_name.ldt FND_FORM_CUSTOM_RULES The Upload syntax for all styles: FNDLOAD
apps/apps@seed115 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt

14. User/ResponsibilitiesFNDLOAD apps/apps@seed115 0 Y DOWNLOAD @FND:patch/115/import/afscursp.lct
file_name.ldt FND_USER Then UPLOAD FNDLOAD
apps/apps@seed115 0 Y UPLOAD [UPLOAD_PARTIAL] @FND:patch/115/import/afscursp.lct file_name.ldt FND_USER []

Saturday, January 22, 2011

Concurrent Program Information

Below script retieves Concurrent Program Name, Application Name, Request Group Name
====================================================================
SELECT fcp.concurrent_program_name, fat.application_name,
       frg.request_group_name, fa.application_short_name, fa.basepath,
       frt.responsibility_name
  FROM fnd_concurrent_programs fcp,
       fnd_application fa,
       fnd_request_group_units frgu,
       fnd_request_groups frg,
       fnd_application_tl fat,
       fnd_responsibility fr,
       fnd_responsibility_tl frt
 WHERE 1=1
   AND fcp.application_id = fa.application_id
   AND frgu.request_unit_id = fcp.concurrent_program_id
   AND frg.request_group_id = frgu.request_group_id
   AND fat.application_id = frg.application_id
   AND fcp.CONCURRENT_PROGRAM_NAME LIKE '<Concurrent_Program_Name>'
   AND frgu.request_group_id = fr.request_group_id
   AND frt.responsibility_id = fr.responsibility_id

Updating Price List Lines - API Approach

Script for Updating Price List Lines :-

Issue :- Need to update unit price of items which are in given Price List ans having  unit price as $9999999.

Process Followed :- First end date all the lines in QP_LIST_LINES which falls under above conditions and then insert new records in QP_LIST_LINES.

API Used :- qp_price_list_pub.process_price_list(Both for Insert and Update)

Steps Followed :-
1. Create a staging table to store Item_Id, New Unit_Price, Transaction_ID, Status and Errror_Message.
2. Created a synonym, sequence, trigger and index on staging table.
3. Validated Items Ids given (For Duplicate Records, Null Records and existence of item ids) in the  data file).
4. Send List_Header_Id, List_Line_Id, Pricing_Attribute_Id and field that has to be updated (In this example, end_date_active) and operation should be qp_globals.g_opr_update.
===========================================================================
Staging Table , Synonym, Sequence, Trigger Creation

SET serverout on size 1000000
CL SCR;

PROMPT +------------------------------------------------------------------------+
PROMPT   Execution of script starts
PROMPT +------------------------------------------------------------------------+

--drop table lss_unit_price_stg;

CREATE TABLE lss_unit_price_stg(
transaction_id_stg   NUMBER NOT NULL
,status_stg    VARCHAR2(2)
,item_id NUMBER
,unit_price NUMBER
,error_message  VARCHAR2(2000)
);

--drop synonym lss_unit_price_stg;

CREATE SYNONYM lss_unit_price_stg FOR lss.lss_unit_price_stg;

--drop index lss_unit_price_stg_ui;

CREATE  UNIQUE INDEX lss_unit_price_stg_ui ON lss_unit_price_stg(transaction_id_stg);

--drop sequence lss_unit_price_stg_s1;

CREATE SEQUENCE lss_unit_price_stg_s1
START WITH 1
INCREMENT BY 1;

-- Creation Of Trigger

CREATE OR REPLACE TRIGGER lss_unit_price_stg_trg
   BEFORE INSERT
   ON lss_unit_price_stg
   FOR EACH ROW
BEGIN
   IF :NEW.transaction_id_stg IS NULL
   THEN
      SELECT lss_unit_price_stg_s1.NEXTVAL, 'N'
        INTO :NEW.transaction_id_stg, :NEW.status_stg
        FROM DUAL;
   END IF;
END;
/
============================
Package Specification

CREATE OR REPLACE PACKAGE lss_unit_price_update_pkg
IS
   PROCEDURE unit_price_validate (p_list_header_id IN NUMBER);

   PROCEDURE unit_price_load (p_list_header_id IN NUMBER);
END lss_unit_price_update_pkg;
/
===============================
Package Body


CREATE OR REPLACE PACKAGE BODY lss_unit_price_update_pkg
IS
   PROCEDURE unit_price_validate (p_list_header_id IN NUMBER)
   IS
      l_error_msg   VARCHAR2 (3000);
      l_item_id     NUMBER;

      CURSOR item_stg_cur
      IS
         SELECT item_id, transaction_id_stg
           FROM lss_unit_price_stg
          WHERE status_stg IN ('N', 'VE');

      CURSOR dup_item_cur
      IS
         SELECT   item_id
             FROM lss_unit_price_stg
         GROUP BY item_id
           HAVING COUNT (*) > 1;

      CURSOR item_nul_cur
      IS
         SELECT transaction_id_stg
           FROM lss_unit_price_stg
          WHERE item_id IS NULL;
   BEGIN
-- Validating Item_Id
      FOR item_stg_rec IN item_stg_cur
      LOOP
         BEGIN
            SELECT DISTINCT inventory_item_id
                       INTO l_item_id
                       FROM qp_list_lines
                      WHERE 1 = 1
                        AND list_header_id = p_list_header_id
                        --AND operand = 9999999
                        --AND end_date_active IS NULL
                        AND inventory_item_id = item_stg_rec.item_id;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_error_msg :=
                             'Inventory Item Id Not Found' || SQLERRM || ';;';

               UPDATE lss_unit_price_stg
                  SET status_stg = 'VE',
                      error_message = error_message || l_error_msg
                WHERE 1 = 1 AND item_id = item_stg_rec.item_id;

               COMMIT;
         END;
      END LOOP;

      DBMS_OUTPUT.put_line ('Vallidating ItemID');

      -- Validating for Duplicate records
      FOR dup_item_rec IN dup_item_cur
      LOOP
         BEGIN
            l_error_msg := 'Duplicate Item Id ;;';

            UPDATE lss_unit_price_stg
               SET status_stg = 'VE',
                   error_message = error_message || l_error_msg
             WHERE item_id = dup_item_rec.item_id;

            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line
                  (   'Error While updating Staging Table for the Item(Duplicate)'
                   || dup_item_rec.item_id
                  );
         END;
      END LOOP;

      DBMS_OUTPUT.put_line ('Vallidating Duplicate Records');

      -- Validating Null Item Ids
      FOR item_nul_rec IN item_nul_cur
      LOOP
         BEGIN
            l_error_msg := 'Given Item Id is Null ;;';

            UPDATE lss_unit_price_stg
               SET status_stg = 'VE',
                   error_message = error_message || l_error_msg
             WHERE transaction_id_stg = item_nul_rec.transaction_id_stg;

            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line
                  (   'Error While updating Staging Table for the Transaction(NULL)'
                   || item_nul_rec.transaction_id_stg
                  );
         END;
      END LOOP;

      DBMS_OUTPUT.put_line ('Vallidating D');

      BEGIN
         UPDATE lss_unit_price_stg
            SET status_stg = 'V'
          WHERE status_stg = 'N' AND error_message IS NULL;

         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line
               ('Exception while updating staging table for successfully calidated records'
               );
      END;
   END unit_price_validate;

   PROCEDURE unit_price_load (p_list_header_id IN NUMBER)
   AS
      pl_code                       VARCHAR2 (2000);
      l_price                       NUMBER;
      l_count                       NUMBER;
      l_msg_dummy                   VARCHAR2 (2000);
      l_output                      VARCHAR2 (2000);
      l_msg_data                    VARCHAR2 (2000);
      l_msg_count                   NUMBER;
      l_list_line_id                NUMBER;
      l_pricing_attribute_id        NUMBER;
      l_item_id                     NUMBER;
      gpr_return_status             VARCHAR2 (10)                     := NULL;
      gpr_msg_count                 NUMBER                               := 0;
      gpr_msg_data                  VARCHAR2 (2000);
      gpr_price_list_rec            qp_price_list_pub.price_list_rec_type;
      gpr_price_list_val_rec        qp_price_list_pub.price_list_val_rec_type;
      gpr_price_list_line_tbl       qp_price_list_pub.price_list_line_tbl_type;
      gpr_price_list_line_val_tbl   qp_price_list_pub.price_list_line_val_tbl_type;
      gpr_qualifiers_tbl            qp_qualifier_rules_pub.qualifiers_tbl_type;
      gpr_qualifiers_val_tbl        qp_qualifier_rules_pub.qualifiers_val_tbl_type;
      gpr_pricing_attr_tbl          qp_price_list_pub.pricing_attr_tbl_type;
      gpr_pricing_attr_val_tbl      qp_price_list_pub.pricing_attr_val_tbl_type;
      ppr_price_list_rec            qp_price_list_pub.price_list_rec_type;
      ppr_price_list_val_rec        qp_price_list_pub.price_list_val_rec_type;
      ppr_price_list_line_tbl       qp_price_list_pub.price_list_line_tbl_type;
      ppr_price_list_line_val_tbl   qp_price_list_pub.price_list_line_val_tbl_type;
      ppr_qualifiers_tbl            qp_qualifier_rules_pub.qualifiers_tbl_type;
      ppr_qualifiers_val_tbl        qp_qualifier_rules_pub.qualifiers_val_tbl_type;
      ppr_pricing_attr_tbl          qp_price_list_pub.pricing_attr_tbl_type;
      ppr_pricing_attr_val_tbl      qp_price_list_pub.pricing_attr_val_tbl_type;
      i                             NUMBER                               := 1;
      k                             NUMBER                               := 0;
      j                             NUMBER                               := 0;
      v_userid                      NUMBER;
      v_applid                      NUMBER;
      v_respid                      NUMBER;
      v_loginid                     NUMBER;

      CURSOR item_stg_cur
      IS
         SELECT item_id, transaction_id_stg
           FROM lss_unit_price_stg
          WHERE status_stg = 'V';
   BEGIN
      DBMS_OUTPUT.put_line (p_list_header_id);

      BEGIN
         fnd_client_info.set_org_context (141);
      END;

      BEGIN
         SELECT user_id
           INTO v_userid
           FROM fnd_user
          WHERE user_name LIKE 'XXXX';
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            v_userid := NULL;
      END;

      BEGIN
         SELECT MAX (login_id)
           INTO v_loginid
           FROM fnd_logins
          WHERE 1 = 1 AND user_id = v_userid;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            v_loginid := NULL;
      END;

      BEGIN
         SELECT application_id, responsibility_id
           INTO v_applid, v_respid
           FROM fnd_responsibility_tl
          WHERE responsibility_name LIKE 'Oracle Pricing Manager';
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            v_applid := NULL;
            v_respid := NULL;
      END;

      fnd_global.apps_initialize (v_userid, v_respid, v_applid);       -- Prod

      FOR item_stg_rec IN item_stg_cur
      LOOP
         BEGIN
            DBMS_OUTPUT.put_line ('Entered Loop');

            SELECT qpll.list_line_id, qppa.pricing_attribute_id,
                   qpll.inventory_item_id
              INTO l_list_line_id, l_pricing_attribute_id,
                   l_item_id
              FROM qp_list_lines qpll, qp_pricing_attributes qppa
             WHERE qpll.list_line_id = qppa.list_line_id
               AND qpll.list_header_id = p_list_header_id
               AND qpll.inventory_item_id = item_stg_rec.item_id;

            DBMS_OUTPUT.put_line ('Retrieving The Price List');
            gpr_price_list_rec.list_header_id := p_list_header_id;
            gpr_price_list_rec.list_type_code := 'PRL';
            gpr_price_list_rec.NAME := 'Test_Price_List_API_990';
            gpr_price_list_rec.description := 'Test_Price_List_API_990';
            gpr_price_list_rec.currency_code := 'USD';
            pl_code := NULL;
            gpr_price_list_rec.operation := qp_globals.g_opr_update;
            gpr_pricing_attr_tbl.DELETE;
            k := 1;
            gpr_price_list_line_tbl (k).list_line_id := l_list_line_id;
            gpr_price_list_line_tbl (k).operation := qp_globals.g_opr_update;
            gpr_price_list_line_tbl (k).end_date_active := NULL;
            gpr_price_list_line_tbl (k).last_updated_by := v_userid;
            gpr_price_list_line_tbl (k).last_update_login := v_loginid;
            gpr_pricing_attr_tbl (k).pricing_attribute_id :=
                                                        l_pricing_attribute_id;
            gpr_pricing_attr_tbl (k).list_line_id := l_list_line_id;
            gpr_pricing_attr_tbl (k).product_attr_value := TO_CHAR (l_item_id);
            gpr_pricing_attr_tbl (k).operation := qp_globals.g_opr_update;

            BEGIN
               qp_price_list_pub.process_price_list
                   (p_api_version_number           => 1,
                    p_init_msg_list                => fnd_api.g_false,
                    p_return_values                => fnd_api.g_false,
                    p_commit                       => fnd_api.g_false,
                    x_return_status                => gpr_return_status,
                    x_msg_count                    => gpr_msg_count,
                    x_msg_data                     => gpr_msg_data,
                    p_price_list_rec               => gpr_price_list_rec,
                    p_price_list_line_tbl          => gpr_price_list_line_tbl,
                    p_pricing_attr_tbl             => gpr_pricing_attr_tbl,
                    x_price_list_rec               => ppr_price_list_rec,
                    x_price_list_val_rec           => ppr_price_list_val_rec,
                    x_price_list_line_tbl          => ppr_price_list_line_tbl,
                    x_price_list_line_val_tbl      => ppr_price_list_line_val_tbl,
                    x_qualifiers_tbl               => ppr_qualifiers_tbl,
                    x_qualifiers_val_tbl           => ppr_qualifiers_val_tbl,
                    x_pricing_attr_tbl             => ppr_pricing_attr_tbl,
                    x_pricing_attr_val_tbl         => ppr_pricing_attr_val_tbl
                   );
               DBMS_OUTPUT.put_line ('The Price List Line' || k
                                     || 'is inserted'
                                    );
               DBMS_OUTPUT.put_line
                                   (   'Price List line : '
                                    || gpr_pricing_attr_tbl (k).product_attr_value
                                   );
               COMMIT;

               BEGIN
                  UPDATE lss_unit_price_stg
                     SET status_stg = 'L'
                   WHERE transaction_id_stg = item_stg_rec.transaction_id_stg;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     DBMS_OUTPUT.put_line
                        (   'Excpetion when updating Staging table for Item(Updated Item)'
                         || l_item_id
                        );
               END;

               IF gpr_return_status <> 'S'
               THEN
                  oe_msg_pub.get (k,
                                  fnd_api.g_false,
                                  gpr_msg_data,
                                  l_msg_dummy
                                 );
                  l_output := (TO_CHAR (k) || ': ' || l_msg_data);
                  DBMS_OUTPUT.put_line (l_output);

                  BEGIN
                     UPDATE lss_unit_price_stg
                        SET status_stg = 'LE',
                            error_message = error_message || l_output || ';;'
                      WHERE transaction_id_stg =
                                               item_stg_rec.transaction_id_stg;
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        DBMS_OUTPUT.put_line
                           (   'Excpetion when updating Staging table for Item(Update Failed)'
                            || l_item_id
                           );
                  END;
               END IF;
            EXCEPTION
               WHEN OTHERS
               THEN
                  BEGIN
                     UPDATE lss_unit_price_stg
                        SET status_stg = 'LE',
                            error_message =
                                       error_message || 'Unknown Exception ;;'
                      WHERE transaction_id_stg =
                                               item_stg_rec.transaction_id_stg;
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        DBMS_OUTPUT.put_line
                           (   'Excpetion when updating Staging table for Item(Update Failed)'
                            || l_item_id
                           );
                  END;

                  DBMS_OUTPUT.put_line ('Error Processing The Price List...');
                  DBMS_OUTPUT.put_line (   'Price List Name : '
                                        || gpr_price_list_rec.NAME
                                       );
                  DBMS_OUTPUT.put_line (SQLERRM);
            END;
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line (SQLERRM);
         END;
      END LOOP;
   END unit_price_load;
END lss_unit_price_update_pkg;
/