Archive for August, 2008

OracleContractors.com Exhibiting at the AUSOUG/OAUG Conference Series 2008 in Perth and Gold Coast

Wednesday, August 27th, 2008 by

The AUSOUG/OAUG National Conference Series starts in 7 weeks time.

The 20:20 Foresight Conference 2008 is organised by the Australian Oracle User Group (AUSOUG) in conjunction with the Oracle Applications User Group (OAUG). These events will offer you the chance to network with like minded Oracle specialists from across Australia and the Asia Pacific region.  You will have the opportunity to learn from some of the best in the business about the future of Oracle technologies and the Oracle e-Business suite, including release 12 and Fusion.

The event commences on Monday 6th and Tuesday 7th October at the Sheraton Hotel, Perth. The second leg of the 20:20 Foresight conferences will be at Conrad Jupiters, Gold Coast on Monday 13th and Tuesday 14th of October.

Once again we will be exhibiting at both events, so come and meet the team in person at the OracleContractors.com booth.

If you have not yet registered and are interested in attending the conferences please contact Stephanie Philpot on Stephanie.philpot@oraclecontractors.com or by phone on +61 (0)2 9455 0305 to take advantage of the OracleContractors.com exclusive discount membership packages.

With the growing uptake of release 12 and as the concept of Fusion begins to take shape, this is an opportunity to learn what is really happening in the Oracle space from unbiased sources.

We look forward to meeting you.

How to delete stock locators by using API

Wednesday, August 13th, 2008 by

1. Locators

If user wants to delete some inactive locators but in oracle application delete button is disabled on locators interface. But Oracle provided an API which can be used for deletion of locators.
Here I took following locators for demonstration.

2. Create Table

Create a new table by using following script with all locator segments

CREATE TABLE DEV_DATA
(
  SR_NO                   NUMBER,
  INV_ORG_ID              NUMBER,
  INVENTORY_ORGANIZATION  NUMBER,
  SUB_INVENTORY           CHAR(3 BYTE),
  WAREHOUSE               CHAR(3 BYTE),
  FLOR                    CHAR(3 BYTE),
  ROWN                    CHAR(3 BYTE),
  RACK                    CHAR(3 BYTE),
  BIN                     CHAR(3 BYTE),
  BOX                     CHAR(3 BYTE),
  SUPPLIER                CHAR(4 BYTE),
  SPARE1                  CHAR(3 BYTE),
  SPARE2                  CHAR(3 BYTE),
  CODE                    CHAR(45 BYTE),
  CREATION_DATE           DATE,
  STATUS                  CHAR(1 BYTE)
)

3. Upload Data in Table by Using SQLLDR

Control File Syntax

LOAD DATA
 INFILE ‘Data.csv’
 APPEND INTO TABLE Dev_Data
 REPLACE FIELDS TERMINATED BY ‘,’
 TRAILING NULLCOLS
(
SR_NO “trim(:SR_NO)”,
INV_ORG_ID “trim(:INV_ORG_ID)”,
INVENTORY_ORGANIZATION “trim(:INVENTORY_ORGANIZATION)”,
SUB_INVENTORY “trim(:SUB_INVENTORY)”,
WAREHOUSE “trim(:WAREHOUSE)”,
FLOR “trim(:FLOR)”,
ROWN “trim(:ROWN)”,
RACK “trim(:RACK)”,
BIN “trim(:BIN)”,
BOX “trim(:BOX)”,
SUPPLIER “trim(:SUPPLIER)”,
SPARE1 “trim(:SPARE1)”,
SPARE2 “trim(:SPARE2)”,
CODE “trim(:CODE)”,
CREATION_DATE “trim(:CREATION_DATE)”
)
 
Verify Data is uploaded successfully by using following two queries

Select count(*) from dev_data

Select * from dev_data

4. Inactive Locator By Passing Inactive date

5. Deleting Script (API)

Connect by Using APPS Credentials to Database
 Run the following scripts this will delete locator

DECLARE
   l_msg_data                VARCHAR2 (100);
   l_msg_count               NUMBER;
   l_return_status           VARCHAR2 (1);
   l_locator_id              NUMBER;
   l_locator_exists          VARCHAR2 (1);
   l_org_id                  NUMBER          := 116;     /*Organization_id */
   l_organization_code       VARCHAR2 (10)   := ‘U11′; /*Organization_Code */
   l_sub_code                VARCHAR2 (10);     /*Variable for Subinventory*/
   l_concatenated_segments   VARCHAR2 (100); /*Variable for Locator Segment*/
   l_user_id                 NUMBER          := 1262;
                                           /* User ID From FND_users Table */
   l_resp_id                 NUMBER          := 20634;  /*Responsibility Id*/
   l_resp_appl_id            NUMBER          := 401;
                                          /* Responsibility Application id */

   /*Fetch data into cursor for deletion of Locator*/

   CURSOR c_loc
   IS
      SELECT inventory_location_id, organization_id, description,
             subinventory_code sub_inventory, code
        FROM apps.dev_data;

   v_loc                     c_loc%ROWTYPE;
