Cross Validation Rules Vs Security Rule

Cross Validation Rules Vs Security Rule

Cross Validation Rules(CVS)

• Cross Validation Rule applies on Segment values.
• Cross validation rules prohibit invalid account combinations
• Cross validation rule once created would immediately take affect with all the responsibilities

Security Rule(SR)

• Security rules applies at Value Set level while
• SR restrict user dedine segment values
• security rule to take affect it should be assigned to a responsibility

INVALID DISTRIBUTION ACCT IN Payables Open Interface

Payables Open Interface used below two API's to validate Distribution Acct.
Check whether below API's are returning correct value.

DECLARE
  l_catsegs VARCHAR2(240);
BEGIN
  IF fnd_flex_keyval.validate_ccid( appl_short_name => 'SQLGL', key_flex_code => 'GL#', structure_number => 50409, combination_id => 281868) THEN
    l_catsegs := fnd_flex_keyval.concatenated_values;
    DBMS_OUTPUT.PUT_LINE(l_catsegs) ;
  ELSE
    DBMS_OUTPUT.PUT_LINE(2) ;
  END IF;
  --
  IF (fnd_flex_keyval.validate_segs( 'CHECK_COMBINATION', 'SQLGL', 'GL#', 50409, l_catsegs, 'V', NVL(SYSDATE, sysdate), 'ALL', NULL, '\nSUMMARY_FLAG\nI\nAPPL=SQLGL;' || 'NAME=GL_CTAX_SUMMARY_ACCOUNT\nN', NULL, NULL, FALSE, FALSE, FND_GLOBAL.RESP_APPL_ID, FND_GLOBAL.RESP_ID, FND_GLOBAL.USER_ID) = TRUE) THEN
    DBMS_OUTPUT.PUT_LINE('True') ;
  ELSE
    DBMS_OUTPUT.PUT_LINE('False') ;
  END IF;
END;

Table for Bugs and Patching

Table for Bugs and Patching

PROMPT Find specific package version
select text from dba_source
where
name = upper( '&PackageName')
and line < 3;

PROMPT Find bugs already installed fixed in your system
select bug_number
from ad_bugs
where bug_number ='&Bug_Number';

PROMPT Find patches applied
select substr(patch_name,1,12) patch_num
from ad_applied_patches
where patch_name like '%&Patch_Number%';


NOTE: A specific bug maybe fixed by multiple patches so it might be good to look for the bug number, instead of the patch number to see if that bug is fixed already on your system. Another way is to look at the file version mentioned in the patch and check if you have that version or higher.

SQL related to Oracle Application Messages

SQL related to Oracle Application Messages

SQL related to Oracle Application Messages

PROMPT ATG
PROMPT Find Messages by Message Text
select m.message_name, m.message_text, m.message_number, a.application_short_name
from fnd_new_messages m, fnd_application a
where upper(m.message_text) like upper('%&EnterMessageText%')
and m.language_code = 'US'
and m.application_id = a.application_id;

PROMPT Find Messages by Message Short Name
select m.message_name, m.message_text, m.message_number, a.application_short_name
from fnd_new_messages m, fnd_application a
where m.message_name like '%&EnterMessageName%'
and m.language_code = 'US'
and m.application_id = a.application_id;

Query for Identifying correct trace file for request id

Query for Identifying correct trace file for request id

PROMPT IDENTIFY CONCURRENT REQUEST FILE
PROMPT From Bug.3211206
PROMPT Use the following query to identify the correct trace file:
PROMPT where "request" is the concurrent request id for the inventory transaction
PROMPT worker.
SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname
where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;

Query for various output of concurrent managers

Query for various output of concurrent managers

