Introduction to HRMS

HRMS suite of Oracle applications is useful in managing the employees of an enterprise. Some of the major featues are capturing employee information, day to day employee management, time entry, performance capture and appraisals, capturing compensation information and managing payroll.

Important modules within HRMS are
• HR (Person, Assignment, work structures)
• Payroll (setting up compensation for employees, running payroll, paying employees).
• Oracle Time and Labor (Time entry and linking it to payroll, projects)
• Self Service HR (self service responsibilities to carry out various manager and employee tasks)
• i-Recruitment (managing recruitment).
• Oracle Advance Benefits (OAB)- managing additonal benefits (pensions, insurances) offered to employees.
We will cover introduction of core HR and Payroll module.


Core HR
Some of the important forms in core HR are Person, Assignment, Organization, Jobs, Position.

Business Group (BG):
The Business Group is one of the most important concepts in HRMS. It is the highest level of organization in HRMS. Business Group can be created using oraganization window of an HRMS manager responsibility. The organization classification has to be Business Group. Once the business group data is saved, we need to navigate to Business Group Information (first entry in menu after clicking Others button in organization form) and define various fields. The entry into this Business Group Information flexfield defines setups which will be active in various forms in any HRMS responsibility.

Whenever a business group is defined, a security profile gets created for it with the same name as the business group. If we want to assign any responsibility to business group, we need to define profile HR:Security profile (System Administrator->Profile->System) as the given business group for that responsibility.
The other important profile associated with many HRMS responsibilities is HR: User Type. It also needs to be define before using a responsibility.
One of the important concepts in HRMS is date-tracking. Many of the HRMS tables are date-tracked and usually have _f as suffix in their table_name. Date-tracking is allowing tables/forms to store information on changes made on various date. Suppose a record gets created on 01-Jan-09 and there is a change in record on 01-Feb-09 and the new record should only be active from 01-Feb-09, we make a date-track update on 01-Feb-09. In this case, the current record starting from 01-Jan-09 gets end dated on 31-Jan-09 and a new record gets created on 01-Feb-09 with the new values.

Person Form:
Person form captures the personal information of an employee. Important points to note are
• The employee number field is either updatable or automatically generated based of the Employee number generation field value of the Business Group Information flexfield of business group.
• The Person unique identifier (NI Number for UK, Social Security Number for US) is based on country defined at Business Group Information.
• Person form contains common fields for all the countries like Last Name, First Name, Sex, Date of Birth and also country specific fields based on country defined at business group. The country specific fields can be found in descriptive flexfield Further Person Information. Each of the country (implemented in oracle applications) has a context defined in it and segments gives the information on additional fields defined. These fields are stored from PER_INFORMATION1 to PER_INFORMATION30 (based on segment definition of Further Person Information DFF) of table per_all_people_f.
• There is a descriptive flexfield (Additional Person Information) defined for capturing additonal person information for a customer. This DFF is located at base of person form at the right corner. This information is stored in columns ATTRIBUTE1 to ATTRIBUTE30 of per_all_people_f.
• The primary key for per_all_people_f is combination of effective_start_date, effective_end_date and person_id as it is a date track table.
• person_id is foriegn key for tables like per_addresses, per_all_assignments_f.

Assignment Form:
Assignment in HRMS is a type of job a person carries. A person can have multiple assignments. For example: In a medical university+hospital organization, a professer can have two assignments as a professor as well as a doctor. However at a given point of time, one and only one assignment has to be primary. Assignment form captures information like Job, Organization, Position, Location and so on. There are country specific information which are also captured on various tabs available in assignment form. This fields are defined from Key flexfield Soft Coding Key Flexfield. Each of the implemented country has a context in it. Assignment form also has an DFF Additional Assignment Information defined for capturing customer specific assignment data.
Assignment information is stored in per_all_assignments_f which has a primary key combination of assignment_id, effective_start_date and effective_end_date. Assignment_id is foreign key to tables like pay_element_entries_f (element entry form).

Other forms:
Few other important forms in HR are organization, job, position, grades, location and so on. These forms define these entitites which are captured on person and assignment form.

