How to get back the sequence in plsql block

begin
EXECUTE IMMEDIATE 'alter sequence ' || p_name || ' increment by ' || (p_val - l_num - 1) || ' minvalue 0'

end;

Report Mismatching Values while displaying

I have created table and created rdf report which is working fine
Later i have changed the table structure ,but the data displaying in the previous order only .
May be its silly thing but it has taken 2 hours for researching this...




Before table Structure  change output.

After table structure change output .

If you compare sub inv and locator then see the change.

What are the default parameters we will get to shell script while running from the concurrent program.

Below are the Default parameters we will get to shell script file while running through concurrent program


$0 - Shellscript name 

$1 - Oracle Data Base username/password 

$2 - User_id 

$3 - Oracle Applications username 

$4 - Concurrent request id 
Also from $5, $6 ..... will be the shell script arguments.

How to find inivisible characters available in table column value?

Below is the Query for finding the ASCII values which is available in the field

select dump(counted_by) from XX_MAS_CYCLE_COUNT_ENTRIES_TBL

How to Delete Bursting file from Data Definations in XML Publisher

How to Delete Bursting file from Data Definations in XML Publisher:

DELETE
FROM XDO_LOBS
WHERE LOB_CODE=<DATA_DEFINITION_CODE>
AND LOB_TYPE='BURSTING_FILE';

Link Purchase Order and Requisition

Query for getting PO Details along with the Requisition Deatils:

SELECT prh.segment1 req_number
      ,prh.authorization_status
      ,prl.line_num req_line_num
      ,prl.item_description req_item_description
      ,prl.unit_price req_unit_price
      ,prl.quantity req_quantity
      ,pd.req_header_reference_num
      ,pd.req_line_reference_num
      ,pl.line_num
      ,pl.item_description
      ,pl.quantity
      ,pl.amount
      ,ph.segment1 po_number
      ,prd.distribution_id
      ,pd.req_distribution_id
  FROM po_requisition_headers_all prh
      ,po_requisition_lines_all   prl
      ,po_req_distributions_all   prd
      ,po_distributions_all       pd
      ,po_line_locations_all      pll
      ,po_lines_all           pl
      ,po_headers_all             ph
 WHERE prh.requisition_header_id = prl.requisition_header_id
   and prh.org_id = prl.org_id
   and prl.requisition_line_id = prd.requisition_line_id
   and prl.org_id = prd.org_id
   and prd.distribution_id = pd.req_distribution_id(+)
   and prd.org_id = pd.org_id(+)
   and pd.line_location_id = pll.line_location_id(+)
   and pd.org_id = pll.org_id(+)
   and pll.po_line_id = pl.po_line_id(+)
   and pll.org_id = pl.org_id(+)
   and pl.po_header_id = ph.po_header_id(+)
   and pl.org_id = ph.org_id(+)

Onhand Quantity at given date

Below is the query that can help in getting onhand quantity at given date. The query inputs the Item ID, organization ID and date.

SELECT   SUM (target_qty)
       , item_id
FROM     (SELECT   moqv.subinventory_code subinv
                 , moqv.inventory_item_id item_id
                 , SUM (transaction_quantity) target_qty
          FROM     mtl_onhand_qty_cost_v moqv
          WHERE    moqv.organization_id = :org_id
          AND      moqv.inventory_item_id = :item_id
          GROUP BY moqv.subinventory_code
                 , moqv.inventory_item_id
                 , moqv.item_cost
          UNION
          SELECT   mmt.subinventory_code subinv
                 , mmt.inventory_item_id item_id
                 , -SUM (primary_quantity) target_qty
          FROM     mtl_material_transactions mmt
                 , mtl_txn_source_types mtst
          WHERE    mmt.organization_id = :org_id
          AND      transaction_date >= TO_DATE (:hist_date) + 1
          AND      mmt.transaction_source_type_id =
                                               mtst.transaction_source_type_id
          AND      mmt.inventory_item_id = :item_id
          GROUP BY mmt.subinventory_code
                 , mmt.inventory_item_id) oq
GROUP BY oq.item_id