PROMPT Programs and Managers
PROMPT Provide various output of concurrent managers related to a specific program.
PROMPT In this case using default of Item Supply/Demand program.
SELECT
fcq.processor_application_id, fcp.concurrent_program_name,
fr.responsibility_id, fr.responsibility_key, fr.data_group_id, fr.request_group_id,
fr.application_id, fa.application_short_name,
fcq.concurrent_queue_id, fcq.CONCURRENT_QUEUE_NAME,
fcq.MIN_PROCESSES, fcq.TARGET_PROCESSES, fcq.TARGET_NODE, fcq.SLEEP_SECONDS, fcq.CONTROL_CODE, fcq.DIAGNOSTIC_LEVEL,
fcpr.*
FROM fnd_application fa,
fnd_concurrent_programs fcp,
fnd_conc_processor_programs fcpp,
fnd_responsibility fr,
fnd_concurrent_queues fcq,
fnd_concurrent_processes fcpr
WHERE fcq.processor_application_id = fcpp.processor_application_id
AND fcq.concurrent_processor_id = fcpp.concurrent_processor_id
AND fcpp.concurrent_program_id = fcp.concurrent_program_id
AND fcpp.program_application_id = fcp.application_id
AND fcp.application_id = fa.application_id
AND fcp.concurrent_program_name = NVL('&EnterProgramShortName', 'INXDSD')
AND fr.application_id = 401
AND fr.data_group_id = fcq.data_group_id
AND fcq.manager_type = '3'
AND fcpr.concurrent_queue_id = fcq.concurrent_queue_id
AND fcpr.queue_application_id = fcq.application_id
-- AND fcpr.process_status_code = 'A'
AND fcpr.instance_number = userenv('instance')
ORDER BY dbms_random.random;

Concurrent program statuses

Concurrent program statuses

Concurrent program statuses

PROMPT Concurrent program values

Select distinct lookup_code, meaning From Fnd_Lookup_Values
Where Lookup_Type = 'CP_STATUS_CODE'
order by lookup_code;

A Waiting
B Resuming
C Normal
D Cancelled
E Error
G Warning
H On Hold
I Normal
M No Manager
P Scheduled
Q Standby
R Normal
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting

Query to check Active users

Query to check Active users

PROMPT Active Users
select fnd.user_name, icx.responsibility_application_id, icx.responsibility_id, frt.responsibility_name,
icx.session_id, icx.first_connect,
icx.last_connect,
DECODE ((icx.disabled_flag),'N', 'ACTIVE', 'Y', 'INACTIVE') status
from
fnd_user fnd, icx_sessions icx, fnd_responsibility_tl frt
where
fnd.user_id = icx.user_id
and icx.responsibility_id = frt.responsibility_id
and icx.disabled_flag <> 'Y'
and trunc(icx.last_connect) = trunc(sysdate)
order by icx.last_connect;

Query to find users who have a responsibility

Query to find users who have a responsibility

PROMPT Find users who have a responsibility
select usr.user_id, usr.user_name, res.RESPONSIBILITY_ID, res.RESPONSIBILITY_NAME
from apps.FND_USER usr, apps.FND_RESPONSIBILITY_TL res, apps.FND_USER_RESP_GROUPS grp
where upper(res.RESPONSIBILITY_NAME) like upper('%' || NVL('&EnterRespName', 'INV')|| '%')
and upper(res.RESPONSIBILITY_NAME) NOT like '%AX%'
and upper(res.RESPONSIBILITY_NAME) NOT like '%OPM%'
and grp.responsibility_id = res.responsibility_id
and grp.user_id = usr.user_id;

Query to check profile options at all levels

Query to check profile options at all levels

