How To Sort Date Columns in Oracle XML Publisher

Some Times We are Unable To Sort Date Columns in Oracle XML Publisher, To Overcome This Issue Please follow below link.

http://oraclemaniac.com/2012/02/04/sort-date-field-in-xml-publisher-template/

Thanks
Amar Alam

How to check Profile Option Values using SQL Queries

There is no better place to check the Profile Options using the Profile
Options Form in System Administrator responsibility. But at times, you either do not have
System Administrator responsibility or you need to check and compare profile options for more than one profile or more than one responsibility at the same time. The following queries are very useful in those scenarios…

SQL Queries for checking Profile Option Values:

The following queries are useful to get the profile option values of a profile option at site, application, responsibility and user level

1) Obtain Profile Option values for Profile Option name like ‘%Ledger%’ and  Responsibility name like ‘%General%Ledger%’

SELECT
substr(pro1.user_profile_option_name,1,35) Profile,
decode(pov.level_id,
10001,'Site',
10002,'Application',
10003,'Resp',
10004,'User') Option_Level,
decode(pov.level_id,
10001,'Site',
10002,appl.application_short_name,
10003,resp.responsibility_name,
10004,u.user_name) Level_Value,
nvl(pov.profile_option_value,'Is Null') Profile_option_Value
FROM
fnd_profile_option_values pov,
fnd_responsibility_tl resp,
fnd_application appl,
fnd_user u,
fnd_profile_options pro,
fnd_profile_options_tl pro1
WHERE
pro1.user_profile_option_name like ('%Ledger%')
and  pro.profile_option_name = pro1.profile_option_name
and  pro.profile_option_id = pov.profile_option_id
and  resp.responsibility_name like '%General%Ledger%' /* comment this line  if you need to check profiles for all responsibilities */
and  pov.level_value = resp.responsibility_id (+)
and  pov.level_value = appl.application_id (+)
and  pov.level_value = u.user_id (+)
order by 1,2;

2) Obtain all Profile Option values setup for a particular responsibility. Replace the responsibility name as per your requirement.

SELECT
substr(pro1.user_profile_option_name,1,35) Profile,
decode(pov.level_id,
10001,'Site',
10002,'Application',
10003,'Resp',
10004,'User') Option_Level,
decode(pov.level_id,
10001,'Site',
10002,appl.application_short_name,
10003,resp.responsibility_name,
10004,u.user_name) Level_Value,
nvl(pov.profile_option_value,'Is Null') Profile_option_Value
FROM
fnd_profile_option_values pov,
fnd_responsibility_tl resp,
fnd_application appl,
fnd_user u,
fnd_profile_options pro,
fnd_profile_options_tl pro1
WHERE
pro.profile_option_name = pro1.profile_option_name
and  pro.profile_option_id = pov.profile_option_id
and  resp.responsibility_name like '%General%Ledger%'
and  pov.level_value = resp.responsibility_id (+)
and  pov.level_value = appl.application_id (+)
and  pov.level_value = u.user_id (+)
order by 1,2;

Similarly, you can tweak the above queries to obtain Profile Option Values set for a particular User or a particular application


Thanks
Amar Alam

SRW Functions

SRW Package is a collection of PL/SQL constructs that provide developers with a suite of built-in functions, procedures, and exceptions that can be used in any of your libraries or reports. The following lists several of these constructs, and briefly describes how Applications uses them in reports.

SRW.DO_SQL and SRW.DO_SQL_FAILURE
When it is necessary to perform data definition statements (DDL), the SRW.DO_SQL packaged procedure must be used. You cannot perform DDL statements in PL/SQL. In conjunction with this, we use the SRW.DO_SQL_FAILURE exception which raises an error if the statement should fail.

Example
BEGIN
SRW.DO_SQL('Create table Test...');
EXCEPTION
  when SRW.DO_SQL_FAILURE then
  .....
END;

