-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Business Units
SELECT haotl.NAME
BU_NAME
,
hao.LOCATION_ID
LOCATION_ID
,
haotl.BUSINESS_GROUP_ID BUSINESS_GROUP_ID ,
haotl.EFFECTIVE_START_DATE
DATE_FROM ,
haotl.EFFECTIVE_END_DATE
DATE_TO ,
hoi.ORG_INFORMATION1
MANAGER_ID ,
hoi.ORG_INFORMATION2
LEGAL_ENTITY_ID ,
hoi.ORG_INFORMATION3
PRIMARY_LEDGER_ID ,
hoi.ORG_INFORMATION4
DEFAULT_SET_ID ,
haotl.NAME
SHORT_CODE
,
hoi.ORG_INFORMATION6
ENABLED_FOR_HR_FLAG ,
hoi.ORG_INFORMATION7 FIN_BUSINESS_UNIT_ID ,
hoi.ORG_INFORMATION8 DEFAULT_CURRENCY_CODE,
hoi.ORG_INFORMATION9
PROFIT_CENTER_FLAG ,
hoi.CREATED_BY
CREATED_BY
,
hoi.CREATION_DATE
CREATION_DATE ,
hoi.LAST_UPDATED_BY
LAST_UPDATED_BY ,
hoi.LAST_UPDATE_DATE
LAST_UPDATE_DATE ,
hoi.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
FROM HR_ALL_ORGANIZATION_UNITS_F hao ,
HR_ORGANIZATION_UNITS_F_TL haotl ,
HR_ORG_UNIT_CLASSIFICATIONS_F houc,
hr_organization_information_f hoi
WHERE hao.ORGANIZATION_ID
= haotl.ORGANIZATION_ID
AND houc.ORGANIZATION_ID
= haotl.ORGANIZATION_ID
AND houc.CLASSIFICATION_CODE = 'FUN_BUSINESS_UNIT'
AND hoi.ORGANIZATION_ID
= haotl.ORGANIZATION_ID
AND hoi.ORG_INFORMATION_CONTEXT = houc.CLASSIFICATION_CODE
AND TRUNC(SYSDATE) BETWEEN haotl.EFFECTIVE_START_DATE AND haotl.EFFECTIVE_END_DATE
AND haotl.LANGUAGE
= USERENV('LANG')
AND haotl.EFFECTIVE_START_DATE = hao.EFFECTIVE_START_DATE
AND haotl.EFFECTIVE_END_DATE = hao.EFFECTIVE_END_DATE
ORDER BY BU_NAME;
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Customer Information -
Query site reference xref information
SELECT
e.owner_table_name,
e.owner_table_id,
b.party_name,
a.orig_system_reference "Customer
REF",
d.site_use_code,
d.orig_system_reference "Site
REF",
d.primary_flag,
d.status
FROM
HZ_CUST_ACCOUNTS
a,
HZ_PARTIES
b ,
HZ_CUST_ACCT_SITES_ALL c ,
HZ_CUST_SITE_USES_ALL d,
HZ_ORIG_SYS_REFERENCES e
WHERE
a.party_id = b.party_id and
a.cust_account_id = c.cust_account_id and
c.cust_acct_site_id = d.cust_acct_site_id and
d.orig_system_reference = e.orig_system_reference
--and
--
upper(party_name) like upper('%comp%')
order by
2,4
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Query Customer Site
Reference information
SELECT b.party_name
,
a.orig_system_reference "Customer
REF",
d.site_use_code
,
d.orig_system_reference "Site
REF" ,
d.primary_flag
,
d.status
,
f.COUNTRY
,
f.ADDRESS1
,
f.ADDRESS2
,
f.ADDRESS3
,
f.ADDRESS4
,
f.CITY
,
f.POSTAL_CODE
,
f.STATE
,
f.PROVINCE
FROM HZ_CUST_ACCOUNTS
a ,
HZ_PARTIES
b ,
HZ_CUST_ACCT_SITES_ALL c ,
HZ_CUST_SITE_USES_ALL d ,
HZ_PARTY_SITES e ,
HZ_LOCATIONs f
WHERE a.party_id
= b.party_id
AND a.cust_account_id = c.cust_account_id
AND c.cust_acct_site_id = d.cust_acct_site_id
AND c.PARTY_SITE_ID = e.PARTY_SITE_ID
AND e.location_id = f.location_id
AND upper(party_name) LIKE upper('%computer
service and rentals%')
ORDER BY 2,3
SELECT
b.party_name,
b.party_type,
a.cust_account_id,
b.party_id,
a.account_number,
a.orig_system_reference,
a.status,
a.account_name
FROM
HZ_CUST_ACCOUNTS
a,
HZ_PARTIES
b
WHERE
a.party_id (+) = b.party_id and
(b.party_id in (300000005352190,300000001469001,300000005352164) or
upper(a.account_name) like upper('%comp%') or
upper(b.party_name) like upper('comp%'))
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Query Customer location
SELECT
HZ.PARTY_ID
, HZ.PARTY_NAME
, LOCATIONPEO.ADDRESS_STYLE
, LOCATIONPEO.COUNTRY ||','|| LOCATIONPEO.ADDRESS1 ||','|| LOCATIONPEO.CITY ||','|| LOCATIONPEO.POSTAL_CODE ADDRESS
, CUSTOMERACCOUNTSITEPEO.STATUS
, CUSTOMERACCOUNTSITEPEO.BILL_TO_FLAG
, CUSTOMERACCOUNTSITEPEO.SHIP_TO_FLAG
FROM
HZ_PARTIES HZ
, HZ_PARTY_SITES PartySitePEO
, HZ_LOCATIONS LocationPEO
, HZ_CUST_ACCT_SITES_ALL CustomerAccountSitePEO
WHERE
HZ.PARTY_ID = PARTYSITEPEO.PARTY_ID AND
(PARTYSITEPEO.LOCATION_ID = LOCATIONPEO.LOCATION_ID) AND
(PARTYSITEPEO.PARTY_SITE_ID = CUSTOMERACCOUNTSITEPEO.PARTY_SITE_ID(+)) and
upper(HZ.PARTY_NAME) like upper('Comp%')
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Query Customer Site
Information - 1
SELECT
HZ.PARTY_NAME
, HZ.PARTY_ID
, PARTYSITEPEO.PARTY_SITE_ID
, LOCATIONPEO.LOCATION_ID
, LOCATIONPEO.ADDRESS_STYLE
, LOCATIONPEO.COUNTRY ||','|| LOCATIONPEO.ADDRESS1 ||','|| LOCATIONPEO.CITY ||','|| LOCATIONPEO.POSTAL_CODE ADDRESS
, CUSTOMERACCOUNTSITEPEO.STATUS
, CUSTOMERACCOUNTSITEPEO.BILL_TO_FLAG
, CUSTOMERACCOUNTSITEPEO.SHIP_TO_FLAG
FROM
HZ_PARTIES HZ
, HZ_PARTY_SITES PartySitePEO
, HZ_LOCATIONS LocationPEO
, HZ_CUST_ACCT_SITES_ALL CustomerAccountSitePEO
WHERE
HZ.PARTY_ID = PARTYSITEPEO.PARTY_ID AND
(PARTYSITEPEO.LOCATION_ID = LOCATIONPEO.LOCATION_ID) AND
(PARTYSITEPEO.PARTY_SITE_ID = CUSTOMERACCOUNTSITEPEO.PARTY_SITE_ID(+)) AND
upper(HZ.PARTY_NAME) like upper('Comp%')
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Customer Site Information
– 2
select
a.PARTY_NAME
, a.PARTY_ID
, a.PARTY_NUMBER
, a.PARTY_TYPE
, a.ORIG_SYSTEM_REFERENCE
, a.COUNTRY
, a.ADDRESS1 ||','|| CITY ||','|| POSTAL_CODE ||','|| STATE "ACCOUNT ADDRESS"
, a.STATUS "Party
Status"
, a.IDEN_ADDR_PARTY_SITE_ID
, a.IDEN_ADDR_LOCATION_ID
, b.PARTY_SITE_ID
, b.LOCATION_ID
, b.ORIG_SYSTEM_REFERENCE "Party
Site Orig ref"
, b.PARTY_SITE_NUMBER
, b.IDENTIFYING_ADDRESS_FLAG
, b.STATUS "Party
Site Status"
, b.PARTY_SITE_NAME
, b.OVERALL_PRIMARY_FLAG
, c.PARTY_SITE_USE_ID
, c.SITE_USE_TYPE
, c.PRIMARY_PER_TYPE
, c.STATUS "Party
Site Use Status"
from
hz_parties a,
HZ_PARTY_SITES b,
HZ_PARTY_SITE_USES c
where
(upper(a.party_name) like upper('Comp%') or
a.party_id in (300000005352190,300000001469001,300000005352164)) and
a.party_id = b.party_id and
b.party_site_id = c. party_site_id
ORder by
a.PARTY_NAME
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Customer Site Information
– 3
select
a.PARTY_NAME
,a.PARTY_ID
,a.PARTY_NUMBER
,a.PARTY_TYPE
,a.ORIG_SYSTEM_REFERENCE
,a.COUNTRY
,a.ADDRESS1 ||','|| CITY ||','|| POSTAL_CODE ||','|| STATE "ACCOUNT ADDRESS"
,a.STATUS
,a.IDEN_ADDR_PARTY_SITE_ID
,a.IDEN_ADDR_LOCATION_ID
,b.ACCOUNT_NAME
,c.CUST_ACCT_SITE_ID
,c.CUST_ACCOUNT_ID
,c.PARTY_SITE_ID
,c.ORIG_SYSTEM_REFERENCE "Cust Acct Site Origin
REF"
,c.STATUS "Cust Acct Site Status"
,c.SET_ID "Cust Acct Site Set ID"
,c.BILL_TO_FLAG
,c.MARKET_FLAG
,c.SHIP_TO_FLAG
,d.ORIG_SYSTEM_REFERENCE "Cust Acct Site Use Origin
REF"
,d.STATUS "Cust Acct Site Use Status"
,d.SET_ID
,d.SITE_USE_ID
,d.SITE_USE_CODE
,d.PRIMARY_FLAG
,d.LOCATION
,d.BILL_TO_SITE_USE_ID
from hz_parties a,
hz_cust_accounts b,
hz_cust_acct_sites_all
c,
hz_cust_site_uses_all
d
where
(upper(a.party_name) like upper('Comp%') or
a.party_id in (300000005352190,300000001469001,300000005352164)) and
a.party_id = b.party_id (+)
and c.cust_account_id = b.cust_account_id
and c.cust_acct_site_id = d.cust_acct_site_id
order by
a.PARTY_NAME
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- TWO NEWS SQLs
SELECT accounts.party_id
,
accounts.account_name
,
accounts.account_number
,
party_sites.party_site_name ,
sites.cust_acct_site_id
,
sites.cust_account_id
,
site_uses.site_use_code
,
site_uses.primary_flag
,
site_uses.site_use_id
,
site_uses.location
,
locations.ADDRESS1 || ' ' || locations.ADDRESS2 || '
' || locations.ADDRESS3 || ' ' || locations.ADDRESS4 || '
' || locations.CITY || ' ' || locations.POSTAL_CODE || '
' || locations.STATE ||
' ' || locations.country ,
territory.territory_short_name
FROM fusion.hz_cust_accounts accounts ,
fusion.hz_cust_acct_sites_all
sites ,
fusion.hz_party_sites
party_sites ,
fusion.hz_cust_site_uses_all site_uses ,
fusion.hz_locations
locations ,
fusion.fnd_territories_vl territory
WHERE sites.cust_account_id
= accounts.cust_account_id
AND party_sites.party_site_id = sites.party_site_id
AND site_uses.cust_acct_site_id = sites.cust_acct_site_id
AND locations.location_id = party_sites.location_id
-- AND site_uses.site_use_code =
'BILL_TO'
-- AND site_uses.primary_flag = 'Y'
AND site_uses.STATUS = 'A'
AND accounts.STATUS = 'A'
AND sites.STATUS = 'A'
AND locations.COUNTRY = territory.territory_code
AND EXISTS
(
SELECT 1
FROM fusion.fnd_setid_assignments
WHERE
set_id
= sites.set_id
AND reference_group_name = 'HZ_CUSTOMER_ACCOUNT_SITE'
AND
determinant_type = 'BU'
)
and upper(accounts.account_name ) like upper('&CUSTOMER_NAME%')
SELECT HZ.PARTY_ID
,
CUSTOMERACCOUNTSITEPEO.CUST_ACCOUNT_ID ,
HZ.PARTY_NAME
,
lOCATIONPEO.ADDRESS_STYLE
,
lOCATIONPEO.COUNTRY ||','|| LOCATIONPEO.ADDRESS1 ||','|| LOCATIONPEO.CITY ||','|| LOCATIONPEO.POSTAL_CODE LOCATION ,
CUSTOMERACCOUNTSITEPEO.STATUS "Account
Site Status" ,
CUSTOMERACCOUNTSITEPEO.BILL_TO_FLAG
,
CUSTOMERACCOUNTSITEPEO.SHIP_TO_FLAG
,
CUSTOMERACCOUNTSITEUSEPEO.SITE_USE_CODE
,
CUSTOMERACCOUNTSITEUSEPEO.PRIMARY_FLAG
,
CUSTOMERACCOUNTSITEUSEPEO.STATUS "Cust_Site_Use Status" ,
CUSTOMERACCOUNTSITEUSEPEO.ORIG_SYSTEM_REFERENCE ,
CUSTOMERACCOUNTSITEUSEPEO.CUST_ACCT_SITE_ID
,
CUSTOMERACCOUNTSITEPEO.SET_ID
FROM fusion.HZ_PARTIES
HZ
,
fusion.HZ_PARTY_SITES
PartySitePEO
,
fusion.HZ_LOCATIONS
LocationPEO
,
fusion.HZ_CUST_ACCT_SITES_ALL
CustomerAccountSitePEO ,
fusion.HZ_CUST_SITE_USES_ALL
CustomerAccountSiteUsePEO
WHERE HZ.PARTY_ID = PARTYSITEPEO.PARTY_ID
AND
(PARTYSITEPEO.LOCATION_ID = LOCATIONPEO.LOCATION_ID)
AND
(PARTYSITEPEO.PARTY_SITE_ID = CUSTOMERACCOUNTSITEPEO.PARTY_SITE_ID)
AND
(CUSTOMERACCOUNTSITEPEO.CUST_ACCT_SITE_ID = CUSTOMERACCOUNTSITEUSEPEO.CUST_ACCT_SITE_ID)
and upper(HZ.PARTY_NAME) like upper('&CUSTOMER_NAME%')
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Customer Contact
Information
SELECT PARTY_ID ,
Person_last_name ,
person_first_name,
party_number ,
party_name ,
status ,
email_address
FROM fusion.hz_Parties
WHERE upper (party_name)LIKE upper('%&PARTY_NAME%')
AND party_type = 'PERSON'
SELECT hzporg.PARTY_NUMBER ,
hzporg.PARTY_NAME ,
hzporg.PARTY_TYPE ,
hZp.Person_last_name ,
hZp.person_first_name,
hZp.party_number ,
hZp.party_name
,
hZp.status
,
hZp.email_address
FROM fusion.HZ_RELATIONSHIPS HZR,
fusion.hz_parties
hzp ,
fusion.HZ_PARTIES hzpORG
WHERE HZR.OBJECT_id = hzp.party_id
AND upper (hzp.party_name) LIKE upper('%&PARTY_NAME%')
AND hzp.party_type
= 'PERSON'
AND hzr.relationship_code = 'CONTACT'
AND hzr.subject_id = hzporg.party_id
ORDER BY hzporg.PARTY_NAME ,
hZp.Person_last_name,
hZp.person_first_name
SELECT hzo.CONTACT_NUMBER ,
hzporg.PARTY_NUMBER ,
hzporg.PARTY_NAME ,
hzporg.PARTY_TYPE ,
hzr.subject_id
,
hzo.ORG_CONTACT_ID ,
hZp.Person_last_name ,
hZp.person_first_name,
hZp.party_number ,
hZp.party_name
,
hZp.status
,
hZp.email_address ,
hzp.party_id
FROM fusion.HZ_RELATIONSHIPS HZR,
fusion.hz_parties
hzp ,
fusion.hz_ORG_CONTACTS hzo ,
fusion.HZ_PARTIES hzpORG
WHERE HZR.OBJECT_id = hzp.party_id
AND upper (hzp.party_name)LIKE upper('%&PARTY_NAME%')
AND hzp.party_type
= 'PERSON'
AND hzr.relationship_code = 'CONTACT'
AND hzr.subject_id
= hzporg.party_id
AND hzo.PARTY_RELATIONSHIP_ID = HZR.Relationship_id
ORDER BY hzporg.PARTY_NAME ,
hZp.Person_last_name,
hZp.person_first_name
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- DOO Set ID Assignments
SELECT fss.SET_ID ,
FSS.SET_NAME ,
FSA.DETERMINANT_VALUE ,
fsa.ENTERPRISE_ID ,
fsa.REFERENCE_GROUP_NAME ,
haotl.ORGANIZATION_ID ,
haotl.NAME BU_NAME
FROM Fnd_Setid_Assignments FSA,
fnd_SETID_SETS FSS,
HR_ORGANIZATION_UNITS_F_TL haotl
WHERE fss.set_ID
= FSA.SET_ID
AND FSA.DETERMINANT_VALUE = haotl.ORGANIZATION_ID
AND Reference_Group_Name LIKE 'DOO%'
AND Determinant_Type = 'BU'
ORDER BY fss.SET_ID ,
FSS.SET_NAME ,
fsa.REFERENCE_GROUP_NAME ,
fsa.REFERENCE_GROUP_NAME;
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- ESS Status
SELECT
STATE
, REQUESTID
, NAME
, EXECUTABLE_STATUS
, ERROR_WARNING_MESSAGE
, ERROR_WARNING_DETAIL
, cmdline
, workdirectoryroot
, logworkdirectory
, inputworkdirectory
, outputworkdirectory
, redirectedoutputfile
FROM
Fusion_ora_ess.request_history
WHERE
REQUESTID = 20780
ORDER BY
REQUESTID DESC
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Extensible FlexFlields
(EFF) - EFF- to be verified
select
fdct.application_ID,
fdct.descriptive_flexfield_code,
fdct.context_code,
fdcb.context_identifier,
fdcb.enabled_flag,
fdct.description
from
fnd_df_contexts_TL fdct,
fnd_df_contexts_B fdcb
where
fdct.context_code = fdcb.context_code and
fdct.application_id = fdcb.application_id and
fdct.language = 'US' and
fdct.descriptive_flexfield_code = 'DOO_FULFILL_LINES_ADD_INFO'
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Select ALL Segments
(Fields defined in a Contexts)
select
fdst.DESCRIPTIVE_FLEXFIELD_CODE
,fdst.CONTEXT_CODE
,fdst.SEGMENT_CODE
,fdst.NAME
,fdsb.application_ID
,fdsb.descriptive_flexfield_code
,fdsb.context_code
,fdsb.segment_code
,fdsb.SEGMENT_IDENTIFIER
,fdsb.column_name
,fdsb.sequence_number
,fdsb.enabled_flag
,fdsb.required_flag
,fdsb.value_set_id
,fdsb.default_type
,fdsb.default_value
,fdsb.derivation_value
,fdsb.range_type
,fdsb.Read_only_flag
,fdsb.display_type
,fdsb.display_width
,fdsb.display_height
,fdsb.checkbox_checked_value
,fdsb.checkbox_unchecked_value
from
fnd_df_segments_tl fdst,
fnd_df_segments_b fdsb
where
fdst.APPLICATION_ID = fdsb.APPLICATION_ID and
fdst.ENTERPRISE_ID = fdsb.ENTERPRISE_ID and
fdst.DESCRIPTIVE_FLEXFIELD_CODE = fdsb.DESCRIPTIVE_FLEXFIELD_CODE and
fdst.CONTEXT_CODE = fdsb.CONTEXT_CODE and
fdst.SEGMENT_CODE = fdsb.SEGMENT_CODE and
fdst.language = 'US' and
fdst.descriptive_flexfield_code = 'DOO_FULFILL_LINES_ADD_INFO'
order by
fdst.CONTEXT_CODE,
fdsb.SEQUENCE_NUMBER
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Select All Categories
select
application_ID
, descriptive_flexfield_code
, context_code
,category_code
from
FND_EF_CATEGORY_CONTEXTS
where
descriptive_flexfield_code = 'DOO_FULFILL_LINES_ADD_INFO'
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- EFF Miscellaneous - 1
select
feupb.application_ID
, feupb.descriptive_flexfield_code
, feupb.flexfield_usage_code
, feupb.category_code
, feupb.page_code
, feupt.name
, feupb.sequence_number
, feupb.mds_document_name
from
FND_EF_UI_PAGES_B feupb,
FND_EF_UI_PAGES_tl feupt
where
feupt.descriptive_flexfield_code = 'DOO_FULFILL_LINES_ADD_INFO'
and feupt.APPLICATION_ID = feupb.APPLICATION_ID
and feupt.DESCRIPTIVE_FLEXFIELD_CODE = feupb.DESCRIPTIVE_FLEXFIELD_CODE
and feupt.FLEXFIELD_USAGE_CODE = feupb.FLEXFIELD_USAGE_CODE
and feupt.CATEGORY_CODE = feupb.CATEGORY_CODE
and feupt.PAGE_CODE = feupb.PAGE_CODE
and feupt.language = 'US'
order by
feupb.descriptive_flexfield_code
, feupb.flexfield_usage_code
, feupb.category_code
, feupb.page_code
, feupb.sequence_number
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- EFF Miscellaneous – 2
select
application_ID
, descriptive_flexfield_code
, flexfield_usage_code
, category_code
, page_code
, context_code
, context_category_code
, sequence_number
, mds_document_name
from
FND_EF_UI_PAGE_TASK_FLOWS
where
descriptive_flexfield_code = 'DOO_FULFILL_LINES_ADD_INFO'
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Profiles and Profile
Values - Query all profile options based on criteria
select
fpob.ENTERPRISE_ID
, fpob.PROFILE_OPTION_NAME
, fpot.DESCRIPTION
, fpob.USER_ENABLED_FLAG
, fpob.USER_UPDATEABLE_FLAG
, fpob.SQL_VALIDATION
from
fnd_profile_options_B fpob,
fnd_profile_options_tl fpot
where
fpot.ENTERPRISE_ID = fpob.ENTERPRISE_ID AND
fpot.PROFILE_OPTION_NAME = fpob.PROFILE_OPTION_NAME and
fpot.LANGUAGE = 'US' AND
upper(fpob.PROFILE_OPTION_NAME) like ('%DOO%')
order by
2, 1
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Query the values set for a
profiles – in this instance DOO
select
p.profile_option_name,
l.level_name,
v.level_value,
l.enabled_flag,
v.profile_option_value
from
fnd_profile_option_values v,
fnd_profile_options_b p,
FND_PROFILE_OPTION_LEVELS l
where
p.profile_option_id = l.profile_option_id and
p.application_id = l.application_id and
-- l.enabled_flag = 'Y' and
l.profile_option_id = v.profile_option_id and
l.application_id = v.application_id and
l.level_name = v.level_name and
p.profile_option_name like 'DOO%'
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Roles assigned to a User
SELECT pu.username ,
pur.role_id ,
pr.active_flag,
pr.role_common_name
FROM fusion.per_users pu ,
fusion.per_user_roles pur,
fusion.per_roles_dn pr
WHERE pu.user_id
= pur.user_id
AND pur.role_id = pr.role_id
AND upper(Pu.Username) like upper('SCM%')
ORDER BY pu.username
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Sets - including Common
Set
SELECT
FSS.ENTERPRISE_ID,
FSS.SET_CODE,
FSS.SET_NAME
FROM
fnd_SETID_SETS
FSS
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Tax Classification Codes
SELECT
FSS.SET_ID ,
FSS.SET_NAME ,
FLVT.LOOKUP_TYPE ,
FLVT.LOOKUP_CODE
FROM
FND_LOOKUP_VALUES_TL
FLVT,
FND_SETID_SETS
FSS
WHERE
FSS.SET_ID = FLVT.SET_ID
AND LOOKUP_TYPE IN(
'ZX_INPUT_CLASSIFICATIONS'
,'ZX_OUTPUT_CLASSIFICATIONS'
,'ZX_WEB_EXP_TAX_CLASSIFICATIONS'
,'ZX_WHT_TAX_CLASSIFICATION_CODE')
ORDER BY
FLVT.LOOKUP_TYPE ,
FLVT.LOOKUP_CODE
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Collected Data - Query
which Entity types have been collected - 1
select
distinct(entity_name)
from
msc_xref_mapping
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Query which Entity types
have been collected - 1
select
entity_name,
attribute_name
from
msc_xref_mapping
group by
entity_name,
attribute_name
order by
1,2
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Verify collection of
specific Enitity Type
select
mai.instance_code,
mxm.sr_instance_id,
mxm.entity_name,
mxm.attribute_name,
mxm.source_value,
mxm.target_value
from
msc_xref_mapping
mxm,
msc_apps_instances mai
where
mai.instance_id = mxm.sr_instance_id and
entity_name = 'UOM_CODES'
order by
1,3,4
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Cross References --
Collected data is stored in the MSC_XREF_MAPPING table and is identified by the
Source System and Cross Reference type (ENTITY_NAME, ATTRIBUTE_NAME)
-- The following SQL can be
run to check which data has been collected
SELECT
DISTINCT (entity_name)
FROM
msc_xref_mapping
ORDER BY 1;
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- The following SQL can then
be run to identify all the data that has been collected for a specific entity.
Carriers
SELECT instance_code ,
Attribute_Name ,
entity_name ,
source_value ,
Target_value ,
instance_id
FROM Fusion.Msc_Xref_Mapping Mxm,
fusion.msc_apps_instances mai
WHERE Mxm.Sr_Instance_Id = Mai.Instance_Id
AND
Entity_Name = 'CARRIERS'
ORDER BY Instance_Code,
Attribute_Name,
Target_Value
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Currencies
SELECT instance_code ,
Attribute_Name ,
entity_name ,
source_value ,
Target_value ,
instance_id
FROM Fusion.Msc_Xref_Mapping Mxm,
fusion.msc_apps_instances mai
WHERE Mxm.Sr_Instance_Id = Mai.Instance_Id
AND
Entity_Name = 'CURRENCIES'
ORDER BY Instance_Code,
Attribute_Name,
Target_Value
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Currency Conversion Types
SELECT instance_code ,
Attribute_Name ,
entity_name ,
source_value ,
Target_value ,
instance_id
FROM Fusion.Msc_Xref_Mapping Mxm,
fusion.msc_apps_instances mai
WHERE Mxm.Sr_Instance_Id = Mai.Instance_Id
AND
entity_name = 'CURRENCY_CONV_TYPES'
ORDER BY Instance_Code,
Attribute_Name,
Target_Value
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Demand Class
SELECT instance_code ,
Attribute_Name ,
entity_name ,
source_value ,
Target_value ,
instance_id
FROM Fusion.Msc_Xref_Mapping Mxm,
fusion.msc_apps_instances mai
WHERE Mxm.Sr_Instance_Id = Mai.Instance_Id
AND
Entity_Name = 'DEMAND_CLASS'
ORDER BY Instance_Code,
Attribute_Name,
Target_Value
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Free On Board (FOB)
select instance_code
, Attribute_Name
, entity_name
, source_value
, Target_value
, instance_id
From
Fusion.Msc_Xref_Mapping Mxm,
fusion.msc_apps_instances mai
where
Mxm.Sr_Instance_Id = Mai.Instance_Id
And Entity_Name = 'FOB'
Order By Instance_Code, Attribute_Name, Target_Value
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Freight Charge Terms
SELECT instance_code ,
Attribute_Name ,
entity_name ,
source_value ,
Target_value ,
instance_id
FROM Fusion.Msc_Xref_Mapping Mxm,
fusion.msc_apps_instances mai
WHERE Mxm.Sr_Instance_Id = Mai.Instance_Id
AND Entity_Name
= 'WSH_FREIGHT_CHARGE_TERMS'
ORDER BY Instance_Code,
Attribute_Name,
Target_Value
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Invoicing and Account
Rules
SELECT instance_code ,
Attribute_Name ,
entity_name ,
source_value ,
Target_value ,
instance_id
FROM Fusion.Msc_Xref_Mapping Mxm,
fusion.msc_apps_instances mai
WHERE Mxm.Sr_Instance_Id = Mai.Instance_Id
AND
entity_name = 'INVOICING_ACCT_RULES'
ORDER BY Instance_Code,
Attribute_Name,
Target_Value
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Mode of Transport
SELECT instance_code ,
Attribute_Name ,
entity_name ,
source_value ,
Target_value ,
instance_id
FROM Fusion.Msc_Xref_Mapping Mxm,
fusion.msc_apps_instances mai
WHERE Mxm.Sr_Instance_Id = Mai.Instance_Id
AND
entity_name = 'WSH_MODE_OF_TRANSPORT'
ORDER BY Instance_Code,
Attribute_Name,
Target_Value
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Payment Terms
SELECT instance_code ,
Attribute_Name ,
entity_name ,
source_value ,
Target_value ,
instance_id
FROM Fusion.Msc_Xref_Mapping Mxm,
fusion.msc_apps_instances mai
WHERE Mxm.Sr_Instance_Id = Mai.Instance_Id
AND
entity_name = 'PAYMENT_TERMS'
ORDER BY Instance_Code,
Attribute_Name,
Target_Value
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Return Reason
SELECT instance_code ,
Attribute_Name ,
entity_name ,
source_value ,
Target_value ,
instance_id
FROM Fusion.Msc_Xref_Mapping Mxm,
fusion.msc_apps_instances mai
WHERE Mxm.Sr_Instance_Id = Mai.Instance_Id
AND
Entity_Name = 'RETURN_REASON'
ORDER BY Instance_Code,
Attribute_Name,
Target_Value
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Service Levels
SELECT instance_code ,
Attribute_Name ,
entity_name ,
source_value ,
Target_value ,
instance_id
FROM Fusion.Msc_Xref_Mapping Mxm,
fusion.msc_apps_instances mai
WHERE Mxm.Sr_Instance_Id = Mai.Instance_Id
AND
entity_name = 'WSH_SERVICE_LEVELS'
ORDER BY Instance_Code,
Attribute_Name,
Target_Value
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Shipment Priority
SELECT instance_code ,
Attribute_Name ,
entity_name ,
source_value ,
Target_value ,
instance_id
FROM Fusion.Msc_Xref_Mapping Mxm,
fusion.msc_apps_instances mai
WHERE Mxm.Sr_Instance_Id = Mai.Instance_Id
AND
Entity_Name = 'SHIPMENT_PRIORITY'
ORDER BY Instance_Code,
Attribute_Name,
Target_Value
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Tax Classification Code
SELECT instance_code ,
Attribute_Name ,
entity_name ,
source_value ,
Target_value ,
instance_id
FROM Fusion.Msc_Xref_Mapping Mxm,
fusion.msc_apps_instances mai
WHERE Mxm.Sr_Instance_Id = Mai.Instance_Id
AND
Entity_Name = 'ZX_OUTPUT_CLASSIFICATIONS'
ORDER BY Instance_Code,
Attribute_Name,
Target_Value
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Tax Exemption Reason Code
SELECT instance_code ,
Attribute_Name ,
entity_name ,
source_value ,
Target_value ,
instance_id
FROM Fusion.Msc_Xref_Mapping Mxm,
fusion.msc_apps_instances mai
WHERE Mxm.Sr_Instance_Id = Mai.Instance_Id
AND
Entity_Name = 'ZX_EXEMPTION_REASON_CODE'
ORDER BY Instance_Code,
Attribute_Name,
Target_Value
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Unit of Measure Code
SELECT instance_code ,
Attribute_Name ,
entity_name ,
source_value ,
Target_value ,
instance_id
FROM Fusion.Msc_Xref_Mapping Mxm,
fusion.msc_apps_instances mai
WHERE mxm.sr_instance_id = mai.instance_id
AND
entity_name = 'UOM_CODES'
ORDER BY Instance_Code,
Attribute_Name,
Target_Value
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- INVENTORY - UOM
SELECT iuctl.uom_class_tl,
iuctl.description
FROM INV_UOM_CLASSES_B iucb,
INV_UOM_CLASSES_TL iuctl
WHERE iucb.uom_class = iuctl.uom_class
AND iuctl.language = 'US'
SELECT iuomb.uom_code
,
iuomb.uom_class
,
iuomb.BASE_UOM_FLAG ,
iuomtl.unit_of_measure,
iuomtl.description ,
iuctl.uom_class_tl ,
iuctl.description
FROM INV_UNITS_OF_MEASURE_B iuomb ,
INV_UNITS_OF_MEASURE_TL iuomtl,
INV_UOM_CLASSES_B iucb ,
INV_UOM_CLASSES_TL iuctl
WHERE iuomb.unit_of_measure_id = iuomtl.unit_of_measure_id
AND iuomtl.language
= 'US'
AND iucb.uom_class = iuomb.uom_class
AND iucb.uom_class = iuctl.uom_class
AND iuctl.language
= 'US'
ORDER BY iuomb.uom_class,
iuomb.uom_code
SELECT * FROM INV_UOM_CLASS_CONVERSIONS
SELECT * FROM INV_UOM_CONVERSIONS
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Inventory Item
SELECT itm.INVENTORY_ITEM_ID
,
itm.ORGANIZATION_ID
,
itm.ITEM_NUMBER
,
itm.ENABLED_FLAG
,
DECODE(itm.location_control_code, 1, 'N', 'Y')
LOC_CTL
,
DECODE(itm.lot_control_code, 1, 'N', 'Y')
LOT_CTL
,
DECODE(itm.revision_qty_control_code, 1, 'N', 'Y')
REV_CTL
,
DECODE(itm.serial_number_control_code, 2, 'Predefined', 5, 'At Receipt', 6, 'SO Issue', 'N') SER_CTL,
DECODE(itm.restrict_locators_code, 1, 'Y', 'N')
RESTR_LOC
,
DECODE(itm.restrict_subinventories_code, 1, 'Y', 'N')
RESTR_SUB
,
DECODE(itm.reservable_type, 1, 'Y', 'N')
RESV_FLAG
,
itm.PURCHASING_ENABLED_FLAG
,
itm.CUSTOMER_ORDER_ENABLED_FLAG
,
itm.INTERNAL_ORDER_ENABLED_FLAG
,
itm.PURCHASING_ITEM_FLAG
,
itm.SHIPPABLE_ITEM_FLAG
,
itm.CUSTOMER_ORDER_FLAG
,
itm.INTERNAL_ORDER_FLAG
,
itm.INVENTORY_ITEM_FLAG
,
itm.INVENTORY_ASSET_FLAG
,
itm.mtl_transactions_enabled_flag
TRANS_FLAG
,
itm.shippable_item_flag
SHIP_FLAG
,
itm.replenish_to_order_flag ATO_FLAG
,
itm.ship_model_complete_flag SMC_FLAG
FROM EGP_SYSTEM_ITEMS_B itm
WHERE itm.ITEM_NUMBER LIKE 'AS54%'
ORDER BY itm.INVENTORY_ITEM_ID,
itm.ORGANIZATION_ID;
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Inventory On Hand
SELECT si.inventory_item_id ,
si.item_number
,
si.organization_id ,
inv.organization_code,
si.enabled_flag
,
si.end_date_active ,
ohq.transaction_quantity ON_HAND
FROM fusion.EGP_SYSTEM_ITEMS_B si ,
fusion.INV_ORG_PARAMETERS inv,
fusion.INV_ONHAND_QUANTITIES_DETAIL ohq
WHERE inv.organization_id = si.organization_id
AND inv.organization_id = ohq.organization_id
AND si.inventory_item_id = ohq.inventory_item_id
AND si.item_number LIKE 'AS54888%'
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Inventory Cross
Referencing - Query Inventory Item Xref information
SELECT esit.inventory_item_id
,
esit.organization_id
,
esib.item_number
,
eirb.cross_reference
,
eirb.sub_type
,
eirb.Item_Relationship_Type,
eirb.item_relationship_id ,
esit.description
,
EIRB.UOM_CODE
FROM egp_item_relationships_b EIRB,
egp_system_items_tl ESIT ,
egp_system_items_b ESIB
WHERE EIRB.ITEM_RELATIONSHIP_TYPE IN ('ITEM_XREF', 'SYS_ITEM_XREF')
AND ESIT.INVENTORY_ITEM_ID = EIRB.INVENTORY_ITEM_ID
AND
(
ESIT.INVENTORY_ITEM_ID = ESIB.INVENTORY_ITEM_ID
AND ESIT.ORGANIZATION_ID = ESIB.ORGANIZATION_ID
)
AND esit.inventory_item_id = 300000001590006
ORDER BY 1,2,3,6,7
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Organization Details --
Organization Parameters
SELECT haotl.NAME
,
iop.*
,
MC.calendar_Code,
mc.description
FROM Inv_Org_Parameters
iop ,
HR_ORGANIZATION_UNITS_F_TL haotl,
MSC_CALENDARS mc
WHERE haotl.organization_id = iop.business_unit_id
AND TO_CHAR(iop.Schedule_id) = mc.SR_CALENDAR_CODE (+)
ORDER BY haotl.NAME,
ORGANIZATION_CODE
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Organization Details
SELECT IOP.ORGANIZATION_CODE,
IOP.ORGANIZATION_ID ,
IOP.INVENTORY_FLAG ,
(
SELECT NAME
FROM
HR_ORGANIZATION_UNITS_F_TL
WHERE iop.organization_id = organization_id
AND sysdate BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
)
"Org Name"
,
IOP.BUSINESS_UNIT_ID ,
(
SELECT name
FROM
HR_ORGANIZATION_UNITS_F_TL
WHERE iop.BUSINESS_UNIT_ID = organization_id
AND sysdate BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
)
"BU Name" ,
(
SELECT ORG_INFORMATION4
FROM
hr_organization_information_f
WHERE iop.BUSINESS_UNIT_ID = organization_id
AND sysdate BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
AND ORG_INFORMATION_CONTEXT = 'FUN_BUSINESS_UNIT'
)
"Set ID"
FROM INV_ORG_PARAMETERS IOP
ORDER BY 4,6
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Organization Parameters
SELECT haotl.NAME
,
iop.*
,
MC.calendar_Code,
mc.description
FROM Inv_Org_Parameters
iop ,
HR_ORGANIZATION_UNITS_F_TL haotl,
MSC_CALENDARS mc
WHERE haotl.organization_id = iop.business_unit_id
AND TO_CHAR(iop.Schedule_id) = mc.SR_CALENDAR_CODE (+)
ORDER BY haotl.NAME,
ORGANIZATION_CODE
ORDER MANAGEMENT
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- GROUP_ID - used to find
SOA Trace in EM
SELECT
/*3-19275415291*/
DISTINCT order_number,
'GROUP_ID='
||
group_id
FROM fusion.doo_orchestration_groups dog,
fusion.doo_fulfill_lines_all dfla ,
fusion.doo_headers_all dha
WHERE dog.Fulfillment_line_id = dfla.fulfill_line_id
AND dha.header_id = dfla.header_id
AND
order_number IN ('&ORDER_NUMBER')
AND submitted_flag
= 'Y'
AND
status
= 'ACTIVE'
ORDER BY 1,
2
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- USE the Value -
GROUP_ID=..... to search in Name field in EM COMPOSITE Instances - used to find
SOA Trace in EM Obtain BPEL_INSTANCE_ID for the ORDER
SELECT DISTINCT ecid
FROM fa_fusion_soainfra.composite_instance
WHERE title = 'ENTER
Order Number'
SELECT distinct(compi.ecid)
FROM SCM_FUSION_SOAINFRA.composite_instance compi ,
SCM_FUSION_SOAINFRA.cube_instance ci
WHERE ci.cikey IN(SELECT dpbi.bpel_instance_id
FROM doo_headers_all
dha ,
DOO_ORCHESTRATION_GROUPS dog,
DOO_PROCESS_BPEL_INSTANCES dpbi
WHERE dha.header_id
= dog.header_id
AND dog.doo_process_instance_id = dpbi.process_instance_id
AND dha.source_order_number = 'ENTER Order Number')
AND ci.ecid = compi.ecid
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- PREVIOUS SQL is a
concatenation of the following 2 SQLs - you should ideally use the first SQL
provided in this blocl
SELECT dha.source_order_number,
dpbi.bpel_instance_id
FROM doo_headers_all
dha ,
DOO_ORCHESTRATION_GROUPS dog,
DOO_PROCESS_BPEL_INSTANCES dpbi
WHERE dha.header_id
= dog.header_id
AND dog.doo_process_instance_id = dpbi.process_instance_id
AND dha.source_order_number = 'ENTER Order Number'
GROUP BY dha.source_order_number,
dpbi.bpel_instance_id
ORDER BY 2 ASC;
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Use the BPEL_INSTANCE_ID
to Obtain the Composite ID from the SCM_FUSION_SOAINFRA instance. The ID
returned can then be used to search on Instance ID in EM
SELECT ci.cikey
,
compi.ecid ,
compi.id
,
compi.source_name,
compi.Source_action_name
FROM FA_FUSION_SOAINFRA.composite_instance compi ,
FA_FUSION_SOAINFRA.cube_instance ci
WHERE ci.cikey IN(ENTER BPEL INSTANCE IDs
obtaine in first SQL)
AND ci.ecid = compi.ecid
ORDER BY ci.cikey,
compi.id
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Error Messages - SQL used
to identify errors raised against an Order - NOTE the commented line to limit
data on the Order Number.
select H.SOURCE_ORDER_NUMBER,
H.ORDER_NUMBER
,
REQ.CREATION_DATE ,
REQ.ACTIVE_FLAG
,
REQ.REQ_ENTITY_TYPE ,
REQ.REQ_ENTITY_ID1 ,
REQ.REQ_ENTITY_ID2 ,
REQ.REQ_ENTITY_ID3 ,
REQ.REQ_ENTITY_ID4 ,
REQ.REQ_ENTITY_ID5 ,
MB.MESSAGE_ID
,
MT.MESSAGE_ID
,
MT.MESSAGE_TEXT
,
MB.MESSAGE_TYPE
,
MB.MSG_REQUEST_ID ,
MB.MESSAGE_NAME
from DOO_MESSAGE_REQUESTS REQ,
DOO_MESSAGES_B MB ,
DOO_MESSAGES_TL MT ,
DOO_HEADERS_ALL H
where MB.MSG_REQUEST_ID =REQ.MSG_REQUEST_ID
and REQ.HEADER_ID =H.HEADER_ID
and MB.MESSAGE_ID = MT.MESSAGE_ID
and MT.LANGUAGE = 'US'
--
AND H.ORDER_NUMBER=&ORDER_NUMBER
order by 1;
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Error Messages returned by
Order Import Process
SELECT
/*3-19174365611*/
dmv.creation_date
,
dohai.interface_status_code
,
DMV.MESSAGE_TEXT
,
dolai.ORDERED_UOM_CODE
,
dolai.product_number
,
dolai.product_id
,
DOLAI.CUSTOMER_PRODUCT_ID
,
dolai.REQUESTED_FULFILLMENT_ORG_CODE,
dohai.*
,
DOLAI.*
FROM fusion.DOO_ORDER_HEADERS_ALL_INT dohai,
fusion.DOO_ORDER_LINES_ALL_INT dOLai ,
fusion.DOO_MESSAGES_VL
dmv ,
fusion.DOO_MESSAGE_REQUESTS DMR
WHERE dohai.source_transaction_ID = dmv.msg_entity_id1
AND dohai.source_transaction_ID = dolai.source_transaction_ID
AND dohai.interface_status_code IS NOT NULL
AND dohai.load_request_id
= dolai.load_request_id
AND DMR.ACTIVE_FLAG
= 'Y'
AND DMV.MSG_REQUEST_ID
= DMR.MSG_REQUEST_ID
AND
(
(
dohai.Load_request_id
IN ('&LOAD_REQUEST_ID')
AND dohai.source_transaction_id IN ('&SOURCE_ORDER_NUMBER')
)
OR
message_text = '&ERROR_MESSAFE'
OR dohai.batch_name = '&BATCH_NAME'
)
ORDER BY dohai.creation_Date DESC ,
dohai.source_transaction_id,
dolai.SOURCE_TRANSACTION_LINE_NO
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Shipping and Inventory
Material Transactions
SELECT dha.source_order_number ,
dha.order_number
,
dfla.STATUS_CODE
,
dfla.SOURCE_LINE_NUMBER ,
wdd.*
,
wnd.*
,
wda.*
,
IMT.*
FROM fusion.doo_headers_All dha ,
fusion.doo_fulfill_lines_all dfla ,
fusion.WSH_DELIVERY_ASSIGNMENTS wda,
fusion.wsh_new_deliveries
wnd ,
fusion.WSH_DELIVERY_DETAILS
wdd ,
fusion.INV_MATERIAL_TXNS IMT
WHERE DHA.HEADER_ID = DFLA.HEADER_ID
--AND
wdd.SOURCE_HEADER_NUMBER
= dha.order_number
AND DFLA.FULFILL_LINE_ID = WDD.SOURCE_SHIPMENT_ID
AND Wdd.Delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id
= wnd.delivery_id (+)
AND wda.delivery_detail_id = imt.TRX_SOURCE_LINE_ID (+)
AND DHA.source_order_number LIKE '%SOURCE_ORDER_NUMBER'
AND DHA.SUBMITTED_FLAG = 'Y'
-- AND
imt.TRANSACTION_TYPE_ID (+) = 52
ORDER BY dha.source_order_number,
imt.transaction_id
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Fulfillment Line Task
Status
-- List all lines on an Order
and show the Orchestration Process and Tasks associated with these
select HEAD.SOURCE_ORDER_NUMBER
,
DLA.LINE_NUMBER
,
FLINE.INVENTORY_ORGANIZATION_ID,
FLINE.FULFILL_ORG_ID
,
ESIB.ITEM_NUMBER
,
FLINE.FULFILL_LINE_NUMBER
,
FLINE.REFERENCE_FLINE_ID
,
FLINE.INVENTORY_ITEM_ID ,
'LINE PROCESS' "
"
,
PROCDEF.PROCESS_DISPLAY_NAME
,
TASKDEF.name
,
TASKS.STATUS_CODE
,
TASKS.STEP_ACTIVE
,
'['
||
STEPDEFB.STEP_NUMBER
||
'] '
||
STEPDEFTL.STEP_NAME as STEP_NAME,
STEPS.STEP_STATUS
,
STEPS.STEP_ACTIVE
,
'HEADER INFO' "
"
,
HEAD.STATUS_CODE
,
HEAD.OPEN_FLAG
,
HEAD.ON_HOLD
,
HEAD.CANCELED_FLAG
,
HEAD.IS_EDITABLE
,
'GROUP INFO' "
"
,
GRPS.STATUS
,
GRPS.IS_GROUP_EDITABLE
,
'FULFILL LINE INFO' "
" ,
FLINE.ORDERED_QTY
,
FLINE.STATUS_CODE
,
FLINE.CANCELED_QTY
,
FLINE.FULFILLED_QTY
,
FLINE.SHIPPED_QTY
,
FLINE.RESERVED_QTY
,
FLINE.RESERVABLE_FLAG
,
FLINE.INVOICEABLE_ITEM_FLAG
,
FLINE.INVOICE_ENABLED_FLAG
,
FLINE.RETURNABLE_FLAG
,
FLINE.INVOICE_INTERFACED_FLAG ,
FLINE.OPEN_FLAG
,
FLINE.CANCELED_FLAG
,
FLINE.SHIP_SET_NAME
,
FLINE.REFERENCE_FLINE_ID
,
'OTHER INFO' "
"
,
GRPS.GROUP_ID
,
GRPS.FULFILLMENT_LINE_ID
,
GRPS.GROUP_SEQ_ID
,
TASKS.DOO_PROCESS_INSTANCE_ID ,
TASKS.TASK_INSTANCE_ID
,
STEPS.STEP_INSTANCE_ID
,
GRPS.LINE_ID
,
TASKS.JEOPARDY_SCORE
,
TASKS.JEOPARDY_REASON
,
PROC.DOO_PROCESS_ID
,
STEPS.STEP_ID
,
TASKS.TASK_ID
from FUSION.DOO_HEADERS_ALL
HEAD
,
FUSION.DOO_FULFILL_LINES_ALL
FLINE ,
FUSION.DOO_ORCHESTRATION_GROUPS
GRPS ,
FUSION.DOO_PROCESS_INSTANCES
PROC ,
FUSION.DOO_PROCESS_DEFINITIONS_TL PROCDEF ,
FUSION.DOO_PROCESS_STEP_INSTANCES
STEPS ,
FUSION.DOO_PROCESS_STEPS_B
STEPDEFB ,
FUSION.DOO_PROCESS_STEPS_TL
STEPDEFTL ,
FUSION.DOO_TASK_INSTANCES
TASKS ,
FUSION.DOO_TASK_DEFINITIONS_TL
TASKDEF ,
FUSION.DOO_LINES_ALL
DLA
,
EGP_SYSTEM_ITEMS_B ESIB
where HEAD.SOURCE_ORDER_NUMBER like 'PMC%'
and GRPS.HEADER_ID
= HEAD.HEADER_ID
and GRPS.FULFILLMENT_LINE_ID
= FLINE.FULFILL_LINE_ID
and GRPS.DOO_PROCESS_INSTANCE_ID = STEPS.DOO_PROCESS_INSTANCE_ID
and GRPS.DOO_PROCESS_INSTANCE_ID = PROC.DOO_PROCESS_INSTANCE_ID
and PROC.DOO_PROCESS_ID
= PROCDEF.DOO_PROCESS_ID
and GRPS.GROUP_ID
= STEPS.GROUP_ID
and STEPS.STEP_ID
= STEPDEFB.STEP_ID
and STEPDEFTL.STEP_ID
= STEPDEFB.STEP_ID
and STEPS.TASK_INSTANCE_ID
= TASKS.TASK_INSTANCE_ID
and TASKDEF.TASK_ID
= TASKS.TASK_ID
and FLINE.LINE_ID
= DLA.LINE_ID
and ESIB.INVENTORY_ITEM_ID
= FLINE.INVENTORY_ITEM_ID
and ESIB.INVENTORY_ORGANIZATION_ID = FLINE.INVENTORY_ORGANIZATION_ID
and PROCDEF.LANGUAGE
= 'US'
and STEPDEFTL.LANGUAGE
= 'US'
and TASKDEF.LANGUAGE
= 'US'
order by HEAD.SOURCE_ORDER_NUMBER desc,
GRPS.GROUP_ID
,
DLA.LINE_NUMBER
,
GRPS.FULFILLMENT_LINE_ID
,
STEPS.STEP_INSTANCE_ID;
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Header and Fulfillment
Line Customer information (Ship to, Bill To , Sold to)
-- Find Customer on the Order
Header
SELECT
headereo.source_order_number,
headereo.SOLD_TO_party_ID,
headereo.SOLD_TO_customer_ID,
PartyPEO.PARTY_NAME,
PartyPEO.PARTY_ID,
CustomerAccountPEO.ACCOUNT_NAME,
CustomerAccountPEO.ACCOUNT_NUMBER,
CustomerAccountPEO.CUST_ACCOUNT_ID
FROM
HZ_PARTIES
PartyPEO,
HZ_CUST_ACCOUNTS
CustomerAccountPEO,
doo_headers_all headereo
WHERE
PartyPEO.PARTY_ID = CustomerAccountPEO.PARTY_ID (+)
and (partypeo.Party_id = headereo.SOLD_TO_party_ID or
CustomerAccountPEO.cust_Account_id = headereo.SOLD_TO_customer_ID)
AND upper(headereo.source_order_number) in ('28134','PMC-161019-007')
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- To find the Ship to Or
Bill to customer on Fulfillment Line Obtain these details
select
source_order_number
, BILL_TO_CUSTOMER_ID
, SHIP_TO_CUSTOMER_ID
, SHIP_TO_PARTY_ID
From
doo_fulfill_lines_All
where
source_order_number in ('28134','PMC-161019-007')
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- And then run the following
SQL replacing values as appropriate
SELECT
PartyPEO.PARTY_NAME,
PartyPEO.PARTY_ID,
CustomerAccountPEO.ACCOUNT_NUMBER,
CustomerAccountPEO.CUST_ACCOUNT_ID
FROM
HZ_PARTIES
PartyPEO,
HZ_CUST_ACCOUNTS
CustomerAccountPEO
WHERE
PartyPEO.PARTY_ID = CustomerAccountPEO.PARTY_ID (+)
and (partypeo.Party_id = 300000005352164 or
CustomerAccountPEO.cust_Account_id = 300000005352164)
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Fulfillment Lines -
Current Version on an Order
-- NOTE: The following SQL
will only return the CURRENT Version of the Fulfillment Line
SELECT
dha.source_order_number,
dha.order_number,
dfla.*
FROM Doo_headers_all
dha ,
Doo_fulfill_lines_all DFLA
WHERE dha.source_order_number Like '28104'
AND DHA.header_id
= dfla.header_id
AND dfla.OBJECT_VERSION_NUMBER =
(
SELECT MAX(DFLA_MAX.OBJECT_VERSION_NUMBER)
FROM
Doo_fulfill_lines_all DFLA_MAX
WHERE
DFLa.fulfill_line_number
= DFLa_MAX.fulfill_line_number
AND DFLA.SOURCE_ORDER_SYSTEM = DFLA_MAX.SOURCE_ORDER_SYSTEM
AND dfla.source_order_number = dfla_max.source_order_number
)
ORDER BY DHA.order_number ,
SOURCE_LINE_NUMBER,
fulfill_line_Number
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Fulfillment Lines -
Additional EFF Data
-- NOTE: The following SQL
will only return the CURRENT Version of the Fulfillment Line
SELECT
dha.source_order_number,
dha.order_number,
dfla.*
FROM Doo_headers_all
dha ,
Doo_fulfill_lines_all DFLA,
Doo_Fulfill_lines_EFF_B dfle
WHERE dha.source_order_number
Like '28104'
AND DHA.header_id
= dfla.header_id
AND dfla.OBJECT_VERSION_NUMBER =
(
SELECT MAX(DFLA_MAX.OBJECT_VERSION_NUMBER)
FROM
Doo_fulfill_lines_all DFLA_MAX
WHERE DFLa.fulfill_line_number = DFLa_MAX.fulfill_line_number
AND DFLA.SOURCE_ORDER_SYSTEM = DFLA_MAX.SOURCE_ORDER_SYSTEM
AND dfla.source_order_number = dfla_max.source_order_number
)
AND DFLa.Fulfill_line_id = dfle.fulfill_line_id (+)
ORDER BY DHA.order_number ,
SOURCE_LINE_NUMBER,
fulfill_line_Number
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Drop Ship - Requisition
and Purchase Order details
SELECT
/*3-20039176351*/
dfla.source_order_number ,
dfla.status_code
,
dla.DISPLAY_LINE_NUMBER ,
prha.REQUISITION_HEADER_ID ,
prha.REQUISITION_NUMBER ,
prha.DOCUMENT_STATUS
,
prha.PROCESS_STATUS
,
prla.LINE_NUMBER
,
prla.LINE_STATUS
,
poh.segment1
,
poh.document_status
,
poh.frozen_flag
,
pol.line_num
,
pol.LINE_STATUS
FROM fusion.Doo_headers_all
dha ,
fusion.doo_fulfill_lines_all
dfla ,
fusion.doo_lines_all
dla
,
fusion.DOO_DOCUMENT_REFERENCES
ddr ,
fusion.PO_headers_ALL
poh
,
fusion.PO_LINES_ALL
pol
,
fusion.POR_REQUISITION_HEADERS_ALL prha,
fusion.POR_REQUISITION_LINES_ALL prLa
WHERE dha.source_order_number LIKE '4935850'
AND
submitted_flag
= 'Y'
AND dha.header_id
= dfla.header_id
AND dfla.line_id
= dla.line_id
AND dfla.fulfill_line_id = ddr.fulfill_line_id
AND ddr.doc_line_id
= prla.REQUISITION_Line_ID
AND prla.PO_LINE_ID
= pol.PO_LINE_ID
AND prha.requisition_header_id = prla.requisition_header_id
AND poh.po_header_id
= pol.po_header_id
ORDER BY ddr.fulfill_line_id,
ddr.creation_date DESC
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Jeopardy Definitions
select
djtb.JEOPARDY_THRESHOLD_ID
, djtt.DESCRIPTION
, djtb.MAXIMUM_UOM
, djtb.MINIMUM_UOM
, djtb.JEOPARDY_SCORE
, djtb.MINIMUM_DELAY_VALUE
, djtb.MAXIMUM_DELAY_VALUE
, djtb.JEOPARDY_HEADER_ID
from
DOO_JEOPARDY_THRESHOLDS_B djtb,
DOO_JEOPARDY_THRESHOLDS_tl djtt
where
djtt.JEOPARDY_THRESHOLD_ID = djtb.JEOPARDY_THRESHOLD_ID and
djtt.language = 'US'
Relationship between Copied Orders
SELECT SOURCE_DHA.ORDER_NUMBER
,
SOURCE_DHA.SOURCE_ORDER_NUMBER
,
COPIED_DHA.ORDER_NUMBER "Copied
Order Number",
COPIED_DHA.SOURCE_ORDER_NUMBER "Copied
Source Order Number"
FROM DOO_DOCUMENT_REFERENCES DDR,
DOO_HEADERS_ALL SOURCE_DHA ,
DOO_HEADERS_ALL COPIED_DHA
WHERE DDR.DOC_ID = SOURCE_DHA.HEADER_ID
AND DDR.HEADER_ID = COPIED_DHA.HEADER_ID
AND DOC_REF_TYPE = 'COPY_REF_ORDER'
AND
(
SOURCE_DHA.ORDER_NUMBER
= 30053
OR COPIED_DHA.ORDER_NUMBER = 30054
)
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- Service Connectors
select
*
from
doo_web_service_details
Source Instances
select
instance_id,
instance_code,
description,
source_time_zone,
order_orch_type,
instance_type,
xref_enabled,
publish_web_service,
subscribe_web_service
from
MSC_APPS_INSTANCES;
select
host.orig_system_id
, hosb.orig_system
, host.description
, hosb.orig_system_type
, hosb.status
, msc.order_orch_type
, msc.apps_ver
, msc.instance_type
, msc.enable_flag
, hosb.SST_FLAG
, hosb.ENABLE_FOR_TCA_FLAG
, hosb.ENABLE_FOR_PLANNING_FLAG
, hosb.ENABLE_FOR_ITEMS_FLAG
, hosb.ENABLE_FOR_ASSET_FLAG
, hosb.START_DATE_ACTIVE
, hosb.END_DATE_ACTIVE
, hosb.ATTRIBUTE_CATEGORY
, msc.db_connections
, msc.parallel_workers
, msc.process_batch_size
, msc.short_instance_id
, msc.publish_web_service
, msc.description
, msc.subscribe_web_service
from
HZ_ORIG_SYSTEMS_b hosb,
HZ_ORIG_SYSTEMS_tl host,
MSC_APPS_INSTANCES msc
where
hosb.orig_system_id = msc.instance_id and
host.orig_system_id = hosb.orig_system_id and
host.language = 'US'
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- PRICING Price list -
Inventory Item appears on
SELECT qplt.PRICE_LIST_ID
,
qplt.name
,
qpli.item_id
,
qpli.PRICE_LIST_ITEM_ID
,
qpli.PRICE_LIST_ID
,
qpli.LINE_TYPE_CODE
,
qpli.ITEM_LEVEL_CODE
,
qpli.ITEM_ID
,
qpli.PRICING_UOM_CODE
,
qpli.PRIMARY_PRICING_UOM_FLAG
,
qplc.PRICE_LIST_CHARGE_ID
,
qplc.PRICING_BASIS_ID
,
qplc.CHARGE_LINE_NUMBER
,
qplc.START_DATE
,
qplc.TIER_CONTEXT_CODE
,
qplc.CHARGE_DEFINITION_ID ,
qplc.PARENT_ENTITY_TYPE_CODE
,
qplc.PARENT_ENTITY_ID
,
qplc.BASE_PRICE
,
qplc.USAGE_UOM_CODE
,
qplc.PRICE_PERIODICITY_CODE
,
qplc.CAN_ADJUST_FLAG
,
qplc.END_DATE
,
qplc.TIERED_PRICING_HEADER_ID
,
qplc.ATTRIBUTE_PRICING_MATRIX_ID ,
qplc.CALCULATION_METHOD_CODE
,
qplc.CALCULATION_TYPE_CODE
,
qplc.COST_CALCULATION_AMOUNT
,
qplc.PRICE_LIST_ID
FROM QP_PRICE_LISTS_TL qplt ,
QP_PRICE_LIST_ITEMS qpli,
qp_price_list_charges qplc
WHERE qplt.PRICE_LIST_ID = qpli.PRICE_LIST_ID
AND qplc.parent_entity_id (+) = qpli.PRICE_LIST_ITEM_ID
and qpli.item_id = &INVENTORY_ITEM_ID
ORDER BY qplt.name ,
qpli.PRICE_LIST_ID,
qpli.PRICE_LIST_ITEM_ID
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
-- PURCHASING - Suppliers
SELECT SUPPLIERSITE.PRC_BU_ID,
(
SELECT HOUTL.NAME
FROM
HR_ORGANIZATION_UNITS_F_TL HOUTL
WHERE houtl.Organization_id = SUPPLIERSITE.PRC_BU_ID
AND language
= 'US'
) AS "Supplier Site BU" ,
SupplierSiteAssignment.BU_ID,
(
SELECT HOUTL.NAME
FROM
HR_ORGANIZATION_UNITS_F_TL HOUTL
WHERE houtl.Organization_id = SupplierSiteAssignment.BU_ID
AND language
= 'US'
) AS "Supplier Site Assignment BU" ,
SupplierSITE.VENDOR_site_code
,
Supplier.VENDOR_ID
,
SupplierSite.VENDOR_SITE_ID
,
SupplierSite.RFQ_ONLY_SITE_FLAG
,
SupplierSiteAssignment.INACTIVE_DATE,
SupplierSite.PURCHASING_SITE_FLAG
FROM POZ_SUPPLIER_SITES_ALL_M
SupplierSite ,
HZ_PARTY_SITES
PartySite
,
POZ_SUPPLIERS Supplier
,
POZ_SITE_ASSIGNMENTS_ALL_M SupplierSiteAssignment,
HZ_PARTIES Party
WHERE PARTY.PARTY_ID
= SUPPLIER.PARTY_ID
AND SUPPLIER.VENDOR_ID
= SUPPLIERSITE.VENDOR_ID
AND SUPPLIERSITE.PARTY_SITE_ID
= PARTYSITE.PARTY_SITE_ID
AND NVL(PARTYSITE.END_DATE_ACTIVE, SYSDATE +1) > SYSDATE
AND NVL(PARTYSITE.START_DATE_ACTIVE, SYSDATE-1) < SYSDATE
AND NVL(SUPPLIERSITE.INACTIVE_DATE, SYSDATE +1) > SYSDATE
AND SupplierSite.VENDOR_SITE_ID
= SupplierSiteAssignment.VENDOR_SITE_ID
AND
(
(
300000001278247 = Supplier.VENDOR_ID
AND 300000005003949 = SupplierSite.VENDOR_SITE_ID
)
OR suppliersite.vendor_site_code LIKE 'TC MAIN-PURCH'
-->:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
2 comments:
can you please provide query to find the Item manufacturer details in Fusion
Please need query for fusion OTL Time Card Entries
Post a Comment