select 
b.user_profile_option_name "Long Name"
, a.profile_option_name "Short Name"
, decode(to_char(c.level_id),'10001','Site'
,'10002','Application'
,'10003','Responsibility'
,'10004','User'
,'Unknown') "Level"
, decode(to_char(c.level_id),'10001','Site'
,'10002',nvl(h.application_short_name,to_char(c.level_value))
,'10003',nvl(g.responsibility_name,to_char(c.level_value))
,'10004',nvl(e.user_name,to_char(c.level_value))
,'Unknown') "Level Value"
, c.PROFILE_OPTION_VALUE "Profile Value"
, c.profile_option_id "Profile ID"
, to_char(c.LAST_UPDATE_DATE,'DD-MON-YYYY HH24:MI') "Updated Date"
, nvl(d.user_name,to_char(c.last_updated_by)) "Updated By"
from 
apps.fnd_profile_options a
, apps.FND_PROFILE_OPTIONS_VL b
, apps.FND_PROFILE_OPTION_VALUES c
, apps.FND_USER d
, apps.FND_USER e
, apps.FND_RESPONSIBILITY_VL g
, apps.FND_APPLICATION h
where 
--a.application_id = nvl(401, a.application_id)
--and a.profile_option_name = nvl('INV', a.profile_option_name)
b.user_profile_option_name like '&ProfileName' -- 'AFLOG_ENABLED'
and a.profile_option_name = b.profile_option_name
and a.profile_option_id = c.profile_option_id
and a.application_id = c.application_id
and c.last_updated_by = d.user_id (+)
and c.level_value = e.user_id (+)
and c.level_value = g.responsibility_id (+)
and c.level_value = h.application_id (+)
order by 
b.user_profile_option_name, c.level_id, 
decode(to_char(c.level_id),'10001','Site'
,'10002',nvl(h.application_short_name,to_char(c.level_value))
,'10003',nvl(g.responsibility_name,to_char(c.level_value))
,'10004',nvl(e.user_name,to_char(c.level_value))
,'Unknown');

Queryies for Inventory Transactions Pending

Queryies for Inventory Transactions Pending

Inventory Transactions

a. Stuck interface transactions (Group By)

PROMPT Stuck Transactions - GroupBy MTI
select transaction_type_id, organization_id, substr(error_code, 1, 30),
substr(error_explanation, 1, 50), to_char(transaction_date, 'YYYY-MM'),
count(*)
from mtl_transactions_interface
group by transaction_type_id, organization_id, to_char(transaction_date, 'YYYY-MM'),
substr(error_code, 1, 30), substr(error_explanation, 1, 50);

b. Stuck pending transactions (Group By)

PROMPT Stuck Transactions - GroupBy MMTT
select transaction_type_id, organization_id, substr(error_code, 1, 30),
substr(error_explanation, 1, 50), to_char(transaction_date, 'YYYY-MM'),
count(*)
from mtl_material_transactions_temp
group by transaction_type_id, organization_id, to_char(transaction_date, 'YYYY-MM'),
substr(error_code, 1, 30), substr(error_explanation, 1, 50);

c. Stuck move order transactions (Group By)

PROMPT Stuck Transactions - GroupBy Move Order
select transaction_type_id, to_char(transaction_date,'YYYY-MON'),
decode(transaction_status,2,'Untransacted Move order', transaction_status),
error_code, error_explanation,
count(*)
from mtl_material_transactions_temp
where organization_id = &Org_id
group by transaction_type_id, to_char(transaction_date,'YYYY-MON'),
decode(transaction_status,2,'Untransacted Move order', transaction_status),
error_code, error_explanation;

d. Uncosted transactions (Group By)

PROMPT Uncosted Transactions - GroupBy MMT
select transaction_type_id, organization_id, costed_flag,
to_char(transaction_date, 'YYYY-MM'), error_code, substr(error_explanation, 1, 50),
count(*)
from mtl_material_transactions
where costed_flag IN ('N','E')
group by transaction_type_id, organization_id, costed_flag, to_char(transaction_date, 'YYYY-MM'),
error_code, substr(error_explanation, 1, 50);

e. Dump information about transaction tables

PROMPT Stuck Transactions Dump - MTI
select transaction_interface_id, inventory_item_id, organization_id, subinventory_code,
locator_id, revision, transaction_quantity, transaction_date, transaction_type_id,
transaction_source_id, transfer_subinventory, transfer_locator,
trx_source_line_id, cost_group_id, process_flag, lock_flag, transaction_mode,
error_explanation, error_code
from mtl_transactions_interface
order by transaction_source_id, trx_source_line_id;

PROMPT Stuck Transactions Dump - MMTT
select transaction_temp_id, inventory_item_id, organization_id, subinventory_code, locator_id, revision,
transaction_quantity, transaction_date, transaction_type_id, transaction_source_id, transfer_subinventory,
transfer_to_location, trx_source_line_id, cost_group_id, process_flag, lock_flag, transaction_mode,
error_explanation, error_code
from mtl_material_transactions_temp
order by transaction_source_id, trx_source_line_id;

