Query to Find On Hand Quantity in Oracle Apps R12

SELECT  segment1,
  it.inventory_item_id,
  it.organization_id,
  transaction_quantity,
  internal_ORDER_FLAG
FROM mtl_system_items_b it,--MATERIAL TABLE
            mtl_onhand_quantities onhand -- ONHAND QTY TABLE
WHERE it.inventory_item_id  = onhand.inventory_item_id
AND it.organization_id      = onhand.organization_id
AND it.organization_id      = 4
AND IT.INVENTORY_ITEM_ID= 28934;

OUTPUT :
SEGMENT1            ITEM_ID     ORG_ID    TRANSACTION QTY    INTERNAL_ORDER_FLAG
M0800RCSM004 28934            4                     42                                   Y
M0800RCSM004 28934            4                     90                                   Y

Query to Find Pricing Table List in Oracle Apps R12

SELECT    qlhb.list_header_id,qlhb.list_type_code,qlht.name,qlht.description

FROM qp_list_headers_b qlhb,
             qp_list_headers_tl qlht
WHERE qlhb.list_header_id = qlht.list_header_id
 AND qlht.name = 'COMM_CONT';

Query to Find Concurrent Programs Performance Query in Oracle Apps R12

SELECT
      f.request_id ,
      pt.user_concurrent_program_name user_conc_program_name,
      f.actual_start_date start_on,
      f.actual_completion_date end_on,
      floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)
        || ' HOURS ' ||
        floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)
        || ' MINUTES ' ||
        round((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600 -
        (floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)*60) ))
        || ' SECS ' time_difference,
      p.concurrent_program_name concurrent_program_name,
      decode(f.phase_code,' R','Running','C','Complete',f.phase_code) Phase,
      f.status_code
FROM  apps.fnd_concurrent_programs p,
      apps.fnd_concurrent_programs_tl pt,
      apps.fnd_concurrent_requests f
WHERE f.concurrent_program_id = p.concurrent_program_id
       AND f.program_application_id = p.application_id
       AND f.concurrent_program_id = pt.concurrent_program_id
       AND f.program_application_id = pt.application_id
       AND pt.user_concurrent_program_name='Order Import'
ORDER BY
      f.actual_start_date DESC;

How to Set Org Context in 11i and R12

In 11i:

Begin
    dbms_application_info.set_client_info('&ORG_ID');
End;


In R12:

BEGIN
   MO_GLOBAL.SET_POLICY_CONTEXT('S', <orgid>);
END; 

Find the Product Installed on your EBS Environment

SELECT a.application_id,
  a.application_short_name,
  a.application_name,
  b.oracle_id,
  b.last_update_date,
  b.product_version,
  b.patch_level
FROM Fnd_application_vl a,
            Fnd_PRODUCT_INSTALLATIONS b
WHERE a.application_id = b.application_id;

Find List of Responsibilities For a Request Set in Oracle Apps R12

SELECT frt.responsibility_name,
  frg.request_group_name,
  frgu.request_unit_type,
  frgu.request_unit_id,
  fcpt.user_request_set_name,
  fr.responsibility_id,
  fr.application_id
FROM apps.fnd_Responsibility fr,
  apps.fnd_responsibility_tl frt,
  apps.fnd_request_groups frg,
  apps.fnd_request_group_units frgu,
  apps.fnd_request_Sets_tl fcpt
WHERE frt.responsibility_id    = fr.responsibility_id
AND frg.request_group_id       = fr.request_group_id
AND frgu.request_group_id      = frg.request_group_id
AND fcpt.request_set_id        = frgu.request_unit_id
AND fcpt.user_request_set_name = 'Import Item Organization Assignment';

Active Employees in HRMS Oracle R12

SELECT           ppf.first_name,ppf.last_name,ppf.full_name,ppf.employee_number,ppf.person_id
FROM per_all_assignments_f paaf,per_assignment_status_types past, per_all_people_f ppf
WHERE paaf.assignment_status_type_id = past.assignment_status_type_id
AND ppf.person_id=paaf.person_id
AND past.user_status = 'Active Assignment'
AND trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date;