SRW.MESSAGE
This procedure allows developers to create their own messages and return them at runtime. It takes two arguments, a message number and message text. It can be used for error handling or debugging. Applications uses this feature to standardize and share messages across reports.

Example
SRW.MESSAGE('500','First debug line');

SRW.USER_EXIT and SRW.REFERENCE
Because Applications reports commonly call user exits it is critical that Oracle Reports support such calls. To facilitate this requirement the Package offers  SRW.USER_EXIT and SRW.REFERENCE. User exits may be called from any PL/SQL interface within a report, but it must be called with SRW.USER_EXIT. Furthermore, to ensure that the value of an object passed to the user exit contains the most recently computed or fetched value, SRW.REFERENCE will add the object to the user exit dependency list.

Example
BEGIN
SRW.REFERENCE(:Currency_code);
SRW.REFERENCE(:Currency_value);
SRW.USER_EXIT('FND FORMAT CURRENCY CODE=":Currency_code"                        
               AMOUNT=":Currency_value"                       
               DISPLAY=":Currency_formatted"');
RETURN(:Currency_formatted);
EXCEPTION WHEN
SRW.USER_EXIT_FAILURE THEN
RETURN('FORMAT ERROR');
END:


Thanks
Amar Alam

Overview of Oracle Shipping

PICK RELEASE:  Pick release is an activity to confirm picking of goods from stock and keeping aside for shipping to a sales order. Normally pick release happens based on scheduled ship date on sales order. Pick release process can be done manually or automatically scheduled. Once pick release is activated it normally prints Pick ticket or Pick Slip for warehouse personnel to pick the material. Pickslip displays the information like Item, quantity, location from where it should be picked and destination place where it should be moved (eg. docking station). Pick release doesn’t decrements the onhand inventory but it decrements the available inventory for next picking.

DELIVERY : Delivery is a batch of sales order lines grouped together based on some criteria (criteria could be shipto location, sales order etc). Delivery can be automatically created by system during pick release or it can be manually created.

TRIP:  Trip is primarily the shipment routing plan, it has pick up location and drop off location. Most of the cases multiple deliveries can be grouped under a trip.

SHIP CONFIRM:   It is an activity where it is confirmed systematically that goods are shipped from the warehouse that finally reduces the on hand quantity and updates the install base with customer & sales order details.


Thanks
Amar Alam

Overview of Oracle Warehouse System (WMS)

Oracle Warehouse Management System (WMS) is advanced inventory module with lot of unique features that improves productivity of distribution centers, manufacturing or inventory handling facilities. Oracle WMS supports manual data entry, bar code readers scanning for data entry as well as Radio Frequency Identification (RFID) scanning. WMS can be enabled at Inventory organization level.

Key business processes supported by Oracle WMS:

   1. Inbound Logistics (Receiving, supplier returns, corrections)
   2. Stocking and internal inventory movements
   3. WIP Job assembly completion, component issues
   4. Outbound Logistics (pickimg, packing, shipping)
   5. Reverse logistics (customer returns)

License Plate Numbers (LPN): LPN is an object carries the location where it is residing and items that are holding. LPN need not be a physical object, it could be logical grouping. LPN simpley a group of items represented systematically. The major advantage of LPN is less number of transactions. Entire LPN can be transacted at once instead of by each line that LPN holds.
Material Status: This is one of the beautiful features of WMS, you can assign status to an item, lot or serial number. Each status is defined with a list of permissble transactions. Eg. if you have status called ‘No Sale’ and set the rules such that shipping is not allowed but all other internal inventory movements are allowed. Material status can enforce this kind of business rules.
Label printing: WMS partially supports this fucntionality. You can define label formats and fields (data) needed, you can associate this with WMS rules to print the label automatically. WMS can generate xml output, but you need a 3rd party label printing/formatting tool to actually print.
Cost groups: This is another major functionality offered by WMS module. In standard Inventory module Inventory valuation accounts are derived by subinventory setups. In WMS based on cost group rules you can have different valuations independent of subinventories.
Putaway Rules: When a matrial is received you need to receive into stock. In a major distribution center it is often complex to know where to store the material. WMS offers putaway rules that can be setup based son business criteria. Eg. If all hazarad material categy must be received into hazardous area then putaway rules can be setup.
WMS Rules engine: WMS rules engine enforces the rules defined for putaway, cost group or label printing. You can simulate the results based on set of rules defined.


Thanks
Amar Alam

Splitting String Using Oracle SQL 9i

 SELECT TRIM( SUBSTR ( txt
                     , INSTR (txt, ',', 1, level ) + 1
                     , INSTR (txt, ',', 1, level+1) - INSTR (txt, ',', 1, level) -1
                     )
            )
         AS token
   FROM ( SELECT ','||:in_string||',' AS txt  FROM dual )
 CONNECT BY level <= LENGTH(txt)-LENGTH(REPLACE(txt,',',''))-1

Example
-------
If value of in_string is entered as
7890,345,567,123,408

Output Is

TOKEN
======
7890
345
567
123
408


Thanks
Amar Alam

Query to Get Customer Related Information for a Sales Order

Here is another handy query to get Customer related information for a sales order.
The query will list SHIP TO and BILL TO Address for a customer.

SELECT ooh.order_number
     , hp_bill.party_name
     , hl_ship.address1 ||Decode(hl_ship.address2,NULL,'',chr(10))
      ||hl_ship.address2||Decode(hl_ship.address3,NULL,'',chr(10))
      ||hl_ship.address3||Decode(hl_ship.address4,NULL,'',chr(10))
      ||hl_ship.address4||Decode(hl_ship.city,NULL,'',chr(10))
      ||hl_ship.city    ||Decode(hl_ship.state,NULL,'',',')
      ||hl_ship.state   ||Decode(hl_ship.postal_code,'',',')
      ||hl_ship.postal_code ship_to_address
     , hl_bill.address1 ||Decode(hl_bill.address2,NULL,'',chr(10))
      ||hl_bill.address2||Decode(hl_bill.address3,NULL,'',chr(10))
      ||hl_bill.address3||Decode(hl_bill.address4,NULL,'',chr(10))
      ||hl_bill.address4||Decode(hl_bill.city,NULL,'',chr(10))
      ||hl_bill.city    ||Decode(hl_bill.state,NULL,'',',')
      ||hl_bill.state   ||Decode(hl_bill.postal_code,'',',')
      ||hl_bill.postal_code bill_to_address
     , ooh.transactional_curr_code currency_code
     , mp.organization_code
     , ooh.fob_point_code
     , ooh.freight_terms_code
     , ooh.cust_po_number
FROM   oe_order_headers_all ooh
     , hz_cust_site_uses_all hcs_ship
     , hz_cust_acct_sites_all hca_ship
     , hz_party_sites hps_ship
     , hz_parties hp_ship
     , hz_locations hl_ship
     , hz_cust_site_uses_all hcs_bill
     , hz_cust_acct_sites_all hca_bill
     , hz_party_sites hps_bill
     , hz_parties hp_bill
     , hz_locations hl_bill
     , mtl_parameters mp
WHERE  1 = 1
AND    header_id = :p_header_id
AND    ooh.ship_to_org_id = hcs_ship.site_use_id
AND    hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id
AND    hca_ship.party_site_id = hps_ship.party_site_id
AND    hps_ship.party_id = hp_ship.party_id
AND    hps_ship.location_id = hl_ship.location_id
AND    ooh.invoice_to_org_id = hcs_bill.site_use_id
AND    hcs_bill.cust_acct_site_id = hca_bill.cust_acct_site_id
AND    hca_bill.party_site_id = hps_bill.party_site_id
AND    hps_bill.party_id = hp_bill.party_id
AND    hps_bill.location_id = hl_bill.location_id
AND    mp.organization_id(+) = ooh.ship_from_org_id

Thanks

Amar Alam