, , , , , ,

OPM – GMD – Delete Row from the formula in R12

Module GMD R12.GMD.A.6 (UNP Product Development; Process Manufacturing Product Development)

The procedure to delete the row from the formula

PROCEDURE delete_formuladetail(
    p_formula_id     NUMBER ,
    p_formulaline_id NUMBER ,
    p_return_status OUT VARCHAR2 ,
    p_message OUT VARCHAR2 )
IS
  l_api_version       CONSTANT NUMBER := 1.1;
  l_init_msg_list     VARCHAR2(1)     := FND_API.G_TRUE ;
  l_commit            VARCHAR2(1)     := FND_API.G_FALSE;
  l_called_from_forms VARCHAR2(10)    := ‘NO’;
  l_return_status     VARCHAR2(1) ;
  l_msg_count         NUMBER ;
  l_msg_data          VARCHAR2(400) ;
  L_FORMULA_DETAIL_TBL GMD_FORMULA_DETAIL_PUB.FORMULA_UPDATE_DTL_TBL_TYPE;
  — l_formula_head fm_form_mst_b%rowtype;
  L_FORMULA_LINE FM_MATL_DTL%ROWTYPE;
  — i pls_integer := 1;
  NO_PROC_FINISH EXCEPTION ;
BEGIN
  IF (P_FORMULA_ID IS NULL) THEN
    P_MESSAGE      := ‘ P_formula_id parameter can not be empty ‘;
    RAISE NO_PROC_FINISH;
  END IF ;
  — получаем данные заголовка
  BEGIN
    SELECT *
    INTO l_formula_head
    FROM fm_form_mst_b fm
    WHERE fm.formula_id = p_formula_id;
  EXCEPTION
  WHEN OTHERS THEN
    p_message := ‘ Unable to find a formula ‘;
    raise no_proc_finish;
  END ;
  — get the data string formula
  BEGIN
    SELECT *
    INTO l_formula_line
    FROM fm_matl_dtl fm
    WHERE fm.formulaline_id = p_formulaline_id;
  EXCEPTION
  WHEN OTHERS THEN
    p_message := ‘ Could not find a string formula ‘;
    raise no_proc_finish;
  END ;
  gme_common_pvt.set_who;
  l_formula_detail_tbl(i).RECORD_TYPE    := ‘D’;
  l_formula_detail_tbl(i).formula_id     := p_formula_id;
  l_formula_detail_tbl(i).formula_no     := l_formula_head.formula_no;
  l_formula_detail_tbl(i).formula_vers   := l_formula_head.formula_vers;
  l_formula_detail_tbl(i).formulaline_id := p_formulaline_id;
  GMD_FORMULA_DETAIL_PUB.DELETE_FORMULADETAIL( P_API_VERSION => L_API_VERSION, P_INIT_MSG_LIST => L_INIT_MSG_LIST, P_COMMIT => L_COMMIT, P_CALLED_FROM_FORMS => L_CALLED_FROM_FORMS, X_RETURN_STATUS => L_RETURN_STATUS, X_MSG_COUNT => L_MSG_COUNT, X_MSG_DATA => L_MSG_DATA, P_FORMULA_DETAIL_TBL => L_FORMULA_DETAIL_TBL );
  IF l_return_status != FND_API.g_ret_sts_success THEN
    IF l_msg_count = 1 THEN
      p_message   := FND_MSG_PUB.get(1,’F’);
      RAISE no_proc_finish;
    ELSE
      FOR l IN 1..l_msg_count
      LOOP
        FND_MSG_PUB.get (p_msg_index => l ,p_encoded => ‘F’ ,p_data => p_message ,p_msg_index_out => l_msg_count);
      END LOOP ;
      RAISE no_proc_finish;
    END IF ;
  END IF ;
  p_return_status := ‘S’;
EXCEPTION
WHEN no_proc_finish THEN
  p_return_status := ‘ E ‘;
WHEN OTHERS THEN
  p_message       := SQLERRM ;
  p_return_status := ‘ E ‘;
END ;

/by
, , , , ,

Some Useful Queries in Oracle Apps R12

How to initialize session in Oracle ?
FND_GLOBAL.APPS_INITIALIZE is used for initializing the session before calling any public or private API’s in Oracle Ebusiness suite. Its not required for all the API’s but its recommended that you set this profile before making any calls to either private or public API. 

