Displaying concurrent programm name dynamically on a report

for displaying concurrent programm name dynamically in a report
 declare user parameter as p_conc_request_id
 write the below commnds in triggers as
  srw.user_exit('fnd srwinit')  in before report trigger and
  srw.user_exit('fnd srwexit') in after report trigger.

and take one place holder column in report level... so, now we r going to use this place holder column in one formaula column.
for that take one formula column and in that we want to write a piece of code which is shown below....

  select fcp.user_concurrent_program_name
  into :cp_conc_name(place holder column name)
  from fnd_concurrent_requests fcr,
  fnd_concurrent_programs_vl fcp
  where fcr.concurrent_program_id=fcp.concurrent_program_id
  and fcr.request_id             =nvl(:p_conc_request_id,fcr.request_id);
  and for request id we can

  declare
    in same in formula column
  as
    return(fnd_profile.value(':p_conc_request_id'));

P2P Overview

Procure to Pay:

First let’s see what the heading itself means? Procure to Pay means Procuring Raw Materials required to manufacture the final or finished Goods to Paying the Supplier from whom the material was purchased. But this is not just two steps. It involves many steps. Let’s see the steps and Oracle Application involved in performing those steps.

1.      Oracle Purchasing: You enter Suppliers of different materials and products you want to purchase to manufacture a finished good that your organization plans to sell.

2.      Oracle Purchasing: You prepare a Request for Quotation (RFQ) and send it to different suppliers to get the best and/or economical price for the product.

3.      Oracle Purchasing: Suppliers sends their quotations and you upload those quotations in Oracle Purchasing to get the best three quotes and further to get the one best quote.

4.      Oracle Purchasing: You prepare a Purchase Order(PO) against the best RFQ to buy the goods from the supplier who quoted the suitable price and sends the PO to that supplier

5.      Oracle Purchasing: The supplier receives the confirmation of purchase from PO and ships the ordered goods. You receive the goods enter a Goods Received Note (GRN) in Oracle Purchasing.

6.      Oracle Inventory / Oracle Assets:It’s up to you whether you want to receive the goods at your head office or you Inventory directly. In either case you move the received goods to your different Raw Material Inventory from Oracle Purchasing to Oracle Inventory and the Item Count increases. If the item is Asset Type then it will move to Oracle Assets at the time of Invoice creation in Oracle Payables.

7.      Oracle General Ledger:Once you move the goods to Oracle Inventory, it sends the Material Accounting to Oracle General Ledger.

8.      Oracle Payables: After this the supplier sends you the invoice for the purchased goods and you Enter or Match the invoice against the PO from Oracle Purchasing in Oracle Payables. As said before, if the item is Asset in nature then it will move to Oracle Asset.

9.      Oracle General Ledger:When you enter the invoice it means that you have created a Liability against that supplier and also you have recorded the expense incurred or asset purchased. Oracle Payables sends the invoice accounting to Oracle General Ledger.

10.  Oracle Payables:You pay the invoice and settle the Liability.

11.  Oracle General Ledger: The liability is settled and your cash movement account is updated.

12.  Oracle Cash Management: As you pay the invoice Oracle Payables sends the payment information to Oracle Cash Management for Bank Reconciliation. Once reconciled, Oracle Cash Management sends the updated Bank/Cash accounting entry to Oracle General Ledger.

13.  Oracle General Ledger: Your cash at bank is updated with actual balance.

14.  Oracle Process Manufacturing(OPM) / Oracle Discrete Manufacturing(ODM):You start the manufacturing of your final product. Both OPM or ODM requests the different raw materials from you inventory organizations and manufactures a finished good.

15.  Oracle Inventory: As the raw materials are issued to OPM and ODM the inventory sends the issuing material accounting to General Ledger and decreases the Item Count from the Raw Material Store. As the finished good is prepared, Oracle Inventory receives the finished good in Finished Good Store and increase the Item Count.

Now the final product is ready to be sold in the market and from here the O2C cycle starts.

O2C Overview

Order to Cash Cycle:

Order to Cash means Customer’s Order Placing to Vendor’s Cash Receiving. When your final product is ready to be sold, you market it. The customer gets fascinated with the marketing campaign and decides to buy your product and from here starts the O2C cycle.