Query to Get Employee Details in Oracle Apps R12

SELECT pad.primary_flag,
       papf.employee_number "Employee Number",
       papf.title "Title",
       papf.first_name "First Name",
       papf.last_name "Last Name",
       TO_CHAR(papf.date_of_birth, 'DD-MON-RRRR') "Birth Date",
       TRUNC(MONTHS_BETWEEN(SYSDATE, papf.date_of_birth) / 12) "Age",
       hrlsex.meaning "Gender",
       ppt.user_person_type "Person Type",
       papf.national_identifier "National Identifier",
       hrlnat.meaning "Nationality",
       hrlms.meaning "Marital Status",
       papf.email_address "E-mail",
       TO_CHAR(papf.effective_start_date, 'DD-MON-RRRR') "Start Date",
       TO_CHAR(papf.effective_end_date, 'DD-MON-RRRR') "End Date",
       TO_CHAR(papf.original_date_of_hire, 'DD-MON-RRRR') "Hire Date",
       pjobs.name "Job",
       ppos.name "Position",
       pgrade.name "Grade",
       haou.name "Organization",
       pbus.name "Business Group",
       hrlat.meaning "Address Type",
       pad.address_line1 || CHR(10) || pad.address_line2 || CHR(10) ||
       pad.address_line3 "Address",
       pad.postal_code "Postal Code",
       ftt.territory_short_name "Country",
       ftt.description "Full Country Name",
       hrleg.meaning "Ethnic Origin"
FROM per_all_people_f papf,
     per_all_assignments_f paaf,
     per_person_types_tl ppt,
     hr_lookups hrlsex,
     hr_lookups hrlnat,
     hr_lookups hrlms,
     hr_lookups hrleg,
     hr_lookups hrlat,
     per_jobs pjobs,
     per_all_positions ppos,
     per_addresses pad,
     per_grades_tl pgrade,
     per_business_groups pbus,
     hr_all_organization_units haou,
     fnd_territories_tl ftt
WHERE 1 = 1
  AND hrlat.lookup_code(+) = pad.address_type
  AND hrlat.lookup_type(+) = 'ADDRESS_TYPE'
  AND hrlsex.lookup_code(+) = papf.sex
  AND hrlsex.lookup_type(+) = 'SEX'
  AND hrlnat.lookup_code(+) = papf.nationality
  AND hrlnat.lookup_type(+) = 'NATIONALITY'
  AND hrlms.lookup_code(+) = papf.marital_status
  AND hrlms.lookup_type(+) = 'MAR_STATUS'
  AND hrleg.lookup_code(+) = papf.per_information1
  AND hrleg.lookup_type(+) = 'US_ETHNIC_GROUP'
  AND ftt.territory_code(+) = pad.country
  AND pad.business_group_id(+) = papf.business_group_id
  AND pad.date_to IS NULL
  AND pad.person_id(+) = papf.person_id
  AND pgrade.grade_id(+) = paaf.grade_id
  AND haou.organization_id(+) = paaf.organization_id
  AND haou.business_group_id(+) = paaf.business_group_id
  AND pbus.business_group_id(+) = paaf.business_group_id
  AND ppos.position_id(+) = paaf.position_id
  AND pjobs.job_id(+) = paaf.job_id
  AND ppt.person_type_id(+) = papf.person_type_id
  AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date and
      paaf.effective_end_date
  AND paaf.person_id = papf.person_id
  AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND
      papf.effective_end_date;

QUERY TO GET BUYER(Employees) NAME

SELECT per.full_name "Buyer Name",
       pa.START_DATE_ACTIVE "Begin Date",
       pa.END_DATE_ACTIVE "End Date",
       per.employee_number,
       per.email_address,
       per.global_name,
       per.local_name
FROM   po_agents pa,
              per_all_people_f per
