Query Used When Setup AGIS

Post date: Jul 11, 2013 2:54:20 PM

select *

from FUN_INTERFACE_CONTROLS

select *

from FUN_INTERFACE_BATCHES

select *

from FUN_INTERFACE_HEADERS

select *

from FUN_INTERFACE_DIST_LINES

select *

from AP_INVOICES_INTERFACE

select *

from RA_INTERFACE_LINES_ALL

For Diagnose AGIS use this script, run in putty after run the sqlplus mode:

REM +=======================================================================+

REM | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |

REM | All rights reserved. |

REM | $Header: agis_script 120.0 16/OCT/2010 $ |

REM +=======================================================================+

REM FILENAME

REM agis_script.sql

REM

REM PURPOSE

REM AGIS Diagnostic script.

REM USAGE

REM Example:

REM sqlplus apps/apps @agis_script.sql

REM PARAMETERS:

REM Output File Path,User Name,Initiator,Recipient Organization,Transaction Type

REM

REM HISTORY

REM Date Version Remarks

REM 16-OCT-10 120.0 Initial version

REM

CLEAR SCREEN

SET VERIFY OFF

SET SERVEROUTPUT ON

SET LINESIZE 1500

CLEAR COLUMNS

CLEAR BREAKS

SET HEADING ON

SET FEEDBACK OFF

CLEAR BUFFER

SET PAGESIZE 58

PROMPT

PROMPT

PROMPT *******************************************************************************************************************

Prompt Note: Its mandatory to provide value for parameters Output File Path,User Name,Initiator and Recipient Organization

Prompt *******************************************************************************************************************

PROMPT

PROMPT

ACCEPT output_path PROMPT 'Please provide the path of the text output file eg. d:\agis_script_output.txt : '

SPOOL &output_path

ACCEPT User_name PROMPT 'Enter Applications User Name : '

ACCEPT Initiator_Organization PROMPT 'Enter Initiator Organization Name : '

ACCEPT Recipient_Organization PROMPT 'Enter Recipient Organization Name : '

ACCEPT Transaction_Type PROMPT 'Enter Transaction Type : '

PROMPT

PROMPT

REM*******************************************************************************************************************************

REM Validation of Parameters

REM*******************************************************************************************************************************

DECLARE

v_row_cnt NUMBER := 0;

BEGIN

BEGIN

SELECT 1

INTO v_row_cnt

FROM fnd_user

WHERE UPPER(user_name) = UPPER('&user_name');

EXCEPTION

WHEN no_data_found THEN

DBMS_OUTPUT.PUT_LINE('****Warning****: The entered Applications user name does not exist');

WHEN others THEN

DBMS_OUTPUT.PUT_LINE('****Warning****: The entered Applications user name is invalid');

END;

BEGIN

SELECT 1

INTO v_row_cnt

FROM hz_parties

WHERE UPPER(party_name) = UPPER('&INITIATOR_ORGANIZATION')

AND rownum <= 1;

EXCEPTION

WHEN no_data_found THEN

DBMS_OUTPUT.PUT_LINE('****Warning****: The entered Initiator Organization name does not exist');

WHEN others THEN

DBMS_OUTPUT.PUT_LINE('****Warning****: The entered Initiator Organization name is invalid');

END;

BEGIN

SELECT 1

INTO v_row_cnt

FROM hz_parties

WHERE UPPER(party_name) = UPPER('&RECIPIENT_ORGANIZATION')

AND rownum <= 1;

EXCEPTION

WHEN no_data_found THEN

DBMS_OUTPUT.PUT_LINE('****Warning****: The entered Recipient Organization name does not exist');

WHEN others THEN

DBMS_OUTPUT.PUT_LINE('****Warning****: The entered Recipient Organization name is invalid');

END;

BEGIN

SELECT 1

INTO v_row_cnt

FROM fun_trx_types_vl

WHERE UPPER(trx_type_name) LIKE nvl(UPPER('&Transaction_type'), '%')

AND rownum <= 1;

EXCEPTION

WHEN no_data_found THEN

DBMS_OUTPUT.PUT_LINE('****Warning****: The entered Transaction Type name does not exist');

WHEN others THEN

DBMS_OUTPUT.PUT_LINE('****Warning****: The entered Transaction Type is invalid');

END;

END;

/

SET FEEDBACK ON

PROMPT

PROMPT

REM*******************************************************************************************************************************

REM FUN Workflow Product License

REM*******************************************************************************************************************************

PROMPT Workflow Product License

PROMPT ========================

PROMPT

COLUMN name HEADING ’Name’ FORMAT A60

COLUMN wf_process_type HEADING 'WF Process|Type' FORMAT A20

COLUMN wf_process_name HEADING 'WF Process|Name' FORMAT A25

COLUMN licensed_flag HEADING 'Licensed|Flag' FORMAT A10

SELECT we.name,

wes.wf_process_type,

wes.wf_process_name,

wes.licensed_flag

FROM wf_events we,

wf_event_subscriptions wes

WHERE we.guid = wes.event_filter_guid

AND wes.wf_process_type LIKE 'FUN%'

/

PROMPT

PROMPT

REM*******************************************************************************************************************************

REM Financials Common Modules Product License

REM*******************************************************************************************************************************

PROMPT IC Product License

PROMPT ==================

PROMPT

COLUMN product_name HEADING 'Product|Name' FORMAT A20

COLUMN status HEADING 'Install|Status' FORMAT A10

SELECT app.application_short_name product_name,

DECODE(prod.status, 'I', 'Installed', 'S', 'Shared', 'Uninstalled') status

FROM fnd_product_installations prod,

fnd_application app

WHERE app.application_id = prod.application_id

AND app.application_short_name = 'FUN'

/

PROMPT

PROMPT

REM*******************************************************************************************************************************

REM AGIS System Options

REM*******************************************************************************************************************************

PROMPT AGIS System Options

PROMPT ===================

PROMPT

COLUMN intercompany_trx_currency HEADING ’Interco|Trxn|Currency’ FORMAT A10

COLUMN min_trx_amt HEADING 'Minimum|Trxn|Amount' FORMAT 99999999.99

COLUMN min_trx_amt_currency HEADING 'Currency' FORMAT A10

COLUMN exchangeratetypemeaning HEADING 'Exchnage|Rate' FORMAT A10

COLUMN allowrejectmeaning HEADING 'Allow|Reject' FORMAT A10

COLUMN glbatchmeaning HEADING 'GL|Batch|Mode' FORMAT A10

COLUMN aparbatchmeaning HEADING 'AP AR|Batch|Mode' FORMAT A10

