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;