PAYROLL:
Payroll modules consists of setting and running up the payroll processes like payroll run, pre-payments, payment method programs like Cheque Writer, Electronic Fund Transfer (EFT- NACHA for US, BACS for UK), Costing, Retro Pay, Advanced Pay and so on. All these processes requires Payroll to setup.

Payroll:
Payroll is a top level entity in payroll module which is run as a part of above payroll processes. Payroll definition can be created at HRMS Manager --> Payroll --> Description. The important details entered are period type (monthly, weekly), payment method (cash, EFT, Cheque). Before setting up the payroll, the payment methods can be defined at HRMS Manager --> Payroll --> Payment Methods.
Table for payroll definition information is pay_all_payrolls_f.
Payroll has different forms to setup data for payroll processes. The most important process in payroll is payroll run.

Payroll Run:
It calculates the all the payment details of the employee in terms of amount he receives and amount gets deducted from his salary. The most important setup to do before running a payroll run is to setup payroll elements and assign these elements (element entries) to employee's assignment. (see the next topic for information on elements).
Once the assignments has been assigned appropriate elements as element entries, payroll run can be initiated by either running the concurrent program Payroll Run or running a quick pay from employee's assingment (assignment--> others--> quick pay).

Elements and Element Entries:
Element are basically the pay components for the person (or assignment). It denotes earning components like Regular Earnings, Bonus, different allowances and deduction components like some monthly deduction against meals. An element can be created for each of the above component. Element can be created through HRMS Manager Responsibillity --> Total Compensation: Basic --> Element Description. Elements can be of various type with most important being earnings and deductions.
Once an element has been created, it has to be made available to the assignments through element links. Element Links are kind of eligilbility crieteria determining which all assignments are eligible to have the element. Element Link can be created through HRMS Manager Responsibillity --> Total Compensation: Basic --> Link. The element can be linked as various levels such as organization, location, payroll etc. So all the assignments coming under that organization/location/payroll can have those elements as element entries. The term element entries denotes the element added to assignments through assignment form --> element entries.
An element can have one or more input values. Input values are different values which these elements can carry.

Tables related to elements are
Elements --> pay_element_types_f
Element input values --> pay_input_values_f
Element entries --> pay_element_entries_f
Element entry value (values entered for elements attached to assignment) --> pay_element_entry_values_f
Element links --> pay_element_links_f

Fast Formula:
Fast formula are ways of doing calculations in HRMS. Fast formula can be attached to various entities in hrms like elements, absences. So whenever HRMS processes these entities, fast formula gets executed. A fast formula has one or more return values. These return values can feed to one or more elements. If it feeds to same element, it is direct feed or else it is indirect feed. Please note that an element can feed input values of elements which have lower priority (please note that priority of 1 means highest and 2 means lower than 1).
Table: ff_formulas_f
Fast formula can interact with the database functions/procedures through formula functions. Formula functions are way to call the database functions and procedures in the fast formula.

Navigation:
Fast Formula: HRMS Manager Responsibillity --> Total Compensation: Basic --> Write Formula
Formula Functions:HRMS Manager Responsibillity --> Other Definition --> Formula Functions.
Fast Formula can be attached to element through formula result screen
HRMS Manager Responsibillity --> Total Compensation: Basic --> Formula Results.
Basic Setups for few payroll processes: (These are common setups and some legislations might have additional setup)
Payroll Run:
• Attach the payroll at assignment.
• There are various legislative setups for each localization, ensure the setups are done.
• Attach elements at assignment --> Elements and give input values for them.
Pre-payments:
• Attach the payment method to the assignment through assignment --> payment method. If a payment method is not coming here, then that payment method may not be in valid payment methods listed in payroll definition
NACHA/BACS/EFT/Check Writer:
• Ensure proper payment method (like check payment method for check writer) are attached to assingment.