COLUMN intercompany_batch_numbering HEADING 'Interco|Batch|Numbering' FORMAT A10

COLUMN inteco_calendar HEADING 'Interco|Calendar' FORMAT A10

COLUMN inteco_period_type HEADING 'Interco|Period|Type' FORMAT A10

SELECT s.default_currency intercompany_trx_currency,

s.min_trx_amt,

s.min_trx_amt_currency,

g.user_conversion_type exchangeratetypemeaning,

k1.meaning allowrejectmeaning,

k2.meaning glbatchmeaning,

k3.meaning aparbatchmeaning,

k4.meaning intercompany_batch_numbering,

s.inteco_calendar,

s.inteco_period_type

FROM fun_system_options s,

gl_daily_conversion_types g,

fnd_lookups k1,

fnd_lookup_values k2,

fnd_lookup_values k3,

fnd_lookup_values k4,

hz_parties hzp,

fnd_user fnduser

WHERE s.exchg_rate_type = g.conversion_type

AND hzp.party_id = fnduser.person_party_id

AND k1.lookup_type = 'YES_NO'

AND k1.lookup_code = s.allow_reject_flag

AND k2.lookup_type = 'FUN_TRANSFER_OPTIONS'

AND k2.lookup_code = s.gl_batch_flag

AND k3.lookup_type = 'FUN_TRANSFER_OPTIONS'

AND k3.lookup_code = s.apar_batch_flag

AND k4.lookup_type = 'FUN_NUMBERING_TYPES'

AND k4.lookup_code = s.numbering_type

AND k2.LANGUAGE = userenv('LANG')

AND k3.LANGUAGE = userenv('LANG')

AND k4.LANGUAGE = userenv('LANG')

AND k2.view_application_id = 435

AND k3.view_application_id = 435

AND k4.view_application_id = 435

AND k2.security_group_id = fnd_global.lookup_security_group(k2.lookup_type, 435)

AND k3.security_group_id = fnd_global.lookup_security_group(k3.lookup_type, 435)

AND k4.security_group_id = fnd_global.lookup_security_group(k4.lookup_type, 435)

AND s.system_option_id = 0

AND UPPER(fnduser.user_name) LIKE UPPER('&USER_NAME')

/

PROMPT

PROMPT

REM*******************************************************************************************************************************

REM IntraCompany Rules Defined for the ledgers

REM*******************************************************************************************************************************

PROMPT IntraCompany Rules

PROMPT ==================

PROMPT

COLUMN ledger_name HEADING 'Ledger Name' FORMAT A30

COLUMN ledger_id HEADING 'Ledger ID' FORMAT 999999

COLUMN Template_id HEADING 'Template ID' FORMAT 999999

COLUMN JE_SOURCE_NAME HEADING 'Source Name' FORMAT A20

COLUMN JE_CATEGORY_NAME HEADING 'Category Name' FORMAT A20

COLUMN STATUS_flag HEADING 'Status' FORMAT A10

COLUMN DR_BSV HEADING 'Debit BSV' FORMAT A30 WRAP

COLUMN CR_BSV HEADING 'Credit BSV' FORMAT A30 WRAP

COLUMN DR_CCID HEADING 'Debit CCID' FORMAT 999999999

COLUMN DR_Combination HEADING 'Debit|Code Combination' FORMAT A50 WRAP

COLUMN CR_CCID HEADING 'Credit CCID' FORMAT 999999999

COLUMN CR_COMBINATION HEADING 'Credit|CodeCombination' FORMAT A50 WRAP

SELECT gl.ledger_name, gl.ledger_id, fbp.template_id,

fbp.je_source_name,

fbp.je_category_name,

flv.meaning status_flag,

decode(fba.dr_bsv, 'OTHER1234567890123456789012345', 'All Other', fba.dr_bsv) DR_BSV,

decode(fba.cr_bsv, 'OTHER1234567890123456789012345', 'All Other', fba.cr_bsv) CR_BSV,

fba.dr_ccid,

glkfv1.concatenated_segments dr_combination,

fba.cr_ccid,

glkfv2.concatenated_segments cr_combination

FROM gl_code_combinations_kfv glkfv1,

gl_code_combinations_kfv glkfv2,

fun_balance_accounts fba,

fun_balance_options fbp,

fnd_lookup_values flv,

hz_parties hzp, hz_relationships HZ_LE,

xle_entity_profiles XLE,

gl_ledger_le_v gl

where hzp.party_type='ORGANIZATION'

AND hzp.PARTY_NAME ='&RECIPIENT_ORGANIZATION'

AND HZ_LE.relationship_code='INTERCOMPANY_ORGANIZATION_OF'

AND HZ_LE.relationship_type='INTERCOMPANY_LEGAL_ENTITY'

AND hz_le.object_type='ORGANIZATION'

AND HZ_LE.SUBJECT_ID=HZP.PARTY_ID

AND XLE.PARTY_ID=HZ_LE.OBJECT_ID

AND xle.legal_entity_id=gl.legal_entity_id

AND gl.ledger_category_code='PRIMARY'

and fbp.template_id = fba.template_id

AND fba.dr_ccid = glkfv1.code_combination_id

AND fba.cr_ccid = glkfv2.code_combination_id

AND flv.view_application_id = 435

AND flv.lookup_code = fbp.status_flag

AND flv.LANGUAGE = userenv('LANG')

AND flv.lookup_type = 'FUN_OPTION_STATUS'

AND flv.security_group_id = fnd_global.lookup_security_group(flv.lookup_type, 435)

AND fbp.ledger_id = gl.ledger_id

union

SELECT gl.ledger_name, gl.ledger_id, fbp.template_id,

fbp.je_source_name,

fbp.je_category_name,

flv.meaning status_flag,

fba.dr_bsv,

fba.cr_bsv,

fba.dr_ccid,

glkfv1.concatenated_segments dr_combination,

fba.cr_ccid,

glkfv2.concatenated_segments cr_combination

FROM gl_code_combinations_kfv glkfv1,

gl_code_combinations_kfv glkfv2,

fun_balance_accounts fba,

fun_balance_options fbp,

fnd_lookup_values flv,

hz_parties hzp, hz_relationships HZ_LE,

xle_entity_profiles XLE,

gl_ledger_le_v gl

where hzp.party_type='ORGANIZATION'

AND hzp.PARTY_NAME ='&INITIATOR_ORGANIZATION'

AND HZ_LE.relationship_code='INTERCOMPANY_ORGANIZATION_OF'

AND HZ_LE.relationship_type='INTERCOMPANY_LEGAL_ENTITY'

