[Home] [Help]
SELECT hdr_from.vendor_id vendor_id, hdr_from.vendor_site_id vendor_site_id, hdr_from.vendor_contact_id vendor_contact_id, hdr_from.org_id org_id, hdr_from.batch_number batch_number, hdr_from.batch_id batch_id, res.source_name frm_contact1_name, nvl(res.source_email, 'NA') frm_contact1_email, nvl(res.source_phone, 'NA') frm_contact1_phone, NULL frm_contact1_fax, FND_MESSAGE.GET_STRING('OZF','OZF_SDB_FRM_GBL_PTNR_ROLE_CD') frm_global_ptnr_role_class_cd, org_from.name frm_business_name, 'OZFSDDUNS' frm_global_business_id, party_from.category_code frm_global_supply_chain_cd, NULL frm_prop_business_id, NULL frm_prop_domain_id1, NULL frm_prop_auth_id1, FND_MESSAGE.GET_STRING('OZF','OZF_SDB_FRM_GBL_PTNR_CLAS_CODE') frm_global_partner_class_code, res.source_name frm_contact2_name, nvl(res.source_email, 'NA') frm_contact2_email, nvl(res.source_phone, 'NA') frm_contact2_phone, NULL frm_contact2_fax, nvl(to_char(loc_from.location_code), 'N/A') frm_global_location_id, NULL frm_prop_domain_id2, NULL frm_prop_auth_id2, NULL frm_prop_loc_id, nvl(loc_from.address_line_1, 'NA') frm_add_line1, nvl(loc_from.address_line_2, 'NA') frm_add_line2, nvl(loc_from.address_line_3, 'NA') frm_add_line3, nvl(loc_from.town_or_city, 'NA') frm_city, nvl(loc_from.country, 'NA') frm_country, nvl(loc_from.postal_code, 'NA') frm_postal_code, nvl(loc_from.postal_code, 'NA') frm_box_identifier, nvl(loc_from.region_1, 'NA') frm_state_region FROM ozf_sd_batch_headers_all hdr_from, hr_all_organization_units org_from, hr_locations_all loc_from, jtf_rs_resource_extns res, hz_parties party_from WHERE hdr_from.org_id = org_from.organization_id AND org_from.location_id = loc_from.location_id AND org_from.party_id = party_from.party_id(+) AND res.resource_id = hdr_from.owner_id AND nvl(res.end_date_active, sysdate) >= sysdate
SELECT HDR_FROM.VENDOR_ID VENDOR_ID
, HDR_FROM.VENDOR_SITE_ID VENDOR_SITE_ID
, HDR_FROM.VENDOR_CONTACT_ID VENDOR_CONTACT_ID
, HDR_FROM.ORG_ID ORG_ID
, HDR_FROM.BATCH_NUMBER BATCH_NUMBER
, HDR_FROM.BATCH_ID BATCH_ID
, RES.SOURCE_NAME FRM_CONTACT1_NAME
, NVL(RES.SOURCE_EMAIL
, 'NA') FRM_CONTACT1_EMAIL
, NVL(RES.SOURCE_PHONE
, 'NA') FRM_CONTACT1_PHONE
, NULL FRM_CONTACT1_FAX
, FND_MESSAGE.GET_STRING('OZF'
, 'OZF_SDB_FRM_GBL_PTNR_ROLE_CD') FRM_GLOBAL_PTNR_ROLE_CLASS_CD
, ORG_FROM.NAME FRM_BUSINESS_NAME
, 'OZFSDDUNS' FRM_GLOBAL_BUSINESS_ID
, PARTY_FROM.CATEGORY_CODE FRM_GLOBAL_SUPPLY_CHAIN_CD
, NULL FRM_PROP_BUSINESS_ID
, NULL FRM_PROP_DOMAIN_ID1
, NULL FRM_PROP_AUTH_ID1
, FND_MESSAGE.GET_STRING('OZF'
, 'OZF_SDB_FRM_GBL_PTNR_CLAS_CODE') FRM_GLOBAL_PARTNER_CLASS_CODE
, RES.SOURCE_NAME FRM_CONTACT2_NAME
, NVL(RES.SOURCE_EMAIL
, 'NA') FRM_CONTACT2_EMAIL
, NVL(RES.SOURCE_PHONE
, 'NA') FRM_CONTACT2_PHONE
, NULL FRM_CONTACT2_FAX
, NVL(TO_CHAR(LOC_FROM.LOCATION_CODE)
, 'N/A') FRM_GLOBAL_LOCATION_ID
, NULL FRM_PROP_DOMAIN_ID2
, NULL FRM_PROP_AUTH_ID2
, NULL FRM_PROP_LOC_ID
, NVL(LOC_FROM.ADDRESS_LINE_1
, 'NA') FRM_ADD_LINE1
, NVL(LOC_FROM.ADDRESS_LINE_2
, 'NA') FRM_ADD_LINE2
, NVL(LOC_FROM.ADDRESS_LINE_3
, 'NA') FRM_ADD_LINE3
, NVL(LOC_FROM.TOWN_OR_CITY
, 'NA') FRM_CITY
, NVL(LOC_FROM.COUNTRY
, 'NA') FRM_COUNTRY
, NVL(LOC_FROM.POSTAL_CODE
, 'NA') FRM_POSTAL_CODE
, NVL(LOC_FROM.POSTAL_CODE
, 'NA') FRM_BOX_IDENTIFIER
, NVL(LOC_FROM.REGION_1
, 'NA') FRM_STATE_REGION
FROM OZF_SD_BATCH_HEADERS_ALL HDR_FROM
, HR_ALL_ORGANIZATION_UNITS ORG_FROM
, HR_LOCATIONS_ALL LOC_FROM
, JTF_RS_RESOURCE_EXTNS RES
, HZ_PARTIES PARTY_FROM
WHERE HDR_FROM.ORG_ID = ORG_FROM.ORGANIZATION_ID
AND ORG_FROM.LOCATION_ID = LOC_FROM.LOCATION_ID
AND ORG_FROM.PARTY_ID = PARTY_FROM.PARTY_ID(+)
AND RES.RESOURCE_ID = HDR_FROM.OWNER_ID
AND NVL(RES.END_DATE_ACTIVE
, SYSDATE) >= SYSDATE
|
|
|
|