Query to get Details of Discoverer Report by sql query

SELECT DISTINCT disco_docs.doc_name "Discoverer Workbook",
                TRUNC (disco_docs.doc_created_date) "Workbook Create Date",
                CASE
                   WHEN INSTR
                          (disco_docs.doc_created_by,
                           '#'
                          ) = 0
                      THEN disco_docs.doc_created_by
                   WHEN INSTR (disco_docs.doc_created_by, '#') > 0
                   AND INSTR (disco_docs.doc_created_by, '#', 2) = 0
                      THEN (SELECT fu.user_name
                              FROM fnd_user fu
                             WHERE fu.user_id =
                                      SUBSTR (disco_docs.doc_created_by, 2, 5))
                   ELSE NULL
                END "Workbook Owner/Creator",
                disco_users.eu_username,
                CASE
                   WHEN INSTR
                          (disco_users.eu_username,
                           '#'
                          ) = 0
                      THEN disco_users.eu_username
                   WHEN INSTR (disco_users.eu_username, '#') > 0
                   AND INSTR (disco_users.eu_username, '#', 2) = 0
                      THEN (SELECT fu.user_name
                              FROM fnd_user fu
                             WHERE fu.user_id =
                                        SUBSTR (disco_users.eu_username, 2, 5))
                   ELSE (SELECT resp.responsibility_name
                           FROM fnd_responsibility_tl resp
                          WHERE resp.responsibility_id =
                                        SUBSTR (disco_users.eu_username, 2, 5))
                END AS "Shared Name / Responsibility"
           FROM eul4_us.eul4_documents disco_docs,
                eul4_us.eul4_access_privs disco_shares,
                eul4_us.eul4_eul_users disco_users
          WHERE disco_docs.doc_id = disco_shares.gd_doc_id
            AND disco_users.eu_username(+) NOT IN ('EUL5', 'PUBLIC')
            AND disco_users.eu_id(+) = disco_shares.ap_eu_id
            --AND disco_docs.doc_name LIKE '%Account%';

Query to find KEY FLEX FIELDS (KFF)

select  FIF.APPLICATION_ID  ,
        FIF.ID_FLEX_CODE    ,
        FIF.ID_FLEX_NAME    ,
        FIF.APPLICATION_TABLE_NAME ,
        FIF.DESCRIPTION     ,
        FIFS.ID_FLEX_NUM    ,
        FIFS.ID_FLEX_STRUCTURE_CODE  ,
        FIFSE.SEGMENT_NAME,
        FIFSE.SEGMENT_NUM,
        FIFSE.FLEX_VALUE_SET_ID
from    FND_ID_FLEXS FIF    ,
        FND_ID_FLEX_STRUCTURES FIFS ,
        FND_ID_FLEX_SEGMENTS FIFSE
where   FIF.APPLICATION_ID = FIFS.APPLICATION_ID
and     FIF.ID_FLEX_CODE   = FIFS.ID_FLEX_CODE
and     FIFSE.APPLICATION_ID = FIF.APPLICATION_ID
and     FIFSE.ID_FLEX_CODE = FIF.ID_FLEX_CODE
and     FIFSE.ID_FLEX_NUM = FIFS.ID_FLEX_NUM
and     FIF.ID_FLEX_CODE LIKE 'GL#'
and     FIF.ID_FLEX_NAME LIKE 'Accounting Flexfield';

query to find users who have a responsibility

PROMPT Find users who have a responsibility
select usr.user_id, usr.user_name, res.RESPONSIBILITY_ID, res.RESPONSIBILITY_NAME
from apps.FND_USER usr, apps.FND_RESPONSIBILITY_TL res, apps.FND_USER_RESP_GROUPS grp
where upper(res.RESPONSIBILITY_NAME) like upper('%' || NVL('&EnterRespName', 'INV')|| '%')
and upper(res.RESPONSIBILITY_NAME) NOT like '%AX%'
and upper(res.RESPONSIBILITY_NAME) NOT like '%OPM%'
and grp.responsibility_id = res.responsibility_id
and grp.user_id = usr.user_id;

Define Statement Cycles in Receivables

Define Statement Cycles