1.      Oracle Order Management:Customer places the order.

2.      Oracle Order Management:You enter the customer order

3.      Oracle Inventory: Check the available unit and the quantity ordered by the customer.

4.      Oracle Order Management:You ship the product to customer site and decreases the Finished Goods inventory.

5.      Oracle Receivables:The customer receives the product and you invoice the customer.

6.      Oracle General Ledger:You record your revenue and receivables.

7.      Oracle Receivables:The customer pays and you receive the cash/check.

8.      Oracle Cash Management:Oracle Receivables sends the customer receipt for Bank Reconciliation. After reconciliation, Oracle Cash Management send the actual bank balance or Oracle General Ledger.

9.      Oracle General Ledger:You have the actual bank balance.

This is how the P2P and O2C cycle works, but this is not the only way, obviously there are many other applications with different cycles. This is one of them

Register Table, Column, Primary Key, Primary key column

DECLARE
  V_APPL_SHORT_NAME varchar2 (40) := 'PER';
  v_tab_name        VARCHAR2 (32) := 'XX_ORACLE-APPSS_BLOGSPOT_COM'; -- Change the table name if you require
  v_tab_type        VARCHAR2 (50) := 'T';
  v_next_extent     NUMBER        := 512;
  v_pct_free        NUMBER;
  v_pct_used        NUMBER;
BEGIN
  -- Unregister the custom table if it exists
  ----- ad_dd.delete_table (p_appl_short_name             => 'PER', p_tab_name => v_tab_name);
  -- Register the custom table
  FOR tab_details IN
  (SELECT table_name ,
    tablespace_name ,
    pct_free ,
    pct_used ,
    ini_trans ,
    max_trans ,
    initial_extent ,
    next_extent
  FROM dba_tables
  WHERE table_name = v_tab_name
  )
  LOOP
    AD_DD.REGISTER_TABLE (
    P_APPL_SHORT_NAME => V_APPL_SHORT_NAME,
    P_TAB_NAME => TAB_DETAILS.TABLE_NAME,
    P_TAB_TYPE => V_TAB_TYPE,
    P_NEXT_EXTENT => NVL (TAB_DETAILS.NEXT_EXTENT, 512),
    p_pct_free => NVL (tab_details.pct_free, 10),
    p_pct_used => NVL (tab_details.pct_used, 70) );
  END LOOP;
  -- Register the columns of custom table
  FOR all_tab_cols IN
  (SELECT column_name ,
    column_id ,
    data_type ,
    data_length ,
    nullable
  FROM all_tab_columns
  WHERE table_name = v_tab_name
  )
  LOOP
    AD_DD.REGISTER_COLUMN
    (
    P_APPL_SHORT_NAME => V_APPL_SHORT_NAME,
    P_TAB_NAME => V_TAB_NAME,
    P_COL_NAME => ALL_TAB_COLS.COLUMN_NAME,
    P_COL_SEQ => ALL_TAB_COLS.COLUMN_ID,
    P_COL_TYPE => ALL_TAB_COLS.DATA_TYPE,
    P_COL_WIDTH => ALL_TAB_COLS.DATA_LENGTH,
    P_NULLABLE => ALL_TAB_COLS.NULLABLE,
    P_TRANSLATE => 'N',
    P_PRECISION => null,
    P_SCALE => null
    );
  END LOOP;
  FOR all_keys IN
  (SELECT constraint_name,
    table_name,
    constraint_type
  FROM all_constraints
  WHERE constraint_type = 'P'
  AND table_name        = v_tab_name
  )
  LOOP
    AD_DD.REGISTER_PRIMARY_KEY
      (P_APPL_SHORT_NAME => V_APPL_SHORT_NAME,
      P_KEY_NAME => ALL_KEYS.CONSTRAINT_NAME,
      P_TAB_NAME => ALL_KEYS.TABLE_NAME,
      P_DESCRIPTION => 'Register primary key',
      p_key_type => 'S',
      P_AUDIT_FLAG => 'N',
      p_enabled_flag => 'Y' );
    FOR all_columns IN
    (SELECT column_name,
      POSITION
    FROM dba_cons_columns
    WHERE table_name    = all_keys.table_name
    AND constraint_name = all_keys.constraint_name
    )
    LOOP
      AD_DD.REGISTER_PRIMARY_KEY_COLUMN
      (P_APPL_SHORT_NAME => V_APPL_SHORT_NAME,
      P_KEY_NAME => ALL_KEYS.CONSTRAINT_NAME,
      P_TAB_NAME => ALL_KEYS.TABLE_NAME,
      P_COL_NAME => ALL_COLUMNS.COLUMN_NAME,
      P_COL_SEQUENCE => ALL_COLUMNS.POSITION
      );
    END LOOP;
  END LOOP;
  COMMIT;