AND hz_le.object_type='ORGANIZATION'

AND HZ_LE.SUBJECT_ID=HZP.PARTY_ID

AND XLE.PARTY_ID=HZ_LE.OBJECT_ID

and xle.legal_entity_id=gl.legal_entity_id

and gl.ledger_category_code='PRIMARY'

and fbp.template_id = fba.template_id

AND fba.dr_ccid = glkfv1.code_combination_id

AND fba.cr_ccid = glkfv2.code_combination_id

AND flv.view_application_id = 435

AND flv.lookup_code = fbp.status_flag

AND flv.LANGUAGE = userenv('LANG')

AND flv.lookup_type = 'FUN_OPTION_STATUS'

AND flv.security_group_id = fnd_global.lookup_security_group(flv.lookup_type, 435)

AND fbp.ledger_id = gl.ledger_id

/

PROMPT

PROMPT

REM*******************************************************************************************************************************

REM Intercompany Rules Defined for Initiator and Recipient Legal Entities

REM*******************************************************************************************************************************

PROMPT InterCompany Rules

PROMPT ==================

PROMPT

COLUMN transacting_legal_entity HEADING 'Transacting|Legal|Entity ID' FORMAT 999999999

COLUMN transacting_le_name HEADING 'Transacting|Legal|Entity Name' FORMAT A30

COLUMN transacting_ledger HEADING 'Transacting|Ledger ID' FORMAT 999999999

COLUMN trasacting_ledger_name HEADING 'Transacting|Ledger Name' FORMAT A30

COLUMN trading_partner_le HEADING 'Trading|Partner|Legal ID' FORMAT 999999999

COLUMN trading_le_name HEADING 'Trading|Partner|Legal Name' FORMAT A20

COLUMN trading_ledger HEADING 'Trading|Ledger' FORMAT 999999999

COLUMN trading_ledger_name HEADING 'Trading|Ledger Name' FORMAT A30

COLUMN ccid Heading 'Code|Combination ID' FORMAT 999999999

COLUMN account_type Heading 'Interco|Account Type' FORMAT A30

COLUMN start_date HEADING 'Start Date' FORMAT A15

COLUMN end_date Heading 'End Date' FORMAT A15

COLUMN trans_bsv HEADING 'Transacting|BSV' FORMAT A15

COLUMN tp_bsv HEADING 'Trading|BSV' FORMAT A15

COLUMN concatenated_segments HEADING 'Code Combinations' FORMAT A30 WRAP

COLUMN transacting_legal_entity HEADING 'Transacting|Legal|Entity ID' FORMAT 999999999

COLUMN transacting_le_name HEADING 'Transacting|Legal|Entity Name' FORMAT A30

COLUMN transacting_ledger HEADING 'Transacting|Ledger ID' FORMAT 999999999

COLUMN trasacting_ledger_name HEADING 'Transacting|Ledger Name' FORMAT A30

COLUMN trading_partner_le HEADING 'Trading|Partner|Legal ID' FORMAT 999999999

COLUMN trading_le_name HEADING 'Trading|Partner|Legal Name' FORMAT A20

COLUMN trading_ledger HEADING 'Trading|Ledger' FORMAT 999999999

COLUMN trading_ledger_name HEADING 'Trading|Ledger Name' FORMAT A30

COLUMN ccid Heading 'Code|Combination ID' FORMAT 999999999

COLUMN account_type Heading 'Interco|Account Type' FORMAT A30

COLUMN start_date HEADING 'Start Date' FORMAT A15

COLUMN end_date Heading 'End Date' FORMAT A15

COLUMN trans_bsv HEADING 'Transacting|BSV' FORMAT A30

COLUMN tp_bsv HEADING 'Trading|BSV' FORMAT A30

COLUMN concatenated_segments HEADING 'Code Combinations' FORMAT A30 WRAP

SELECT interaccteo.from_le_id transacting_legal_entity,

gll1.legal_entity_name transacting_le_name,

interaccteo.ledger_id transacting_ledger,

gll1.ledger_name trasacting_ledger_name,

decode(interaccteo.to_le_id, '-99', 'All Other', interaccteo.to_le_id) trading_partner_le,

gll2.legal_entity_name trading_le_name,

gll2.ledger_id trading_ledger,

gll2.ledger_name trading_ledger_name,

interaccteo.ccid,

decode(interaccteo.type, 'P', 'Intercompany Payables', 'R', 'Intercompany Receivables', 'N') account_type,

interaccteo.start_date,

interaccteo.end_date,

decode(trans_bsv, 'OTHER1234567890123456789012345', 'All Other', trans_bsv) trans_bsv,

decode(tp_bsv, 'OTHER1234567890123456789012345', 'All Other', tp_bsv) tp_bsv,

glkfv.concatenated_segments

FROM fun_inter_accounts interaccteo,

gl_code_combinations_kfv glkfv,

gl_ledger_le_v gll1,

gl_ledger_le_v gll2

WHERE interaccteo.from_le_id IN (select XLE.LEGAL_ENTITY_ID from hz_parties hzp, hz_relationships HZ_LE, xle_entity_profiles XLE

where hzp.party_type='ORGANIZATION'

AND upper(hzp.PARTY_NAME)=upper('&INITIATOR_ORGANIZATION')

AND HZ_LE.relationship_code='INTERCOMPANY_ORGANIZATION_OF'

AND HZ_LE.relationship_type='INTERCOMPANY_LEGAL_ENTITY'

AND hz_le.object_type='ORGANIZATION'

AND HZ_LE.SUBJECT_ID=HZP.PARTY_ID

AND XLE.PARTY_ID=HZ_LE.OBJECT_ID)

AND (interaccteo.to_le_id IN (select XLE.LEGAL_ENTITY_ID from hz_parties hzp, hz_relationships HZ_LE, xle_entity_profiles XLE

where hzp.party_type='ORGANIZATION'

AND upper(hzp.PARTY_NAME) =upper('&RECIPIENT_ORGANIZATION')

AND HZ_LE.relationship_code='INTERCOMPANY_ORGANIZATION_OF'

AND HZ_LE.relationship_type='INTERCOMPANY_LEGAL_ENTITY'

AND hz_le.object_type='ORGANIZATION'

AND HZ_LE.SUBJECT_ID=HZP.PARTY_ID

AND XLE.PARTY_ID=HZ_LE.OBJECT_ID)

or interaccteo.to_le_id=-99)

AND gll2.legal_entity_id(+) = interaccteo.to_le_id

AND gll1.legal_entity_id(+) = interaccteo.from_le_id

AND gll1.ledger_category_code(+) = 'PRIMARY'