Brief Overview of payroll run:
When a payroll run is initiated, the run proccess all the elements attached to the assignments. If the fast formulae are assigned to these elements, the run will process these fast formulae and feed their results into the elements collecting the results. These elements will also be processed for any further fast formulae attached. Whenever an element is processed in a payroll run, run outputs these elements as run results. Payroll balances are summations of these run results over a period of time. Balance Type can be defined at HRMS Manager Responsibillity --> Total Compensation: Basic --> Balance. The feed tab on balance form indicates which element run results add or substract from balance type and dimension shows for which time the balance type can be summed up. A defined balance gives the value of balance type over a period of time (dimension). For example: Consider a balance type Regular Earnings. The feed for that balance type can be element Regular Earnings. The dimensions attached to balance type can be period to date and year to date. So defined balances available will be Regular Earnings Period to Date(REGULAR_EARNINGS_PTD) and Regular Earnings Year to Date(REGULAR_EARNINGS_YTD).
The results of payroll run and other processes can be viewed from HRMS Manager Responsibillity --> View --> Payroll Process Results. This screen has navigations for viewing run results and defined balance values for each of the processed assignment of payroll run.

Tables Involved:
Payroll process information: pay_payroll_actions
For each assignment processed in payroll processes, an entry is made into pay_assignment_actions.
Payroll run results are stored into pay_run_results
The values of these run results are stored in pay_run_result_values.
Balance Type Definition: pay_balance_types
Balance Dimension Information: pay_balance_dimensions
Defined balance (definition and NOT values): pay_defined_balances.
The defined balance values are calculated either at run time from run or through run balances or latest balances tables.

Get links to tables in new modules--Application Diagonistics

1. Log on to Application Diagonistics
2. Select the application
3. Select the responsibility
4. Give any transaction value and find the links to all tables in that module(for example for payables module enter invoice id).This responsibility is generally used to check if some setup is missing by functional users.Lets make use to find links as well.

OAF Personalisation: Add region in set of pages

Create a region oracle/apps///xxpage.xml and an associated controller if needed


Jdeveloper :

Create a stack layout region without the AM definition.
Create a default single column region
Create required items under this region

Personalisation :