BEGIN

/* APPS_INITIALIZE required because indirectly use profile options */

   fnd_global.apps_initialize (l_user_id, l_resp_id, l_resp_appl_id);

–Open Cursor

   OPEN c_loc;

   LOOP
      FETCH c_loc
       INTO v_loc;

      EXIT WHEN c_loc%NOTFOUND;
      l_concatenated_segments := v_loc.code;
      l_sub_code := v_loc.sub_inventory;
      l_locator_id := v_loc.inventory_location_id;

      fnd_msg_pub.initialize;

      DBMS_OUTPUT.put_line (   ‘Trying to Delete ‘
                            || l_concatenated_segments
                            || ‘-’
                            || l_locator_id
                           );
      inv_loc_wms_pub.delete_locator
                          (x_return_status              => l_return_status,
                           x_msg_count                  => l_msg_count,
                           x_msg_data                   => l_msg_data,
                           p_inventory_location_id      => l_locator_id,
                           p_organization_id            => l_org_id,
                           p_organization_code          => ‘U11′,
                           p_concatenated_segments      => l_concatenated_segments,
                           p_validation_req_flag        => ‘N’
                          );
      COMMIT;
      DBMS_OUTPUT.put_line (’Return Status ‘ || l_return_status);

      IF l_return_status IN (’E', ‘U’)
      THEN
         DBMS_OUTPUT.put_line (’# of Errors ‘ || l_msg_count);

         IF l_msg_count = 1
         THEN
            DBMS_OUTPUT.put_line (’Error ‘ || l_msg_data);
         ELSE
            FOR i IN 1 .. l_msg_count
            LOOP
               DBMS_OUTPUT.put_line (’Error ‘ || fnd_msg_pub.get (i, ‘F’));
            END LOOP;
         END IF;
      ELSE
         DBMS_OUTPUT.put_line (’Locator Id is ‘ || l_locator_id);
      END IF;
   END LOOP;
CLOSE c_loc;
END;

6. Check values

Oracle User Productivity Kit (UPK) - How to work with the Topic Editor

Wednesday, August 13th, 2008 by

In the last post we looked at how to use the Oracle User Productivity Kit (UPK) and we recorded a Topic. Now we will look at how to work with the Topic Editor.
 
Opening the Topic Editor

Start the UPK Developer and select the module you were working on from the last section.

Open the Non Catalog Requisition topic in the Topic Editor by double-clicking it.

Navigating Frames Using the Navigation Buttons
1. Click Last frame to navigate to the End frame.
2. Click First frame to navigate back to the Start (Introduction) frame.
3. Use Next frame and Previous frame to navigate forward and backward through the frames.

Adding Text to a Start Frame
1. Display the Start (Introduction) frame, if necessary.
2. Click in the text box in the Bubble Properties window.
3. Type In this topic, you will create a non catalog Requisition.

Action Area
Actions determines what you intend to do in the current frame. Three basic action types:

A keyboard action - press a key on the keyboard to accomplish a task.
A string input action - enter text into a document, field, dialog box, and so on.
A mouse action - clicking, double-clicking, pointing, rolling the wheel, and dragging.

The actions are determined automatically during the recording

Creating a New Action Area
1. Open the Non Catalog Requisition topic in the Topic Editor, if necessary.
2. Navigate to the second recorded frame.
3. Click Modify action areas in the Action Properties window.
4. Click the Create new action area command. If necessary, move the bubble to view the new action area.
5. Size the new action area and place it over the Next button.
6. If necessary, reposition the bubble near the action areas.
7. Exit the Topic Editor and Save.

Deleting an Action Area
1. Select the new action area you added to the second frame of the Non Catalog Requisition topic.
2. Select the new action area.
3. Click Modify action areas in the Action Properties window.
4. Click the Delete action area command.
5. Click Yes.
6. Reposition the bubble as necessary.

Adding Custom Text to a Bubble
1. Navigate to the frame in which the action is to enter your password.
2. In the bubble, add the custom text Remember: Please obtain a password from support before you use the system.

Formatting Custom Bubble Text in a Frame
1. Drag to select the custom text Remember: in the bubble.
2. Bold it and change the text color to dark red.

Selecting a Bubble Icon for a Frame
1. In the Bubble Properties window, click the Bubble icon button and select an icon.

Changing the Bubble Pointer for a Frame
1. Change the bubble pointer as desired by clicking the Pointer position button.

Changing the Bubble Background Color for a Frame
1. Change the bubble color as desired by clicking the Background color button.
2. Exit the Topic Editor and Save.

Inserting an Explanation Frame
1. Open the Non Catalog Requisition topic in the Topic Editor.
2. Select the frame in which the action is to Click the iProcurement link, which is about the fourth from the beginning.
3. On the Insert menu, click Explanation Frame.
Or
Click Insert frame, action, or path and choose Insert Explanation Frame.
4. In the Bubble Properties window, type Note: Each field indicated with a * is a required field.
   
Moving the Bubble
1. If necessary, navigate to the Explanation frame.
2. Change the bubble to one with a Left top pointer using the Pointer position button.

Inserting a Missing Frame
1. Navigate to the EBS login page. Switch back to the Topic Editor.
2. Navigate to the Start frame in the Non Catalog Requisition topic.
3. On the Insert menu, click Missing Frame(s).
Or
Click Insert frame, action, or path and choose Insert Missing Frame(s).
The Recorder launches.
4. Activate the EBS login page by clicking in the title bar and navigate to the page you want to insert. Press
[PrintScreen].
5. Follow the instructions on how to record a topic
6. Click Finish in the Recorder once completed.
7. Navigate to the first recorded frame.

Deleting a Frame
1. Navigate to the frame you just inserted, if necessary.
2. On the Delete menu, click Frame.
Or
Click Delete frame, action, or path and choose Delete Frame.
3. Click Yes.

Editing a Screenshot
1. In the Topic Editor select the last recorded frame and click Edit screenshot.
2. Make changes to the screenshot as desired (play with editing the picture).
3. On the File menu, click Exit.
4. Click Yes to save the changes. Notice the changes in the screenshot in the Topic Editor.
5. Exit the Topic Editor and Save.
Next we will look at How to preview and publish our documents

Download the pdf version with screen shots  - email us for the screen shots.

Serial number is not available when doing any inventory transaction but shows in on hand quantity interface

Wednesday, August 13th, 2008 by

I was facing problem in one of my client site that oracle shows on hand quantity for one item in on hand quantity screen with serial numbers but when user is going to transact it either by sub inventory transfer or by Miscellaneous issue it shows me on hand  zero on transactional form

I have investigate it in detail and find different notes on Metalink for the solution and fusion of these notes is solution

First of all get Inventory Item id , serial number and organization id from on hand quantity interface by using examine option.

Step 1

Run the following query to check exact status of that serial numbers

SELECT SUBSTR (inventory_item_id, 1, 9) “Item Id”,
       SUBSTR (serial_number, 1, 20) “Serial Number”,
       SUBSTR (current_organization_id, 1, 9) “Organization Id”,
       SUBSTR (lot_number, 1, 18) “Lot Number”,
       SUBSTR (DECODE (current_status,
                       1, ‘Defined but not used’,
                       3, ‘Resides in Stores’,
                       4, ‘Out of Stores’,
                       5, ‘Intransit’,
                       6, ‘Invalid’,
                       NULL, ‘Verify Serial Number’,
                       current_status
                      ),
               1,
               25
              ) “Status”,
       SUBSTR (current_subinventory_code, 1, 25) “Subinv”,
       SUBSTR (current_locator_id, 1, 9) “Locator”,
       SUBSTR (group_mark_id, 1, 15) “Group Mark Id”,
       SUBSTR (line_mark_id, 1, 15) “Line Mark Id”,
       SUBSTR (lot_line_mark_id, 1, 15) “Lot Line Mark Id”, group_mark_id,
       line_mark_id, lot_line_mark_id
FROM mtl_serial_numbers
WHERE inventory_item_id = ‘&EnterItemID’
AND serial_number = ‘&EnterSerialNumber’  
AND current_organization_id = ‘&EnterOrgID’

Above query shows me the status “Resides in Stores” but this serial number is not displayed in transactional forms

Step 2

Then run the following query

SELECT i.inventory_item_id itemname, s.inventory_item_id itemid,
       s.serial_number serialno, s.current_organization_id org,
       s.current_subinventory_code sub, s.current_status stat,
       s.group_mark_id gmid, s.line_mark_id lmid, s.lot_line_mark_id
  FROM mtl_system_items i, mtl_serial_numbers s
WHERE inventory_item_id = ‘&EnterItemID’
AND serial_number = ‘&EnterSerialNumber’  
AND current_organization_id = ‘&EnterOrgID’
AND s.current_organization_id = i.organization_id
AND s.inventory_item_id = i.inventory_item_id;

Check the value of following columns

1. group_mark_id
This column is Group identifier (used by Oracle Inventory for ensuring that serial numbers used by one user’s session do not become available incorrectly to another user)
2. line_mark_id
This column Used by Oracle Inventory for ensuring that serial numbers used by one user’s session do not become available incorrectly to another user. The column is used by Oracle Inventory’s serial number marking and unmarking routines
3. lot_line_mark_id

Identifier used in serial number marking and unmarking routines

Step 3

If value of above mentioned column is not blank / null then run following query

UPDATE mtl_serial_numbers
 SET group_mark_id = NULL,
       line_mark_id = NULL,
       lot_line_mark_id = NULL
WHERE inventory_item_id = ‘&EnterItemID’
AND serial_number = ‘&EnterSerialNumber’  
AND current_organization_id = ‘&EnterOrgID’
AND current_status = 3
A serial number can have one of following statuses
1. Defined but not used
2. Resides in stores
3. Issued out of stores
4. Resides in transit

I used status = 3 in above query as I was looking for only those serial numbers which Resides in stores but not available to me for transaction

Step 4

Commit the record and then check are you able for transaction or not

Reference: - metalink.oracle.com

Under the Bonnet

Wednesday, August 13th, 2008 by

Oracle databases are the preferred choice of local authorities striving to achieve e-Government targets for delivery of ICS compliant ESCR applications by the end of September to satisfy statutory reporting requirements laid down by the DCSF (Department of Children, Schools and Families) and the Department of Health.

Having an involvement in this nationwide project here is a technical outline for those working on similar contracts who might wish to make comparisons.
The Electronic Social Care Record (ESCR) enables accurate case recording of the stages defined in the ICS (Integrated Children’s System) process.  The defined application infrastructure specification is that it must be a single integrated, end-to-end, flexible and configurable, future-proofed browser-enabled Java solution, to ensure ICS compliancy and integration with SSA and Contact Point. Councils and Boroughs were given a choice of suitable applications and most decided on those which use Oracle RDBMS databases, the most popular being Framework-i (supplied by Corelogic), CareFirst (OLM), Swift (Anite), RAISE (Careworks), Paris (In4tek) and PROtocol (Liquidlogic).

While developing training courses in various counties and boroughs over the past 3 years  – as an Independent Trainer – I’ve been involved with three of the above applications (Swift, Framework-i and CareFirst). I currently work with “Framework-i” version 3.0.3, a J2EE application which can run on Oracle RDBMS versions 9.2. and 10g and Microsoft SQL Server (versions 2000 and 2005), under Unix (AIX and Sun Solaris), Linux and Windows Server. The project required producing and delivering twelve types of specialised training courses aimed at approx 500 employees and this is now almost completed. The same Framework-i” application is used by around 25 other local authorities.

For the technically-minded the user-interface is via a standard web browser and data exchange follows eGIF standards with data exchanged as XML, either through web services, following the standard SOAP protocol, or as standard XML files. Being part of the public sector any variation on XML Schemas must undergo public consultation and be agreed by the e-Government Unit. The  “Framework-i” application is about to be upgraded to version 3.1.3 which supports ICS version 1B for the imminent implementation and will be later upgraded to V3.2.0/1 to accommodate ICS version 1C. It can run on laptops, tablet PCs, wireless mobile devices, digital pens and similar and is capable of integrating with national systems such as ContactPoint, eCare and the NHS Spine, which might be incorporated in future phases.


Buy Glucophage Buy Zocor Buy Zyban Zithromax Buy Cipro Buy Xanax Prozac Lipitor Didrex Viagra Buy Xanax Online Effexor Buy Line Xanax Buy Zocor Meridia Buy Cheap Phentermine Buy Zyrtec Buy Zithromax Buy Vicodin Online Clonazepam Biaxin Valium Buy Lortab Buy Tenuate Buy Ephedrine Buy Cialis Online Buy Darvocet Buy Butalbital Buy Percocet Buy Levitra Hydrocodone Buy Didrex Percocet Buy Ephedrine Butalbital Carisoprodol Ultracet Buy Phentermine Online Buy Lortab Cipro Buy Phentermine Buy Biaxin Diflucan Ambien Buy Nexium Buy Ambien Buy Tramadol Valium Buy Tenuate Norvasc Buy Adipex Glucophage Buy Flexeril Flexeril Phentermine Online Adipex Buy Valium Ativan Buy Xanax Buy Glucophage Zyprexa Vicodin Buy Lortab Oxycontin Buy Darvocet Buy Clonazepam Buy Valium Online Buy Vicodin Online Alprazolam Paxil Buy Bupropion Online Xanax Buy Cipro Buy Prozac Zyban Cipro Buy Celexa Zithromax Buy Hydrocodone Online Buy Xanax Online Buy Tramadol