WHERE 1=1
AND pa.AGENT_ID = per.person_id
AND PER.person_id='1294';

Sales Order Type Name in Oracle Apps R12

SELECT  distinct ooha.order_type_id, ott.NAME   "Order Type Name"
FROM    oe_transaction_types_tl ott, 
              oe_order_headers_all ooha
 WHERE  ooha.order_type_id = ott.transaction_type_id  ;

OUTPUT :
1162 Delivery - On Account APO
1166 MM APO Charitable Donation
1170 MM APO Trade Shows
1241 MM US Samples - Apollo
1261 MM US Samples - Langley
1143 Will Call - Cash
1384 E-commerce Parcel
1101 Internal Order MT
1168 MM APO Return
1092 Will Call - On Account APO

Programmatically Close PO using PO_ACTIONS API

DECLARE
   x_action         CONSTANT VARCHAR2 (20)  := 'FINALLY CLOSE';
   -- Change this parameter as per the requirement
   x_calling_mode   CONSTANT VARCHAR2 (2)   := 'PO';
   x_conc_flag      CONSTANT VARCHAR2 (1)   := 'N';
   x_return_code_h           VARCHAR2 (100);
   x_auto_close     CONSTANT VARCHAR2 (1)   := 'N';
   x_origin_doc_id           NUMBER;
   x_returned                BOOLEAN        := NULL;

BEGIN
 
   apps.mo_global.set_policy_context ('S', 204);
   fnd_global.apps_initialize (1015932, 50578, 201);
   DBMS_OUTPUT.put_line
             ('Calling PO_Actions.close_po for Closing/Finally Closing PO =>');
   x_returned :=
      po_actions.close_po (p_docid              => 131583,
                           p_doctyp             => 'PO',
                           p_docsubtyp          => 'STANDARD',
                           p_lineid             => NULL,
                           p_shipid             => NULL,
                           p_action             => x_action,
                           p_reason             => NULL,
                           p_calling_mode       => x_calling_mode,
                           p_conc_flag          => x_conc_flag,
                           p_return_code        => x_return_code_h,
                           p_auto_close         => x_auto_close,
                           p_action_date        => SYSDATE,
                           p_origin_doc_id      => NULL
                          );
   COMMIT;

   IF x_returned = TRUE
   THEN
      DBMS_OUTPUT.put_line
                   ('Purchase Order which just got Closed to Finally Closed. ');
      DBMS_OUTPUT.put_line (x_return_code_h);

   ELSE
      DBMS_OUTPUT.put_line
                      ('API Failed to Close/Finally Close the Purchase Order');
   END IF;

Query for Supplier Details with Bank in Oracle Apps R12

SELECT       
                aps.vendor_name "vendor_name",
                ass.vendor_site_code "Payee_Identifier"
                , accts.ext_bank_account_id "Payee_Bank_Account_Identifier"
                , bank.party_name "Bank_Name"
                , branch.bank_branch_name "Branch_Name"
                , accts.country_code "Account_Country_Code"
                , accts.bank_account_name "Account_Name"
                , accts.bank_account_num "Account_Number"
                , accts.currency_code "Account_Currency_Code"
                , NULL "Allow_International_Payments"
                , uses.start_date "Account_start_date"
                , uses.end_date "Account_end_date"
                , accts.iban "IBAN"
                ,accts.check_digits "check_digits"
                , accts.bank_account_name_alt "Account_Alternate_Name"
                , accts.bank_account_type "Bank_account_type"
                , accts.account_suffix "Account_Suffix"
                , accts. description "DESCRIPTION"
                , accts.agency_location_code "Agency_Location_Code"
                , accts.exchange_rate_agreement_num   "Exchange_Rate_Agreement_Number"
                , accts.exchange_rate_agreement_type "Exchange_Rate_Agreement_Type"
                , accts.exchange_rate "EXCHANGE_RATE"
                , accts.secondary_account_reference "SECONDARY_ACCOUNT_REFERENCE"
           FROM  ap_suppliers aps
                ,ap_supplier_sites_all ass
                ,iby_pmt_instr_uses_all uses
                ,iby_external_payees_all payee
                ,iby_ext_bank_accounts accts
                ,hz_parties bank
                ,ce_bank_branches_v branch
          WHERE uses.instrument_type   = 'BANKACCOUNT'
            AND aps.vendor_id          = ass.vendor_id
            AND aps.party_id           = payee.payee_party_id
            AND payee.ext_payee_id     = uses.ext_pmt_party_id
            AND payee.payment_function = 'PAYABLES_DISB'
            AND uses.instrument_id     = accts.ext_bank_account_id
            AND accts.bank_id          = bank.party_id
            --AND accts.bank_id          = bankprofile.party_id
            AND accts.branch_id         = branch.branch_party_id
            AND aps.end_date_active  IS NULL
            AND ass.inactive_date    IS NULL;

