Query to find AP Bank details at Suppliers and Supplier Sites

Query:

SELECT 'Bank Account At Supplier Site Level' Bank_Account_Level
     , sup.segment1
     , sup.vendor_name
     , epa.org_id
     , ss.vendor_site_code
     , NULL   Party_Site_Code
     , eba.bank_account_num
     , piu.order_of_preference priority
     , eba.ext_bank_account_id
  FROM ap_suppliers sup
     , ap_supplier_sites_all       ss
     , iby_external_payees_all epa
     , iby_pmt_instr_uses_all  piu
     , iby_ext_bank_accounts   eba
 WHERE sup.vendor_id     = ss.vendor_id
   AND ss.vendor_site_id = epa.supplier_site_id
   AND epa.ext_payee_id  = piu.ext_pmt_party_id    
   AND piu.instrument_id = eba.ext_bank_account_id
   AND sup.segment1      = '10824'
UNION
SELECT 'Bank Account at Supplier Level'
     , sup.segment1
     , sup.vendor_name
     , epa.org_id
     , NULL
     , NULL
     , eba.bank_account_num
     , piu.order_of_preference priority
     , eba.ext_bank_account_id
  FROM ap_suppliers sup
     , iby_external_payees_all epa
     , iby_pmt_instr_uses_all  piu
     , iby_ext_bank_accounts   eba
 WHERE sup.party_id        = epa.payee_party_id
   AND epa.ext_payee_id    = piu.ext_pmt_party_id    
   AND piu.instrument_id   = eba.ext_bank_account_id
   AND sup.segment1        = '10824'
   AND supplier_site_id    IS NULL
   AND party_site_id       IS NULL
   ;

Query to find SR's assigned to specific user in Oracle Apps R12

Query:

select * from
(SELECT cia.incident_number       SR_NUMBER
,       papf.full_name             SR_Person
,       fu.user_name               SR_user
,       'OWNER'                    Record_Type
,       cia.INCIDENT_DATE          SR_Creation_date
,       cia.INCIDENT_LAST_MODIFIED_DATE
,       NULL                       old_status
,       cidv.NEW_STATUS_NAME       current_status
,       cidv.NEW_TYPE_NAME         SR_type
,       'NULL'   Notes
,       cidv.INCIDENT_DATE        Date1
,       cia.summary
,       cia.resolution_summary
FROM    cs_incidents_all          cia
,       jtf_rs_resource_extns     jrre
,       per_all_people_f          papf
,       fnd_user                  fu
,       CS_INCIDENTS_DIARY_V      cidv
WHERE   cia.INCIDENT_OWNER_ID = jrre.RESOURCE_ID
AND     jrre.SOURCE_ID        = papf.person_id
AND     papf.person_id        = fu.employee_id
AND     cia.incident_id       = cidv.incident_id
AND     cidv.INCIDENT_OWNER_FLAG = 'Y'
AND     cidv.INCIDENT_TYPE_FLAG  ='Y'
UNION
select  cia.incident_number SR_NUMBER
,       papf.full_name     SR_Person
,       cidv.owner         SR_Owner
,       'STATUS UPDATE'       Record_Type
,       cia.INCIDENT_DATE  SR_Creation_date
,       cia.INCIDENT_LAST_MODIFIED_DATE
,       cidv.OLD_STATUS_NAME  old_status
,       cidv.NEW_STATUS_NAME  current_status
,       cidv.NEW_TYPE_NAME   SR_type
,       'NULL'   Notes
,       cidv.INCIDENT_DATE  Date1
,       cia.summary
,       cia.resolution_summary
FROM    cs_incidents_all          cia
,       CS_INCIDENTS_DIARY_V      cidv
,       CS_INCIDENT_STATUSES_tL   cstt
,       CS_INCIDENT_SEVERITIES_tl cist
,       fnd_user                  fu
,       per_all_people_f          papf
WHERE   cia.incident_id            = cidv.incident_id
AND     cidv.INCIDENT_OWNER_FLAG  != 'Y'
AND     cidv.OLD_STATUS_NAME      != cidv.NEW_STATUS_NAME
AND     cia.INCIDENT_STATUS_ID     = cstt.INCIDENT_STATUS_ID
AND     cstt.language              = 'US'
AND     cia.INCIDENT_SEVERITY_ID   = cist.INCIDENT_SEVERITY_ID
AND     cist.language              = 'US'
AND     cidv.owner                = fu.USER_NAME
AND     fu.employee_id            = papf.person_id
UNION
select  cia.incident_number SR_NUMBER
,       papf.full_name     SR_Person
,       fu.user_name       SR_Owner
,       'NOTE'             Record_Type
,       cia.INCIDENT_DATE  SR_Creation_date
,       cia.INCIDENT_LAST_MODIFIED_DATE
,       NULL  old_stus
,       cist.name  current_status
,       citt.name   SR_type
,       jnv.NOTES Notes
,       jnv.ENTERED_DATE  Date1
,       cia.summary
,       cia.resolution_summary
FROM    cs_incidents_all cia
,       JTF_NOTES_VL  jnv
,       cs_incident_types_tl citt
,       cs_incident_statuses_tl cist
,       fnd_user      fu
,       per_all_people_f   papf
WHERE   cia.incident_id     = jnv.source_object_id
AND     jnv.SOURCE_OBJECT_CODE ='SR'
and     cia.INCIDENT_TYPE_ID = citt.INCIDENT_TYPE_ID
AND     citt.language              = 'US'
AND     cia.INCIDENT_STATUS_ID = cist.INCIDENT_STATUS_ID
AND     cist.language              = 'US'
AND     jnv.created_by      = fu.user_id
AND     fu.employee_id =papf.person_id
UNION
SELECT  incident_number            SR_NUMBER
,       papf.full_name             SR_Person
,       fu.user_name               SR_user
,       'RE ASSIGN'                Record_Type
,       cia.INCIDENT_DATE          SR_Creation_date
,       cia.INCIDENT_LAST_MODIFIED_DATE
,       cidv.OLD_STATUS_NAME       old_status
,       cidv.NEW_STATUS_NAME       current_status
,       cidv.NEW_TYPE_NAME         SR_type
,       NULL                Notes
,       cidv.INCIDENT_DATE         Date1
,       cia.summary
,       cia.resolution_summary
FROM    cs_incidents_all          cia
,       jtf_rs_resource_extns     jrre
,       per_all_people_f          papf
,       fnd_user                  fu
,       CS_INCIDENTS_DIARY_V      cidv
,       jtf_rs_resource_extns     old_res
,       per_all_people_f          old_per
,       fnd_user                  old_user
WHERE   cia.INCIDENT_OWNER_ID = jrre.RESOURCE_ID
AND     jrre.SOURCE_ID        = papf.person_id
AND     papf.person_id        = fu.employee_id
AND     cia.incident_id       = cidv.incident_id
AND     cidv.OWNER_OLD        != OWNER_NEW
AND     cidv.OWNER_OLD        = old_res.RESOURCE_ID
AND     old_res.SOURCE_ID     = old_per.person_id
AND     old_per.person_id     = old_user.employee_id
AND     cidv.INCIDENT_OWNER_FLAG = 'Y'
)
where SR_user = 'SATISHV'
--AND   trunc(Date1) > trunc(sysdate-365)
order by SR_NUMBER
;







Query Credits : Siva Gollapalle

Sample code for UTL_FILE Out Bound

SQL Query: To know the path of UTL_FILE directory

                      SELECT  *
                      FROM     V$PARAMETER
                      WHERE  name  =  'utl_file_dir';


Code:

set serveroutput on;
/

DECLARE

l_file_rec  UTL_FILE.FILE_TYPE;

BEGIN

l_file_rec := UTL_FILE.FOPEN('utl_file_dir path'
                                                     ,'XXVE_FNDUSER_DATA.txt'
                                                     ,'W');

-- note, file name support .txt,.xls,.csv
                         
FOR i IN(SELECT user_id,user_name,last_update_date,last_updated_by
                 FROM    fnd_user
               WHERE rownum<100)
LOOP

UTL_FILE.PUT_LINE(l_file_rec,i.user_id||','||i.user_name||','||i.last_update_date||','||i.last_updated_by                                        );

END LOOP;
UTL_FILE.FCLOSE(l_file_rec);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception at Others Block'||','||SQLCODE||','||SQLERRM);
END;