AND gll2.ledger_category_code(+)= 'PRIMARY'

AND interaccteo.ccid = glkfv.code_combination_id

union

SELECT interaccteo.from_le_id transacting_legal_entity,

gll1.legal_entity_name transacting_le_name,

interaccteo.ledger_id transacting_ledger,

gll1.ledger_name trasacting_ledger_name,

decode(interaccteo.to_le_id, '-99', 'All Other', interaccteo.to_le_id) trading_partner_le,

gll2.legal_entity_name trading_le_name,

gll2.ledger_id trading_ledger,

gll2.ledger_name trading_ledger_name,

interaccteo.ccid,

decode(interaccteo.type, 'P', 'Intercompany Payables', 'R', 'Intercompany Receivables', 'N') account_type,

interaccteo.start_date,

interaccteo.end_date,

decode(trans_bsv, 'OTHER1234567890123456789012345', 'All Other', trans_bsv) trans_bsv,

decode(tp_bsv, 'OTHER1234567890123456789012345', 'All Other', tp_bsv) tp_bsv,

glkfv.concatenated_segments

FROM fun_inter_accounts interaccteo,

gl_code_combinations_kfv glkfv,

gl_ledger_le_v gll1,

gl_ledger_le_v gll2

WHERE interaccteo.from_le_id IN (select XLE.LEGAL_ENTITY_ID from hz_parties hzp, hz_relationships HZ_LE, xle_entity_profiles XLE

where hzp.party_type='ORGANIZATION'

AND upper(hzp.PARTY_NAME) = upper('&RECIPIENT_ORGANIZATION')

AND HZ_LE.relationship_code='INTERCOMPANY_ORGANIZATION_OF'

AND HZ_LE.relationship_type='INTERCOMPANY_LEGAL_ENTITY'

AND hz_le.object_type='ORGANIZATION'

AND HZ_LE.SUBJECT_ID=HZP.PARTY_ID

AND XLE.PARTY_ID=HZ_LE.OBJECT_ID)

and (interaccteo.to_le_id=-99 or interaccteo.to_le_id IN(select XLE.LEGAL_ENTITY_ID from hz_parties hzp, hz_relationships HZ_LE, xle_entity_profiles XLE

where hzp.party_type='ORGANIZATION'

AND upper(hzp.PARTY_NAME) = upper('&INITIATOR_ORGANIZATION')

AND HZ_LE.relationship_code='INTERCOMPANY_ORGANIZATION_OF'

AND HZ_LE.relationship_type='INTERCOMPANY_LEGAL_ENTITY'

AND hz_le.object_type='ORGANIZATION'

AND HZ_LE.SUBJECT_ID=HZP.PARTY_ID

AND XLE.PARTY_ID=HZ_LE.OBJECT_ID)

)

AND gll2.legal_entity_id(+) = interaccteo.to_le_id

AND gll1.legal_entity_id (+)= interaccteo.from_le_id

AND gll1.ledger_category_code(+) = 'PRIMARY'

AND gll2.ledger_category_code(+)= 'PRIMARY'

AND interaccteo.ccid = glkfv.code_combination_id

/

PROMPT

PROMPT

REM*******************************************************************************************************************************

REM Initiator Organization and its Legal Entity and Operating Unit Details

REM*******************************************************************************************************************************

PROMPT Initiator Organization and Legal Entity Details

PROMPT ===============================================

PROMPT

COLUMN ORGID HEADING 'Org ID' FORMAT 999999

COLUMN ORGNAME HEADING 'Intercompany|Org' FORMAT A15

COLUMN LEID HEADING 'Legal|Entity ID' FORMAT 999999999

COLUMN LENAME HEADING 'Legal|Entity Name' FORMAT A30

COLUMN OUID HEADING 'Operating|Unit ID' FORMAT 9999999

COLUMN OUNAME HEADING 'Operating|Unit Name' FORMAT A30

COLUMN INTERCOMPANYFLAG HEADING 'Intercompany|Status' FORMAT A10

COLUMN status HEADING 'Status' FORMAT A10

SELECT distinct hzp.party_id orgid,

hzp.party_name orgname,

le.legal_entity_id leid,

le.name lename,

hzr_ou.subject_id ouid,

hou.name ouname,

decode(nvl(hzusg.status_flag, 'N'), 'A', 'Y', 'N') intercompanyflag,

arl.meaning status

FROM hz_parties hzp,

hz_party_usg_assignments hzusg,

hz_relationships hzr_ou,

hr_operating_units hou,

hz_relationships hzr_le,

hz_parties hzp_le,

xle_entity_profiles le,

ar_lookups arl

WHERE hzp.party_type = 'ORGANIZATION'

AND hzr_ou.object_id(+) = hzp.party_id

AND hzr_ou.subject_table_name(+) = 'HR_ALL_ORGANIZATION_UNITS'

AND hzr_ou.object_table_name(+) = 'HZ_PARTIES'

AND hzr_ou.relationship_type(+) = 'INTERCOMPANY_OPERATING_UNIT'

AND hzr_ou.relationship_code(+) = 'OPERATING_UNIT_OF'

AND hzr_ou.directional_flag(+) = 'B'

AND hzr_ou.status(+) = 'A'

AND TRUNC(hzr_ou.start_date(+)) <= TRUNC(sysdate)

AND TRUNC(nvl(hzr_ou.end_date(+), sysdate)) >= TRUNC(sysdate)

AND hou.organization_id(+) = hzr_ou.subject_id

--AND le.party_id(+) = hzp.party_id

AND le.party_id(+) = hzr_le.object_id

AND hzr_le.subject_id(+) = hzp.party_id

AND hzr_le.subject_table_name(+) = 'HZ_PARTIES'

AND hzr_le.object_table_name(+) = 'HZ_PARTIES'

AND hzr_le.relationship_code(+) = 'INTERCOMPANY_ORGANIZATION_OF'

AND hzr_le.relationship_type(+) = 'INTERCOMPANY_LEGAL_ENTITY'

AND hzr_le.directional_flag(+) = 'F'

AND hzr_le.status(+) = 'A'

AND TRUNC(hzr_le.start_date(+)) <= TRUNC(sysdate)

AND TRUNC(hzr_le.end_date(+)) >= TRUNC(sysdate)

AND hzusg.party_id(+) = hzp.party_id

AND hzusg.party_usage_code(+) = 'INTERCOMPANY_ORG'

AND arl.lookup_code = hzp.status

AND arl.lookup_type = 'REGISTRY_STATUS'

AND UPPER(hzp.party_name) = UPPER('&INITIATOR_ORGANIZATION')