P2P and O2C PROCESS TABLES in Oracle Apps R12

PROCURE TO PAY :

ORDER TO CASH :

List Of Tables Based On Module And Schema In Oracle Apps R-12

Tables related to order management module
1.Blanket tables
BLANKET	ONT.OE_BLANKET_HEADERS_ALL
BLANKET	ONT.OE_BLANKET_HEADERS_EXT
BLANKET	ONT.OE_BLANKET_LINES_ALL
BLANKET	ONT.OE_BLANKET_LINES_EXT
2.BOM(Bill of Materials) tables
BOM	BOM.BOM_COMPONENTS_B
BOM	BOM.BOM_DEPARTMENTS
BOM	BOM.BOM_OPERATIONAL_ROUTINGS
BOM	BOM.BOM_OPERATION_RESOURCES
BOM	BOM.BOM_OPERATION_SEQUENCES
BOM	BOM.BOM_RESOURCES
BOM	BOM.BOM_STANDARD_OPERATIONS
BOM	BOM.BOM_STRUCTURES_B
CUSTROUT	BOM.BOM_CALENDARS
CUSTROUT	BOM.BOM_CALENDAR_DATES
CUSTROUT	BOM.BOM_CALENDAR_EXCEPTIONS
CUSTROUT	BOM.BOM_CALENDAR_SHIFTS
CUSTROUT	BOM.BOM_CAL_WEEK_START_DATES
CUSTROUT	BOM.BOM_CAL_YEAR_START_DATES
CUSTROUT	BOM.BOM_EXCEPTION_SETS
CUSTROUT	BOM.BOM_EXCEPTION_SET_DATES
CUSTROUT	BOM.BOM_PERIOD_START_DATES
CUSTROUT	BOM.BOM_SHIFT_DATES
CUSTROUT	BOM.BOM_SHIFT_EXCEPTIONS
CUSTROUT	BOM.BOM_SHIFT_TIMES
CUSTROUT	BOM.BOM_WORKDAY_PATTERNS
CUSTROUT	WSH.WSH_CALENDAR_ASSIGNMENTS
Sales order base tables
ORDER	ONT.OE_ORDER_HEADERS_ALL
ORDER	ONT.OE_ORDER_LINES_ALL
Tables related Delivery
ORDER	WSH.WSH_DELIVERY_ASSIGNMENTS
ORDER	WSH.WSH_DELIVERY_DETAILS
ORDER	WSH.WSH_DELIVERY_LEGS
ORDER	WSH.WSH_NEW_DELIVERIES
ORDER	last_sale_info
Tables related to contract module
CONTRACT	OKC.OKC_K_GROUPS_B
CONTRACT	OKC.OKC_K_GROUPS_TL
CONTRACT	OKC.OKC_K_GRPINGS
CONTRACT	OKC.OKC_K_HEADERS_ALL_B
CONTRACT	OKC.OKC_K_ITEMS
CONTRACT	OKC.OKC_LINE_STYLES
CONTRACT	OKC.OKC_LINE_STYLES_TL
CONTRACT	OKS.OKS_BILL_CONT_LINES
CONTRACT	OKS.OKS_BILL_TRANSACTIONS
CONTRACT	OKS.OKS_BILL_TXN_LINES
CONTRACT	OKS.OKS_K_HEADERS_B
CONTRACT	OKS.OKS_K_LINES_B
CONTRACT	OKS.OKS_K_LINES_TL
CONTRACT	OKS.OKS_SUBSCR_HEADER_B
CONTRACT	OKS.OKS_SUBSCR_HEADER_TL
Tables related to AR module (TCA)
CUSTOMER	APPLSYS.FND_DOCUMENTS_SHORT_TEXT
CUSTOMER	APPS.FND_DOCUMENTS_VL
CUSTOMER	AR.AR_CONS_BILL_CYCLES_B
CUSTOMER	AR.AR_CONS_BILL_CYCLES_TL
CUSTOMER	AR.AR_STATEMENT_CYCLES
CUSTOMER	AR.HZ_CONTACT_POINTS
CUSTOMER	AR.HZ_CUSTOMER_PROFILES
CUSTOMER	AR.HZ_CUST_ACCOUNTS
CUSTOMER	AR.HZ_CUST_ACCOUNT_ROLES
CUSTOMER	AR.HZ_CUST_ACCT_SITES_ALL
CUSTOMER	AR.HZ_CUST_PROFILE_AMTS
CUSTOMER	AR.HZ_CUST_SITE_USES_ALL
CUSTOMER	AR.HZ_LOCATIONS
CUSTOMER	AR.HZ_ORG_CONTACTS
CUSTOMER	AR.HZ_PARTIES
CUSTOMER	AR.HZ_PARTY_SITES
CUSTOMER	AR.HZ_RELATIONSHIPS
CUSTOMER	AR.HZ_RELATIONSHIP_TYPES
CUSTOMER	AR.HZ_ROLE_RESPONSIBILITY
CUSTOMER	AR.RA_RULES
CUSTOMER	AR.RA_TERMS_B
CUSTOMER	AR.RA_TERMS_TL
CUSTOMER	AR.RA_TERRITORIES
CUSTOMER	GL.GL_LEDGERS
Tables related to organizations :
CUSTOMER	HR.HR_ALL_ORGANIZATION_UNITS
CUSTOMER	HR.HR_ALL_ORGANIZATION_UNITS_TL
CUSTOMER	HR.HR_ORGANIZATION_INFORMATION
Tables related to  CRM:
CUSTOMER	JTF.JTF_RS_RESOURCE_EXTNS
CUSTOMER	JTF.JTF_RS_RESOURCE_EXTNS_TL
CUSTOMER	JTF.JTF_RS_SALESREPS
Tables related to  iSTORE:
CUSTOMER	istore_Accounts
CUSTOMER	istore_Enrollments
CUSTOMER	istore_Roles
FND Tables:
FND	APPLSYS.FND_APPLICATION
FND	APPLSYS.FND_APPLICATION_TL
FND	APPLSYS.FND_LOOKUP_VALUES
FND	APPLSYS.FND_TERRITORIES
FND	APPLSYS.FND_TERRITORIES_TL
FND	APPLSYS.FND_TIMEZONES_B
FND	APPLSYS.FND_TIMEZONES_TL
ITEM	APPLSYS.FND_DOCUMENTS_SHORT_TEXT
ITEM	APPS.FND_DOCUMENTS_VL
Tables related to Item :
ITEM	BOM.CST_ITEM_COSTS
ITEM	GL.GL_CODE_COMBINATIONS
ITEM	HR.HR_LOCATIONS_ALL
ITEM	HR.HR_LOCATIONS_ALL_TL
ITEM	HR.PER_ALL_PEOPLE_F
ITEM	INV.MTL_ATP_RULES
ITEM	INV.MTL_CATEGORIES_B
ITEM	INV.MTL_CATEGORIES_TL
ITEM	INV.MTL_CATEGORY_SETS_B
ITEM	INV.MTL_CATEGORY_SETS_TL
ITEM	INV.MTL_COMMODITY_CODES
ITEM	INV.MTL_CUSTOMER_ITEMS
ITEM	INV.MTL_CUSTOMER_ITEM_XREFS
ITEM	INV.MTL_ITEM_CATEGORIES
ITEM	INV.MTL_ITEM_LOCATIONS
ITEM	INV.MTL_ONHAND_QUANTITIES_DETAIL
ITEM	INV.MTL_PARAMETERS
ITEM	INV.MTL_SYSTEM_ITEMS_B
ITEM	INV.MTL_SYSTEM_ITEMS_TL
ITEM	INV.MTL_UNITS_OF_MEASURE_TL
ITEM	APPLSYS.FND_DOCUMENTS_SHORT_TEXT
ITEM	APPS.FND_DOCUMENTS_VL
ITEM	MRP.MRP_ASSIGNMENT_SETS
ITEM	MRP.MRP_SOURCING_RULES
ITEM	MRP.MRP_SR_ASSIGNMENTS
ITEM	MRP.MRP_SR_RECEIPT_ORG
ITEM	MRP.MRP_SR_SOURCE_ORG
ITEM	PO.PO_HAZARD_CLASSES_B
ITEM	PO.PO_HAZARD_CLASSES_TL
ITEM	PO.PO_UN_NUMBERS_B
ITEM	PO.PO_UN_NUMBERS_TL
Oracle Installed Base Tables:
OAT	CSI.CSI_IEA_VALUES
OAT	CSI.CSI_II_RELATIONSHIPS
OAT	CSI.CSI_INSTANCE_STATUSES
OAT	CSI.CSI_IPA_RELATION_TYPES
OAT	CSI.CSI_IP_ACCOUNTS
OAT	CSI.CSI_ITEM_INSTANCES
OAT	CSI.CSI_ITEM_INSTANCES_H
OAT	CSI.CSI_I_ASSETS
OAT	CSI.CSI_I_ORG_ASSIGNMENTS
OAT	CSI.CSI_I_PARTIES
OAT	CSI.CSI_I_VERSION_LABELS
OAT	CSI.CSI_I_VERSION_LABELS_H
OAT	CSI.CSI_T_PARTY_ACCOUNTS
OAT	CSI.CSI_T_PARTY_DETAILS
OAT	CSI.CSI_T_TXN_LINE_DETAILS
Tables related to PO(Purchase order):
PO	HR.PER_ALL_PEOPLE_F
PO	PO.PO_AGENTS
PO	PO.PO_DISTRIBUTIONS_ALL
PO	PO.PO_HEADERS_ALL
PO	PO.PO_LINES_ALL
PO	PO.PO_LINE_LOCATIONS_ALL
PO	PO.PO_LINE_TYPES_B
PO	PO.PO_REQUISITION_HEADERS_ALL
PO	PO.PO_REQUISITION_LINES_ALL
PO	PO.PO_REQ_DISTRIBUTIONS_ALL
PO	PO.RCV_SHIPMENT_HEADERS
PO	PO.RCV_SHIPMENT_LINES
PO	PO.RCV_TRANSACTIONS
Tables related to Price list:
PRICE	QP.QP_LIST_HEADERS_B
PRICE	QP.QP_LIST_HEADERS_TL
PRICE	QP.QP_LIST_LINES
PRICE	QP.QP_PRICING_ATTRIBUTES
PRICE	QP.QP_QUALIFIERS
Tables related to AP:
VENDOR	AP.AP_SUPPLIERS
VENDOR	AP.AP_SUPPLIER_SITES_ALL
VENDOR	AR.HZ_PARTY_USG_ASSIGNMENTS
VENDOR	GL.GL_CODE_COMBINATIONS
VENDOR	IBY.IBY_ACCOUNT_OWNERS
VENDOR	IBY.IBY_EXTERNAL_PAYEES_ALL
VENDOR	IBY.IBY_EXT_BANK_ACCOUNTS