Click on create item icon on the page which you need to personalise
Create a Flexible Layout item style
Create a Flexible Content item style and set extends property to the region created (oracle/apps///xxpage.xml)

Check Printing Oracle Payables

Check Printing: Tags to be used

Group:OutboundPayment	<?for-each:OutboundPayment?>
SortByCheckNumber	<?sort:PaymentNumber/CheckNumber?>
Variablesetr	<?xdoxslt:set_variable($_XDOCTX, 'V_GROSS_AMT', PaymentAmount/Value)?>
Variablesetr2	<?xdoxslt:set_variable($_XDOCTX, 'V_DIS_AMT_TOT', DiscountTaken/Amount/Value)?>
Variablesetr3	<?xdoxslt:set_variable($_XDOCTX, 'V_PAID_AMT_TOT',(xdoxslt:get_variable($_XDOCTX,'V_GROSS_AMT')-xdoxslt:get_variable($_XDOCTX,'V_DIS_AMT_TOT')) )?>
BankName	<?BankName?>
AlternateBankAccountName	<?AlternateBankAccountName?>
CheckNumber	<?CheckNumber?>
PaymentDate	<?format-date:PaymentDate;'DD-MON-YYYY'?>
PaymentAmt	*****<?format-number:PaymentAmount/Value;'999G999D99'?>*****
Check Amount in words	*****<?xdofx:upper(PaymentAmountText)?>*****
Choose	<?choose:?>
When Void by Overflow	<?when:PaymentStatus/Code='VOID_BY_OVERFLOW'?>
End when	<?end when?>
Otherwise	<?otherwise:?>
If	<?if@inlines:sum(./PaymentAmount/Value) < 5000?>
eiif	<?end if?>
End otherwise	<?end otherwise?>
End Choose	<?end choose?>
Payee Name	<?Payee/Name ?>
Address Line 1	<?Payee/Address/AddressLine1?>
C	<?if:AddressLine2!=NULL?>
Address Line 2	<?Payee/Address/AddressLine2?>
EC	<?end if?>
C	<?if:AddressLine3!=NULL?>
Address Line 3	<?Payee/Address/AddressLine3?>
EC	<?end if?>
C	<?if:concat(Payee/Address/City,',',Payee/Address/State,' ',Payee/Address/PostalCode)!=', '?>
City, State Zip	<?value-of:concat(Payee/Address/City,',',Payee/Address/State,' ',Payee/Address/PostalCode)?>
EC	<?end if?>
Country	<?Payee/Address/CountryName?>
CheckNumber	<?xdofx:lpad(CheckNumber,8,'0' )?>
BranchNumber	<?BranchNumber?>
BankAccountNumber	<?BankAccountNumber?>
LinesPerPageVariable	<xsl:variable name="lpp" select="number(10)"/>
LinesTreeVariable	<xsl:variable xdofo:ctx="incontext" name="invLines" select=".//DocumentPayable"/>
FE invLines	<?for-each:$invLines?> <?if:(position()-1) mod $lpp=0?> <xsl:variable name="start" xdofo:ctx="incontext" select="position()"/>
CDATE	<?format-date:xdoxslt:get_variable($_XDOCTX,'V_PAY_DATE');' DD-MON-YYYY'?>
Vendor Number	<?xdoxslt:get_variable($_XDOCTX,'V_SUP_NO')?>
ChecklNumber	<?xdoxslt:get_variable($_XDOCTX,'V_CHK_NO')?>
FE	<?for-each:$invLines?><?if:position()>=$start and position()<$start+$lpp?>
Invoice Number	<?DocumentNumber/ReferenceNumber?>
Invoice Date	<?format-date:DocumentDate;'DD-MON-YYYY'?>
TotalDocumentAmount	<?format-number:TotalDocumentAmount/Value;'999G999D99'?>
Discount Amt	<?format-number:DiscountTaken/Amount/Value;'999G999D99'?>
0.00	<?xdofx:TotalDocumentAmount/Value-DiscountTaken/Amount/Value?>
EFE	<?end if?><?end for-each?><?end if?><?end for-each?>
CheckNumber	<?xdoxslt:get_variable($_XDOCTX,'V_CHK_NO')?>
Payment Date	<?format-date:xdoxslt:get_variable($_XDOCTX,'V_PAY_DATE');' DD-MON-YYYY'?>
if	<?if:count($invLines)<$start+$lpp?>
0.00	<?xdoxslt:get_variable($_XDOCTX,'V_GROSS_AMT')?>
Enif	<?end if?>
If	<?if:count($invLines)<$start+$lpp?>
0.00	<?xdoxslt:get_variable($_XDOCTX,'V_DIS_AMT_TOT')?>
Enif	<?end if?>
if	<?if:count($invLines)<$start+$lpp?>
0.00	<?xdoxslt:get_variable($_XDOCTX,'V_PAID_AMT_TOT')?>
Enif	<?end if?>
End Group: OutboundPayments	<?End for-each?>

For-each:xdoxslt:foreach_number($_XDOCTX,1,3,1) à This will loop 3 times

Printing labels in Avery template

How to create label using Avery template? What is need?

A scenario where we have to print the employee visiting cards in which user needs 3x3 = 9(9 emp inf labels in one page) 4x20 = 80 (80 emp inf labels in one page).For the data need to be repeated across the sheet use the below logic.

Here it is use the following xml

<ADDRESSES>
<ADDRESS>
<COMPANY>1 Oracle Street</COMPANY>
<NAME>Redwood Shores</NAME>
<ADD1>CA</ADD1>
<ADD2>94065</ADD2>
</ADDRESS>
<ADDRESS>
<COMPANY>2 Oracle Street</COMPANY>
<NAME>Redwood Shores</NAME>
<ADD1>CA</ADD1>
<ADD2>94065</ADD2>
</ADDRESS>
<ADDRESS>
...
...
</ADDRESSES>

Use the following code in Template.

For 3 Columns Avery template design in RTF for 3x3 = 9 Fields

In the first shape add this

<?for-each@shape:/Root/Row[(position() mod 3) = 1]?><?Code?><?shape-offset-y:(position()-1)*72?><?end-for-each?>

In the second column shape add this
<?for-each@shape:/Root/Row[(position() mod 3) = 2]?><?Code?><?shape-offset-y:(position()-1)*72?><?end-for-each?>

In the third column shape add this
<?for-each@shape:/Root/Row[(position() mod 3) = 0]?><?Code?><?shape-offset-y:(position()-1)*72?><?end-for-each?>

For 4Columns Avery template design in RTF for 4x20 = 80 Fields

In the first shape add this

<?for-each@shape:/main/DATA_RECORD[(position() mod 4) = 1]?><?Code?><?shape-offset-y:(position()-1)*36?><?end-for-each?>

In the second column shape add this
<?for-each@shape:/main/DATA_RECORD[(position() mod 4) = 2]?><?Code?><?shape-offset-y:(position()-1)*36?><?end-for-each?>

In the third column shape add this
<?for-each@shape:/main/DATA_RECORD[(position() mod 4) = 3]?><?Code?><?shape-offset-y:(position()-1)*36?><?end-for-each?>

In the fourth column shape add this

<?for-each@shape:/main/DATA_RECORD[(position() mod 4) = 0]?><?Code?><?shape-offset-y:(position()-1)*36?><?end-for-each?>

Demo repeating frame for the data to repeat in avery labels

ForLoopOutside

___________________________________

ForLoopInside

Company

Name

Add1

Add2
InsideEnd

___________________________________
OutsideEnd

Tags defined for 4x20 = 80 Repeatation of data in an avery label.

ForLoopOutside = <?for-each:DATA_RECORD[position() mod 4=1]?>

ForLoopInside = mailto:?for-each@column:.%20%20following-sibling::DATA_RECORD[position()%3c4]?

Company =<??>

Name =<??>

Add1 =<??>

Add2 =<??>

InsideEnd = <?end for-each?>

OutsideEnd= <?end for?><?split-by-page-break:?>

Template for Avery tables to print

How to find the backend SQL query of the JSP and reports

How To Generate Trace Files in in HTML/JSP (using Profile Option)
Note: This requires proper responsibility to set SQL Initialization statement using Profile option. Step 1. Login to the desired Form application.
Step 2. Select +Profile >> System ('Find System Profile Values' screen will pop up)
Step 3. Check 'User' and Type in the Username (in which the account for that user will be trace) Step 4. Type 'Initialization%' (Initialization SQL Statement - Custom)in the Profile box and Hit 'Find' (Click here for preview.)
Step 5. In the User box, type the following statement and Hit 'Save' (Click here for preview)
BEGIN FND_CTL.FND_SESS_CTL('','','TRUE','TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER = TESTING MAX_DUMP_FILE_SIZE = 5000000 EVENTS ='''''' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''''');END;
Example :BEGIN FND_CTL.FND_SESS_CTL ('','','TRUE','TRUE','LOG', 'ALTER SESSION SET TRACEFILE_IDENTIFIER =''MURALIAR0001'' EVENTS=''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'''); END;
Step 6. Login to HTML / JSP page with username/password and start your flow. (Everything you do once login to HTML / JSP will get trace.)
Step 7. Logout of HTML / JSP application once you completed with your flow.
Step 8. Go back to the Profile option in the Form application and delete the Initialization SQL statement, and Hit 'Save'.
Step 9. Log in to the database server or login server and retrieve your trace file. Identify and retrieve the trace file using the tracefile_identifier specified in Step 5.
In this case the tracefile_identifier is “TESTING”. (Click here for Trace file locations) * Note: If you need to regenerate your trace or tracing a new flow, then repeat Step 1 to Step 8. To avoid self-confusion, choose a different name for your trace identifier everytime you set to trace. Step 10. See TKPROF section on how to format trace file into readable text.Trace Options Definition
No Trace
Tracing is not activated
Activities will not get traced.
Regular Trace
(Level 1) Contains SQL, execution statistics, and execution plan.
Provides execution path, row counts as well as produces smallest flat file.
Trace with Binds(Level 4) Regular Trace plus value supplied to SQL statement via local variables.
Trace with Waits(Level 8) Regular Trace plus database operation timings that the SQL waited to have done in order to complete, i.e. disk access.
Trace with Binds and Waits(Level 12) Regular trace with both waits and binds information.Contains the most complete information and will produce the largest trace file.
To get the trace file path on server.
select value from v$parameter where name = 'user_dump_dest' in DB node.

Queries for use

Genral Ledger
---------------
select jh.name Journal_name,jh.period_name Period,jh.currency_code Currency ,jh.je_category Category,jh.je_source Source,jl.je_line_num Linenumber,gcc.segment1'.'gcc.segment2'.'gcc.segment3'.'gcc.segment4'.'gcc.segment5 Account,jl.entered_dr Debit,jl.entered_cr Credit
from gl_je_headers jh,
gl_je_lines jl,
gl_code_combinations gcc
where jl.CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID
and jh.JE_HEADER_ID=jl.JE_HEADER_ID
and jh.name=:name
and jh.period_name=:period
and jh.currency_code='STAT'
and jh.je_source='82'
INVOICE DETAILS
---------------------
selectap.invoice_num Invoice_num,ap.invoice_date Invoice_date,ap.invoice_currency_code Invoice_currency,ap.invoice_amount Invoice_amount,aid.amount Amount,aid.accounting_date Gl_date,pv.vendor_name Supplier,pvs.vendor_site_code Site_name--,at.name Payment_terms,ap.terms_date Term_date,alc.meaning Invoice_type,alc.lookup_code,alc1.meaning Invoice_distribution_type,alc2.meaning Payment_method,gcc.segment1'.'gcc.segment2'.'gcc.segment3'.'gcc.segment4'.'gcc.segment5 Account
from ap_invoices_all ap,
ap_invoice_distributions_all aid,
po_vendors pv,
po_vendor_sites_all pvs,
fnd_lookup_values alc,
fnd_lookup_values alc1,
fnd_lookup_values alc2--,
ap_terms_tl at,
gl_code_combinations gcc
where alc.lookup_type='INVOICE TYPE'
and alc.lookup_code=ap.INVOICE_TYPE_LOOKUP_CODE
and alc1.lookup_type='INVOICE DISTRIBUTION TYPE'
and alc1.lookup_code=aid.LINE_TYPE_LOOKUP_CODE
and alc2.lookup_type='PAYMENT METHOD'
and alc2.lookup_code=ap.PAYMENT_METHOD_LOOKUP_CODE
and ap.invoice_id=aid.invoice_id
and ap.vendor_id = pv.vendor_id
and gcc.code_combination_id=aid.DIST_CODE_COMBINATION_ID
and pv.vendor_id=pvs.vendor_id
and ap.vendor_site_id=pvs.vendor_site_id
--and ap.terms_id=at.term_id(+)
and ap.invoice_num='214901'
PAYMENT DETAILS IN 11i
-----------------------------
select alc.MEANING Payment_type,acs.name Document,aca.check_number Document_number,aca.amount Payment_amount,aca.CURRENCY_CODE Payment_currency,aca.CHECKRUN_NAME Batchname,aca.EXCHANGE_DATE Rate_date--,aca.EXCHANGE_RATE Exc_rate,aba.bank_account_name Bank_account,aba.CURRENCY_CODE Account_currency,alc1.meaning Payment_method,alc2.meaning Check_status,pv.vendor_name Supplier,pvs.VENDOR_SITE_CODE Suppliersite
from ap_checks_all aca,
ap_check_stocks_all acs,
ap_bank_accounts_all aba,
po_vendors pv,
po_vendor_sites_all pvs,
fnd_lookup_values alc,
fnd_lookup_values alc1,
fnd_lookup_values alc2--,
gl_daily_conversion_types gdct
--,gl_daily_conversion_types gdct1
where alc.lookup_type='PAYMENT TYPE'
and alc.lookup_code=aca.PAYMENT_TYPE_FLAG
and alc1.lookup_type='PAYMENT METHOD'
and alc1.lookup_code=aca.PAYMENT_METHOD_LOOKUP_CODE
and alc2.lookup_type='CHECK STATE'
and alc2.lookup_code=aca.STATUS_LOOKUP_CODE
--and aca.EXCHANGE_RATE_TYPE=gdct.CONVERSION_TYPE
and aca.check_stock_id=acs.check_stock_id
and aca.BANK_ACCOUNT_ID=aba.BANK_ACCOUNT_ID
and aca.VENDOR_ID=pv.vendor_id
and aca.VENDOR_SITE_ID=pvs.VENDOR_SITE_ID
and aca.check_number='1000013'
AR to GL query
-----------------
SELECT gjjlv.period_name "Period" , gjb.name "Batch name" , gjjlv.header_name "Journal Entry For" , gjjlv.je_source "Source" , glcc.concatenated_segments "Accounts" , gjjlv.line_entered_dr "Entered Debit" , gjjlv.line_entered_cr "Entered Credit" , gjjlv.line_accounted_dr "Accounted Debit" , gjjlv.line_accounted_cr "Accounted Credit" , gjjlv.currency_code "Currency" , arm.name "Payment Method" , acra.receipt_number "Receipt Num" , acra.receipt_date "Receipt Date" , RA.CUSTOMER_NAME "Reference" , gjjlv.created_by "Gl Transfer By"
FROM apps.gl_je_journal_lines_v gjjlv ,
gl_je_lines gje ,
gl_je_headers gjh ,
gl_je_batches gjb ,
ar_cash_receipts_all acra ,
apps.ra_customers ra ,
apps.gl_code_combinations_kfv glcc ,
ar_receipt_methods arm
WHERE gjh.period_name IN ('OCT-2007','NOV-2007')
AND glcc.code_combination_id = gje.code_combination_id
AND gjh.JE_BATCH_ID = gjb.JE_BATCH_ID
AND gjh.JE_HEADER_ID = gje.JE_HEADER_ID
AND gjh.period_name = gjb.default_period_name
AND gjh.period_name = gje.period_name
AND gjjlv.period_name = gjh.period_name
AND gjjlv.je_batch_id = gjh.je_batch_id
AND gjjlv.je_header_id = gjh.je_header_id
AND gjjlv.LINE_JE_LINE_NUM = gje.je_line_num
AND gjjlv.line_code_combination_id = glcc.code_combination_id
AND gjjlv.line_reference_4 = acra.receipt_number
AND ra.customer_id = acra.pay_from_customer
AND acra.receipt_method_id = arm.receipt_method_id
AND gjjlv.SUBLEDGER_DOC_SEQUENCE_VALUE = acra.DOC_SEQUENCE_VALUE
AND gjjlv.SUBLEDGER_DOC_SEQUENCE_id = acra.DOC_SEQUENCE_ID
--and glcc.segment1 ='30D'
AR Transactional query
------------------------------
SELECT ol.line_id,trxl.customer_trx_id, trx.trx_number
FROM ra_customer_trx_all trx,
ra_customer_trx_lines_all trxl,
oe_order_lines_all ol,
oe_order_headers_all oh,
oe_transaction_types_tl tt
WHERE trxl.interface_line_context ='ORDER ENTRY'
AND trxl.INTERFACE_LINE_ATTRIBUTE1 = to_char(oh.order_number)
AND trxl.INTERFACE_LINE_ATTRIBUTE2 = TT.name
AND TT.transaction_type_id = oh.ORDER_TYPE_ID
AND to_char(ol.LINE_ID) = TRXL.INTERFACE_LINE_ATTRIBUTE6
AND TRXL.line_type = 'LINE'
AND TRXL.INTERFACE_LINE_ATTRIBUTE11 = '0'
AND trx.CUSTOMER_TRX_ID = trxl.CUSTOMER_TRX_ID
AND oh.header_id = ol.header_id
AND EXISTS( SELECT 1 from fnd_languages where installed_flag = 'B' AND tt.language = language_code and rownum=1)
AND ol.line_id ").append(s).toString());