/

PROMPT

PROMPT

REM*******************************************************************************************************************************

REM Recipient Organization and its Legal Entity and Operating Unit Details

REM*******************************************************************************************************************************

PROMPT Recipient Organization and Legal Entity Details

PROMPT ===============================================

PROMPT

COLUMN ORGID HEADING 'Org ID' FORMAT 999999

COLUMN ORGNAME HEADING 'Intercompany|Org' FORMAT A15

COLUMN LEID HEADING 'Legal|Entity ID' FORMAT 999999999

COLUMN LENAME HEADING 'Legal|Entity Name' FORMAT A30

COLUMN OUID HEADING 'Operating|Unit ID' FORMAT 9999999

COLUMN OUNAME HEADING 'Operating|Unit Name' FORMAT A30

COLUMN INTERCOMPANYFLAG HEADING 'Intercompany|Status' FORMAT A10

COLUMN status HEADING 'Status' FORMAT A10

SELECT distinct hzp.party_id orgid,

hzp.party_name orgname,

le.legal_entity_id leid,

le.name lename,

hzr_ou.subject_id ouid,

hou.name ouname,

decode(nvl(hzusg.status_flag, 'N'), 'A', 'Y', 'N') intercompanyflag,

arl.meaning status

FROM hz_parties hzp,

hz_party_usg_assignments hzusg,

hz_relationships hzr_ou,

hr_operating_units hou,

hz_relationships hzr_le,

hz_parties hzp_le,

xle_entity_profiles le,

ar_lookups arl

WHERE hzp.party_type = 'ORGANIZATION'

AND hzr_ou.object_id(+) = hzp.party_id

AND hzr_ou.subject_table_name(+) = 'HR_ALL_ORGANIZATION_UNITS'

AND hzr_ou.object_table_name(+) = 'HZ_PARTIES'

AND hzr_ou.relationship_type(+) = 'INTERCOMPANY_OPERATING_UNIT'

AND hzr_ou.relationship_code(+) = 'OPERATING_UNIT_OF'

AND hzr_ou.directional_flag(+) = 'B'

AND hzr_ou.status(+) = 'A'

AND TRUNC(hzr_ou.start_date(+)) <= TRUNC(sysdate)

AND TRUNC(nvl(hzr_ou.end_date(+), sysdate)) >= TRUNC(sysdate)

AND hou.organization_id(+) = hzr_ou.subject_id

--AND le.party_id(+) = hzp.party_id

AND le.party_id(+) = hzr_le.object_id

AND hzr_le.subject_id(+) = hzp.party_id

AND hzr_le.subject_table_name(+) = 'HZ_PARTIES'

AND hzr_le.object_table_name(+) = 'HZ_PARTIES'

AND hzr_le.relationship_code(+) = 'INTERCOMPANY_ORGANIZATION_OF'

AND hzr_le.relationship_type(+) = 'INTERCOMPANY_LEGAL_ENTITY'

AND hzr_le.directional_flag(+) = 'F'

AND hzr_le.status(+) = 'A'

AND TRUNC(hzr_le.start_date(+)) <= TRUNC(sysdate)

AND TRUNC(hzr_le.end_date(+)) >= TRUNC(sysdate)

AND hzusg.party_id(+) = hzp.party_id

AND hzusg.party_usage_code(+) = 'INTERCOMPANY_ORG'

AND arl.lookup_code = hzp.status

AND arl.lookup_type = 'REGISTRY_STATUS'

AND UPPER(hzp.party_name) = UPPER('&RECIPIENT_ORGANIZATION')

/

PROMPT

PROMPT

REM*******************************************************************************************************************************

REM Inter Company Transaction Type Details

REM*******************************************************************************************************************************

PROMPT Transaction Type Details

PROMPT ========================

PROMPT

COLUMN TRX_TYPE_ID HEADING 'Transaction|Type ID' FORMAT 999999

COLUMN TRX_TYPE_NAME HEADING 'Transaction|Type Name' FORMAT A30

COLUMN allowinvoicing HEADING 'Allow|Invoicing' FORMAT A15

COLUMN manualapproval HEADING 'Manual|Approval' FORMAT A15

COLUMN period_year HEADING 'Period|Year' FORMAT 999999

COLUMN period_name HEADING 'Period|Name' FORMAT A15

COLUMN status HEADING 'Period|Status' FORMAT A15

COLUMN inteco_calendar HEADING 'Interco|Calendar' FORMAT A15

COLUMN start_date HEADING 'Start Date' FORMAT A15

COLUMN end_date HEADING 'End Date' FORMAT A15

SELECT ftt.trx_type_id,

ftt.trx_type_name,

lkup1.meaning allowinvoicing,

lkup2.meaning ManualApproval ,

fps.period_year,

fps.period_name,

fps.start_date,

fps.end_date,

lkup3.meaning status,

fps.inteco_calendar

FROM fun_trx_types_vl ftt,

fun_period_statuses fps,

fnd_lookup_values lkup1,

fnd_lookup_values lkup2,

fnd_lookup_values lkup3,

fun_system_options fso

WHERE ftt.trx_type_id = fps.trx_type_id

AND fps.inteco_calendar = fso.inteco_calendar

AND fso.inteco_period_type = fps.inteco_period_type

AND fps.status = lkup3.lookup_code

AND ftt.allow_invoicing_flag = lkup1.lookup_code

AND ftt.manual_approve_flag =lkup2.lookup_code

AND lkup3.view_application_id = 435

AND lkup3.lookup_type = 'FUN_PERIOD_STATUS'

AND lkup1.lookup_type ='FUN_REQUIRED_TYPE'

AND lkup2.lookup_type ='FUN_REQUIRED_TYPE'--AND lkup.SECURITY_GROUP_ID = fnd_global.lookup_security_group(lkup.LOOKUP_TYPE,435)

AND lkup3.LANGUAGE = userenv('LANG')

AND lkup1.language =userenv('LANG')

AND lkup2.LANGUAGE =userenv('LANG')

AND UPPER(ftt.trx_type_name) LIKE UPPER('&Transaction_type%')

AND fps.status = 'O'

ORDER BY trx_type_name,

start_date,

period_name

/

PROMPT

PROMPT

REM*******************************************************************************************************************************

REM Supplier Association Details

REM*******************************************************************************************************************************

PROMPT Supplier Details

PROMPT ================

PROMPT

COLUMN relation_id HEADING 'Relation ID' FORMAT 999999999

COLUMN tr_ledger HEADING 'Transaction | Ledger Name' FORMAT A30