Source: http://psoug.org/snippet/UTL_FILE-import-data_102.htm

Sample Code to create AR Credit Memo Using AR_INVOICE_API_PUB.CREATE_INVOICE

Code:

set serveroutput on;
/

declare
l_return_status             varchar2(1);
l_msg_count                number := null;
l_msg_data                  varchar2(2000):=null;
l_batch_source_rec     ar_invoice_api_pub.batch_source_rec_type ;
l_trx_header_tbl          ar_invoice_api_pub.trx_header_tbl_type ;
l_trx_lines_tbl             ar_invoice_api_pub.trx_line_tbl_type  ;
l_trx_dist_tbl               ar_invoice_api_pub.trx_dist_tbl_type  ;
l_trx_salescredits_tbl  ar_invoice_api_pub.trx_salescredits_tbl_type ;
l_cust_trx_id               number;
lv_sqlerrm                   varchar2(4000) :=NULL;
ln_msg_index_out        number;

BEGIN
     
   mo_global.init ('AR');
   mo_global.set_policy_context ('S', 'org_id');
   fnd_global.apps_initialize(user_id,resp_id,resp_appl_id);

  l_batch_source_rec.batch_source_id :=  200;
 
 
  l_trx_header_tbl(1).trx_header_id             :=  9810;       -- header id for sample
-- Pass Transaction number, if system automatically generates then not required
  l_trx_header_tbl(1).trx_number                 :=  '98985699';
  l_trx_header_tbl(1).trx_date                      :=  sysdate;
  l_trx_header_tbl(1).trx_currency               :=  'GBP';
  l_trx_header_tbl(1).trx_class                     :=  'CM';      
  l_trx_header_tbl(1).cust_trx_type_id         := 1019;      -- cust_trx_type_id
  l_trx_header_tbl(1).bill_to_customer_id    :=  628528;
    l_trx_header_tbl(1).finance_charges        :=  null;
  l_trx_header_tbl(1).status_trx                     :=  'OP';
  l_trx_header_tbl(1).printing_option            :=  'PRI';
 
  l_trx_lines_tbl(1).trx_header_id                  :=  9810;         -- pass trx_header_id from header value
  l_trx_lines_tbl(1).trx_line_id                      :=  1013;         -- pass trx_line_id
  l_trx_lines_tbl(1).line_number                   :=  1;
  l_trx_lines_tbl(1).description                     :=  'Test';
  l_trx_lines_tbl(1).quantity_invoiced          :=1;
  l_trx_lines_tbl(1).unit_selling_price :=  -10;   --unit_selling_price should be negative for creditdemo
--  l_trx_lines_tbl(1).uom_code                   :=  'EA';         -- Not required for Credit Memo
  l_trx_lines_tbl(1).line_type                     :=  'LINE';
--l_trx_lines_tbl(1).AMOUNT                  := -10;

  l_trx_dist_tbl(1).trx_dist_id                    :=  1014;
  l_trx_dist_tbl(1).trx_line_id                     :=  1013;
  l_trx_dist_tbl(1).trx_header_id               :=  9810;
  l_trx_dist_tbl(1).ACCOUNT_CLASS    := 'REV';
  l_trx_dist_tbl(1).percent                          := 100;
  l_trx_dist_tbl(1).CODE_COMBINATION_ID := 3175;      -- pass correct CODE_COMBINATION_ID of transaction type
 
   
