AR Cash Receipt Query

SELECT ACR.ATTRIBUTE1 CHECK_ID                              ,
    ACR.ATTRIBUTE2 CHECK_NUMBER                                ,
    ACR.ATTRIBUTE6 RECEIPT_DATE                                ,
    RCT.TRX_NUMBER AR_INV_NUMBER                                      ,
    ARM.NAME PAYMENT_METHOD                                           ,
    TO_CHAR(ACR.REVERSAL_DATE,'DD-MON-YYYY') AR_RECEIPT_REVERSAL_DATE ,
    AL.MEANING RECEIPT_STATUS                                         ,
    HOU.NAME OPERATING_UNIT_NAME                                      ,
    ACR.RECEIPT_NUMBER AR_RECEIPT_NUMBER                              ,
    TO_CHAR(ACR.RECEIPT_DATE,'DD-MON-YYYY') AR_RECEIPT_DATE           ,
    ACR.CURRENCY_CODE ENTERED_CURRENCY_CODE                           ,
    ACR.AMOUNT*SIGN(ARA.ACCTD_AMOUNT_APPLIED_FROM) ENTERED_AMOUNT     ,
    GSOB.CURRENCY_CODE ACCOUNTED_CURRENCY_CODE                        ,
    ARA.ACCTD_AMOUNT_APPLIED_FROM ACCOUNTED_AMOUNT                    ,
    GCCK.CONCATENATED_SEGMENTS CASH_GL_ACCOUNT                        ,
    GCCK.SEGMENT1 RECEIPT_LE_ME                                       ,
    ABA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUMBER                          ,
    ABB.BANK_NAME BANK_NAME                                           ,
    ACR.ORG_ID                                                        ,
    ACR.STATUS                                                        ,
    ACR.CASH_RECEIPT_ID                                               ,
    ACR.REMITTANCE_BANK_ACCOUNT_ID
    --   ,ARA.APPLIED_CUSTOMER_TRX_ID
    --   ,ARA.CODE_COMBINATION_ID
    ,
    ARM.RECEIPT_METHOD_ID ,
    ABA.BANK_ACCOUNT_ID   ,
    ABB.BANK_BRANCH_ID
     FROM APPS.RA_CUSTOMER_TRX_ALL RCT     ,
    APPS.AR_RECEIPT_METHODS ARM            ,
    APPS.AR_CASH_RECEIPTS_ALL ACR          ,
    APPS.AR_RECEIVABLE_APPLICATIONS_ALL ARA,
    APPS.GL_SETS_OF_BOOKS GSOB             ,
    APPS.GL_CODE_COMBINATIONS_KFV GCCK     ,
    APPS.AP_BANK_ACCOUNTS_ALL ABA          ,
    APPS.AP_BANK_BRANCHES ABB              ,
    APPS.HR_OPERATING_UNITS HOU            ,
    APPS.AR_LOOKUPS AL                     ,
    APPS.AR_RECEIPT_METHOD_ACCOUNTS_ALL ARMA
    WHERE acr.cash_receipt_id        = ara.cash_receipt_id
  AND acr.receipt_method_id          = arm.receipt_method_id
  AND ara.applied_customer_trx_id    = rct.customer_trx_id(+)
  AND acr.set_of_books_id            = gsob.set_of_books_id
  AND arma.cash_ccid                 = gcck.code_combination_id
  AND arm.receipt_method_id          = arma.receipt_method_id
  AND aba.bank_account_id            = arma.bank_account_id
  AND acr.remittance_bank_account_id = aba.bank_account_id
  AND aba.bank_branch_id             = abb.bank_branch_id
  AND acr.org_id                     = hou.organization_id
  AND al.lookup_type                 = 'CHECK_STATUS'
  AND acr.status                     = al.lookup_code

What is TCA (Trading Community Architecture)?

Oracle Trading Community Architecture (TCA) is a data model that allows you to manage complex information about the parties, or customers, who belong to your commercial community, including organizations, locations, and the network of hierarchical relationships among them. This information is maintained in the TCA Registry, which is the single source of trading community information for Oracle E-Business Suite applications.