COLUMN Transaction_LE_ID HEADING 'Transaction|LE ID' FORMAT 999999999

COLUMN Transaction_LE_Name HEADING 'Transaction|LE Name' FORMAT A30

COLUMN Transaction_OU HEADING 'Transaction|OU ID' FORMAT 999999999

COLUMN Transaction_OU_Name HEADING 'Transaction|OU Name' FORMAT A30

COLUMN Transaction_IC_Org_ID HEADING 'Transaction|IC Org ID' FORMAT 999999999

COLUMN Transaction_IC_Name HEADING 'Transaction|IC Org Name' FORMAT A30

COLUMN tp_ledger HEADING 'Trading Partner|Ledger Name' FORMAT A30

COLUMN Trading_LE_ID HEADING 'Trading|LE ID' FORMAT 999999999

COLUMN Trading_LE_Name HEADING 'Trading|LE Name' FORMAT A30

COLUMN Trading_OU HEADING 'Trading|OU ID' FORMAT 999999999

COLUMN Trading_OU_Name HEADING 'Trading|OU Name' FORMAT A30

COLUMN Trading_IC_ORG_ID HEADING 'Trading|IC Org ID' FORMAT 999999999

COLUMN Trading_IC_Name HEADING 'Trading|IC Org Name' FORMAT A30

COLUMN vendor_id HEADING 'Vendor|ID' FORMAT 999999999

COLUMN vendor_name HEADING 'Supplier|Name' FORMAT A30

COLUMN vendor_site_id HEADING 'Vendor|Site ID' FORMAT 999999999

COLUMN vendor_site_code HEADING 'Vendor|Site Code' FORMAT A30

select RT.relation_id,

TRL.LEDGER_NAME tr_ledger,

RT.transaction_le_id,

trl.legal_entity_name Transaction_LE_Name,

RT.TRANSACTION_ORG_ID,

ou.name Transaction_OU_Name,

RT.TRANSACTION_ORGANIZATION_ID Transaction_IC_Org_ID,

ip.party_name Transaction_IC_Name,

TPL.LEDGER_NAME tp_ledger,

RT.TP_LE_ID Trading_LE_ID,

TPL.LEGAL_ENTITY_NAME Trading_LE_Name,

RT.TP_ORG_ID Trading_OU,

ou1.name Trading_OU_Name,

RT.TP_ORGANIZATION_ID Trading_IC_ORG_ID,

rp.party_name Trading_IC_Name,

sm.vendor_id,

vo.vendor_name,

sm.vendor_site_id,

vos.vendor_site_code

from fun_trade_relations rt,

hz_parties ip,

hz_parties rp,

GL_LEDGER_LE_V trl,

gl_ledger_le_v tpl,

fun_supplier_maps sm,

po_vendors vo,

PO_vendor_sites_all vos,

hr_operating_units ou,

hr_operating_units ou1

where rt.transaction_organization_id=ip.party_id

and rt.tp_organization_id=rp.party_id

and ip.party_type='ORGANIZATION'

and rp.party_type='ORGANIZATION'

AND UPPER(iP.PARTY_NAME) = UPPER('&INITIATOR_ORGANIZATION')

and UPPER(rP.PARTY_NAME) = UPPER('&RECIPIENT_ORGANIZATION')

and rt.transaction_le_id=trl.legal_entity_id

and rt.tp_le_id=tpl.legal_entity_id

and rt.relation_id=sm.relation_id

and vo.vendor_id=sm.vendor_id

and sm.vendor_site_id=vos.vendor_site_id

and vo.vendor_id=voS.vendor_id

AND ou.organization_id = rt.transaction_org_id

and ou1.organization_id = rt.tp_org_id

/

PROMPT

PROMPT

REM*******************************************************************************************************************************

REM Customer Association Details

REM*******************************************************************************************************************************

PROMPT Customer Details

PROMPT ================

PROMPT

COLUMN relation_id HEADING 'Relation ID' FORMAT 999999999

COLUMN ledger_name HEADING ' Transaction|Ledger Name' FORMAT A30

COLUMN transaction_le_id HEADING 'Transaction|LE ID' FORMAT 999999999

COLUMN legal_entity_name HEADING 'Transaction|LE Name' FORMAT A30

COLUMN Transaction_OU HEADING 'Transaction|OU ID' FORMAT 999999999

COLUMN Transaction_OU_Name HEADING 'Transaction|OU Name' FORMAT A30

COLUMN Transaction_IC_Org_ID HEADING 'Transaction|IC Org ID' FORMAT 999999999

COLUMN Transaction_IC_Name HEADING 'Transaction|IC Org Name' FORMAT A30

COLUMN tp_ledger_name HEADING 'Trading Partner|Ledger Name' FORMAT A30

COLUMN Trading_LE_ID HEADING 'Trading|LE ID' FORMAT 999999999

COLUMN Trading_LE_Name HEADING 'Trading|LE Name' FORMAT A30

COLUMN Trading_IC_ORG_ID HEADING 'Trading|IC Org ID' FORMAT 999999999

COLUMN Trading_IC_Name HEADING 'Trading|IC Org Name' FORMAT A30

COLUMN Trading_OU HEADING 'Trading|OU ID' FORMAT 999999999

COLUMN Trading_OU_Name HEADING 'Trading|OU Name' FORMAT A30

COLUMN PARTY_NAME heading 'Trading Partner|Org Name' FORMAT A35

COLUMN customer_number HEADING 'Customer|Number' FORMAT 999999999

COLUMN account_name HEADING 'Customer|Name' FORMAT A30

COLUMN location HEADING 'Customer|Location' FORMAT A30

select RT.relation_id,

TRL.LEDGER_NAME,

RT.transaction_le_id,

trl.legal_entity_name,

RT.TRANSACTION_ORG_ID transaction_OU,

ou.name Transaction_OU_Name,

RT.TRANSACTION_ORGANIZATION_ID Transaction_IC_Org_ID,

ip.party_name Transaction_IC_Name,

TPL.LEDGER_NAME tp_ledger_name,

RT.TP_LE_ID Trading_LE_ID,

TPL.LEGAL_ENTITY_NAME Trading_LE_Name,

RT.TP_ORG_ID Trading_OU,

ou1.name Trading_OU_Name,

RT.TP_ORGANIZATION_ID Trading_IC_ORG_ID,

RP.PARTY_NAME,

ca.account_number customer_number,

ca.account_name,

u.location

from fun_trade_relations rt,

hz_parties ip,

hz_parties rp,

GL_LEDGER_LE_V trl,

gl_ledger_le_v tpl,

fun_customer_maps cm,