--Here we call the API to create Invoice with the stored values


    AR_INVOICE_API_PUB.CREATE_INVOICE
    (p_api_version                => 1.0
    ,p_init_msg_list              => FND_API.G_FALSE
    ,p_commit                      => 'F'
    ,p_batch_source_rec      => l_batch_source_rec
    ,p_trx_header_tbl           => l_trx_header_tbl
    ,p_trx_lines_tbl              => l_trx_lines_tbl
    ,p_trx_dist_tbl                => l_trx_dist_tbl
    ,p_trx_salescredits_tbl   => l_trx_salescredits_tbl
--    ,x_customer_trx_id     => l_cust_trx_id
    ,x_return_status             => l_return_status
    ,x_msg_count                => l_msg_count
    ,x_msg_data                  => l_msg_data
    );
 
    dbms_output.put_line('l_return_status: '||l_return_status);

    IF l_return_status = fnd_api.g_ret_sts_error OR
        l_return_status = fnd_api.g_ret_sts_unexp_error THEN

        dbms_output.put_line('FND_MSG_PUB.Count_Msg ');
        dbms_output.put_line(l_return_status||':'||sqlerrm);
       
     FOR l_msg_count IN 1..FND_MSG_PUB.Count_Msg
      LOOP
     
      FND_MSG_PUB.Get(p_msg_index     => l_msg_count
                     ,p_encoded       => 'F'
                     ,p_data          => l_msg_data
                     ,p_msg_index_OUT => ln_msg_index_out
                     );
   
       lv_sqlerrm   := SUBSTR(lv_sqlerrm||REPLACE(l_msg_data,CHR(10),''),1,3998);
     
       dbms_output.put_line(l_return_status||':'||lv_sqlerrm);

     END LOOP;

    FND_MSG_PUB.Delete_Msg;
           
    ELSE
         
         dbms_output.put_line('FND_MSG_PUB.Count_Msg else :'||FND_MSG_PUB.Count_Msg);
     
    END IF;
  -- commit;
END;

/

Note: In case CM not created even though API returns success status, you can check API error messages using the following query

Query : 
              select * from ar_trx_errors_gt;
              select * from  ar_trx_header_gt;

On-Hand Available Serial Numbers Query

Query: 


SELECT DISTINCT msn.serial_number,msn.lot_number
FROM   mtl_serial_numbers        msn
,      mtl_system_items_b        msi
,      mtl_onhand_quantities     moq
,      mtl_secondary_inventories sub
WHERE  msi.inventory_item_id       = msn.inventory_item_id
AND    msi.organization_id         = msn.current_organization_id
AND    msi.inventory_item_id       = moq.inventory_item_id
AND    msi.organization_id         = (XXIP_CEMLI_UTILS_PKG.CHECKGET_DDLD_ENABLED_ORG_ID(msi.organization_id))
AND    moq.lot_number              = msn.lot_number
AND    moq.inventory_item_id       = msn.inventory_item_id
AND    msn.current_status          = 3
AND    msn.reservation_id is null
AND    moq.organization_id         = sub.organization_id
AND    moq.subinventory_code       = sub.secondary_inventory_name
AND    sub.reservable_type         = 1
AND    msi.segment1                = :p_item
AND    msn.serial_number           NOT IN ( select  msnt.FM_SERIAL_NUMBER
                                           from    mtl_material_transactions_temp  mmtt
                                           ,       MTL_TRANSACTION_LOTS_TEMP mtlt
                                           ,       MTL_SERIAL_NUMBERS_TEMP  msnt
                                           where   mmtt.TRANSACTION_TEMP_ID = mtlt.TRANSACTION_TEMP_ID
                                           and     mtlt.SERIAL_TRANSACTION_TEMP_ID = msnt.TRANSACTION_TEMP_ID
                                           and     mmtt.INVENTORY_ITEM_ID          = msi.inventory_item_id
                                           and     mmtt.ORGANIZATION_ID            = msi.organization_id
                                           UNION
                                           select  msnt1.FM_SERIAL_NUMBER
                                           from    mtl_material_transactions_temp  mmtt1
                                           ,       MTL_SERIAL_NUMBERS_TEMP  msnt1
                                           where   mmtt1.TRANSACTION_TEMP_ID = msnt1.TRANSACTION_TEMP_ID
                                           and     mmtt1.INVENTORY_ITEM_ID          = msi.inventory_item_id
                                           and     mmtt1.ORGANIZATION_ID            = msi.organization_id
                                          )
order by msn.serial_number;


Query Credits: Siva Gollapalle

Sample code to create AR Invoice using ar_invoice_api_pub.create_invoice

set serveroutput on;
/

declare
l_return_status         varchar2(1);
l_msg_count             number;
l_msg_data              varchar2(2000);
l_batch_source_rec      ar_invoice_api_pub.batch_source_rec_type;
l_trx_header_tbl        ar_invoice_api_pub.trx_header_tbl_type;
l_trx_lines_tbl         ar_invoice_api_pub.trx_line_tbl_type;
l_trx_dist_tbl          ar_invoice_api_pub.trx_dist_tbl_type;
l_trx_salescredits_tbl  ar_invoice_api_pub.trx_salescredits_tbl_type;
l_cust_trx_id           number;

