Query For Value Sets In Oracle Apps R12

Query For Value Sets In Oracle Apps R12

 This sql query will assist in obtaining the entire list of value set values.

Value Set Tables in Oracle Apps R12

• FND_FLEX_VALUE_SETS
• FND_FLEX_VALUES
• FND_FLEX_VALUES_TL

Query to get Table Based Value Sets in Oracle Apps R12

1 2 3 4 5 6 7 8 9 10 11 12

select ffvs.flex_value_set_id ,     ffvs.flex_value_set_name ,     ffvs.description set_description ,     ffvs.validation_type,     ffvt.value_column_name ,     ffvt.meaning_column_name ,     ffvt.id_column_name ,     ffvt.application_table_name ,     ffvt.additional_where_clause FROM fnd_flex_value_sets ffvs ,     fnd_flex_validation_tables ffvt WHERE ffvs.flex_value_set_id = ffvt.flex_value_set_id;

Query to get Independent Value Set Details in Oracle Apps R12

Query to get Independent Value Set Details in Oracle Apps R12

Oracle PL/SQL

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SELECT ffvs.flex_value_set_id ,     ffvs.flex_value_set_name ,     ffvs.description set_description ,     ffvs.validation_type,     ffv.flex_value_id ,     ffv.flex_value ,     ffvt.flex_value_meaning ,     ffvt.description value_description FROM fnd_flex_value_sets ffvs ,     fnd_flex_values ffv ,     fnd_flex_values_tl ffvt WHERE     ffvs.flex_value_set_id     = ffv.flex_value_set_id     and ffv.flex_value_id      = ffvt.flex_value_id     AND ffvt.language          = USERENV('LANG');

Query To Get AR Receivable Credit Memo Details In Oracle Apps R12

 

This query is used to get Receivable AR credit memo details in Oracle Apps R12, we create credit memo in AR to reduce the liability for the customer, AR Credit memo will reduce down the outstanding for the customer.

Some important Receivable AR credit memo table in Oracle Apps R12

ORG_ORGANIZATION_DEFINITIONS

AR_CUSTOMERS

HZ_CUST_ACCOUNTS

HZ_PARTIES

RA_CUST_TRX_TYPES_ALL

HZ_CUST_SITE_USES_ALL

RA_CUSTOMER_TRX_ALL

RA_CUSTOMER_TRX_LINES_ALL

RA_CUST_TRX_LINE_GL_DIST_ALL

<div><br class="Apple-interchange-newline">SELECT RCT.TRX_NUMBER "TRX NUMBER", RCT.TRX_DATE "TRX DATE", RCG.GL_DATE, RCT.PURCHASE_ORDER, OOD.ORGANIZATION_NAME, HCSUA.LOCATION, RCL.DESCRIPTION, HCA.ACCOUNT_NUMBER, HP.PARTY_NAME, RTT.NAME TRANSACTION_NAME, DECODE(RTT.TYPE,'CM','Credit Memo',RTT.TYPE) TRANSACTION_TYPE, RCL.LINE_TYPE AR_LINE_TYPE, sum((DECODE(RCT.INVOICE_CURRENCY_CODE,'INR',RCG.AMOUNT*1,RCG.AMOUNT*RCT.EXCHANGE_RATE))) TOTAL_INV_AMOUNT FROM apps.ORG_ORGANIZATION_DEFINITIONS OOD, apps.ar_customers ac, apps.HZ_CUST_ACCOUNTS HCA, apps.HZ_PARTIES HP, apps.RA_CUST_TRX_TYPES_ALL RTT, apps.HZ_CUST_SITE_USES_ALL HCSUA, apps.RA_CUSTOMER_TRX_ALL RCT, apps.RA_CUSTOMER_TRX_LINES_ALL RCL, apps.RA_CUST_TRX_LINE_GL_DIST_ALL RCG WHERE RCT.CUSTOMER_TRX_ID = RCL.CUSTOMER_TRX_ID AND RCL.CUSTOMER_TRX_LINE_ID = RCG.CUSTOMER_TRX_LINE_ID AND RCT.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID and HCA.CUST_ACCOUNT_ID=ac.customer_id AND HCA.PARTY_ID = HP.PARTY_ID AND RTT.TYPE='CM' AND RCT.CUST_TRX_TYPE_ID = RTT.CUST_TRX_TYPE_ID AND TO_NUMBER(RCT.INTERFACE_HEADER_ATTRIBUTE10) = OOD.ORGANIZATION_ID (+) AND RCT.BILL_TO_SITE_USE_ID=HCSUA.SITE_USE_ID GROUP BY RCT.CUSTOMER_TRX_ID,RCT.TRX_NUMBER, RCT.CUST_TRX_TYPE_ID,RCT.TRX_DATE,RCG.GL_DATE, RCT.CUSTOMER_TRX_ID,RCT.PURCHASE_ORDER,OOD.ORGANIZATION_NAME, RCL.DESCRIPTION, HCA.ACCOUNT_NUMBER, HP.PARTY_NAME, HCSUA.LOCATION, RTT.NAME, RTT.TYPE, RCL.LINE_TYPE;</div>

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

