Query to get Responsibility Name for given Concurrent Program Name in Oracle APPS

SELECT frt.responsibility_name, frg.request_group_name,
    frgu.request_unit_type,frgu.request_unit_id,
    fcpt.user_concurrent_program_name
    FROM fnd_Responsibility fr, fnd_responsibility_tl frt,
    fnd_request_groups frg, fnd_request_group_units frgu,
    fnd_concurrent_programs_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.concurrent_program_id = frgu.request_unit_id
    AND frt.LANGUAGE = USERENV('LANG')
    AND fcpt.LANGUAGE = USERENV('LANG')
    AND fcpt.user_concurrent_program_name = :conc_prg_name
    ORDER BY 1,2,3,4

Query to get Concurrent Program details for given Value Set Name in Oracle APPS

select fcpl.user_concurrent_program_name
      , fcp.concurrent_program_name
      , fav.application_short_name
      , fav.application_name
      , ffvs.flex_value_set_name
      , flv.meaning default_type
 from   fnd_concurrent_programs fcp
      , fnd_concurrent_programs_tl fcpl
      , fnd_descr_flex_col_usage_vl fdfcuv
      , fnd_flex_value_sets ffvs
      , fnd_lookup_values flv
      , fnd_application_vl fav
 where  fcp.concurrent_program_id = fcpl.concurrent_program_id
 and    fcpl.language = 'US'
 and    fav.application_id=fcp.application_id
 and    fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
 and    ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
 and    flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
 and    flv.lookup_code(+) = fdfcuv.default_type
 and    flv.language(+) = userenv ('LANG')
 AND ffvs.flex_value_set_name = :value_set_name

OKC_K_HEADERS_ALL_B.STS_CODE in OLFM

OKC_K_HEADERS_ALL_B.STS_CODE = OKC_STATUSES_B.CODE

SELECT b.code, tl.meaning, b.ste_code
  FROM okc_statuses_b b
      ,okc_statuses_tl tl
 WHERE b.code = tl.code

Asset Return Statuses(OKL_ASSET_RETURN_STATUS) in OLFM

SELECT LOOKUP_TYPE, LOOKUP_CODE, MEANING, DESCRIPTION
  FROM fnd_lookup_values
 WHERE lookup_type = 'OKL_ASSET_RETURN_STATUS'
   AND language = USERENV('LANG')

Termination Quote Reasons/Repurchase Quote Reasons(OKL_QUOTE_REASON) in Oracle Lease and Finance Management(OLFM)

SELECT LOOKUP_TYPE, LOOKUP_CODE, MEANING, DESCRIPTION 

  FROM fnd_lookup_values

 WHERE lookup_type = 'OKL_QUOTE_REASON'

   AND language = USERENV('LANG')

AR_PAYMENT_SCHEDULES_ALL.CLASS in Oracle APPS

• The AR_PAYMENT_SCHEDULES_ALL table stores all transactions except adjustments and miscellaneous cash receipts. 

• Oracle Receivables updates this table when activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, or receipt. 

• Oracle Receivables groups different transactions by entering one of these values in the CLASS column: 
• INV for Invoice
• DM for Debit Memo
• CM for Credit Memo
• CB for Chargeback
• PMT for receipt and 
• BR for Bills Receivable

• Transaction classes determine if a transaction relates to RA_CUSTOMER_TRX_ALL table or AR_CASH_RECEIPTS_ALL table. 

• Using the CUSTOMER_TRX_ID foreign key column, the AR_PAYMENT_SCHEDULES_ALL table joins to RA_CUSTOMER_TRX_ALL table for nonpayment transaction entries, such as the creation of Credit Memos, Debit Memos, Invoices, or Chargebacks. 

• Using the CASH_RECEIPT_ID foreign key column, the AR_PAYMENT_SCHEDULES_ALL table joins to the AR_CASH_RECEIPTS_ALL table for invoice-related payment transactions.

• When a receipt is applied, Receivables updates the AMOUNT_APPLIED, AMOUNT_DUE_REMAINING, and STATUS columns. 

• The STATUS column changes from OP for open to CL for closed for any transaction that has an AMOUNT_DUE_REMAINING value of 0. 

• For a receipt, the amount due that remains includes On Account and Unapplied amounts. 

• Receivables stores debit items such as invoices, debit memos, chargebacks, deposits, and guarantees as positive numbers in the AMOUNT_DUE_REMAINING and AMOUNT_DUE_ORIGINAL columns. 

• Credit items such as credit memos and receipts are stored as negative numbers. 

• Receipts are confirmed or not confirmed as designated by the CONFIRMED_FLAG column. 

• The sum of the AMOUNT_DUE_REMAINING column for a customer for all confirmed payment schedules reflects the current customer balance. 

• If this amount is negative, then the AMOUNT_DUE_REMAINING column indicates the credit balance amount currently available for this customer. 

• For invoices with split terms, Oracle Receivables creates one record in the RA_CUSTOMER_TRX_ALL table and one record in the AR_PAYMENT_SCHEDULES_ALL table for each installment. 

• In the AR_PAYMENT_SCHEDULES_ALL table, the DUE_DATE and AMOUNT_DUE_REMAINING columns can differ for each installment of a split term invoice. Each installment is differentiated by the TERMS_SEQUENCE_NUMBER column. 

• If you create a debit memo reversal when you reverse a receipt, Receivables creates a new payment schedule record for the debit memo and populates the REVERSED_CASH_RECEIPT_ID column with the CASH_RECEIPT_ID column for the reversed receipt. 

• Receivables creates a new payment schedule record when you create a chargeback. The ASSOCIATED_CASH_RECEIPT_ID column is the cash receipt of the payment you entered when you created the chargeback. 

• The ACTUAL_DATE_CLOSED column gives the date you applied a payment or credit to an open transaction that set the AMOUNT_DUE_REMAINING column to 0 for that transaction. 

• The GL_DATE_CLOSED column indicates the accounting date the transaction was closed.

AR_CASH_RECEIPTS_ALL.STATUS in Oracle APPS

• The AR_CASH_RECEIPTS_ALL table stores one record for each receipt that you enter.

• Oracle Receivables concurrently creates records in the AR_CASH_RECEIPT_HISTORY_ALL, AR_PAYMENT_SCHEDULES_ALL, and AR_RECEIVABLE_APPLICATIONS_ALL tables for invoice-related receipts. 

• For receipts that are not related to invoices, such as miscellaneous receipts, Receivables creates records in the AR_MISC_CASH_DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ALL table. 

• Receivables associates a status with each receipt. These statuses include: 
• APP for applied
• UNAPP for unapplied
• UNID for unidentified
• NSF for nonsufficient funds
• REV for reversed receipt and 
• STOP for stop payment

• Receivables does not update the status of a receipt from UNAPP to APP until the entire amount of the receipt is either applied or placed on account. 

• A receipt can have a status of APP even if the entire receipt amount is placed on account.

AR_RECEIVABLE_APPLICATIONS_ALL.STATUS in Oracle APPS

• The AR_RECEIVABLE_APPLICATIONS_ALL table stores all accounting entries for both Cash and Credit Memo Applications. 

• The APPLICATION_TYPE column stores either CASH or CM (for credit memo applications). 

• Each row in this table includes the amount applied, status, and accounting flexfield information. 

• Status Column have below values: 
• APP for applied
• UNAPP for unapplied
• ACC for on-account
• UNID for unidentified
• ACTIVITY for receivable activity, and 
• OTHER ACC for other receipt application.