END;

Employee Income Query

SELECT ppeo.EMPLOYEE_NUMBER,
  ppeo.FULL_NAME NAME,
  ety.ELEMENT_NAME,
  PACT.EFFECTIVE_DATE,
  NVL (TO_NUMBER (RRV.RESULT_VALUE), 0)
FROM pay_run_results rrs,
  pay_run_result_values rrv,
  pay_element_types_f ety,
  pay_element_classifications cla,
  pay_assignment_actions asact,
  pay_payroll_actions pact,
  per_all_assignments_f assm,
  per_all_people_f ppeo,
  pay_input_values_f inv
WHERE rrs.RUN_RESULT_ID = rrv.RUN_RESULT_ID
AND rrs.ELEMENT_TYPE_ID =ety.ELEMENT_TYPE_ID
  -- AND rrv.RESULT_VALUE NOT IN ('0')
  -- and  ety.ELEMENT_NAME like '%Abse%'
  -- AND cla.CLASSIFICATION_NAME IN ('Earnings')
AND ety.CLASSIFICATION_ID              =cla.CLASSIFICATION_ID
AND rrs.ASSIGNMENT_ACTION_ID           =asact.ASSIGNMENT_ACTION_ID
AND TO_NUMBER (pact.PAYROLL_ACTION_ID) = TO_NUMBER (asact.PAYROLL_ACTION_ID)
AND asact.ASSIGNMENT_ID                = assm.ASSIGNMENT_ID
AND assm.EFFECTIVE_END_DATE            > SYSDATE
AND assm.PERSON_ID                     = ppeo.PERSON_ID
AND ppeo.EFFECTIVE_END_DATE            > SYSDATE
AND inv.EFFECTIVE_END_DATE             > SYSDATE
AND RRV.INPUT_VALUE_ID                 = INV.INPUT_VALUE_ID
AND PACT.EFFECTIVE_DATE BETWEEN sysdate AND sysdate-30
AND inv.name LIKE'%%'

Password and Session profile options

Password profile options

SINCE PRODUCTION is SETUP with VERY high security PASSWORDS GET locked EASILY.
In a big team of developers someone might enter the old password several times and lock a username which everyone shares. After which it becomes a problem setting a new password as all the old ones cannot be reused. To fix this problem set the following profile option value to NULL.

Signon Password Case
Signon Password Custom
Signon Password Failure Limit
Signon Password Hard to Guess
Signon Password Length
Signon Password No Reuse

 

Apps Session
I also like to keep a session alive for several hours on a non production instance as I keep getting in and out of the instance. Hence I change the following profile option to 300 for 300 minutes, i.e. 5 hours.
ICX:Session Timeout

R12 Login Page: How to Personalize the Logo

To implement this there are 2 actions required. The first is to change profile option

    Login to Applications as System Administrator
    Select: System Administrator > Profile - System
    Query profile "Corporate Branding Image for Oracle Applications"
    Change the SITE level value to the name of the xxx.gif file ( eg  my_company_logo.gif )
    Save the change

The second action is performed on the Application server.

    Open a telnet/PTTY session to the Application server and source Applications environment
    Navigate to the $OA_MEDIA directory
    Copy the image file to this directory and make sure permissions are set properly

Now clear your browser cache and access the environment. Now the new logo should be rendered.

How to run one report from another report

Running nested reports:

For running one report from another report the user has to use SRW.RUN_REPORT

Go to layout editor

-->

 create a button and label it as ‘Click this button’

Open the property palette of the button and set the Button Behavior:

Type=pl/sql: Pl/sql

Trigger= provide the code

procedure U_ButtonButtonAction is