Define statement cycles to determine when to send statements to your customers. You enter statement cycles when you define or modify individual customer and site profile classes in the Customer Profile Classes window.


Navigation:
Receivables Manager Resp==>Setup>Print>Statement Cycles

Resolving Stuck IN PROCESS of Purchase Order and Purchase Order Requisition

First the WF_ITEM_KEY, WF_ITEM_TYPE  of the IN PROCESS status PO need to be identified. Following sql statement can be used to get the details based on the PO No (segment1) and ORG_ID;
SELECT         wf_item_key, wf_item_type, authorization_status,
org_id, last_update_date, segment1 po_no
FROM             po_headers_all
WHERE          authorization_status = ‘IN PROCESS’
AND                org_id = 337
AND                segment1 in (‘po_no’,’po_no2′)
ORDER BY    last_update_date desc
.
Similarly  WF_ITEM_KEY, WF_ITEM_TYPE  of the IN PROCESS status PO REQ can be  identified. Using the following sql statement for the PO REQ No (segment1) and ORG_ID;
–To select all the inProcess for PO REQ
SELECT         wf_item_type , wf_item_key, authorization_status,
org_id , last_update_date, segment1 poreq_no
FROM             po_requisition_headers_all
WHERE         authorization_status = ‘IN PROCESS’
AND               org_id = 247
AND               segment1 in (‘po_req_no’,’po_req_no2′)
ORDER BY   last_update_date DESC
.
After getting the WF_ITEM_KEY and WF_ITEM_TYPE , you need to run the following process in the APPS SESSION LOGIN to remove stuck in process status;
For PO
begin
—  wf_engine.startprocess( ‘WF_ITEM_TYPE’ , ‘WF_ITEM_KEY’ );
wf_engine.startprocess( ‘POAPPRV’  ,  ‘123001-244471’ );
end;
For PO REQ
begin
—  wf_engine.startprocess( ‘WF_ITEM_TYPE’ , ‘WF_ITEM_KEY’ );
wf_engine.startprocess(‘REQAPPRV’  ,  ‘123001-244471’ );
end;
 

How to change PO Default Rate Type or PO System Parameters in oracle apps R12

Purchasing Super User => SETUP==>Organizations==>Purchasing Options ==> Document Default

SELECT default_rate_type FROM po_system_parameters;