What is the difference between ORG_ID & ORGANIZATION_ID?

ORG_ID:

Ø  Unique ID for the Operating Unit.

Ø  Come under operating unit in Multiorg.

Ø  Used to distinguish the data of different business organizations.

ORGANIZATION_ID:

Ø  ID for the Inventory Organization which is under an Operating Unit.

Ø  Come under inventory organization.

Ø  Used to identify the items belongs to which inventory organization it has assigned.

GL_QUERY

select  jh.je_source,
jh.je_batch_id "Batch Number",
jb.name "Batch Name",
jb.creation_date "Batch Date",
jb.description "Batch Comment",
jl.je_line_num "Line Number",
gcc.segment2 "Account Number",
gcc.segment1 "Balancing Segment",
gcc.segment3 "Cost Center",
fds.type "Doc Type",
substr(jb.default_period_name, instr(jb.default_period_name,'-')+1,length(jb.default_period_name)) "Fiscal Year",
jb.posted_date "Posting Date",
jb.default_effective_date "Effective Date",
jb.default_period_name "Effective Period",
jl.accounted_dr,
jl.accounted_cr,
jh.currency_code "Currency Indicator",
jh.currency_conversion_rate ,
jh.description "Header Description",
jl.description "Line Description",
jh.accrual_rev_status "Reverse Indicator",
jh.accrual_rev_effective_date "Reverse Date",
jh.accrual_rev_period_name,
jh.created_by,
jb.creation_date,
jh.last_update_date
from gl_je_headers jh,
gl_je_batches jb,
gl_je_lines jl,
gl_code_combinations gcc,
fnd_document_sequences fds
where jh.je_batch_id = jb.je_batch_id
and jh.je_header_id = jl.je_header_id
and jl.code_combination_id = gcc.code_combination_id
and fds.doc_sequence_id =jh.doc_sequence_id
AND to_date(TO_CHAR(jb.creation_date,'dd-mon-rrrr') ,'dd-mon-rrrr') BETWEEN to_date('01-Jan-2012','dd-mon-rrrr') AND to_date('31-Dec-2012','dd-mon-rrrr');
--and jb.name ='E14 Purchasing 18290241: A 161476'
--and jl.je_line_num =1;

What are Key and Descriptive Flexfield?

Key Flex Field: KFF is a Mandatory field which is already driving the functionality and it is the field where at least first segment is made mandatory.

·         KFF will be filled in segments

·         KFF is mandatory

·         KFF Drives functionality

·         Key flex field is nothing but a collection of segments. It stores data in code combinations.

·         As its name, it is used to capture ‘Key’ information of an organization which is mandatory like company, department, account etc.

·         It displays like simple text item

·         KFFs are flexible enough to let any organization use the code style they want, without programming.

·         Example of KFF is Revenue Account value on customer set up. Customer -> Bill To -> Account tab -> Revenue field

Navigation is Inventory (Navigator)->Setup->FlexField-Key->Segments then you can search for application and the flex field.

Examples: in Oracle Inventory

System Items, Stock Locators, Item Categories, Item Catalogs etc

Descriptive Flex Field: Descriptive flex field is the data which can be entered into the each of the oracle forms which can take extra information, and this option is there for almost all forms in oracle. you find the option of dff in every form that you work

·         DFF: Is not a mandatory field

·         DFF Information will go into attributes of the table

·         DFF does not drive any functionality but you can drive by writing a custom code on the dff fields.

·         DFF is used optionally to store any additional business information.

·         It displays like [] on screen

·         Programming may be required in DFFs

·         Example: DFF created in Alerts

Navigation is Inventory (Navigator)->Setup->FlexField-Descriptive->Segments then you can search for application and the flex field

Examples: in Oracle Inventory

System Items, subinventories, stock locators any other form

DFF	KFF
Capture Additional info	Unique Info, Mandatory
Captured in attribute prefixed columns	Segment prefixed
Not reported on standard reports	Is reported on standard reports
To provide expansion space on your form With the help of []. [] Represents descriptive Flex field. FLEX FILED : DESCRIPTIVE : REGIGSTER	Used for entering and displaying key information For example Oracle General uses a key Flex field called Accounting Flex field to uniquely identifies a general account. FLEX FILED : KEY : REGIGSTER