Mentioned below is a sample call to FND_GLOBAL.APPS_INITIALIZE function

fnd_global.APPS_INITIALIZE(user_id=>l_user_id, 
                           resp_id=>l_resp_id, 
                           resp_appl_id=>l_resp_appl_id);

l_user_id is the fnd user ID which will be utilized during the call. 
l_resp_id is the responsibility ID 
l_resp_appl_id is the responsibility application ID. 
You can use either sysadmin or use some user who has all the above listed responsibilities.

For SYSADMIN, utilize the following query to get the respective values

select fnd.user_id , 
       fresp.responsibility_id, 
       fresp.application_id 
from   fnd_user fnd 
,      fnd_responsibility_tl fresp 
where  fnd.user_name = ‘SYSADMIN’ 
and    fresp.responsibility_name = ‘Order Management Super User’;

Another option is Help > Diagnostics > Examine and get the values from $profile session values.

How to Find PO Requisitions associated with PO Orders?
By using below query , we can find out the all the requisitions (Purchasing Requisitions) which are associated with what all Purchasing orders.

They are 2 types of requisitions.
1) Purchasing Requisition
2) Internal Requisition.
Note:- Only Purchase Requisitions will be converted to the Purchase orders.

SELECT prha.segment1 “Requisition Number”,
prha.type_lookup_code “Requisition Type”,
pha.segment1 “Purchase Order Number”,
pha.type_lookup_code “Purchase Order Type”
FROM po_headers_all pha,
po_distributions_all pda,
po_req_distributions_all rd,
po_requisition_lines_all prla,
po_requisition_headers_all prha
WHERE pha.po_header_id = pda.po_header_id
AND pda.req_distribution_id = rd.distribution_id
AND rd.requisition_line_id = prla.requisition_line_id
AND prla.requisition_header_id = prha.requisition_header_id

How to get the Cancelled Requisitions?
You can get all the Canceled Requisitions in the PO Module using the following Query.

SELECT
prha.requisition_header_id “requisition_header_id”
,prha.segment1 “Requisition Number”
,prha.preparer_id “preparer_id”
,TRUNC(prha.creation_date) “creation_date”
,prha.description “description”
,prha.note_to_authorizer “note_to_authorizer”
FROM
po_requisition_headers_all prha
,po_action_history pah
WHERE action_code=’CANCEL’
AND pah.object_type_code=’

REQUISITION’
AND pah.object_id=prha.requisition_header_id
How to find On-hand inventory information?
Execute below query to see the onhand inventory information in oracle applications.

SELECT
NVL(substr(org.organization_code, 1,3), ‘ ‘) orgcode
,NVL(substr(msi.segment1, 1, 8), ‘ ‘) seg11
,NVL(substr(msi.segment1, 9, 8), ‘ ‘) seg12
,NVL(substr(msi.segment1, 17, 4), ‘ ‘) seg13
,NVL(moq.subinventory_code, ‘ ‘) sub_inv_code
,NVL(to_char(round(sum(moq.transaction_quantity))), ‘ ‘) trans_qnty
FROM mtL_system_items msi
,org_organization_definitions org
,mtl_onhand_quantities moq
,hr_organization_units hou
WHERE moq.inventory_iteM_id = msi.inventory_item_id
AND moq.organizatioN_id = msi.organizatioN_id
AND moq.organizatioN_id = org.organizatioN_id
AND moq.organization_id = hou.organization_id
GROUP BY org.organization_code
, moq.subinventory_code
, msi.segment1;

How to know the version in oracle apps?
select * from v$version;
 
With the help of above query we can find out the oracle apps version. 


How to know the application is multi-org or not?


By using below query we can get the multi org status.


 


SELECT multi_org_flag
FROM   fnd_product_groups




How to know Which User is Locked the table?



This Query will list the name of user who locked the table table. The object name is taken as an input parameter.

SELECT c.owner
      ,c.object_name
      ,c.object_type
      ,fu.user_name locking_fnd_user_name
      ,fl.start_time locking_fnd_user_login_time
      ,vs.module
      ,vs.machine
      ,vs.osuser
      ,vlocked.oracle_username
      ,vs.sid
      ,vp.pid
      ,vp.spid AS os_process
      ,vs.serial#
      ,vs.status
      ,vs.saddr
      ,vs.audsid
      ,vs.process