PROMPT Stuck Transactions Dump - MMT
select
transaction_id, inventory_item_id, organization_id, subinventory_code,
locator_id, revision, transaction_quantity, transaction_date,
transaction_type_id, transaction_source_id, transfer_subinventory,
transfer_locator_id, trx_source_line_id, cost_group_id,
error_explanation, error_code,
from mtl_material_transactions
where costed_flag IN ('N','E')
order by transaction_source_id, trx_source_line_id;

R12 Queries SLA tables

R12 Queries SLA tables

To get the sum from the SLA tables.

QR13 - Sum from the SLA Tables

select /*+ parallel(xal) parallel(xah) leading(xah) */ sum(nvl(accounted_cr,0)) -sum(nvl(accounted_dr,0)) diff, currency_code
from xla_ae_lines xal, xla_Ae_headers xah
where xal.accounting_class_code = 'LIABILITY'
and xal.code_combination_id =15151 <>
and xal.application_id = 200
and xal.ae_header_id = xah.ae_header_id
and xal.application_id =xah.application_id
and xah.ledger_id = 1 <>
and xah.gl_transfer_status_code = 'Y'
and xah.accounting_entry_status_code='F'
and xah.balance_type_code='A'
and (xah.upg_batch_id is null or xah.upg_batch_id=-9999) -- will help ignore upgraded data
and xah.accounting_date between '01-MAR-2008' and '31-MAR-2008' <>
group by currency_code


R12.B) To get the sum from the GL tables

QR14 - Sum from the GL Tables

select l.code_combination_id ccid,k.concatenated_segments,
sum(nvl(l.accounted_cr, 0))- sum(nvl(l.accounted_dr,0)) diff,
currency_code
from gl.gl_je_headers h
, gl.gl_je_lines l
,gl_code_combinations_kfv k
where h.currency_code = 'USD'
and l.ledger_id = 1 <>
and l.code_combination_id = k.code_combination_id
and h.je_header_id = l.je_header_id
and h.actual_flag = 'A'
and h.je_from_sla_flag= 'Y' -- will help ingore upgraded data
and l.code_combination_id = 15151 <>
and h.je_source = 'Payables'
and h.period_name in ('MAR-08') <>
group by l.code_combination_id, k.concatenated_segments, currency_code

R12.A and R12.B should match per CCID, currency within the period. If it does, check per period, per CCID, per currency--This should be the difference between XAL and XTB.

QR15 - To get the difference from SLA Lines table per CCID and currency

select /*+ parallel (xah,xal) leading(xah) */ sum(nvl(accounted_cr,0)) -sum(nvl(accounted_dr,0)) diff, currency_code
from xla_ae_lines xal, xla_ae_headers xah
where xal.accounting_class_code = 'LIABILITY'
and xah.event_type_code <> 'MANUAL'
and xal.code_combination_id =15011 -- i/p def code
and xal.application_id = 200
and xal.ae_header_id = xah.ae_header_id
and xal.application_id =xah.application_id
and xah.ledger_id = 1
and xah.gl_transfer_status_code = 'Y'
and xah.accounting_entry_status_code='F'
and xah.accounting_date between '01-MAR-2008' and '31-MAR-2008'
group by currency_code

Note: In the following SQL query, we have introduced another filter--event_class_code.
This is done in order to remove the UNDO ACCOUNTING datafix entries. Read the section, Understanding how UNDO Accounting can impact Reconciliation, for more details. Such entries will match in SLA-to-GJL lines comparison, but will not match between XAL-to-XTB

QR16 - Get the XTB Difference

select SUM(NVL(ACCTD_ROUNDED_CR,0)) - SUM(NVL(ACCTD_ROUNDED_DR,0)) diff, trx_currency_code
from xla_trial_balances xtb
where definition_code = &definition_code
and code_combination_id=15011
and gl_date between '01-MAR-2008' and '31-MAR-2008'
group by trx_currency_code

The Difference of the sum of Credit-Debit for the CCID/Currency combination in XAL (lines) should match the difference of the Sum Credit-Debit for the CCID/Transaction Currency in XTB, assuming all LIABILITY CCIDs are defined in the Trial Balance Definition.