BEGIN

  srw.run_report('module=C:\Documents and Settings\Administrator\Desktop\POXDETIT.rdf destype=Screen');

END;

Reports

Reports

1. How to run one report from another report 
2. displaying concurrent programm name dynamically in a report 
3. Scheduling the Discoverer reports 

Workflow

Concepts related to Workflow

1. How to Launch Workflow from PL SQL
2.  USER Management | Role Categories | Roles | Indirect Responsibilities

How to Launch Workflow from PL SQL

The below script will launch the desired workflow FROM PL/SQL code:

DECLARE
  v_itemtype VARCHAR2(50);
  v_itemkey  VARCHAR2(50);
  v_process  VARCHAR2(50);
  v_userkey  VARCHAR2(50);
BEGIN
  v_itemtype          := 'DEMOIT';
  v_itemkey           := '1233';
  v_userkey           := '1233';
  v_process           := 'DEMOPROCESS';
  WF_ENGINE.Threshold := -1;
  WF_ENGINE.CREATEPROCESS(v_itemtype, v_itemkey, v_process);
  wf_engine.setitemuserkey(v_itemtype, v_itemkey, v_userkey );
  wf_engine.setitemowner (v_itemtype, v_itemkey,'SYSADMIN');
  WF_ENGINE.STARTPROCESS(v_itemtype, v_itemkey);
  COMMIT;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line(SQLERRM);
END;

TCA

TCA Related information

1. Query to get Customer Information

Query to get Customer Informaton

SELECT DISTINCT hp.party_name "Customer Name",
  hca.account_number,
  hca.status,
  hcsu.location,
  hcsu.site_use_code,
  hcsu.status loc_stat,
  ps.class,
  hcsu.site_use_id,
  hcpc.name profile_name,
  hl.address1,
  hl.address2,
  hl.address3,
  hl.city,
  hl.state,
  hl.postal_code,
  ps.customer_id,
  ps.customer_site_use_id,
  hps.identifying_address_flag,
  ps.trx_date,
  HOU.NAME "Operating Unit"
FROM apps.hz_parties hp,
  apps.hz_party_sites hps,
  apps.hz_locations hl,
  apps.hz_cust_accounts hca,
  apps.hz_cust_acct_sites hcas,
  apps.hz_cust_site_uses hcsu,
  apps.hz_customer_profiles hcp,
  apps.hz_cust_profile_classes hcpc,
  apps.ar_payment_schedules_all ps,
  apps.hr_operating_units hou
WHERE hp.party_id           = hca.party_id(+)
AND hp.party_id             = hcp.party_id
AND hp.party_id             = hps.party_id
AND hps.party_site_id       = hcas.party_site_id
AND hps.location_id         = hl.location_id
AND hca.cust_account_id     = hcas.cust_account_id
AND hcas.cust_acct_site_id  = hcsu.cust_acct_site_id
AND hca.cust_account_id     = hcp.cust_account_id
AND hca.cust_account_id     = ps.customer_id
AND hcp.profile_class_id    = hcpc.profile_class_id
AND ps.customer_site_use_id =hcsu.site_use_id andhcsu.org_id =hou.organization_id;

Oracle Inventory Related Stuff

1. Get Onhand Quantity from table and API

Get Onhand Quantity from table and API

This script can be used to get the below quantities.
1. On-hand Quantity
2. Available to Reserve
3. Quantity Reserved
4. Quantity Suggested
5. Available to Transact
6. Available to Reserve

YOU CAN ALSO GET the on-HAND QUANTITIES from the table MTL_ONHAND_QUANTITIES
Also from GET ON-HAND QUANTITIES API

DECLARE
   x_return_status         VARCHAR2 (50);
   x_msg_count             VARCHAR2 (50);
   x_msg_data              VARCHAR2 (50);
   v_item_id               NUMBER;
   v_org_id                NUMBER;
   v_qoh                   NUMBER;
   v_rqoh                  NUMBER;
   v_atr                   NUMBER;
   v_att                   NUMBER;
   v_qr                    NUMBER;
   v_qs                    NUMBER;
   v_lot_control_code      BOOLEAN;
   v_serial_control_code   BOOLEAN;