KFF & DFF share common features. They are multi segment fields. Application validates individual segments based on make value set rules & ensure that the segments make sense using CVR(cross validation rules).

Oracle Applications KEY FLEX FIELDS

1)     GL :- ACCOUNTING

2)     AR :- SALES TAX LOCATION, TERRITORY,

3)     AP :- BANK DETAILS, COST ALLOCATION, PEOPLE GROUP

Oracle Applications DESCRIPTIVE FLEX FIELDS (Partial)

1)     GL :-  daily rates

2)     AR :- credit history, information

3)     PA :- bank branch, payment terms, site address,  

What is Flexfields?

Flex Fields are used to capture the additional business information. As name indicates, flex field is nothing but which gives the flexibility. A flex field is a flexible data field that your organization can customize to your business needs without programming. Oracle Applications uses two types of flexfields, those are given below.

Key flexfields (KFF)

Descriptive flexfields (DFF)

What is Current record attribute property?

Specifies the number of the current record in the block's list of records. Applies to Block

Which trigger related to mouse?

When-Mouse-Click

When-Mouse-DoubleClick

When-Mouse-Down

When-Mouse-Enter

When-Mouse-Leave

When-Mouse-Move

When-Mouse-Up

Difference between VAT and Property Class?

A.    Named visual attributes define only font, color, and pattern attributes; property classes can contain these and any other properties.

B.    You can change the appearance of objects at runtime by changing the named visual attribute programmatically; property class assignment cannot be changed programmatically.

C.    When an object is inheriting from both a property class and a named visual attribute, the named visual attribute settings take precedence, and any visual attribute properties in the class are ignored.

Can a Property class itself be based on a property class?

Yes

What are property classes? Can property classes have trigger?

Property class inheritance is a powerful feature that allows you to quickly define objects that conform to your own interface and functionality standards. Property classes also allow you to make global changes to applications quickly.  By simply changing the definition of a property class, you can change the definition of all objects that inherit properties from that class.

Yes. All type of triggers.

How do I recover the responsibilities in apps if it is disable?

Navigate to

Ø  System administrator responsibilities

Ø  Security

Ø  Responsibility

Ø  Define

Ø  Query on responsibilities name

Ø  Check the effective date to field

OR by using the fnd_responsibility_tl & fnd_responsibility

Select * from fnd_responsibility_tl where responsibility_name =’xxcss sms3 it admin’;

Select * from fnd_responsibility where responsibility_id =51162;

Responsibilities Table?

FND_RESPONSIBILITY: FND_RESPONSIBILITY stores information about responsibilities. Each row includes the name and description of the responsibility, the application it belongs to, and values that identify the main menu, and the first form that it uses.

Can a responsibility be assigned to multiple users?

Yes

How many responsibilities can be defined for a Module?

Unlimited

Can u change VAT at run time?

Yes. You can programmatically change an object's named visual attribute setting to change the font, color, and pattern of the object at runtime.

What is a Visual attributes (VAT)?

Visual attributes are the font, color, and pattern properties that you set for form and menu objects that appear in your application's interface.

What is OLE Activation style property?

 Specifies the event that will activate the OLE containing item.

Can you have VBX and OCX controls in forms?

Yes     

Can you change the mouse pointer?

Yes. Specifies the mouse cursor style.  Use this property to dynamically change the shape of the cursor.

Which trigger are created when master -detail relation?

·      NON-ISOLATED (default)

a) on check delete master

b) on clear details

c) on populate details

·      ISOLATED

a) on clear details

b) on populate details

·      CASCADE

a) pre-delete

b) on clear details

c) on populate details

Query to findout the Responsibilites name from Request Set

SELECT frt.responsibility_name,
  frg.request_group_name,
  frgu.request_unit_type,
  frgu.request_unit_id,
  fcpt.user_request_set_name
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 frt.language = USERENV('LANG')
  --and fcpt.language = USERENV('LANG')