QR17 - Get the difference from the SLA Lines table per CCID and currency

select /*+ parallel (xah,xal) leading(xah) */ sum(nvl(accounted_cr,0)) -sum(nvl(accounted_dr,0)) diff, currency_code, entity_id
from xla_ae_lines xal, xla_Ae_headers xah
where xal.accounting_class_code = 'LIABILITY'
and xal.code_combination_id =15011
-- i/p def code
and xal.application_id = 200
and xal.ae_header_id = xah.ae_header_id
and xal.application_id =xah.application_id
and xah.ledger_id = 1
and xah.gl_transfer_status_code = 'Y'
and xah.accounting_entry_status_code='F'
and xah.accounting_date between '01-MAR-2008' and '31-MAR-2008'
group by entity_id, currency_code,entity_id



QR18 - Get the difference from the XTB table

select SUM(NVL(ACCTD_ROUNDED_CR,0)) - SUM(NVL(ACCTD_ROUNDED_DR,0)) diff, trx_currency_code, source_entity_id
from xla_trial_balances xtb
where definition_code = &definition_code
and code_combination_id=15011
and gl_date between '01-MAR-2008' and '31-MAR-2008'
group by trx_currency_code, source_entity_id


If R12.A and R12.B do not match per CCID and currency within a period, then investigate the batches within that period from SLA and compare the amounts in SLA and GL using the group_id. This may not always work as the group_id sometimes gets deleted from GL.

QR19 - SLA query considering the SLA Manual entries

select /*+ parallel(xal) parallel(xah) leading(xah) */
--xah.entity_id source_entity_id,
sum(nvl(accounted_cr,0)) -sum(nvl(accounted_dr,0)) diff , xah.group_id
from xla_ae_lines xal, xla_Ae_headers xah
where xal.accounting_class_code = 'LIABILITY' and xah.event_type_code <> 'MANUAL'
and xal.code_combination_id =52261
and xal.application_id = 200
and xal.ae_header_id = xah.ae_header_id
and xal.application_id =xah.application_id
and xah.ledger_id = 2
and xah.gl_transfer_status_code = 'Y'
and xah.accounting_entry_status_code='F'
and xah.balance_type_code='A'
group by xah.group_id


QR20 - GL query for amounts per Group ID

select 'GL' Module, b.group_id,
SUM(NVL(accounted_cr,0)) - SUM(NVL(accounted_dr,0)) diff_acc
from apps.gl_je_headers a, apps.gl_je_batches b,apps.gl_je_lines c
where
a.je_header_id=c.je_header_id
and a.je_batch_id=b.je_batch_id
and c.ledger_id=2
and a.posted_date is NOT NULL
and a.je_source ='Payables'
and a.actual_flag='A' and c.code_combination_id=52261
group by b.group_id;

Queries for Reconciliation Issue

Queries for Reconciliation Issue

QR24 - Run this SQL script to determine the Batch ID for a Group

select je_batch_id from gl_je_batches where group_id=2068553 


QR25 - Run this SQL script to list the Amounts in the 
SLA table for all Manual (YTD) transactions

select /*+ parallel(l) parallel(h) parallel(gl) leading(h) */ 
gl.concatenated_segments,l.code_combination_id,sum(nvl(entered_cr,0)),
sum(nvl(entered_dr,0)),sum(nvl(entered_cr,0))-sum(nvl(entered_dr,0)), currency_code
from xla_ae_headers h, xla_ae_lines l, gl_code_combinations_kfv gl
where gl.code_combination_id = l.code_combination_id and
h.application_id = 200
and l.application_id = h.application_id
and l.ae_header_id = h.ae_header_id
and h.accounting_date between to_date('01-JUL-2007','DD-MON-YYYY') AND
to_date('31-MAR-2008','DD-MON-YYYY') -- <>
and h.ledger_id = 1 <>
AND h.gl_transfer_status_code='Y'
AND h.accounting_entry_status_code='F'
AND l.code_combination_id = 112771 <>
and h.event_type_code='MANUAL'
and h.application_id=200
and h.balance_type_code='A'
group by l.code_combination_id, gl.concatenated_segments, currency_code