FROM fnd_logins      fl
    ,fnd_user        fu
    ,v$locked_object vlocked
    ,v$process       vp
    ,v$session       vs
    ,dba_objects     c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name = :P_TABLE_NAME
AND nvl(vs.status,'XX') != 'KILLED';




How to get Customer Address Details in Oracle apps?





The following Query will provide the customer address details in oracle applications.

SELECT  hca.account_number customer_number,
                hp.party_name customer_name,
                hps.party_site_number site_number, hl.address1 address1,
                hl.address2 address2, hl.address3 address3,
                hl.address4 address4, hl.city city,
                hl.postal_code postal_code, hl.state state,
                ftt.territory_short_name country,
                hcsua1.LOCATION bill_to_location,
                hcsua2.LOCATION ship_to_location
  FROM hz_parties hp,
                hz_party_sites hps,
                hz_cust_accounts hca,
                hz_cust_acct_sites_all hcasa1,
                hz_cust_site_uses_all hcsua1,
                hz_locations hl,
                fnd_territories_tl ftt,
                hz_cust_acct_sites_all hcasa2,
                hz_cust_site_uses_all hcsua2
  WHERE hp.party_id = hps.party_id(+)
       AND hp.party_id = hca.party_id(+)
       AND hcasa1.party_site_id(+) = hps.party_site_id
       AND hcasa2.party_site_id(+) = hps.party_site_id
       AND hcsua1.cust_acct_site_id(+) = hcasa1.cust_acct_site_id
       AND hcsua2.cust_acct_site_id(+) = hcasa2.cust_acct_site_id
       AND hcsua1.site_use_code(+) = 'bill_to'
       AND hcsua2.site_use_code(+) = 'ship_to'
       AND hcasa1.org_id(+) = fnd_profile.VALUE ('org_id')
       AND hcasa2.org_id(+) = fnd_profile.VALUE ('org_id')
       AND hps.location_id = hl.location_id
       AND hl.country = ftt.territory_code
       AND ftt.LANGUAGE = USERENV ('lang')
  ORDER BY customer_number;





Script to find Oracle API’s for any module


With the help of below query we can find the API for a module in Oracle Apps.


select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like ‘%Header%’
and a.type = u.object_type
and a.name like ‘AR_%API%’ –- Checking for AR Related APIs
order by
a.owner, a.name







/by 
, , , , , 
Running Fast Formula From Backend – Oracle HRMS


Prerequisites:



Step1 : Create the Element and Element links
Step2: write the fast formula
Step3: Attach the fast formula in Formula Results

Package used to call the fast formula from the backed : ff_exec

Simple Code Snippet below: 

      l_formula_id        NUMBER;
      l_element_inputs    ff_exec.inputs_t;
      l_element_outputs   ff_exec.outputs_t;
      l_in_count          NUMBER;
      l_out_count         NUMBER;
      l_pay_value         NUMBER;

   BEGIN

      BEGIN
         SELECT formula_id
           INTO l_formula_id
           FROM ff_formulas_f
          WHERE formula_name = ‘XX_PAYROLL_FORMULA’
          AND p_effective_date BETWEEN effective_start_date  
             AND effective_end_date;
      EXCEPTION 
      WHEN OTHERS 
      THEN
      DBMS_OUTPUT.put_line (‘NO formula exists’);
      END;

      IF l_formula_id IS NOT NULL

      THEN

— Insert FND_SESSIONS row ( Optional )

  INSERT INTO fnd_sessions
             ( session_id, 
               effective_date
             )
        VALUES 
            ( USERENV (‘sessionid’), 
              p_effective_date
             );

—
— Initialize the formula.
—

ff_exec.init_formula (l_formula_id,
                      p_effective_date,
                      l_element_inputs,
                      l_element_outputs
                      );

—
— Loop through the Input Values
—
FOR l_in_count IN l_element_inputs.FIRST .. l_element_inputs.LAST
    LOOP

    —
    — Pass The each Input value name and its Value : Eg: START_DATE and p_start_date
    —    
       IF (l_element_inputs (l_in_count).NAME = ‘START_DATE’)
        THEN
           l_element_inputs (l_in_count).VALUE :=
                fnd_date.date_to_canonical (p_start_date);
        END IF;