hz_cust_accounts ca,

hr_operating_units ou,

hr_operating_units ou1,

hz_cust_site_uses_all u

where rt.transaction_organization_id=ip.party_id

and rt.tp_organization_id=rp.party_id

and ip.party_type='ORGANIZATION'

and rp.party_type='ORGANIZATION'

AND UPPER(iP.PARTY_NAME) = UPPER('&RECIPIENT_ORGANIZATION')

and UPPER(rP.PARTY_NAME) = UPPER('&INITIATOR_ORGANIZATION')

and rt.transaction_le_id=trl.legal_entity_id

and rt.tp_le_id=tpl.legal_entity_id

and rt.relation_id=cm.relation_id

and ca.cust_account_id =cm.cust_account_id

AND ou.organization_id = rt.transaction_org_id

and cm.site_use_id=u.site_use_id

and ou1.organization_id = rt.tp_org_id

/

PROMPT

PROMPT Please provide the file &output_path for the review.

PROMPT

CLEAR COLUMNS

CLEAR BUFFER

TTITLE OFF

SPOOL OFF;

select * from WF_ITEM_ACTIVITY_STATUSES

--where item_key like ''

--where activity_result_code='FUNIMAIN'

where item_key like '%78098%'

Select *--trxH.trx_id, trxH.trx_number, trxH.status

from fun_trx_headers trxH, fun_trx_batches trxB

Where trxB.batch_id = trxH.batch_id

and trxB.batch_number = '42'

select * from wf_local_user_roles role

where role_name like 'FUN_ADHOC_RECI_%' --where role.role_name like 'FUN_ADHOC_RECI_||42';

SELECT distinct fu.employee_id PERSON_ID,

p.PARTY_NAME PERSON_NAME,

pap.EFFECTIVE_START_DATE PERSON_START_DATE,

pap.EFFECTIVE_END_DATE PERSON_END_DATE,

fu.USER_ID USER_ID,

fu.USER_NAME USER_NAME,

fu.START_DATE USER_START_DATE,

fu.END_DATE USER_END_DATE

FROM hz_parties p,

fnd_user fu,

fun_trx_headers ftrx,

hz_relationships hzr,

hz_org_contacts hc,

hz_org_contact_roles hcr,

PER_ALL_PEOPLE_F PAP

WHERE p.party_type = 'PERSON'

AND p.party_id = hzr.subject_id

AND hzr.object_id = ftrx.initiator_id

AND hzr.relationship_code = 'CONTACT_OF'

AND hzr.relationship_type = 'CONTACT'

AND hzr.directional_flag = 'F'

AND hzr.subject_table_name = 'HZ_PARTIES'

AND hzr.object_table_name = 'HZ_PARTIES'

AND hzr.subject_type = 'PERSON'

AND hc.party_relationship_id = hzr.relationship_id

AND hcr.org_contact_id = hc.org_contact_id

AND hcr.role_type = 'INTERCOMPANY_CONTACT_FOR'

AND fu.person_party_id = p.party_id

AND sysdate BETWEEN

nvl(hzr.start_date, sysdate -1)

AND nvl(hzr.end_date, sysdate + 1)

-- AND ftrx.trx_id = <trx_id>

AND hzr.ADDITIONAL_INFORMATION1 = 'Y'

AND sysdate BETWEEN

nvl(fu.start_date, sysdate -1)

AND nvl(fu.end_date, sysdate + 1)

AND PAP.PERSON_ID=FU.EMPLOYEE_ID(+)

AND P.PARTY_ID=PAP.PARTY_ID

ORDER BY PERSON_NAME, USER_NAME;

select rowid,fu.*

from fnd_user fu

where user_name like '%AGIS%'

select we.name, wes.wf_process_type, wes.wf_process_name,

wes.licensed_flag

from wf_events we, wf_event_subscriptions wes

where we.guid = wes.event_filter_guid

and wes.wf_process_type like 'FUN%';

select app.APPLICATION_SHORT_NAME,

prod.status

from fnd_product_installations prod,

fnd_application app

where app.application_id = prod.application_id

and app.APPLICATION_SHORT_NAME = 'FUN';

select *

from dba_objects

select text from all_source where name = 'FUN_WF_COMMON' and text like '%Header: %';

Patch 9235651:R12.FUN.B

Patch 10183867:R12.FUN.B.

select *

from ad_bugs

--where bug_number --in ('9235651','10183867','12619394')

SELECT fnd_application.application_short_name, fnd_application.application_id, fnd_product_installations.status

FROM fnd_application,fnd_product_installations

WHERE fnd_application.application_id = fnd_product_installations.application_id;

select owner_tag, name, licensed_flag

from wf_events

where name like 'oracle.apps.fun%'

--and licensed_flag <>'Y';

select owner_tag, wf_process_type, licensed_flag from wf_event_subscriptions

where wf_process_type like 'FUN%';

select * from wf_events

where name like 'oracle.apps.fun%'--where application_id =

and owne

select *

from dba_objects

where status ='INVALID'

select *

from ad_bugs

where bug_number='6526807'

select distinct gjh.NAME JOURNAL_NAME

,gjb.NAME BATCH_NAME

,gjh.JE_SOURCE SOURCE

,gjh.JE_CATEGORY CATEGORY

,gjh.currency_code

,gcc.SEGMENT1||'.'||gcc.SEGMENT2||'.'||gcc.SEGMENT3||'.'||

gcc.SEGMENT4||'.'||gcc.SEGMENT5||'.'||gcc.SEGMENT6||'.'||

gcc.SEGMENT7||'.'||gcc.SEGMENT8 ACCOUNT--||'.'||gcc.SEGMENT9 ACCOUNT

,NVL(gjl.ENTERED_DR,0) DEBIT_VALAS

,NVL(gjl.ENTERED_CR,0) CREDIT_VALAS

,NVL(gjl.ACCOUNTED_DR,0) DEBIT

,NVL(gjl.ACCOUNTED_CR,0) CREDIT

,xah.EVENT_TYPE_CODE

,xal.ACCOUNTING_CLASS_CODE

,NVL(xal.ENTERED_DR,0) DEBIT_VALAS_XLA

,NVL(xal.ENTERED_CR,0) CREDIT_VALAS_XLA

,NVL(xal.ACCOUNTED_DR,0) DEBIT_XLA

,NVL(xal.ACCOUNTED_CR,0) CREDIT_XLA

-- ,aia.INVOICE_NUM

-- ,aia.INVOICE_AMOUNT

-- ,nvl(aia.EXCHANGE_RATE,1) KURS

-- ,XAL.GL_SL_LINK_ID