SELECT         RCT.TRX_NUMBER "TRX NUMBER", RCT.TRX_DATE "TRX DATE", RCG.GL_DATE, RCT.PURCHASE_ORDER, OOD.ORGANIZATION_NAME, HCSUA.LOCATION, RCL.DESCRIPTION, HCA.ACCOUNT_NUMBER, HP.PARTY_NAME, RTT.NAME TRANSACTION_NAME, DECODE(RTT.TYPE,'CM','Credit Memo',RTT.TYPE) TRANSACTION_TYPE, RCL.LINE_TYPE AR_LINE_TYPE, sum((DECODE(RCT.INVOICE_CURRENCY_CODE,'INR',RCG.AMOUNT*1,RCG.AMOUNT*RCT.EXCHANGE_RATE))) TOTAL_INV_AMOUNT FROM apps.ORG_ORGANIZATION_DEFINITIONS OOD, apps.ar_customers ac, apps.HZ_CUST_ACCOUNTS HCA, apps.HZ_PARTIES HP, apps.RA_CUST_TRX_TYPES_ALL RTT, apps.HZ_CUST_SITE_USES_ALL HCSUA, apps.RA_CUSTOMER_TRX_ALL RCT, apps.RA_CUSTOMER_TRX_LINES_ALL RCL, apps.RA_CUST_TRX_LINE_GL_DIST_ALL RCG WHERE RCT.CUSTOMER_TRX_ID = RCL.CUSTOMER_TRX_ID AND RCL.CUSTOMER_TRX_LINE_ID = RCG.CUSTOMER_TRX_LINE_ID AND RCT.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID and HCA.CUST_ACCOUNT_ID=ac.customer_id AND HCA.PARTY_ID = HP.PARTY_ID AND RTT.TYPE='CM' AND RCT.CUST_TRX_TYPE_ID = RTT.CUST_TRX_TYPE_ID AND TO_NUMBER(RCT.INTERFACE_HEADER_ATTRIBUTE10) = OOD.ORGANIZATION_ID (+) AND RCT.BILL_TO_SITE_USE_ID=HCSUA.SITE_USE_ID GROUP BY RCT.CUSTOMER_TRX_ID,RCT.TRX_NUMBER, RCT.CUST_TRX_TYPE_ID,RCT.TRX_DATE,RCG.GL_DATE, RCT.CUSTOMER_TRX_ID,RCT.PURCHASE_ORDER,OOD.ORGANIZATION_NAME, RCL.DESCRIPTION, HCA.ACCOUNT_NUMBER, HP.PARTY_NAME, HCSUA.LOCATION, RTT.NAME, RTT.TYPE, RCL.LINE_TYPE;

 

 

Query To Get All Assigned Responsibilities Of User In Oracle Apps R12

 SELECT distinct
    fuser.USER_NAME USER_NAME
    , fuser.user_id
    , fuser.creation_date
    , fuser.last_update_date
    , fuser.LAST_LOGON_DATE
    , fuser.START_DATE
    , fuser.END_DATE
    , per.FULL_NAME FULL_NAME
    , per.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
    , frt.RESPONSIBILITY_NAME RESPONSIBILITY
    , TO_CHAR(furg.START_DATE,'DD-MON-YYYY') resp_attched_date
    , TO_CHAR(furg.END_DATE,'DD-MON-YYYY') resp_remove_date
FROM
    FND_USER fuser
    , PER_PEOPLE_F per
    , fnd_user_resp_groups_direct furg
    , FND_RESPONSIBILITY_TL frt
WHERE
    fuser.EMPLOYEE_ID = per.PERSON_ID
    AND fuser.USER_ID = furg.USER_ID
    AND (to_char(fuser.END_DATE) is null
    OR fuser.END_DATE > sysdate)
    AND frt.RESPONSIBILITY_ID = furg.RESPONSIBILITY_ID
    AND frt.LANGUAGE = 'US'
    and fuser.user_name like 'SYSADMIN%'
ORDER BY
    fuser.USER_NAME;

Organization Table In Oracle Apps R12

 Here are tables for organization in oracle apps R12

Multiple organization tables are available in Oracle R12, listed tables are described about their uses in Oracle Apps R12

Organization Table in Oracle Apps R12

HR_ORGANIZATION_UNITS: This table stores information about all the organizations (including inventory organizations) are available.

HR_ALL_ORGANIZATION_UNITS: This table stores definitions that identify business groups and organization units within a single business group

HR_ORGANIZATION_INFORMATION

HR_LOCATIONS

HR_LEGAL_ENTITIES

HR_OPERATING_UNITS: This table stores information about all the operating units are available in the system.

Inventory Organization Table in Oracle Apps R12

ORG_ORGANIZATION_DEFINITIONS: This is a view and depends on MTL_PARAMETERS table

Sub Inventory Table in Oracle Apps R12

MTL_SECONDARY_INVENTORIES: This table stores the sub inventory information.

To import suppliers using Interface table

 How to insert data in PO_VENDORS TABLE.

1. INSERT DATA IN AP_SUPPLIERS_INT ( Mandatory columns: VENDOR_INTERFACE_ID, VENDOR_NAME, STATUS)
The status should be 'NEW'
2. EXECUTE THE IMPORT PROGRAM
Logon to responsibility PAYABLES >> REQUEST >> SUPPLIERS OPEN INTERFACE IMPORT

3. CHECK IN PO_VENDORS TABLE
OR
Logon to responsibility PAYABLES >> SUPPLIERS >> INQUIRY
Search for the supplier name