AND fcpt.user_request_set_name LIKE '%GEPS GL US Payroll Interface Request Set%'
ORDER BY 1,2,3,4;

Query to findout the Responsibilites name from CP

SELECT frt.responsibility_name,
  frg.request_group_name,
  frgu.request_unit_type,
  frgu.request_unit_id,
  fcpt.user_concurrent_program_name
FROM apps.fnd_Responsibility fr,
  apps.fnd_responsibility_tl frt,
  apps.fnd_request_groups frg,
  apps.fnd_request_group_units frgu,
  apps.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 LIKE '%GEPS AR Contact Inbound Interface%'
ORDER BY 1,2,3,4;

What are the types of layouts in reports?

There are 8 default layout style provided.

A.    Tabular - It is the most basic type of report where each column corresponds to a column selected from the database.

B.    Form-like - It is a report one record per page. The field value is placed to the field labels.

C.    Mailing Label - A mailing label report prints mailing labels in multiple column on each page. The printing can be a landscape or a portrait.

D.   Form Letter - A Form letter report contains database values embedded into any text that is entered or imported into the report editor.

E.    Group Left - A group left report divides the rows of a table into sets, based on a common value in one of the columns. This style is used to restrict a column from repeating the same value several times while values of related columns change.

F.    Group Above - A group above report contains two or more groups of data. For every value of the master group, the related values of the detail group(s) are fetched from the database.

G.   Matrix - A matrix (cross tab) report contain one row of tables, one column of labels and information in a grid format that is related to the row and column labels. A distinguished feature of matrix report is that the number of columns is not known until the data is fetched from the database. To create a matrix report, at least four groups are required. One group must be a cross-product group, two of the groups must be within the cross-product group to furnish the labels, and at least one group must provide the information to fill the cells. The groups can provide to a single query or to multiple queries.

H.    Matrix with Group: A matrix with group report is a group above report with a separate matrix for each value of the master group.

Master-Detail Relation in Oracle Form

Isolated: Masters can be deleted when Child is existing.

Non- Isolated: Masters cannot be deleted when Child is existing.

Cascading: Child Record Automatically Deleted when Masters is deleted.

On-Check-Delete-Master:

Fires when Form Builder attempts to delete a record in a block that is a master block in a master-detail relation.

On-Clear-Details:

Fires when Form Builder needs to clear records in a block that is a detail block in a master-detail relation because those records no longer correspond to the current record in the master block.

On-Populate-Details:

Fires when Form Builder needs to fetch records into a block that is the detail block in a master-detail relation so that detail records are synchronized with the current record in the master block.

Properties	Triggers	Trigger Level	Procedure
Isolated	On Populate details       On Clear Details	Block                    Form	Check Package Failure Clear all master Detail Query Master Detail
Non- Isolated	On Populate details         On Check Delete master    On Clear Details	Block                 Block                            Form	Check Package Failure Clear all master Detail Query Master Detail
Cascading	On Populate details         Pre Delete On Clear Details	Block                 Block                            Form	Check Package Failure Clear all master Detail Query Master Detail

Match Approval Level

Two–Way: Purchase order and invoice quantities must match within tolerance before the corresponding invoice can be paid.

Three–Way: Purchase order, receipt, and invoice quantities must match within tolerance before the corresponding invoice can be paid.

Four–Way: Purchase order, receipt, accepted, and invoice quantities must match within tolerance before the corresponding invoice can be paid.

Types of Purchase Orders

There are 4 types of Purchase Orders:

1.     Standard PO: A Standard PO is created for one–time purchase of various items

2.     Planned PO: A Planned PO is a long–term agreement committing to buy items or services from a single source. You must specify tentative delivery schedules and all details for goods or services that you want to buy, including charge account, quantities, and estimated cost.

3.     Blanket agreement: A Blanket PO is created when you know the detail of the goods or services you plan to buy from a specific supplier in a period, but you do not know the detail of your delivery schedules.

4.     Contract agreement: Contract purchase agreements are created with your suppliers to agree on specific terms and conditions without indicating the goods and services that you will be purchasing