from gl_je_headers gjh

,gl_je_lines gjl

,gl_je_batches gjb

,gl_import_references gir

,gl_code_combinations gcc

,xla_ae_lines xal

,xla_ae_headers xah

-- ,ap_invoice_distributions_all aida

-- ,ap_invoices_all aia

where gjh.JE_HEADER_ID = gjl.JE_HEADER_ID

and gjh.JE_BATCH_ID = gjb.JE_BATCH_ID

and gjh.JE_HEADER_ID = gir.JE_HEADER_ID

and gjl.JE_LINE_NUM = gir.JE_LINE_NUM

and gjl.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID

-- and gir.GL_SL_LINK_ID = xal.GL_SL_LINK_ID

-- and gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE

-- and xal.AE_HEADER_ID = xah.AE_HEADER_ID

-- and xal.APPLICATION_ID = xah.APPLICATION_ID

-- and xah.EVENT_ID = aida.ACCOUNTING_EVENT_ID

-- and aida.INVOICE_ID = aia.invoice_id

AND GJH.PERIOD_NAME in ('NOV-11')--,'Jan-10','Feb-10','Mar-10','Apr-10','May-10','Jun-10','Jul-10','Aug-10','Oct-10','Nov-10','Dec-10')

-- AND gcc.segment3 in ('2133969')--,'211112','211113','211114','216112')

-- AND GJH.ACTUAL_FLAG = 'A'

-- AND GJH.STATUS = 'P'

--AND GCC.ENABLED_FLAG = 'Y'

--and aia.INVOICE_NUM = 'KB10010914'

--and (nvl(XAL.ACCOUNTED_DR,0) -nvl( xal.accounted_cr,0)) <> 0

--ORDER BY xah.AE_HEADER_ID, xal.AE_LINE_NUM

select decode(gjb.STATUS,'P','POSTED','OTHER') BATCH_STATUS

,gjb.NAME BATCH_NAME

,gjh.NAME JOURNAL_NAME

,gjh.period_name PERIOD

,gjh.running_total_dr JOURNAL_DEBIT

,gjh.running_total_cr JOURNAL_CREDIT

from gl_je_headers gjh

,gl_je_batches gjb

where

gjh.JE_BATCH_ID = gjb.JE_BATCH_ID

and gjh.period_name='NOV-11'

and gjh.status='P'

and gjb.STATUS='P'

ORDER BY GJB.NAME

select *

from gl_je_headers

/* Formatted on 2011/12/01 15:25 (Formatter Plus v4.8.8) */

SELECT DISTINCT gjh.NAME journal_name, gjb.NAME batch_name,

gjh.je_source SOURCE, gjh.je_category CATEGORY,

gjh.currency_code, gjh.period_name period,

gcc.segment1

|| '.'

|| gcc.segment2

|| '.'

|| gcc.segment3

|| '.'

|| gcc.segment4

|| '.'

|| gcc.segment5

|| '.'

|| gcc.segment6

|| '.'

|| gcc.segment7

|| '.'

|| gcc.segment8 ACCOUNT,

NVL (gjl.entered_dr, 0) debit_valas,

NVL (gjl.entered_cr, 0) credit_valas,

NVL (gjl.accounted_dr, 0) debit_accounted,

NVL (gjl.accounted_cr, 0) credit_accounted

FROM gl_je_headers gjh,

gl_je_lines gjl,

gl_je_batches gjb,

gl_import_references gir,

gl_code_combinations gcc

WHERE gjh.je_header_id = gjl.je_header_id

AND gjh.je_batch_id = gjb.je_batch_id

AND gjh.je_header_id = gir.je_header_id

AND gjl.je_line_num = gir.je_line_num

AND gjl.code_combination_id = gcc.code_combination_id

AND gjh.period_name IN ('JAN-11', 'FEB-11', 'MAR-11')

select *

from gl_je_lines

where period_name ='NOV-11'

SELECT hzp.* FROM

HZ_PARTIES HZP,

HZ_PARTY_USG_ASSIGNMENTS HUA

WHERE

HUA.PARTY_ID = HZP.PARTY_ID

AND HUA.PARTY_USAGE_CODE = 'INTERCOMPANY_ORG'

--and hzp.PARTY_NAME='EDS Manufacturing'

SELECT object_name, object_type, status

FROM all_objects

--where status !='VALID'

WHERE object_name='FUN_TRX_PVT';

SELECT object_name, status

FROM all_objects

WHERE object_name LIKE 'FUN%' AND status !='VALID';

select *

from ad_bugs

where bug_number='9235651'

select *

from ad_bugs

where bug_number='9001593'--'9001593'

DECLARE

p_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;

p_party_object_version_number NUMBER;

x_profile_id NUMBER;

x_return_status VARCHAR2(2000);

x_msg_count NUMBER;

x_msg_data VARCHAR2(2000);

x_party_id NUMBER;

BEGIN

p_organization_rec.organization_name := 'EDS Manufacturing INT';

x_party_id := 4045;

p_organization_rec.party_rec.party_id := x_party_id;

select object_version_number into p_party_object_version_number

from hz_parties where party_id = x_party_id;

hz_party_v2pub.update_organization('T',p_organization_rec,p_party_object_version_number,x_profile_id,x_return_status,x_msg_count,x_msg_data);

IF x_return_status = 'S' THEN

dbms_output.put_line('p_party_object_version_number = '||TO_CHAR(p_party_object_version_number));

dbms_output.put_line('x_profile_id = '||TO_CHAR(x_profile_id));

dbms_output.put_line(SubStr('x_return_status = '||x_return_status,1,255));

dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));

dbms_output.put_line(SubStr('x_msg_data = '||x_msg_data,1,255));

ELSE

dbms_output.put_line('Encountered ERROR!!!!!!');

dbms_output.put_line('-----------------------');

dbms_output.put_line(x_msg_data);

END IF;

FOR I IN 1..FND_MSG_PUB.Count_Msg

LOOP

dbms_output.put_line(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ));

END LOOP;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM);

RAISE;

END;

select c.COMPONENT_NAME,c.CORRELATION_ID,

c.COMPONENT_TYPE,

c.COMPONENT_STATUS,

p.parameter_name,

v.parameter_value

from FND_SVC_COMP_PARAM_VALS v,

fnd_svc_comp_params_b p,

fnd_svc_components c

where v.component_id = c.component_id

and v.parameter_id = p.parameter_id

and upper(c.COMPONENT_NAME) like upper('Workflow%Deferred%')

and Parameter_name ='PROCESSOR_IN_THREAD_COUNT'

order by 1,3;