Oracle Cheat Sheet
QUERY
fcp.concurrent_program_name short_name ,
fat.application_name program_application_name ,
fet.executable_name ,
fat1.application_name executable_application_name ,
flv.meaning execution_method ,
fet.execution_file_name ,
fcp.enable_trace
FROM fnd_concurrent_programs_tl fcpt ,
fnd_concurrent_programs fcp ,
fnd_application_tl fat ,
fnd_executables fet ,
fnd_application_tl fat1 ,
FND_LOOKUP_VALUES FLV
WHERE 1 =1
AND upper(fcpt.user_concurrent_program_name) like '%BANK%LOAD%'
AND fcpt.concurrent_program_id = fcp.concurrent_program_id
AND fcpt.application_id = fcp.application_id
AND fcp.application_id = fat.application_id
AND fcpt.application_id = fat.application_id
AND fcp.executable_id = fet.executable_id
AND fcp.executable_application_id = fet.application_id
AND fet.application_id = fat1.application_id
AND flv.lookup_code = fet.execution_method_code
--AND FLV.LOOKUP_TYPE ='CP_EXECUTION_METHOD_CODE'
;
========================================================================================================================
QUERY:
select fcpv.user_concurrent_program_name,frv.responsibility_name
from fnd_request_group_units frgu
,fnd_concurrent_programs_vl fcpv
,fnd_responsibility_vl frv
where
frgu.request_unit_id = fcpv.concurrent_program_id
and frgu.unit_application_id = fcpv.application_id
and frgu.request_group_id = frv.request_group_id
and FRGU.APPLICATION_ID = FRV.APPLICATION_ID
and fcpv.user_concurrent_program_name like 'JAFI LLN Invoice Print Report';
========================================================================================================================
QUERY:
FIND REQUEST GROUP ATTACHED TO USER CONC PROGRAM
SELECT DISTINCT FRT.RESPONSIBILITY_NAME,
FRG.REQUEST_GROUP_NAME,
FRGU.REQUEST_UNIT_TYPE,
FRGU.REQUEST_UNIT_ID,
FCPT.USER_CONCURRENT_PROGRAM_NAME, FRG.APPLICATION_ID
FROM APPS.FND_RESPONSIBILITY FR,
APPS.FND_RESPONSIBILITY_TL FRT,
APPS.FND_REQUEST_GROUPS FRG,
APPS.FND_REQUEST_GROUP_UNITS FRGU,
APPS.FND_CONCURRENT_PROGRAMS_TL FCPT
WHERE FRT.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID
AND FRG.REQUEST_GROUP_ID = FR.REQUEST_GROUP_ID
AND FRGU.REQUEST_GROUP_ID = FRG.REQUEST_GROUP_ID
AND FCPT.CONCURRENT_PROGRAM_ID = FRGU.REQUEST_UNIT_ID
AND UPPER(FCPT.USER_CONCURRENT_PROGRAM_NAME) = UPPER('ICON: Generate Intercompany Journals for FTE Based Revenue')
ORDER BY 1;
========================================================================================================================
FND_REQUEST.SUBMIT_REQUEST CHANGES
1.If your fnd sbumit request uses custom program then we need to check for any changes in 1i and R12
2.Check user conc program name from below query
select user_concurrent_program_name from FND_CONCURRENT_PROGRAMS_VL where CONCURRENT_PROGRAM_NAME like 'GLLEZL';
XML Publisher Report Bursting Program
3. Check list of parameters by running below Query in 11i and R12
select fdfc.COLUMN_SEQ_NUM "Seq"
,fdfc.END_USER_COLUMN_NAME "Parameter"
,fdfc.description "Description"
,fvs.FLEX_VALUE_SET_NAME "Value Set"
,fdfc.default_type "Default Type"
,fdfc.default_value "Default Value"
--, flv.meaning
,fdfc.required_flag "Required"
,fdfc.display_flag "Display"
, fdfc.form_left_prompt "Prompt"
, display_size "Display Size"
, CONCATENATION_DESCRIPTION_LEN "Concatenated Description Size"
, MAXIMUM_DESCRIPTION_LEN "Description Size"
, fdfc.srw_param "Token"
-- ,flv.lookup_type
-- ,fdfc.*
from FND_DESCR_FLEX_COL_USAGE_VL fdfc
, fnd_concurrent_programs_vl fcp
, fnd_flex_value_sets fvs
--, fnd_lookup_values flv
where fdfc.DESCRIPTIVE_FLEXFIELD_NAME = '$SRS$.'|| fcp.concurrent_program_name
and fdfc.FLEX_VALUE_SET_ID = fvs.FLEX_VALUE_SET_ID
and fcp.user_concurrent_program_name like 'ICON: GL Rates Upload File Modification Program'
--and flv.lookup_type like 'FLEX_DEFAULT_TYPE'
--and flv.LOOKUP_CODE = NVL(fdfc.default_type,flv.LOOKUP_CODE)
order by fdfc.COLUMN_SEQ_NUM;
o/p is 11i had 2 parameters and R12 has 3
we need to insert parameter in same position.parameters have been mentioned as arguments in the submit request query
So,
FND_REQUEST.
SUBMIT_REQUEST (
application => 'XDO',
program => 'XDOBURSTREP',
description => NULL,
start_time => NULL,
sub_request => FALSE-- ,argument1 => 'Y' -- Data Security parameter
,
argument1 =>'N', --Dummy for Data Security --Added by KOHLID on 12-Mar-2014 for R12 upgrade
argument2 => FND_PROFILE.VALUE ('CONC_REQUEST_ID') -- Request Id to be burst
,
argument3 => 'Y' -- Debug Flag
);
here arg1 has been added as new parameter was seen in position 1.Value of parameter is to be taken from
'Default Value' Column o/p of the query above
Defaut value was :SELECT XDO_CP_DATA_SECURITY_PKG.GET_CONCURRENT_REQUEST_IDS FROM DUAL
run this query o/p is N
so new line is added as below
argument1 =>'N', --Dummy for Data Security --Added by KOHLID on 12-Mar-2014 for R12 upgrade
Dummy for Data Security is the 'Parameter' from above query
========================================================================================================================
COMMAND:
LCT FILES FOR CP,TMP,PRF
LDT UPLOAD COMMANDS
FNDLOAD apps/s3cur3fst 0 Y UPLOAD $FND_TOP/patch/115/import/xdotmpl.lct XXICONSBILL_TMP.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct JAFI_INT_FTP_GET_SHAAM_FILE.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XXICONSBILL_PRF.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
TO Upload User
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct BALACHANDERG_USER.ldt
========================================================================================================================
COMMAND:
LDT DOWNLOAD COMMAND
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct JAFI_INT_FTP_GET_SHAAM_FILE.ldt PROGRAM APPLICATION_SHORT_NAME='JAFI' CONCURRENT_PROGRAM_NAME='JAFI_INT_FTP_GET_SHAAM_FILE'
TO Download User
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct BALACHANDERG_USER.ldt FND_USER USER_NAME="BALACHANDERG"
========================================================================================================================REPLACEMENT QUERY:
QUERY TO FIND REPLACEMENT OF TAX_TYPE IN AP_TAXES_CODES_ALL TO ZX_RATES B (make this AS inline query
( SELECT a.tax_type_code tax_type
FROM ZX_TAXES_B A,
ZX_RATES_B B
WHERE A.TAX = B.TAX
AND a.tax_regime_code = b.tax_regime_code ) taxtype_inline,
========================================================================================================================
REPLACEMENT QUERY:
select tax_rate.tax_rate_id,offset_tax_rate.tax_rate_id
from zx_rates_b tax_rate
,zx_rates_b offset_tax_rate
where offset_tax_rate.OFFSET_TAX = tax_rate.tax
and offset_tax_rate.OFFSET_STATUS_CODE = tax_rate.TAX_STATUS_CODE
and offset_tax_rate.OFFSET_TAX_RATE_CODE = tax_rate.TAX_RATE_CODE
========================================================================================================================
COMMAND: COMMPILE CUSTOM.pll
frmcmp_batch module=$AU_TOP/resource/CUSTOM.pll userid=apps/s3cur3fst output_file=$AU_TOP/resource/CUSTOM.plx module_type=LIBRARY batch=yes compile_all=special
========================================================================================================================
REPLACEMENT QUERY:
QUERY TO FIND REPLACEMENT OF OFFSET_TAX_CODE_ID IN AP_TAXES_CODES_ALL TO ZX_RATES B (make this AS inline query )
( SELECT OFFSET1.TAX_RATE_ID OFFSET_TAX_CODE_ID
FROM ZX_RATES_B TAX1,
ZX_RATES_B OFFSET1
WHERE OFFSET1.OFFSET_TAX = TAX1.TAX
AND OFFSET1.OFFSET_STATUS_CODE = TAX1.TAX_STATUS_CODE
AND OFFSET1.OFFSET_TAX_RATE_CODE = TAX1.TAX_RATE_CODE ) APC
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
INITAIZE ORG ID (MOAC CHANGes)
begin
mo_global.set_policy_context('S', '103');
end;
exec mo_global.set_policy_context('S',103);
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
INITAIZE ORG ID (11i)
begin
fnd_client_info.set_org_context(103);
end;
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
for moving a report back to server
sudo su - oracle
run env
/apps/app/R12FINT2/apps/apps_st/appl/xbol/12.0.0/reports/US--take orgnal from this.
/apps/app/R12FINT2/apps/apps_st/appl/xbol/12.0.0/bin --modified move here
mv XXICHALY.rdf /apps/app/R12FINT2/apps/apps_st/appl/xbol/12.0.0/reports/US
COMPILE FORM
frmcmp_batch module=HR_ADMIN.pll userid=apps/apps123 module_type=LIBRARY
frmcmp_batch module=HRMF009.fmb userid=apps/apps123
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
--count number of payments in payment batch
SELECT count(*)
INTO v_paymnets_num
FROM AP_SELECTED_INVOICE_CHECKS_ALL ASIC
WHERE ASIC.CHECKRUN_NAME = p_checkrun_name
AND ASIC.CHECK_NUMBER > 0
AND ASIC.OK_TO_PAY_FLAG = 'Y' ;
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
Query to have details about invoices
SELECT aca.check_number "Document Number",
asa.segment1 "Supplier Number",
asa.vendor_name "Supplier Name",
aca.STATUS_LOOKUP_CODE "Payment Reconcilation Status",
aca.CLEARED_AMOUNT "Payment Amount",
aca.CURRENCY_CODE "Currency",
aca.CLEARED_DATE "Payment Date",
aia.invoice_num,
aia.invoice_amount,
AP_INVOICES_PKG.GET_APPROVAL_STATUS (aia.INVOICE_ID,
aia.INVOICE_AMOUNT,
aia.PAYMENT_STATUS_FLAG,
aia.INVOICE_TYPE_LOOKUP_CODE)
"Invoice Acct Status"
FROM ap_checks_all aca,
ap_invoice_payments_all aipa,
ap_invoices_all aia,
ap_suppliers asa
WHERE aca.check_id = aipa.check_id
AND aipa.invoice_id = aia.invoice_id
AND aia.vendor_id = asa.vendor_id
AND aipa.ACCRUAL_POSTED_FLAG = 'N'
AND POSTED_FLAG = 'N'
AND TRUNC (aca.CLEARED_DATE) BETWEEN '01-JUN-2013' AND '30-JUN-2013'
ORDER BY check_number;
JOIN IN AP_CHECKS_ALL and AP_INVOICES_ALL
select c.check_number
from
AP_INVOICES_ALL i,
AP_CHECKS_ALL c,
AP_INVOICE_PAYMENTS_ALL p
where p.invoice_id = i.invoice_id
and c.check_id = p.check_id
and i.invoice_num='DTEST2'
================================================================================================
find app;ication short name
SELECT application_name, application_short_name, fa.application_id
FROM fnd_application fa, fnd_application_tl fat
WHERE application_short_name LIKE 'FND'
AND fa.application_id = fat.application_id
ORDER BY fa.application_id;
====================================================================================================
create soft link
ln -s $FND_TOP/bin/fndcpesr JAFIRemoveNameSpace
apps/apps@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=up1db.jafi.org.il)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=UP1)(INSTANCE_NAME=UP1))
Fin instance
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rstnssiovm0008.us.oracle.com)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=r12incub)(INSTANCE_NAME=r12incub)))
========================================================================================================================
DOWNLOAD Templates
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD s1tkey2apps -JDBC_CONNECTION 179.10.0.4:1598:SIDEV -LOB_TYPE TEMPLATE -APPS_SHORT_NAME OFA -LOB_CODE FAREG_XML -LANGUAGE en -TERRITORY 00 -LOG_FILE FAREG_XML.log -DEBUG true
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD appsdev50 -JDBC_CONNECTION 179.10.0.4:1598:SIDEV -LOB_TYPE TEMPLATE -APPS_SHORT_NAME OFA -LOB_CODE FAREG_XML -LANGUAGE en -TERRITORY US -XDO_FILE_TYPE RTF -FILE_CONTENT_TYPE 'application/rtf' -FILE_NAME XXALDARBNKTRF.rtf -LOG_FILE XXAPMRO_ALDAR.log -DEBUG TRUE
========================================================================================================================
FREQ USED IBY QUERIES
--iby_pay_instructions_all
--pay_admin_assigned_ref_code is Checkrun name
select pay_admin_assigned_ref_code,a.* from iby_pay_instructions_all a order by creation_date desc; --where payment_instruction_id= 244914;
--iby_payments_all
select * from iby_payments_all where payment_instruction_id=244325;
select payment_instruction_id,PAYMENT_PROCESS_REQUEST_NAME,a.* from iby_payments_all a where payment_id = 82715;
select PAYMENT_PROCESS_REQUEST_NAME,a.* from iby_payments_all a order by creation_date desc;
--iby_docs_payable_all
select * from iby_docs_payable_all a,iby_payments_all b where a.payment_id=b.payment_id and payment_instruction_id=244325;
--Query to see invoice number
select calling_app_doc_unique_ref2 from iby_docs_payable_all where payment_id=82715;
--Join ASIC and IPI..Process type STANDARD is for PPR.ASIC doesnt have entires for Quick Payment in R12
SELECT process_type
FROM ap_inv_selection_criteria_all aisc
,iby_pay_instructions_all ipi
WHERE aisc.checkrun_name = ipi.pay_admin_assigned_ref_code
AND ipi.PAYMENT_INSTRUCTION_ID = '244940'
AND ipi.process_type='STANDARD';
FROM FND_CONCURRENT_REQUESTS
WHERE CONCURRENT_PROGRAM_ID = (SELECT CONCURRENT_PROGRAM_ID
FROM fnd_concurrent_programs_vl
WHERE CONCURRENT_PROGRAM_NAME='IBY_FD_PAYMENT_FORMAT_TEXT')
and argument1='244910'; --payment_instruction_id
--Query to get the XML file
select * from IBY_TRXN_DOCUMENTS where payment_instruction_id=244911;
--Find which bank is attached to Format
SELECT DISTINCT
CBA.bank_account_id
, CBA.bank_account_name
, CBA.currency_code
, CBA.bank_account_num
, CPD.payment_document_name
FROM ce_bank_accounts CBA
, ce_payment_documents CPD
, iby_formats_vl IFV
WHERE CBA.bank_account_id = CPD.internal_bank_account_id
AND CPD.format_code = IFV.format_code
AND IFV.format_name like 'JAFI%MASAV%';
========================================================================================================================
VISION connection string
apps/apps@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=up2apn.jafi.org.il)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=UP2)(INSTANCE_NAME=UP2)))
========================================================================================================================
SELECT CONCURRENT_PROGRAM_ID,CONCURRENT_PROGRAM_NAME
FROM fnd_concurrent_programs_vl
--WHERE CONCURRENT_PROGRAM_NAME='IBY_FD_PAYMENT_FORMAT_TEXT';
WHERE CONCURRENT_PROGRAM_ID=''
SELECT CONCURRENT_PROGRAM_NAME,a.CONCURRENT_PROGRAM_ID,a.*
FROM fnd_concurrent_requests a,
fnd_concurrent_programs_vl b
WHERE a.CONCURRENT_PROGRAM_ID=b.CONCURRENT_PROGRAM_ID
AND a.request_id= 42949649;
--Query to find Details aganist Request ID
select fcpv.USER_CONCURRENT_PROGRAM_NAME,
fcpv.CONCURRENT_PROGRAM_NAME,
fcr.CONCURRENT_PROGRAM_ID,
frv.responsibility_name,
fcr.*
from fnd_request_group_units frgu
,fnd_concurrent_programs_vl fcpv
,fnd_responsibility_vl frv
,fnd_concurrent_requests fcr
where
frgu.request_unit_id = fcpv.concurrent_program_id
and frgu.unit_application_id = fcpv.application_id
and frgu.request_group_id = frv.request_group_id
and FRGU.APPLICATION_ID = FRV.APPLICATION_ID
and fcpv.CONCURRENT_PROGRAM_ID=fcr.CONCURRENT_PROGRAM_ID
and fcr.RESPONSIBILITY_ID=frv.RESPONSIBILITY_ID
and fcr.request_id= 42949649;
--and fcpv.user_concurrent_program_name like 'JAFI IL Fixed Assets Combination Report';
QUERY
--To find Executable,Concurrent Program details
SELECT fcpt.user_concurrent_program_name ,fcp.concurrent_program_name short_name ,
fat.application_name program_application_name ,
fet.executable_name ,
fat1.application_name executable_application_name ,
flv.meaning execution_method ,
fet.execution_file_name ,
fcp.enable_trace
FROM fnd_concurrent_programs_tl fcpt ,
fnd_concurrent_programs fcp ,
fnd_application_tl fat ,
fnd_executables fet ,
fnd_application_tl fat1 ,
FND_LOOKUP_VALUES FLV
WHERE 1 =1
AND upper(fcpt.user_concurrent_program_name) like '%BANK%LOAD%'
AND fcpt.concurrent_program_id = fcp.concurrent_program_id
AND fcpt.application_id = fcp.application_id
AND fcp.application_id = fat.application_id
AND fcpt.application_id = fat.application_id
AND fcp.executable_id = fet.executable_id
AND fcp.executable_application_id = fet.application_id
AND fet.application_id = fat1.application_id
AND flv.lookup_code = fet.execution_method_code
--AND FLV.LOOKUP_TYPE ='CP_EXECUTION_METHOD_CODE'
;
========================================================================================================================
QUERY:
FIND RESPONSIBILTY ATTACHED TO USER CONC PROGRAM NAME
select fcpv.user_concurrent_program_name,frv.responsibility_name
from fnd_request_group_units frgu
,fnd_concurrent_programs_vl fcpv
,fnd_responsibility_vl frv
where
frgu.request_unit_id = fcpv.concurrent_program_id
and frgu.unit_application_id = fcpv.application_id
and frgu.request_group_id = frv.request_group_id
and FRGU.APPLICATION_ID = FRV.APPLICATION_ID
and fcpv.user_concurrent_program_name like 'JAFI LLN Invoice Print Report';
========================================================================================================================
QUERY:
FIND REQUEST GROUP ATTACHED TO USER CONC PROGRAM
SELECT DISTINCT FRT.RESPONSIBILITY_NAME,
FRG.REQUEST_GROUP_NAME,
FRGU.REQUEST_UNIT_TYPE,
FRGU.REQUEST_UNIT_ID,
FCPT.USER_CONCURRENT_PROGRAM_NAME, FRG.APPLICATION_ID
FROM APPS.FND_RESPONSIBILITY FR,
APPS.FND_RESPONSIBILITY_TL FRT,
APPS.FND_REQUEST_GROUPS FRG,
APPS.FND_REQUEST_GROUP_UNITS FRGU,
APPS.FND_CONCURRENT_PROGRAMS_TL FCPT
WHERE FRT.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID
AND FRG.REQUEST_GROUP_ID = FR.REQUEST_GROUP_ID
AND FRGU.REQUEST_GROUP_ID = FRG.REQUEST_GROUP_ID
AND FCPT.CONCURRENT_PROGRAM_ID = FRGU.REQUEST_UNIT_ID
AND UPPER(FCPT.USER_CONCURRENT_PROGRAM_NAME) = UPPER('ICON: Generate Intercompany Journals for FTE Based Revenue')
ORDER BY 1;
========================================================================================================================
FND_REQUEST.SUBMIT_REQUEST CHANGES
1.If your fnd sbumit request uses custom program then we need to check for any changes in 1i and R12
2.Check user conc program name from below query
select user_concurrent_program_name from FND_CONCURRENT_PROGRAMS_VL where CONCURRENT_PROGRAM_NAME like 'GLLEZL';
XML Publisher Report Bursting Program
3. Check list of parameters by running below Query in 11i and R12
select fdfc.COLUMN_SEQ_NUM "Seq"
,fdfc.END_USER_COLUMN_NAME "Parameter"
,fdfc.description "Description"
,fvs.FLEX_VALUE_SET_NAME "Value Set"
,fdfc.default_type "Default Type"
,fdfc.default_value "Default Value"
--, flv.meaning
,fdfc.required_flag "Required"
,fdfc.display_flag "Display"
, fdfc.form_left_prompt "Prompt"
, display_size "Display Size"
, CONCATENATION_DESCRIPTION_LEN "Concatenated Description Size"
, MAXIMUM_DESCRIPTION_LEN "Description Size"
, fdfc.srw_param "Token"
-- ,flv.lookup_type
-- ,fdfc.*
from FND_DESCR_FLEX_COL_USAGE_VL fdfc
, fnd_concurrent_programs_vl fcp
, fnd_flex_value_sets fvs
--, fnd_lookup_values flv
where fdfc.DESCRIPTIVE_FLEXFIELD_NAME = '$SRS$.'|| fcp.concurrent_program_name
and fdfc.FLEX_VALUE_SET_ID = fvs.FLEX_VALUE_SET_ID
and fcp.user_concurrent_program_name like 'ICON: GL Rates Upload File Modification Program'
--and flv.lookup_type like 'FLEX_DEFAULT_TYPE'
--and flv.LOOKUP_CODE = NVL(fdfc.default_type,flv.LOOKUP_CODE)
order by fdfc.COLUMN_SEQ_NUM;
o/p is 11i had 2 parameters and R12 has 3
we need to insert parameter in same position.parameters have been mentioned as arguments in the submit request query
So,
FND_REQUEST.
SUBMIT_REQUEST (
application => 'XDO',
program => 'XDOBURSTREP',
description => NULL,
start_time => NULL,
sub_request => FALSE-- ,argument1 => 'Y' -- Data Security parameter
,
argument1 =>'N', --Dummy for Data Security --Added by KOHLID on 12-Mar-2014 for R12 upgrade
argument2 => FND_PROFILE.VALUE ('CONC_REQUEST_ID') -- Request Id to be burst
,
argument3 => 'Y' -- Debug Flag
);
here arg1 has been added as new parameter was seen in position 1.Value of parameter is to be taken from
'Default Value' Column o/p of the query above
Defaut value was :SELECT XDO_CP_DATA_SECURITY_PKG.GET_CONCURRENT_REQUEST_IDS FROM DUAL
run this query o/p is N
so new line is added as below
argument1 =>'N', --Dummy for Data Security --Added by KOHLID on 12-Mar-2014 for R12 upgrade
Dummy for Data Security is the 'Parameter' from above query
========================================================================================================================
COMMAND:
LCT FILES FOR CP,TMP,PRF
LDT UPLOAD COMMANDS
FNDLOAD apps/s3cur3fst 0 Y UPLOAD $FND_TOP/patch/115/import/xdotmpl.lct XXICONSBILL_TMP.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct JAFI_INT_FTP_GET_SHAAM_FILE.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XXICONSBILL_PRF.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
TO Upload User
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct BALACHANDERG_USER.ldt
========================================================================================================================
COMMAND:
LDT DOWNLOAD COMMAND
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct JAFI_INT_FTP_GET_SHAAM_FILE.ldt PROGRAM APPLICATION_SHORT_NAME='JAFI' CONCURRENT_PROGRAM_NAME='JAFI_INT_FTP_GET_SHAAM_FILE'
TO Download User
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct BALACHANDERG_USER.ldt FND_USER USER_NAME="BALACHANDERG"
========================================================================================================================REPLACEMENT QUERY:
QUERY TO FIND REPLACEMENT OF TAX_TYPE IN AP_TAXES_CODES_ALL TO ZX_RATES B (make this AS inline query
( SELECT a.tax_type_code tax_type
FROM ZX_TAXES_B A,
ZX_RATES_B B
WHERE A.TAX = B.TAX
AND a.tax_regime_code = b.tax_regime_code ) taxtype_inline,
========================================================================================================================
REPLACEMENT QUERY:
select tax_rate.tax_rate_id,offset_tax_rate.tax_rate_id
from zx_rates_b tax_rate
,zx_rates_b offset_tax_rate
where offset_tax_rate.OFFSET_TAX = tax_rate.tax
and offset_tax_rate.OFFSET_STATUS_CODE = tax_rate.TAX_STATUS_CODE
and offset_tax_rate.OFFSET_TAX_RATE_CODE = tax_rate.TAX_RATE_CODE
========================================================================================================================
COMMAND: COMMPILE CUSTOM.pll
frmcmp_batch module=$AU_TOP/resource/CUSTOM.pll userid=apps/s3cur3fst output_file=$AU_TOP/resource/CUSTOM.plx module_type=LIBRARY batch=yes compile_all=special
========================================================================================================================
REPLACEMENT QUERY:
QUERY TO FIND REPLACEMENT OF OFFSET_TAX_CODE_ID IN AP_TAXES_CODES_ALL TO ZX_RATES B (make this AS inline query )
( SELECT OFFSET1.TAX_RATE_ID OFFSET_TAX_CODE_ID
FROM ZX_RATES_B TAX1,
ZX_RATES_B OFFSET1
WHERE OFFSET1.OFFSET_TAX = TAX1.TAX
AND OFFSET1.OFFSET_STATUS_CODE = TAX1.TAX_STATUS_CODE
AND OFFSET1.OFFSET_TAX_RATE_CODE = TAX1.TAX_RATE_CODE ) APC
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
INITAIZE ORG ID (MOAC CHANGes)
begin
mo_global.set_policy_context('S', '103');
end;
exec mo_global.set_policy_context('S',103);
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
INITAIZE ORG ID (11i)
begin
fnd_client_info.set_org_context(103);
end;
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
for moving a report back to server
sudo su - oracle
run env
/apps/app/R12FINT2/apps/apps_st/appl/xbol/12.0.0/reports/US--take orgnal from this.
/apps/app/R12FINT2/apps/apps_st/appl/xbol/12.0.0/bin --modified move here
mv XXICHALY.rdf /apps/app/R12FINT2/apps/apps_st/appl/xbol/12.0.0/reports/US
COMPILE FORM
frmcmp_batch module=HR_ADMIN.pll userid=apps/apps123 module_type=LIBRARY
frmcmp_batch module=HRMF009.fmb userid=apps/apps123
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
--count number of payments in payment batch
SELECT count(*)
INTO v_paymnets_num
FROM AP_SELECTED_INVOICE_CHECKS_ALL ASIC
WHERE ASIC.CHECKRUN_NAME = p_checkrun_name
AND ASIC.CHECK_NUMBER > 0
AND ASIC.OK_TO_PAY_FLAG = 'Y' ;
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
Query to have details about invoices
SELECT aca.check_number "Document Number",
asa.segment1 "Supplier Number",
asa.vendor_name "Supplier Name",
aca.STATUS_LOOKUP_CODE "Payment Reconcilation Status",
aca.CLEARED_AMOUNT "Payment Amount",
aca.CURRENCY_CODE "Currency",
aca.CLEARED_DATE "Payment Date",
aia.invoice_num,
aia.invoice_amount,
AP_INVOICES_PKG.GET_APPROVAL_STATUS (aia.INVOICE_ID,
aia.INVOICE_AMOUNT,
aia.PAYMENT_STATUS_FLAG,
aia.INVOICE_TYPE_LOOKUP_CODE)
"Invoice Acct Status"
FROM ap_checks_all aca,
ap_invoice_payments_all aipa,
ap_invoices_all aia,
ap_suppliers asa
WHERE aca.check_id = aipa.check_id
AND aipa.invoice_id = aia.invoice_id
AND aia.vendor_id = asa.vendor_id
AND aipa.ACCRUAL_POSTED_FLAG = 'N'
AND POSTED_FLAG = 'N'
AND TRUNC (aca.CLEARED_DATE) BETWEEN '01-JUN-2013' AND '30-JUN-2013'
ORDER BY check_number;
JOIN IN AP_CHECKS_ALL and AP_INVOICES_ALL
select c.check_number
from
AP_INVOICES_ALL i,
AP_CHECKS_ALL c,
AP_INVOICE_PAYMENTS_ALL p
where p.invoice_id = i.invoice_id
and c.check_id = p.check_id
and i.invoice_num='DTEST2'
================================================================================================
find app;ication short name
SELECT application_name, application_short_name, fa.application_id
FROM fnd_application fa, fnd_application_tl fat
WHERE application_short_name LIKE 'FND'
AND fa.application_id = fat.application_id
ORDER BY fa.application_id;
====================================================================================================
create soft link
ln -s $FND_TOP/bin/fndcpesr JAFIRemoveNameSpace
apps/apps@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=up1db.jafi.org.il)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=UP1)(INSTANCE_NAME=UP1))
Fin instance
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rstnssiovm0008.us.oracle.com)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=r12incub)(INSTANCE_NAME=r12incub)))
========================================================================================================================
DOWNLOAD Templates
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD s1tkey2apps -JDBC_CONNECTION 179.10.0.4:1598:SIDEV -LOB_TYPE TEMPLATE -APPS_SHORT_NAME OFA -LOB_CODE FAREG_XML -LANGUAGE en -TERRITORY 00 -LOG_FILE FAREG_XML.log -DEBUG true
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD appsdev50 -JDBC_CONNECTION 179.10.0.4:1598:SIDEV -LOB_TYPE TEMPLATE -APPS_SHORT_NAME OFA -LOB_CODE FAREG_XML -LANGUAGE en -TERRITORY US -XDO_FILE_TYPE RTF -FILE_CONTENT_TYPE 'application/rtf' -FILE_NAME XXALDARBNKTRF.rtf -LOG_FILE XXAPMRO_ALDAR.log -DEBUG TRUE
========================================================================================================================
FREQ USED IBY QUERIES
--iby_pay_instructions_all
--pay_admin_assigned_ref_code is Checkrun name
select pay_admin_assigned_ref_code,a.* from iby_pay_instructions_all a order by creation_date desc; --where payment_instruction_id= 244914;
--iby_payments_all
select * from iby_payments_all where payment_instruction_id=244325;
select payment_instruction_id,PAYMENT_PROCESS_REQUEST_NAME,a.* from iby_payments_all a where payment_id = 82715;
select PAYMENT_PROCESS_REQUEST_NAME,a.* from iby_payments_all a order by creation_date desc;
--iby_docs_payable_all
select * from iby_docs_payable_all a,iby_payments_all b where a.payment_id=b.payment_id and payment_instruction_id=244325;
--Query to see invoice number
select calling_app_doc_unique_ref2 from iby_docs_payable_all where payment_id=82715;
--Join ASIC and IPI..Process type STANDARD is for PPR.ASIC doesnt have entires for Quick Payment in R12
SELECT process_type
FROM ap_inv_selection_criteria_all aisc
,iby_pay_instructions_all ipi
WHERE aisc.checkrun_name = ipi.pay_admin_assigned_ref_code
AND ipi.PAYMENT_INSTRUCTION_ID = '244940'
AND ipi.process_type='STANDARD';
FROM FND_CONCURRENT_REQUESTS
WHERE CONCURRENT_PROGRAM_ID = (SELECT CONCURRENT_PROGRAM_ID
FROM fnd_concurrent_programs_vl
WHERE CONCURRENT_PROGRAM_NAME='IBY_FD_PAYMENT_FORMAT_TEXT')
and argument1='244910'; --payment_instruction_id
--Query to get the XML file
select * from IBY_TRXN_DOCUMENTS where payment_instruction_id=244911;
--Find which bank is attached to Format
SELECT DISTINCT
CBA.bank_account_id
, CBA.bank_account_name
, CBA.currency_code
, CBA.bank_account_num
, CPD.payment_document_name
FROM ce_bank_accounts CBA
, ce_payment_documents CPD
, iby_formats_vl IFV
WHERE CBA.bank_account_id = CPD.internal_bank_account_id
AND CPD.format_code = IFV.format_code
AND IFV.format_name like 'JAFI%MASAV%';
========================================================================================================================
VISION connection string
apps/apps@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=up2apn.jafi.org.il)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=UP2)(INSTANCE_NAME=UP2)))
========================================================================================================================
SELECT CONCURRENT_PROGRAM_ID,CONCURRENT_PROGRAM_NAME
FROM fnd_concurrent_programs_vl
--WHERE CONCURRENT_PROGRAM_NAME='IBY_FD_PAYMENT_FORMAT_TEXT';
WHERE CONCURRENT_PROGRAM_ID=''
SELECT CONCURRENT_PROGRAM_NAME,a.CONCURRENT_PROGRAM_ID,a.*
FROM fnd_concurrent_requests a,
fnd_concurrent_programs_vl b
WHERE a.CONCURRENT_PROGRAM_ID=b.CONCURRENT_PROGRAM_ID
AND a.request_id= 42949649;
--Query to find Details aganist Request ID
select fcpv.USER_CONCURRENT_PROGRAM_NAME,
fcpv.CONCURRENT_PROGRAM_NAME,
fcr.CONCURRENT_PROGRAM_ID,
frv.responsibility_name,
fcr.*
from fnd_request_group_units frgu
,fnd_concurrent_programs_vl fcpv
,fnd_responsibility_vl frv
,fnd_concurrent_requests fcr
where
frgu.request_unit_id = fcpv.concurrent_program_id
and frgu.unit_application_id = fcpv.application_id
and frgu.request_group_id = frv.request_group_id
and FRGU.APPLICATION_ID = FRV.APPLICATION_ID
and fcpv.CONCURRENT_PROGRAM_ID=fcr.CONCURRENT_PROGRAM_ID
and fcr.RESPONSIBILITY_ID=frv.RESPONSIBILITY_ID
and fcr.request_id= 42949649;
--and fcpv.user_concurrent_program_name like 'JAFI IL Fixed Assets Combination Report';
No comments:
Post a Comment