END LOOP;

—
–Run The formula
—

ff_exec.run_formula (l_element_inputs, l_element_outputs);

—
— Get the Out Put Values
—
FOR l_out_count IN l_element_outputs.FIRST .. l_element_outputs.LAST

    LOOP
      —
      — Get all the Out Put Values Here L_PAY_VALUE is the out put value
      —
      IF (l_element_outputs (l_out_count).NAME = ‘L_PAY_VALUE’)
        THEN
           l_pay_value := l_element_outputs (l_out_count).VALUE;
      END IF;

    END LOOP;

RETURN (l_pay_value);

END;


/by 
, , , , , , 
Complete GL Interface – PL/SQL Procedure
Pre-Requisites:
    ————–
      a) set of books should be defined
      b) Current Conversion rates and accounting periods need to be defines
      c) Source and Category Name Should be defined


    Interface Tables:
    —————-
      GL_INTERFACE
 
    Base Tables:
    ———–
       GL_JE_HEADERS
       GL_JE_LINES
       GL_JE_BATCHES


 Standard Program:
 Go to General Ledger Vision Operations(USA)
   Run =>Import
 Here Give the Source name and Save.


While Click on the save button  Back end One Program Concurrent Program is running.If it is Success the Records are Successfully loaded from interface table to base Table Others wise Some Error are there.
Copy that Request_id and Enter into Generals our Records will be there………..


   Validation Columns:
   ——————
       Source       period_name   currency_code   set_of_books_id
       je_source    je_catregory  accounting_date entered_dr , entered_cr
       accounted_cr accounted_dr  encumberance_type_id


    Source = ‘NEW’
    period need to be open status in  gl_period_statuses
    souce_name defined in gl_je_source table
    category_name defines  gl_je_Category
    currency available in fnd_Currencies
    accounted_cr and accounted_dr total should be same.


Control file for GL_Interface:


LOAD DATA
INFILE *
TRUNCATE INTO TABLE GL_INTERFACE_TEMP
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
TRAILING NULLCOLS
(STATUS,
 SET_OF_BOOKS_ID,
 ACCOUNTING_DATE,
 CURRENCY_CODE,
 DATE_CREATED,
 CREATED_BY,
 ACTUAL_FLAG,
 USER_JE_CATEGORY_NAME,
 USER_JE_SOURCE_NAME,
 SEGMENT1,
 SEGMENT2,
 SEGMENT3,
 SEGMENT4,
 SEGMENT5,
 ENTERED_DR,
 ENTERED_CR, 
 ACCOUNTED_DR,
 ACCOUNTED_CR,
 GROUP_ID)


BEGIN DATA
NEW,1,11-AUG-2002,USD,11-AUG-2002,1318,A,Inventory,JETFORMS,01,110,7730,0000,000,555,555,555,555,11
NEW,1,11-AUG-2002,USD,11-AUG-2002,1318,A,Inventory,JETFORMS,01,110,7730,0000,000,554,554,554,554,11


Script
sqlldr apps/apps control=’/apps/aptest/visappl/xxcus/11.5.0/bin/xx_gl.ctl’    log=’/apps/aptest/visappl/xxcus/11.5.0/bin/xx_gl.log’


exit 0


GL Interface Package:


CREATE OR REPLACE package body APPS.xx_gl_int_pkg
is
procedure dis_log(p_msg in varchar2)
is
begin
fnd_file.put_line(fnd_file.log,p_msg);
end;