BEGIN
   -- Set the variable values
  V_ITEM_ID             := '6566';
  V_ORG_ID              := 61;
  v_qoh                 := NULL;
  v_rqoh                := NULL;
  v_atr                 := NULL;
  v_lot_control_code    := FALSE;
  V_SERIAL_CONTROL_CODE := false;
-- Set the org context
  FND_CLIENT_INFO.SET_ORG_CONTEXT (1);
 
-- Call API
   inv_quantity_tree_pub.query_quantities
   (
   p_api_version_number       => 1.0,
    p_init_msg_lst             => 'F',
    x_return_status            => x_return_status,
    x_msg_count                => x_msg_count,
    x_msg_data                 => x_msg_data,
    p_organization_id          => v_org_id,
    p_inventory_item_id        => v_item_id,
    p_tree_mode                => apps.inv_quantity_tree_pub.g_transaction_mode,
    -- or 3
    p_is_revision_control      => FALSE,
    p_is_lot_control           => v_lot_control_code,
    -- is_lot_control,
    p_is_serial_control        => v_serial_control_code,
    p_revision                 => NULL,        -- p_revision,
    p_lot_number               => NULL,           -- p_lot_number,
    p_lot_expiration_date      => SYSDATE,
    p_subinventory_code        => NULL,    -- p_subinventory_code,
    p_locator_id               => NULL,           -- p_locator_id,
    -- p_cost_group_id            => NULL,       -- cg_id,
    p_onhand_source            => 3,
    x_qoh                      => v_qoh,      -- Quantity on-hand
    x_rqoh                     => v_rqoh,           --reservable quantity on-hand
    x_qr                       => v_qr,
    x_qs                       => v_qs,
    x_att                      => v_att,  -- available to transact
    x_atr                      => v_atr    -- available to reserve
   );

   DBMS_OUTPUT.put_line ('On-Hand Quantity: ' || v_qoh);
   DBMS_OUTPUT.put_line ('Available to reserve: ' || v_atr);
   DBMS_OUTPUT.put_line ('Quantity Reserved: ' || v_qr);
   DBMS_OUTPUT.put_line ('Quantity Suggested: ' || v_qs);
   DBMS_OUTPUT.put_line ('Available to Transact: ' || v_att);
   DBMS_OUTPUT.put_line ('Available to Reserve: ' || v_atr);
EXCEPTION
   WHEN OTHERS
  THEN
      DBMS_OUTPUT.PUT_LINE ('ERROR: ' || SQLERRM);
end;
/
---GET ON-HAND QUANTITIES FROM TABLE

SELECT * FROM MTL_ONHAND_QUANTITIES;

Unix Related

1. UNIX : Loading records in to table using SQL LOADER
2. UNIX: FND_LOAD commands
3. Usage of :$FLEX$ in oracle apps
4. Steps to Move a Concurrent program from one instance(Database) to other
5. Bouncing Apache
6. XML Parsing -reading xml file in oracle pl sql example 

Query to get PO, Invoice, Receipt number

SELECT DISTINCT pha.segment1 po_number ,
  aia.invoice_num invoice_number ,
  rsh.receipt_num receipt_number
FROM po_headers_all pha ,
  po_distributions_all pda ,
  ap_invoice_distributions_all aid ,
  ap_invoices_all aia ,
  rcv_shipment_lines rsl ,
  rcv_shipment_headers rsh
WHERE pha.po_header_id    =pda.po_header_id
AND aid.po_distribution_id=pda.po_distribution_id
AND aia.invoice_id        =aid.invoice_id
AND rsl.po_header_id      =pha.po_header_id
AND rsl.shipment_header_id=rsh.shipment_header_id
AND pha.segment1          =NVL(:P_PO_NUM,pha.segment1)
AND aia.invoice_num       =NVL(:P_INVOICE_NUM,aia.invoice_num)
AND rsh.receipt_num       =NVL(:P_RECEIPT_NUM,rsh.receipt_num) ;

Apache bounce

Bouncing Apache

Perform following steps to bounce Apache:
 

• login to the oracle apps tier with ‘putty’ or any equivalent software.
   
• cd $ADMIN_SCRIPTS_HOME
   
• adapcctl.sh stop
   
• adoacorectl.sh stop
   
• adapcctl.sh start
   
