1.Query to find the Flexfields and valuesets
SELECT A.ID_FLEX_STRUCTURE_CODE,
B.ID_FLEX_CODE,
E.LANGUAGE,
D.FLEX_VALUE,
E.DESCRIPTION,
E.FLEX_VALUE_MEANING,
B.FLEX_VALUE_SET_ID,
B.APPLICATION_COLUMN_NAME,
B.SEGMENT_NAME,
C.FLEX_VALUE_SET_NAME,
D.FLEX_VALUE_ID
FROM APPS.fnd_id_flex_structures A,
APPS.fnd_id_flex_segments B,
APPS.fnd_flex_value_sets C,
APPS.fnd_flex_values D,
APPS.fnd_flex_values_tl E
WHERE A.ID_FLEX_NUM=B.ID_FLEX_NUM
--AND A.ID_FLEX_CODE='GL#'
--AND B.ID_FLEX_CODE='GL#'
AND B.FLEX_VALUE_SET_ID=C.FLEX_VALUE_SET_ID
AND C.FLEX_VALUE_SET_ID=D.FLEX_VALUE_SET_ID
AND D.FLEX_VALUE_ID=E.FLEX_VALUE_ID
AND A.APPLICATION_ID IN (101,201)
AND E.LANGUAGE='US'
ORDER BY A.ID_FLEX_STRUCTURE_CODE
2.Query to find the Responsibilites
SELECT B.RESPONSIBILITY_ID,
A.RESPONSIBILITY_NAME,
A.LANGUAGE,
B.RESPONSIBILITY_KEY ,
B.APPLICATION_ID
FROM APPS.FND_RESPONSIBILITY_TL A,
APPS.FND_RESPONSIBILITY B
WHERE A.RESPONSIBILITY_ID(+)=B.RESPONSIBILITY_ID
AND A.LANGUAGE = USERENV('LANG')
ORDER BY A.RESPONSIBILITY_NAME
3. Query to find the Descriptive Field information for PO and GL
select flex_vl.title,flex.descriptive_flexfield_name,
context_vl.descriptive_flex_context_code,context_vl.description,
col_usage_vl.application_column_name, col_usage_vl.end_user_column_name
from apps.FND_DESCRIPTIVE_FLEXS flex,
apps.FND_DESCRIPTIVE_FLEXS_vl flex_vl
,apps.FND_DESCR_FLEX_CONTEXTS_VL context_vl
,apps.FND_DESCR_FLEX_COL_USAGE_VL col_usage_vl
where flex_vl.application_id in (201,101)
and flex_vl.title = 'Approved Supplier List'
and flex.descriptive_flexfield_name = flex_vl.descriptive_flexfield_name
--'PO_APPROVED_SUPPLIER_LIST'
and flex.descriptive_flexfield_name = context_vl.descriptive_flexfield_name
and flex.descriptive_flexfield_name = col_usage_vl.descriptive_flexfield_name
and col_usage_vl.descriptive_flex_context_code = context_vl.descriptive_flex_context_code
--'2346'
order by col_usage_vl.descriptive_flex_context_code,col_usage_vl.application_column_name
4.Query to find the Menus
SELECT
B.ROWID ROW_ID,
B.MENU_ID,
B.MENU_NAME,
B.TYPE,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY ,
B.LAST_UPDATE_LOGIN ,
B.CREATION_DATE ,
B.CREATED_BY ,
T.USER_MENU_NAME ,
T.DESCRIPTION,
T.LANGUAGE,
C.SUB_MENU_ID
FROM
apps.FND_MENUS_TL T,
apps.FND_MENUS B,
apps.FND_MENU_ENTRIES C
-- (SELECT USER_MENU_NAME,MENU_ID FROM FND_MENUS_TL)SUB
WHERE
B.MENU_ID = T.MENU_ID
AND T.LANGUAGE = USERENV('LANG')
--AND T.USER_MENU_NAME = 'Activity Based Management'
AND B.MENU_ID=C.MENU_ID
5.Query to find the Responsibility,Attached Menu and Application to the
particular responsibility
SELECT distinct
B.RESPONSIBILITY_ID,
A.RESPONSIBILITY_NAME,
A.LANGUAGE,
B.RESPONSIBILITY_KEY ,
B.APPLICATION_ID,
C.USER_MENU_NAME,
E.APPLICATION_NAME
FROM APPS.FND_RESPONSIBILITY_TL A,
APPS.FND_RESPONSIBILITY B,
APPS.FND_MENUS_TL C,
APPS.FND_MENUS D,
apps.FND_APPLICATION_TL E,
apps.FND_APPLICATION F
WHERE A.RESPONSIBILITY_ID(+)=B.RESPONSIBILITY_ID
AND B.MENU_ID=C.MENU_ID
AND B.MENU_ID=D.MENU_ID
AND E.APPLICATION_ID=F.APPLICATION_ID
AND F.APPLICATION_ID=B.APPLICATION_ID
AND A.LANGUAGE='US'
6.Vendor Extraction Query
select
d.vendor_name
, d.segment1 GSL_NUmber
, e.vendor_site_code
, e.address_line1
, e.address_line2
, e.address_line3
, e.city
, e.state
, e.zip
, e.province
, e.country
, substr(e.attribute14,1,3)
, d.last_update_date
, d.last_updated_by
, d.creation_date
, d.created_by
, d.set_of_books_id
,e.vendor_site_code
,e.vendor_site_code_alt
,e.purchasing_site_flag
,e.pay_site_flag
,e.address_line1
,e.address_lineS_alt
,e.address_line2
,e.address_line3
,e.city
,e.state
,e.zip
,e.province
,e.country
,e.phone
,e.customer_num
,e.ship_to_location_id
,e.bill_to_location_id
,e.ship_via_lookup_code
,e.freight_terms_lookup_code
,e.payment_method_lookup_code
,e.terms_date_basis
,e.accts_pay_code_combination_id
,e.prepay_code_combination_id
,e.payment_priority
,e.termS_id
,e.invoice_amount_limit
,e.pay_date_basis_lookup_code
,e.invoice_currency_code
,e.payment_currency_code
,e.hold_unmatched_invoices_flag
,e.ap_tax_rounding_rule
,e.auto_tax_calc_flag
,e.auto_tax_calc_override
,e.address_line4
,e.county
,e.match_option
FROM PO.po_vendors D
,PO.PO_VENDOR_SITES_ALL E
WHERE E.vendor_id = D.vendor_id
AND org_id in (2265)
-- Initializing the default values from System Parameters
-- Get default values from Payable Options
BEGIN
SELECT auto_tax_calc_flag,
auto_tax_calc_override,
amount_includes_tax_flag,
pay_date_basis_lookup_code,
hold_unmatched_invoices_flag
FROM ap_system_parameters_all
WHERE set_of_books_id = l_sob_id
AND org_id = l_org_id;
-- Get default values from Financial Options (DFT02)
BEGIN
SELECT match_option,
ship_to_location_id,
bill_to_location_id,
ship_via_lookup_code,
freight_terms_lookup_code,
tax_rounding_rule
FROM financials_system_params_all
WHERE set_of_books_id = l_sob_id
AND org_id = l_org_id;
SELECT term_id
FROM ap_terms
WHERE name = vendor_record.terms_name
AND ENABLED_FLAG='Y'
AND (END_DATE_ACTIVE > SYSDATE OR END_DATE_ACTIVE IS NULL);
7. Extraction Query for Blanket Purchase Agreement:
SELECT pha.segment1 po_number, pha.type_lookup_code "PO TYPE", pha.revision_num,
pha.creation_date "PO DATE", pha.vendor_id,pv.vendor_name,
pha.vendor_site_id,pvs.vendor_site_code,
bill_hrl.location_code "Bill To",
ship_hrl.location_code "Ship To",
pha.authorization_status,
-- pav.agent_name "Buyer",
ppf.full_name "Buyer",
pha.currency_code,pla.line_num,
pla.item_id,
(SELECT DISTINCT segment1 FROM apps.mtl_system_items
WHERE inventory_item_id=pla.item_id ) item_name,
pla.item_description,
pla.category_id,
(SELECT DISTINCT segment1||'.'||segment2
FROM apps.mtl_categories
WHERE category_id=pla.category_id) "Category" ,
pla.line_type_id,
(SELECT DISTINCT line_type
FROM apps.po_line_types
WHERE line_type_id = pla.line_type_id) line_type,
pla.unit_meas_lookup_code,pla.unit_price,
pla.tax_name
--ats.name TAX_CODE
FROM
apps.po_headers_all pha,
apps.po_lines_all pla,
apps.po_vendors pv,
apps.po_vendor_sites_all pvs,
apps.po_agents_v pav,
apps.hr_locations bill_hrl,
apps.hr_locations ship_hrl,
-- apps.po_agents_v pav,
apps.per_all_people_f ppf
--apps.ap_tax_codes ats
WHERE
pha.po_header_id = pla.po_header_id
AND pha.vendor_id = pv.vendor_id
AND pha.vendor_id = pvs.vendor_id
AND pha.vendor_site_id = pvs.vendor_site_id(+)
--AND pla.tax_code_id=ats.tax_id
--AND pha.agent_id = pav.agent_id(+)
AND pha.agent_id = ppf.person_id
AND pha.type_lookup_code IN ('BLANKET','STANDARD')
AND NVL (pha.closed_code, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED')
AND NVL (pla.closed_code, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED')
AND pha .org_id =2265
AND bill_hrl.location_id = pha.bill_to_location_id
AND ship_hrl.location_id = pha.ship_to_location_id
ORDER BY 1 DESC
|
Oracle Database
|