procedure main(errbuf out varchar2,
               retcode out varchar2
               )
 is
 cursor c1 is select a.rowid row_id,a.* from GL_INTERFACE_TEMP a;
 v_gl_int    gl_interface%rowtype;
 v_process_flag    varchar2(10);
 v_error_msg   varchar2(100);
 v_tot_err_msg   varchar2(1000);
 begin
 
   dis_log(‘before entering the loop’);
  
 
 for i in c1 loop
                v_error_msg :=null;
                v_process_flag:=’S’;
                v_tot_err_msg:=null;
                v_gl_int:=null;
              –currency_code validation
                begin
                select  currency_code into v_gl_int.currency_code
                                      from fnd_currencies
                                     where currency_code=i.currency_code;
                 exception
                 when no_data_found then
                    v_process_flag:=’E’;
                    v_error_msg  := ‘Invalid Currency Code =>’||i.currency_code;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                 when others then
                    v_process_flag:=’E’;
                    v_error_msg   := ‘ Exception at Currency Code =>’||i.currency_code;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                end;     
               
               
                –user_je_source_name validation
               
                begin
               
                  select user_je_source_name into v_gl_int.user_je_source_name
                                             from gl_je_sources
                                            where user_je_source_name=i.user_je_source_name;
                  exception
                 when no_data_found then
                    v_process_flag:=’E’;
                    v_error_msg  := ‘Invalid Sourec Name =>’||i.user_je_source_name;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                 when others then
                    v_process_flag:=’E’;
                    v_error_msg   := ‘ Exception at Sourec Name =>’||i.user_je_source_name;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                end;     
               
                –category_name  validation
                begin
                     select user_je_category_name into v_gl_int.user_je_category_name
                     from gl_je_categories
                     where user_je_category_name=i.user_je_category_name;
                  exception
                  when no_data_found then
                    v_process_flag:=’E’;
                    v_error_msg  := ‘Invalid category_name =>’||i.user_je_category_name;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                  when others then
                    v_process_flag:=’E’;
                    v_error_msg   := ‘ Exception at category_name =>’||i.user_je_category_name;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;   
                 
                end;
               
                 –user id validation
                
                begin
                     select user_id into v_gl_int.created_by from fnd_user
                                   where  user_id = i.created_by;
                  exception
                  when no_data_found then
                    v_process_flag:=’E’;
                    v_error_msg  := ‘Invalid user id =>’||i.created_by;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                  when others then
                    v_process_flag:=’E’;
                    v_error_msg   := ‘ Exception at user id =>’||i.created_by;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                   
                end;
               
                 — set of books id validation
                
                begin
                
                      SELECT SET_OF_BOOKS_ID INTO v_gl_int.set_of_books_id
                      FROM GL_SETS_OF_BOOKS WHERE SET_OF_BOOKS_ID=i.set_of_books_id;
                   exception
                  when no_data_found then
                    v_process_flag:=’E’;
                    v_error_msg  := ‘Invalid set of books id =>’||i.set_of_books_id;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                  when others then
                    v_process_flag:=’E’;
                    v_error_msg   := ‘ Exception atset of books id =>’||i.set_of_books_id;
                    v_tot_err_msg:= v_tot_err_msg||’ ‘||v_error_msg ;
                end;
             
                         v_gl_int.status                    :=i.status;
                        — v_gl_int.set_of_books_id           :=i.set_of_books_id;
                         v_gl_int.accounting_date           :=i.accounting_date;
                        — v_gl_int.currency_code             :=i.currency_code;
                         v_gl_int.date_created              :=i.date_created;
                         –v_gl_int.created_by                :=i.created_by;
                         v_gl_int.actual_flag               :=i.actual_flag ;
                         –v_gl_int.user_je_category_name     :=i.user_je_category_name;
                        –v_gl_int.user_je_source_name       :=i.user_je_source_name;
                         v_gl_int.segment1                  :=i.segment1;
                         v_gl_int.segment2                  :=i.segment2;
                         v_gl_int.segment3                  :=i.segment3;
                         v_gl_int.segment4                  :=i.segment4;
                         v_gl_int.segment5                  :=i.segment5 ;
                         v_gl_int.entered_dr                :=i.entered_dr;
                         v_gl_int.entered_cr                :=i.entered_cr;
                         v_gl_int.accounted_dr               :=i.accounted_dr;
                         v_gl_int.accounted_cr              :=i.accounted_cr;
                         v_gl_int.group_id                  :=i.group_id;
                        
              
                
                 dis_log(‘before inserting the loop’);  
                
                   if v_process_flag = ‘S’ then     
               
                    insert into gl_interface values v_gl_int;
                
                   end if;
           update GL_INTERFACE_TEMP set process_flag=v_process_flag,
                                           error_message=v_tot_err_msg
                       where rowid=i.row_id;
                 
                 dis_log(‘after inserting the loop’);     
 end loop;
 exception
 when others then
 dis_log(‘exception occured at main loop’);
 end main;
 end xx_gl_int_pkg;


/by 
, , , , , , 
How to find INV 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


/by