Vendor Extract Query

SELECT ven.segment1 vendor_num
,ven.vendor_name
,sites.vendor_site_code
,sites.vendor_site_code_alt
,sites.creation_date
,sites.inactive_date
, sites.address_line1
|| ' '
|| sites.address_line2
|| ' '
|| sites.address_line3
|| ' '
|| sites.city
|| ' '
|| sites.province
|| ' '
|| sites.state
|| ' '
|| sites.zip address
,sites.purchasing_site_flag purchasing_site
,sites.pay_site_flag pay_to_site, t.NAME "Terms"
FROM po_vendor_sites_all sites, po_vendors ven, ap_terms_tl t
WHERE sites.vendor_id = ven.vendor_id AND sites.pay_group_lookup_code <> 'EMPLOYEE'
and t.TERM_ID = sites.TERMS_ID and t.LANGUAGE = 'US'

AP Invoice Query

SELECT sites.vendor_site_code||'|'||
inv.attribute4||'|'|| -- transaction_code
inv.invoice_type_lookup_code ||'|'|| -- TYPE
inv.invoice_num||'|'||
inv.attribute5||'|'|| -- rtk_po_number
po.po_number||'|'|| -- oracle_po_number
inv.pay_group_lookup_code||'|'||
inv.invoice_date||'|'||
inv.invoice_amount||'|'|| -- gross_amount
trms.description||'|'|| -- payment_terms
(inv.invoice_date + NVL (trms.due_cutoff_day, 0))||'|'|| -- discount_due_date
psch.due_date||'|'|| -- net_due_date
pay.creation_date||'|'|| -- payment_date
pay.amount ||'|'|| --payment_amount
inv.discount_amount_taken||'|'|| -- discount_amount
inv.invoice_currency_code||'|'||
inv.payment_method_lookup_code||'|'|| -- payment_method
inv.goods_received_date||'|'||
dist.line_type_lookup_code||'|'||
dist.description||'|'||
dist.amount||'|'||
dist.dist_code_combination_id||'|'||-- dist_ccid
glcc.segment1 || glcc.segment2 || glcc.segment3 || glcc.segment4 || glcc.segment5 || glcc.segment6 PAYMENT_TRX_REC
--distribution_account
FROM ap.ap_invoices_all inv
,ap.ap_invoice_payments_all pay
,ap_terms_vl trms
,ap.ap_invoice_distributions_all dist
,gl.gl_code_combinations glcc
,ap_payment_schedules_all psch
,po.po_vendor_sites_all sites
, (SELECT poh.segment1 po_number
,pod.po_distribution_id po_dist_id
FROM po.po_headers_all poh, po.po_distributions_all pod
WHERE poh.po_header_id = pod.po_header_id) po
WHERE inv.payment_status_flag = 'Y'
AND TRUNC (pay.creation_date) >= TO_DATE ('01-10-2009', 'DD-MM-YYYY')
AND TRUNC (pay.creation_date) <= TO_DATE ('31-10-2009', 'DD-MM-YYYY') --and to_char(pay.creation_date,'mm-yyyy') = '09-2010' AND inv.invoice_id = pay.invoice_id AND inv.invoice_id = dist.invoice_id AND pay.invoice_id = psch.invoice_id AND inv.terms_id = trms.term_id AND inv.vendor_site_id = sites.vendor_site_id AND glcc.code_combination_id = dist.dist_code_combination_id AND dist.po_distribution_id = po.po_dist_id(+) AND sites.pay_group_lookup_code <> 'EMPLOYEE'

--- Concurrent program parameters

select * from FND_DESCR_FLEX_COL_USAGE_VL
where descriptive_flexfield_name like '%APGLTRANS%'
and enabled_flag='Y'
order by column_seq_num asc

Remove Concurrent programs

begin
fnd_program.delete_program('XXHBC_HYP_PLSHEET',
'HBC Custom Application');
fnd_program.delete_executable('XXHBC_HYP_PLSHEET',
'HBC Custom Application');
end;