Use the Default Options region to define the defaults you can later use to speed up data entry and enforce system wide requirements in many Oracle Purchasing forms.
*These options must be defined separately for each Operating Unit.
*By assigning a value at the responsibility level when that responsibility has been tied to an operating unit,it is possible to have different values for different operating units.
Requisition Import Group-By:
Use the ReqImport process to import requisitions from other Oracle or non-Oracle systems. Sources for requisitions can include Work In Process, Master Scheduling/MRP, and Inventory, as well as custom systems. Requisition Import creates a requisition line and a requisition distribution for each row it finds in the interface table. It then groups these lines on requisitions according to parameters defined below.
All: group all requisition lines on one requisition
Buyer: group lines for each buyer name on a separate requisition.
Category: group lines for each purchasing category on a separate requisition.
Item: group lines for each item on a separate requisition
Location: group lines for each location on a separate requisition
Vendor: group lines for each vendor on a separate requisition.
Rate Type:
Select the currency Rate Type that defaults on requisitions, purchase
orders, RFQs, and quotations. Use conversion rate types to automatically
assign a rate when you convert foreign currency journal amounts to functional
currency equivalents. You enter daily conversion rates for specific
combinations of foreign currency, date, and conversion rate type. If the
Rate Type is User, you can override this default for each document line. If
either your functional currency (defined in your set of books) or your
transaction currency (the currency you enter in a purchasing document window)
is Euro (the European Monetary Unit currency), and the other is another
European currency, Purchasing defaults in the appropriate conversion Rate Type,
Rate, and Rate Date.
Spot: An exchange rate which you enter to perform conversion based on the
rate on a specific date. It applies to the immediate delivery of a currency.
Corporate: An exchange rate you define to standardize rates for your
company. This rate is generally a standard market rate determined by
senior financial management for use throughout the organization.
User: An exchange rate you specify when you enter a foreign currency
journal entry.
EMU Fixed: An exchange rate General Ledger provides automatically when
you enter journals (after the EMU effective starting date) using a foreign
currency that has a fixed relationship with the Euro.
Minimum Release Amount:
This is the minimum release amount which defaults on blanket, contract, and
planned purchase orders. This amount is in your functional currency.
Taxable:
Check the Taxable box to set the default taxable status to Yes.
This default is for new items and for purchase order shipments without an item number. You can override the taxable status for each item or shipment.
The taxable status is printed on purchase orders
Price Break Type:
The type selected here will default on blanket and planned purchase orders.
Cumulative: price breaks apply to the cumulative quantity on all released shipments for the item
Non-cumulative: price breaks apply to quantities on individual released shipments for the item.
Price Type:
The type selected here will default on purchase orders. Use the
Lookup Codes window to define price types.
These are:
Cost Plus Fee
Cost Plus Percentage
Fixed
Indexed
Variable
Quotation Warning Delay (Days):
This option sets the number of days of warning before a quotation expires. When the limit is reached, you receive the following message in the Alert Notifications window: "Quotations active or approaching expiration".
RFQ Required:
Clicking this box causes an RFQ to be required before an item can be autocreated onto a P.O.
Receipt Close % Tolerance:
Purchasing automatically closes a shipment for receiving if it is within the receiving close tolerance at the receiving close point. It can be overridden for specific items and orders. If you are not going to receive the goods then on the purchase order set the receipt close tolerance to 100%. When it is set to 100% then when the PO is approved the line will be closed for receiving. Then when you match the invoice to the PO the status will change to Closed.
Invoice Close % Tolerance:
Purchasing automatically closes a shipment for invoicing if it is within the vendor invoice close tolerance at time of invoice matching. It can be overridden for specific items and orders.
CAUTION: If you set this to 100%, the PO will automatically close for
invoicing upon approval.
Line Type:
This is the default Line Type for requisition, RFQ, quotation, and purchase order lines. When you create any of these documents, the line type is part of your item information. You can override the line type for each document line.
Invoice Matching:
Select one of the following default Invoice Match options:
Two-Way - Purchase Order and invoice quantities must match within
tolerance. Often used for services where no receiver is
generated.
Three-Way - Purchase Order, receipt and invoice quantities must match
within tolerance. Must enter receiver.
Four-Way - Purchase Order, receipt, inspection and invoice quantities
must match within tolerance. Must enter receiver and
inspection quantities

Query for to get Tax Details In oracle apps R12

SELECT hou.organization_id            org_id,

       led.name                       ledger,

       hou.name                       operating_unit,

       --

       zxr.tax_regime_code            tax_regime_code,

       zxr.tax                        tax_code,

       zxr.tax_status_code            tax_status_code,

       zxr.tax_rate_code              tax_rate_code,

       zxr.tax_jurisdiction_code      tax_jurisdiction_code,

       --

       zxr.rate_type_code             rate_type_code,

       zxr.percentage_rate            percentage_rate,

       zxr.effective_from             rate_effective_from,

       zxr.effective_to               rate_effective_to,

       --

       acc.tax_account_ccid           tax_account_ccid,

       gcc.concatenated_segments      tax_account

  FROM

       zx_rates_vl                  zxr,

       zx_accounts                  acc,

       hr_operating_units           hou,

       gl_ledgers                   led,

       xxrl_gl_code_combinations_v  gcc

 WHERE

       1=1

   --

   -- AND zxr.tax_regime_code = 'UK VAT'

   -- AND zxr.tax_rate_code = 'UK_AR_DOM'

   --

   AND acc.tax_account_entity_code = 'RATES'

   AND zxr.active_flag = 'Y'

   AND TRUNC (SYSDATE) BETWEEN

          TRUNC (zxr.effective_from) AND

          NVL (TRUNC (zxr.effective_to), TRUNC (SYSDATE) + 1)

   --

   AND led.ledger_id = hou.set_of_books_id

   AND gcc.code_combination_id = acc.tax_account_ccid

   AND hou.organization_id = acc.internal_organization_id

   AND acc.tax_account_entity_id = zxr.tax_rate_id

   --

   ;