QR26 - Run this SQL script to list all transactions that are marked as transferred from SLA, but is not present in the GL

SELECT l.ae_header_id, l.gl_sl_link_id, l.gl_sl_link_table 
FROM xla_ae_lines l, xla_ae_headers h WHERE 
l.application_id=h.application_id AND 
l.ae_header_id=h.ae_header_id AND 
h.application_id=:p_application_id AND 
h.ledger_id= :p_ledger_id AND 
h.upg_batch_id IS NULL AND 
h.gl_transfer_status_code='Y' AND 
h.accounting_entry_status_code='F' AND 
h.accounting_date BETWEEN :p_period_start_date AND :p_period_end_date AND 
h.event_type_code <> ' MANUAL' 
AND NOT EXISTS 
(SELECT 1 FROM gl_import_references ir , gl_je_headers gh 
WHERE ir.gl_sl_link_id=l.gl_sl_link_id AND 
ir.gl_sl_link_table=l.gl_sl_link_table AND 
ir.je_header_id=gh.je_header_id AND 
ir.je_batch_id=gh.je_batch_id AND 
gh.ledger_id>0); 



QR27 - Run this SQL script periodically to check for multiple postings

select distinct a.gl_sl_link_id, a.gl_sl_link_table 
from gl_import_references a 
where (a.gl_sl_link_id,a.gl_sl_link_table) in 
(select distinct gl_sl_link_id, gl_sl_link_table 
from xla_ae_headers xah ,xla_ae_lines xal 
where xah.application_id = xal.application_id 
and xah.ae_header_id = xal.ae_header_id 
and xah.ledger_id= :ledger_id 
and xah.application_id =:appl_id -- 200 for AP, 222 for AR etc. 
and xah.accounting_entry_status_code='F' 
and xah.accounting_date between :p_start and :p_end) 
and exists (select 1 from gl_je_headers gh 
where gh.je_batch_id = a.je_batch_id 
and gh.je_header_id = a.je_header_id 
and gh.ledger_id >0 
and nvl(gh.accrual_rev_je_header_id,0) =0 
) 
group by a.gl_sl_link_id, a.gl_sl_link_table 
having count(*) > 1 


QR28 - Run this SQL script to identify where the GL_SL_LINK_ID 
is in GL but not in SLA, for a batch or period.

select imp.gl_sl_link_id 
from gl_import_references imp, gl_je_lines gl
where gl.je_header_id=imp.je_header_id and
gl.je_line_num=imp.je_line_num 
and code_combination_id=52261 
and gl.je_header_id in
(select je_header_id from gl_je_headers where --je_batch_id=2586374
je_source='Payables' and ledger_id=2
and je_source ='Payables' and posted_date is NOT NULL
and actual_flag='A' 
and period_name in ('Jan-08','Feb-08','Mar-08','Apr-08') )
and not exists
(select 1 from xla_ae_lines where gl_sl_link_id=imp.gl_sl_link_id
and gl_sl_link_table='XLAJEL') and imp.gl_sl_link_id is not null


QR29 - Run this SQL script to list all the Batches with Null Group IDs: 

select je_batch_id from gl_je_batches where group_id is null
and je_batch_id in
(select distinct je_batch_id from gl_je_headers
where ledger_id=2 and je_source='Payables' and status='P'
and period_name='Jan-08')

Tip to Personalize FORMS without Apps Password

Tip to Personalize FORMS without Apps Password

Tip to Personalize FORMS without Apps Password

Using below Profile option, you can perform FORMS Personalization without knowing APPS Password.
Profile option: Utilities:Diagnostics

Set to Yes at Responsibility level. This can be set at User Level also.

XMLAGG & listagg

XMLAGG  & listagg 

SELECT   dept,
         RTRIM (XMLAGG (XMLELEMENT (e, empname || ',')).EXTRACT ('//text()'),
                ','
               ) empnames
    FROM testingemp
GROUP BY dept

SELECT dept,listagg (empname, ',') WITHIN GROUP (ORDER BY empname)
        empnames
FROM testingemp
GROUP BY dept