• adoacorectl.sh start

Oracle Discoverer Reports and EUL Table

Everything is stored in EUL tables, i.e. tables under the EUL owner DB scheme (about 50 tables).

Reports are stored in the EUL5_DOCUMENTS table, I suppose (this is not documented so you can only guess)

But it is strongly recommended not to insert or update data in these tables directly using SQL commands!   You should run only SELECT on EUL tables.
 
/* List of Workbooks */

SELECT doc_name "Workbook",
doc_developer_key ,
doc_description "Description"
FROM eul_owner.eul5_documents -- in my case EUL_US.eul5_documents
WHERE eul5_documents.doc_name LIKE '%&enter_workbook_name%'
ORDER BY doc_name;

/*With respect to workbook usage, try this:*

SELECT a.QS_DOC_OWNER USERNAME,
  a.QS_DOC_NAME WORKBOOK ,
  a.QS_DOC_DETAILS WORKSHEET ,
  MAX(a.QS_CREATED_DATE) LAST_USED
FROM eul_owner.EUL5_QPP_STATS a,
  eul_owner.EUL5_DOCUMENTS b
WHERE a.QS_DOC_NAME = b.doc_name
GROUP BY a.QS_DOC_OWNER,
  a.QS_DOC_NAME ,
  a.QS_DOC_DETAILS ;






The Oracle Discoverer 10g Handbook in appendix A discusses many of the EUL tables.

To get the frequency of each report

SELECT *
FROM
(SELECT --a.QS_DOC_OWNER USERNAME,
a.QS_DOC_NAME WORKBOOK ,
--a.QS_DOC_DETAILS WORKSHEET   ,
COUNT(*) Frequency
FROM EUL_OWNER.EUL5_QPP_STATS a,
EUL_US.EUL5_DOCUMENTS b
WHERE a.QS_DOC_NAME = b.doc_name
GROUP BY --a.QS_DOC_OWNER,
a.QS_DOC_NAME
--a.QS_DOC_DETAILS
) a
ORDER BY a.frequency DESC ;

This tells me what workbooks are using specific folders and who owns the workbooks.

/* Formatted on 2008/08/07 17:49 (Formatter Plus v4.8.8) */
SELECT DISTINCT
DECODE (eul5_elem_xrefs.ex_to_type,'ITE', 'Item','JOI', 'Join','FIL', 'Condition',eul5_elem_xrefs.ex_to_type) dependency,
CASE WHEN eul5_documents.doc_created_by = 'OPM_EUL'
THEN 'OPM_EUL'
ELSE apps.gmf_fnd_get_users.fnd_get_users(TO_NUMBER (SUBSTR (eul5_documents.doc_created_by,2,14)))
END user_name,
eul5_documents.doc_created_by workbook_created_by,
eul5_documents.doc_description workbook_description,
eul5_documents.doc_name workbook_name,
eul5_elem_xrefs.ex_to_par_name folder_name,
eul5_documents.doc_content_type
FROM opm_eul.eul5_documents eul5_documents,
opm_eul.eul5_elem_xrefs eul5_elem_xrefs
WHERE (eul5_elem_xrefs.ex_from_id) = TO_CHAR(eul5_documents.doc_id)
AND eul5_documents.doc_content_type = 'application/vnd.oracle-disco.wb';




IF your reports are based ON custom folders (ie. SQL statements enbedded IN the EUL) THEN  there is  no RELIABLE WAY of DETERMINING the VIEWS/tables THAT ARE accessed. ONE of the REASONS I AVOID CUSTOM FOLDERS. YOU CAN TRY THIS sql for simple FOLDERS.
  ;
  SELECT d.doc_name,
    d.doc_developer_key,
    o.obj_name folder_name,
    o.obj_type folder_type,
    o.sobj_ext_table database_object,
    e.exp_name item_name
  FROM EUL_US.eul5_documents d ,
    EUL_US.eul5_elem_xrefs x ,
    EUL_US.eul5_expressions e ,
    EUL_US.eul5_objs o
  WHERE x.ex_from_type = 'DOC'
  AND x.ex_from_id     = d.doc_id
  AND x.ex_to_id       = e.exp_id
  AND e.IT_OBJ_ID      = o.obj_id;