ORg ID setting

begin
FND_CLIENT_INFO.SET_ORG_CONTEXT('22') ;
Fnd_Global.apps_initialize(2572675,50407,20003);
end;

DFF Columns Mapping with Attributes List

select fa.application_short_name,
fdft.title,
fdf.application_table_name,
-- fdf.descriptive_flexfield_name,
-- fdf.description,
fdfcu.application_column_name,
-- fdfcu.end_user_column_name,
fdfcu.enabled_flag enabled,
fdfcu.required_flag required,
--fdfcu.form_left_prompt,
--fdfcu.description,
--fdfcu.creation_date,
fu.user_name created_by , fdfcu.*
from fnd_descriptive_flexs fdf,
fnd_descriptive_flexs_tl fdft,
fnd_descr_flex_contexts fdfc,
fnd_descr_flex_column_usages fdfcu,
fnd_application fa,
fnd_user fu
where fdf.application_id=fdft.application_id
and fdf.descriptive_flexfield_name=fdft.descriptive_flexfield_name
and fdft.application_id=fa.application_id
and fdf.application_id=fdfc.application_id
and fdf.descriptive_flexfield_name=fdfc.descriptive_flexfield_name
and fdfc.descriptive_flex_context_code=fdfcu.descriptive_flex_context_code
and fdfc.descriptive_flexfield_name=fdfcu.descriptive_flexfield_name
and fdfc.application_id=fdfcu.application_id
and fdfcu.created_by=fu.user_id
and fdf.descriptive_flexfield_name ='MTL_SYSTEM_ITEMS'

--- What are different KFF fields

SELECT kfc.segment_name,
kfc.segment_num,
kfc.application_column_name,
kfc.flex_value_set_id,
kff.application_table_name,
kff.id_flex_code flex_code,
kff.id_flex_name flex_name
FROM fnd_id_flex_segments_vl kfc,
fnd_id_flexs kff
WHERE kfc.id_flex_code = kff.id_flex_code
and application_table_name='MTL_SYSTEM_ITEMS_B'

--- this responsibility is with which user ?

select responsibility_name, user_name, fu.description ,furs.* from fnd_responsibility_tl fr,
fnd_user fu,
FND_USER_RESP_GROUPS furs
where 1=1
and fu.user_id=furs.user_id
and responsibility_name='HBC NZOOM Office'
and fr.responsibility_id=furs.responsibility_id
and nvl(furs.end_Date,'31-DEC-4712') > sysdate
and fr.LANGUAGE='US'

-- get list of responsibilities available to any user

select * from FND_USER_RESP_GROUPS furs, fnd_user fu , fnd_responsibility_vl fr
where 1=1
and fu.user_id=furs.user_id
and user_name='ACNIC22'
and fr.responsibility_id=furs.responsibility_id

-- get list of responsibilities that can access request set

SELECT distinct c.user_request_set_name, a.request_group_name,
d.responsibility_name , a.*,b.*
FROM apps.fnd_request_groups a,
apps.fnd_request_group_units b,
apps.fnd_request_sets_vl c,
apps.fnd_responsibility_vl d
WHERE a.request_group_id = b.request_group_id
AND a.application_id = b.application_id
AND c.request_set_id = b.request_unit_id
AND d.request_group_id = a.request_group_id
and c.user_request_set_name='Extract Data from GLP to FIS'

Get list of responsibilities that can access ccp

SELECT c.user_concurrent_program_name, --a.request_group_name,
d.responsibility_name
FROM apps.fnd_request_groups a,
apps.fnd_request_group_units b,
apps.fnd_concurrent_programs_tl c,
apps.fnd_responsibility_vl d
WHERE a.request_group_id = b.request_group_id
AND a.application_id = b.application_id
AND c.concurrent_program_id = b.request_unit_id
AND d.request_group_id = a.request_group_id
AND c.user_concurrent_program_name=:p