BEGIN
     
   mo_global.init ('AR');
   mo_global.set_policy_context ('S', 204);
   fnd_global.apps_initialize(userid,respid,resp_appl_id);

  l_batch_source_rec.batch_source_id :=  2997;
  l_trx_header_tbl(1).trx_header_id  :=  9899;   -- pass trx_header_id number  value1
  l_trx_header_tbl(1).TRX_NUMBER     :=  '996779';  -- pass invoice number
  l_trx_header_tbl(1).trx_date       :=  sysdate;
  l_trx_header_tbl(1).trx_currency   :=  'GBP';
  l_trx_header_tbl(1).cust_trx_type_id :=  1;        -- pass cust_trx_type_id
  l_trx_header_tbl(1).bill_to_customer_id :=  1290;
  l_trx_header_tbl(1).term_id    :=  4;
  l_trx_header_tbl(1).finance_charges  :=  null;
  l_trx_header_tbl(1).status_trx   :=  'OP';
  l_trx_header_tbl(1).printing_option :=  'PRI';
  --l_trx_header_tbl(1).reference_number :=  '1111';
  l_trx_lines_tbl(1).trx_header_id :=  9899;   --  pass trx_header_id number  same as  value1
  l_trx_lines_tbl(1).trx_line_id   :=  101;    --  pass trx_line_id value2
  l_trx_lines_tbl(1).line_number   :=  1;
  --l_trx_lines_tbl(1).inventory_item_id  :=  1185;
 l_trx_lines_tbl(1).description :=  'Test Invoice on June262017';
 l_trx_lines_tbl(1).quantity_invoiced   :=  5;
 l_trx_lines_tbl(1).unit_selling_price :=  200;   --Price
 l_trx_lines_tbl(1).uom_code    :=  'EA';
 l_trx_lines_tbl(1).line_type   :=  'LINE';
 l_trx_dist_tbl(1).trx_dist_id  :=  101;  --  pass trx_line_id  same as value2
 l_trx_dist_tbl(1).trx_line_id  :=  101;  --  pass trx_line_id  same as value2
 l_trx_dist_tbl(1).ACCOUNT_CLASS := 'REV';
 l_trx_dist_tbl(1).percent     := 100;
 l_trx_dist_tbl(1).CODE_COMBINATION_ID := 32414;   -- pass CODE_COMBINATION_ID from distributions window
     
--Here we call the API to create Invoice with the stored values

    AR_INVOICE_API_PUB.create_invoice
    (p_api_version          => 1.0
    --,p_commit               => 'T'
    ,p_batch_source_rec     => l_batch_source_rec
    ,p_trx_header_tbl       => l_trx_header_tbl
    ,p_trx_lines_tbl        => l_trx_lines_tbl
    ,p_trx_dist_tbl         => l_trx_dist_tbl
    ,p_trx_salescredits_tbl => l_trx_salescredits_tbl
    ,x_return_status        => l_return_status
    ,x_msg_count            => l_msg_count
    ,x_msg_data             => l_msg_data
    );
 
    dbms_output.put_line('Created:'||l_msg_data||l_return_status);

    IF l_return_status = fnd_api.g_ret_sts_error OR
       l_return_status = fnd_api.g_ret_sts_unexp_error THEN

        dbms_output.put_line(l_return_status||':'||sqlerrm);
    Else
        dbms_output.put_line(l_return_status||':'||sqlerrm);
        If (ar_invoice_api_pub.g_api_outputs.batch_id IS NOT NULL) Then
            Dbms_output.put_line('Invoice(s) suceessfully created!') ;
            Dbms_output.put_line('Batch ID: ' || ar_invoice_api_pub.g_api_outputs.batch_id);
            Dbms_output.put_line('customer_trx_id: ' || l_cust_trx_id);
        Else
            Dbms_output.put_line(sqlerrm);
        End If;
    end if;
    commit;
End;
/

AR Invoice and Tax Information Query

Query: A

   select  rct.trx_number "INVOICE NUMBER"
,      to_char(rct.TRX_DATE,'DD-MON-YYYY') "INVOICE DATE"
,      rbs.name "SOURCE"
,      rctt.name "TYPE"
,      RCTL.LINE_NUMBER
,      RCTL.LINE_TYPE
,      (select distinct segment1 from mtl_system_items_b msib
         where msib.inventory_item_id = rctl.inventory_item_id) "LINE_ITEM"
,     RCTL.DESCRIPTION
,     rctl.QUANTITY_INVOICED  "QUANTITY"
,     rctl.UNIT_SELLING_PRICE "UNIT PRICE"
,     round((rctl.QUANTITY_INVOICED * rctl.UNIT_SELLING_PRICE),2) AMOUNT
,     decode(rctl.UOM_CODE,'EA','Each') UOM
,     ZL.TRX_LINE_NUMBER
,      ZL.TAX_REGIME_CODE
,      ZL.TAX
,      ZL.TAX_CODE
,      ZL.TAX_JURISDICTION_CODE
,      TJ.TAX_JURISDICTION_NAME
,      ZL.TAX_RATE_CODE
,      ZL.TAX_RATE
,      TRT.TAX_RATE_NAME
,      ZL.TAX_STATUS_CODE
,      TS.TAX_STATUS_NAME
,      ZL.TAXABLE_AMT
,      ZL.tax_only_line_flag
 from RA_CUSTOMER_TRX_ALL rct
,     ra_customer_trx_lines_all            rctl
,     RA_BATCH_SOURCES_ALL   rbs
,     ra_cust_trx_types_all                   rctt
,     ZX_LINES                                   zl
,     ZX_REGIMES_TL                      TR
,     ZX_JURISDICTIONS_TL          TJ
,     ZX_TAXES_TL                           TX
,     ZX_RATES_TL                           TRT
,     ZX_STATUS_TL                         TS
where 1=1
  AND rct.BATCH_SOURCE_ID                           =  rbs.BATCH_SOURCE_ID
  AND rct.CUST_TRX_TYPE_ID                          =  rctt.CUST_TRX_TYPE_ID
  AND rct.org_id                                                      =  rctt.org_id
  AND RCT.CUSTOMER_TRX_ID                       =  RCTL.CUSTOMER_TRX_ID
  AND RCT.org_id                                                   =  RCTL.org_id
  AND rctl.TAX_LINE_ID                                      =  zl.TAX_LINE_ID(+)
  AND ZL.TAX_REGIME_ID                                =  TR.TAX_REGIME_ID(+)
  AND NVL(ZL.TAX_JURISDICTION_ID,1)       =  TJ.TAX_JURISDICTION_ID(+)
  AND NVL(ZL.TAX_ID,1)                                    =  TX.TAX_ID(+)
  AND NVL(ZL.TAX_STATUS_ID,1)                    =  TS.TAX_STATUS_ID(+)
  AND NVL(ZL.TAX_RATE_ID,1)                        =  TRT.TAX_RATE_ID(+)
  AND TR.LANGUAGE(+)                                    =  userenv('LANG')
  AND TJ.LANGUAGE(+)                                     = userenv('LANG')
  AND TX.LANGUAGE(+)                                    = userenv('LANG')
  AND TRT.LANGUAGE(+)                                  = userenv('LANG')
  AND TS.LANGUAGE(+)                                    = userenv('LANG')
--  AND RCTL.LINE_TYPE       = 'LINE' --'TAX'  --  if you want tax line info
  AND rct.trx_number                                             =  'p_invoice_number';

Printable Button in OAF

1.       Create a button on Page Layout Region like

2.       Change Properties of Printable Button in Property Inspector

ID: PrintablePageID

Item Style: button

Attribute Set: /oracle/apps/fnd/attributesets/Buttons/PrintablePage

Note: After specifying Attribute set just Tab Out, Comments will add automatically.

Comments: UI Standards 'Printable Page' button as of 12/09/02

BC4J

View Instance:  XXEmpDetails          (select any View Object)

View Attribute: (Optional)

Navigation

Destination URI: OA.jsp?page=/xxvem/oracle/apps/ont/order/webui/XXVEMOrderAttachmentsPG&retainAM=Y&OARF=printable&HeaderId={@XXHeaderId}

Target Frame: _blank

Note: Here  

 /xxvem/oracle/apps/ont/order/webui/XXVEMOrderAttachmentsPG is the Actual Page Path and  {@XXHeaderId} is the View Attribute

VISUAL

Prompt: Printable Page

Add Code in Controller: In Process Form Request

 if (LafUtils.isPrintableFacet(pageContext))
   {
       // Printable page mode processing
        OAButtonBean print =(OAButtonBean)webBean.findIndexedChildRecursive("PrintPageId");
        print.setRendered(Boolean.FALSE);
     

   }

Register IN OUT mode Parameter in OAF

Code : 
 
    OADBTransaction oadbtransaction = getOADBTransaction();
    StringBuffer str = new StringBuffer();
    str.append( " BEGIN ");
    str.append( " APPS.XXVEM_PKGNAME_.XXVEM_PROCNAME( ");
    str.append( " p_line_id    => :1, ");
    str.append( " p_Invoice_id    => :2, ");
    str.append( " p_bill_id => :3, ");
    str.append( " p_prcedure_id     => :4, ");
    str.append( " p_success_flag    => :5, ");
    str.append( " p_message => :6  ");
    str.append( "    ); ");
    str.append( " END; ");

 CallableStatement oraclecallablestatement=                     oadbtransaction.createCallableStatement(str.toString(), 1);
        String msgs[]=new String[3];
        String SuccFlag;
        String ErrMsg;

        oraclecallablestatement.setInt(1,  LineId );
        oraclecallablestatement.setInt(2,  Invoiceid );
        oraclecallablestatement.setString(3,  billid );
        oraclecallablestatement.setInt(4,  prcedureid );
        oraclecallablestatement.registerOutParameter(4, Types.INTEGER);
        oraclecallablestatement.registerOutParameter(5, Types.VARCHAR);
        oraclecallablestatement.registerOutParameter(6, Types.VARCHAR);
        oraclecallablestatement.execute();
        getOADBTransaction().commit();
     
        PrcedureId = oraclecallablestatement.getString(4).toString();
        SuccFlag = oraclecallablestatement.getString(5);
        Msg= oraclecallablestatement.getString(6);
        msgs[0]= PrcedureId;
        msgs[1]=SuccFlag;
        msgs[2]=Msg;

Explanation: 

In the Above Code, I am registering 4th parameter as IN OUT mode parameter.
Register as IN parameter  :  oraclecallablestatement.setInt(4,  prcedureid );
Register as OUT parameter :  oraclecallablestatement.registerOutParameter(4, Types.INTEGER);
I am passing parameter to string array : msgs[0]= PrcedureId;

so I need to type cast the variable at line : PrcedureId = oraclecallablestatement.getString(4).toString();

OAF Page Deployment into Oracle Apps R12

1. In JDeveloper compile the page.
    When page got compiled, it will create .class files for all our java files.
    java files and xml files    ->  Jdeveloper installed place\jdevhome\jdev\myprojects
    class files and xml files   ->  Jdeveloper installed place\jdevhome\jdev\myclasses
 
2. Java Files can also be compiled in UNIX.
    Go to JAVA_TOP(/u01/oracle/DEMO/apps/apps_st/comn/java/classes).

3. In JAVA_TOP copy your package structure of myprojects (eg:      xxvem/oracle/apps/per/selfservice/server,                                                                  xxvem/oracle/apps/per/selfservice/webui)

   Compile each Java file.
   eg. xxvem/oracle/apps/per/selfservice/server/
           xx_paymentsVOImpl.java
     
        javac xx_paymentsVOImpl.java  -> To compile java file.
       Continue this process for all java files.

    OR

   Copy Package structure for all class files from \jdevhome\jdev\myclasses to server
   /oracle/DEMO/apps/apps_st/comn/java/classes/ ( cd $JAVA_TOP)
 

4. Run your import script for pages and regions.
   Note: When you did modifications on the page then you need to move page.xml file and run Import script. If you changed the only code in CO, AM, VO no needs to run the import script every time, bounce the services is enough.

5.Import script should be run from webui path (e.g. xxvem/oracle/apps/per/selfservice/webui)

6.Go to xxvem/oracle/apps/per/selfservice/webui in UNIX
   and run below Import script.
 
java oracle.jrad.tools.xml.importer.XMLImporter $JAVA_TOP/xxvem/oracle/apps/per/selfservice/webui/XXVEMSelfservicePG.xml -rootdir  $JAVA_TOP -username apps -password veMUL76 -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<HOSTNAMW>)(PORT=<Port Number>))(CONNECT_DATA=(SID= <SID Value>)))"

7. Print Documents to check imported XML page content (Optional)

     Example:

     BEGIN
 jdr_utils.printDocument('/xxvem/oracle/apps/per/selfservice/webui/XXVEMSelfservicePG.xml,1000);
     EXCEPTION
     WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE(SQLERRM);
     END;
   
8. Create a Function(Application Developer->Applicaton->Function)
     In Properties -> select Type      = SSWA JSP Function
     In WEBHTMl    -> select HTML Call =
     OA.jsp?page=/xxvem/oracle/apps/per/selfservice/webui/XXVEMSelfservicePG
   
9. Create a Menu and submenu.Attach this Function to sub menu and submenu to a menu.
     Attach menu to Responsibility and run the page.

How to Create/Delete Implementation user in Oracle Fusion instance

We have two types of users in Oracle fusion.

1. Implementation User
2. Employee User

All implementation users can create by using IDM (Identity Manager). Implementation user cannot act as an employee. If you create any user from IDM there is no possibility to mapping employee to a user. All Employee users can create from HCM (Human Capital Management).

Create User:

Step1: Go to Home page URL and enter username and password.

Here Username: FUFIN_USR

           Password: *********

Click on “Sign In” Button.

Step2: you are navigating to User Home page, please see below Image

Step3: Click on Username (FUFIN_USR) or down arrow mark beside the username. You will “Settings and Actions” window, select “Setup and Maintenance” under Administration.

Step4: Search with “Manage Job Roles” in search place as shown in below image.

Click on Role Name, it will take to you IDM(Identity Manager) page.

Step 5: Creating Implementation User

             Click on “Administration”.

Click on “Create User” hyperlink.

Enter Mandatory Field values and click on SAVE button.

Last Name: FUSFIN_USR

For Organization click on LOV option and search with “%” and select default Organization provided by Oracle.

Organization: Xellerate Users

For User Type select from drop down list

User Type: Contractor

Enter Account Setting Details

User Login : FUSFIN_USR

Password : *********

Conform Password : *********

Click on SAVE Button.

The user has created successfully.

Step 6: Assign Roles to user

Click on Roles Tab

Click on Assign Button and search with Role display name and select the role and press Add Button.

In the same way, you can assign two more roles.

1. IT Security Manager

2. Employee

You have assigned roles to user successfully

How to Run JOB..?

Go to Home page and select navigator

Navigation: Navigator à Tools à Schedule Processes.

Click on Schedule New Process button.

Type some random text and press tab button.

Search with LDAP (case sensitive) and select ‘’ Retrieve Latest LDAP Changes” and press OK.

Parameter window will appear on the screen. Just click on submit button.

You will get confirmation.

Close the window. And press refresh button.

Delete User:

Step 1: Go to IDM (Identity Manager) and search for user name.

Click on user name and select Delete User Button.

Select Yes Button.

The user has deleted successfully.

OAF: Insert Data into Database

1. First, we have to write one new method in AMImpl class fro creating new record

public void InsertRecord()
{
 InsertVOImpl vo= getInsertVO1();
 OADBTransaction trans= getOADBTransaction();
 vo.executeQuery();
 Row v_row;
 v_row = (Row)vo.createRow();
 vo.insertRow(v_row);
}

2. In controller,  we have to initialise the AM in processRequest

public void processRequest(OAPageContext pageContext, OAWebBean webBean)
{
 super.processRequest(pageContext, webBean);
 InsertRecordsAMImpl am=(InsertRecordsAMImpl)pageContext.getApplicationModule(webBean);
 am.InsertRecord();  /* Name of the method which we created in AM */
}

3. In processFormRequest, we have to write code for save the record into database.

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
 super.processFormRequest(pageContext, webBean);
 InsertRecordsAMImpl am=(InsertRecordsAMImpl)pageContext.getApplicationModule(webBean);
 if(pageContext.getParameter("item6")!=null)
 {
 am.getOADBTransaction().commit();
 throw new OAException("Employee Created sucsessfully",OAException.CONFIRMATION);
 }
}