SELECT  listagg (empname, ',') WITHIN GROUP (ORDER BY empname) employeenames
FROM testingemp

select rtrim (xmlagg (xmlelement (e, empname || ',')).extract ('//text()'), ',') Empnames
from testingemp

Setup to Add new ORG To Purchasing

Setup to Add new ORG To Purchasing

Setup to Add new Inventory ORG To Purchasing

 

Following is the navigation for this setup:

 

Responsibility: TFI US Inventory Super User (Or) any Inventory responsibility which has access to Setups
Navigation: Setup --> Organizations -- Organization Access

Enter a new line with following details to give New Organization Access.

Org: Org Name
Application: Purchasing
Name:Any responsibility you want access to New Organization

Query for Requisition Approval groups

This summary is not available. Please click here to view the post.

Payables Invoice Matching Explained

Payables Invoice Matching Explained

 Explained very clearly in Payables Invoice Matching Explained [ID 1441364.1]

OVERVIEW

Oracle Payables shares purchase order information from your purchasing system to enable online matching with invoices.

You can match Payables invoices to purchase orders to ensure that you pay only for the goods that you have ordered, or you can match to purchase order receipts to ensure that you pay only for goods that you have received.

Purchase order matched invoices are invoices that you match to any of the following:

Purchase order shipments
Purchase order receipts
Purchase order receipt lines
Purchase order distributions
You can set up controls in your system to require you to purchase order match each invoice. You can also set an option on a purchase order shipment that controls whether invoices should match to that shipment directly or to a receipt. You set tolerances to specify the range of variance you will allow if the amounts or quantities on the invoice are greater than the amounts or quantities on the purchase order or receipt.

When you enter an invoice and match it, Payables automatically creates distributions for you and checks that the match is within the tolerance you define.

After you save the match, Payables updates the quantity or amount billed for each matched shipment and its corresponding distribution(s) based on the amount you enter in the Quantity Invoiced field. Payables also updates the amount billed on the purchase order distribution(s).

You can match an invoice to a purchase order in different ways:

Receipt match. Matching to receipts allows you to pay only for goods or services you receive, and to pay for partial shipments without getting invoice holds. In addition, any exchange rate variance is likely to be smaller because the time between the receipt and invoice is less than the time between the purchase order and invoice.
If you use one of the Periodic Costing options available in Oracle Cost Management to record costs of goods that you order, it is critical that you always match to receipts to ensure accurate cost accounting. When you match an invoice for goods to a receipt, you can also link other charges, such as freight, tax, and miscellaneous to that receipt, so that costing can include those charges in the cost of the goods. If you match to a purchase order instead of a receipt, you will not have accurate costing data.

Purchase order shipment match. Based on the Quantity Invoiced, Payables prorates the Match Amount across all non-fully billed purchase order distributions associated with the purchase order shipments you match to. Payables automatically creates invoice distributions based on the purchase order distributions.
You can match to individual purchase order shipments. Payables automatically creates invoice distributions based on the purchase order distributions.

Purchase order distribution match. You can allocate the match amount to specific purchase order distributions. Payables automatically creates invoice distributions based on the purchase order distributions you match to.
Price correction. Use a price correction to adjust the invoiced unit price of previously matched purchase order shipments, distributions, or receipts without adjusting the quantity billed.
When you are matching to a purchase order, open the View PO window to have easy access to purchase order information.

You can match a single invoice to multiple purchase order shipments, or you can match multiple invoices to a single purchase order shipment. Oracle Payables ensures that you match only to purchase orders for the supplier on the invoice and that the purchase order and invoice currencies match.

Purchase orders are created for Inventory Items or Expense Items. The accounting entries created by matching invoices to POs will be different for Inventory Items as compared to Expense Items depending on whether you accrue expense items on receipt or at period end.

SETUP

Oracle Payables supports three levels of matching which verify that purchase order and invoice information match within defined tolerances as follows:


2-way matching    quantity billed <= quantity ordered
      
3-way matching    quantity billed <= quantity ordered
         quantity billed <= quantity received
      
4-way matching    quantity billed <= quantity ordered
         quantity billed <= quantity received
         quantity billed <= quantity accepted

This option can be setup as a default at various levels: