VIEW: APPS.IBY_XML_FNDCPT_PAYER_1_0_V

Source

View Text - Preformatted

SELECT XMLConcat( XMLElement("PartyInternalID", ext_fc.party_id), XMLElement("PartyNumber", ext_fc.party_number), XMLElement("Name", ext_fc.party_name), XMLElement("PartyType", XMLElement("Code", ext_fc.party_type), XMLElement("Meaning", party_type_lookup.meaning) ), XMLElement("TaxRegistrationNumber", ext_fc.tax_registration_number), XMLElement("LegalRegistrationNumber", ext_fc.le_registration_number), CASE WHEN ext_fc.party_attr_cat IS NULL THEN NULL ELSE XMLElement("PartyDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'HZ_PARTIES', ext_fc.party_id, NULL)) END, XMLElement("AlternateName",ext_fc.alternative_name), XMLElement("CustomerAccountNumber", ext_fc.account_number), CASE WHEN ext_fc.bill_to_address_id IS NULL THEN NULL ELSE XMLElement("Address", XMLConcat( XMLElement("AddressInternalID", hz_loc.location_id), XMLElement("AddressLine1", hz_loc.address1), XMLElement("AddressLine2", hz_loc.address2), XMLElement("AddressLine3", hz_loc.address3), XMLElement("City", hz_loc.city), XMLElement("County", hz_loc.county), XMLElement("State", hz_loc.state), XMLElement("Country", hz_loc.country), XMLElement("CountryName", terr.territory_short_name), XMLElement("ISO3DigitCountry", terr.iso_territory_code), XMLElement("PostalCode", hz_loc.postal_code), XMLElement("AlternateAddressName", null) ) ) END, CASE WHEN ext_fc.party_number IS NULL THEN NULL ELSE XMLElement("FirstPartyReference", ext_fc.party_number) END ), ext_fc.party_id, ext_fc.trxnmid FROM iby_ext_fc_payer_1_0_v ext_fc, fnd_lookup_values party_type_lookup, hz_locations hz_loc, fnd_territories_vl terr WHERE ext_fc.bill_to_address_id=hz_loc.location_id(+) AND ext_fc.party_type=party_type_lookup.lookup_code(+) AND party_type_lookup.lookup_type = 'PARTY_TYPE' AND party_type_lookup.LANGUAGE = userenv('LANG') AND party_type_lookup.VIEW_APPLICATION_ID = 222 AND (hz_loc.country = terr.territory_code(+))

View Text - HTML Formatted

SELECT XMLCONCAT( XMLELEMENT("PARTYINTERNALID"
, EXT_FC.PARTY_ID)
, XMLELEMENT("PARTYNUMBER"
, EXT_FC.PARTY_NUMBER)
, XMLELEMENT("NAME"
, EXT_FC.PARTY_NAME)
, XMLELEMENT("PARTYTYPE"
, XMLELEMENT("CODE"
, EXT_FC.PARTY_TYPE)
, XMLELEMENT("MEANING"
, PARTY_TYPE_LOOKUP.MEANING) )
, XMLELEMENT("TAXREGISTRATIONNUMBER"
, EXT_FC.TAX_REGISTRATION_NUMBER)
, XMLELEMENT("LEGALREGISTRATIONNUMBER"
, EXT_FC.LE_REGISTRATION_NUMBER)
, CASE WHEN EXT_FC.PARTY_ATTR_CAT IS NULL THEN NULL ELSE XMLELEMENT("PARTYDESCRIPTIVEFLEXFIELD"
, IBY_EXTRACTGEN_PVT.GET_DFFS( 'HZ_PARTIES'
, EXT_FC.PARTY_ID
, NULL)) END
, XMLELEMENT("ALTERNATENAME"
, EXT_FC.ALTERNATIVE_NAME)
, XMLELEMENT("CUSTOMERACCOUNTNUMBER"
, EXT_FC.ACCOUNT_NUMBER)
, CASE WHEN EXT_FC.BILL_TO_ADDRESS_ID IS NULL THEN NULL ELSE XMLELEMENT("ADDRESS"
, XMLCONCAT( XMLELEMENT("ADDRESSINTERNALID"
, HZ_LOC.LOCATION_ID)
, XMLELEMENT("ADDRESSLINE1"
, HZ_LOC.ADDRESS1)
, XMLELEMENT("ADDRESSLINE2"
, HZ_LOC.ADDRESS2)
, XMLELEMENT("ADDRESSLINE3"
, HZ_LOC.ADDRESS3)
, XMLELEMENT("CITY"
, HZ_LOC.CITY)
, XMLELEMENT("COUNTY"
, HZ_LOC.COUNTY)
, XMLELEMENT("STATE"
, HZ_LOC.STATE)
, XMLELEMENT("COUNTRY"
, HZ_LOC.COUNTRY)
, XMLELEMENT("COUNTRYNAME"
, TERR.TERRITORY_SHORT_NAME)
, XMLELEMENT("ISO3DIGITCOUNTRY"
, TERR.ISO_TERRITORY_CODE)
, XMLELEMENT("POSTALCODE"
, HZ_LOC.POSTAL_CODE)
, XMLELEMENT("ALTERNATEADDRESSNAME"
, NULL) ) ) END
, CASE WHEN EXT_FC.PARTY_NUMBER IS NULL THEN NULL ELSE XMLELEMENT("FIRSTPARTYREFERENCE"
, EXT_FC.PARTY_NUMBER) END )
, EXT_FC.PARTY_ID
, EXT_FC.TRXNMID
FROM IBY_EXT_FC_PAYER_1_0_V EXT_FC
, FND_LOOKUP_VALUES PARTY_TYPE_LOOKUP
, HZ_LOCATIONS HZ_LOC
, FND_TERRITORIES_VL TERR
WHERE EXT_FC.BILL_TO_ADDRESS_ID=HZ_LOC.LOCATION_ID(+)
AND EXT_FC.PARTY_TYPE=PARTY_TYPE_LOOKUP.LOOKUP_CODE(+)
AND PARTY_TYPE_LOOKUP.LOOKUP_TYPE = 'PARTY_TYPE'
AND PARTY_TYPE_LOOKUP.LANGUAGE = USERENV('LANG')
AND PARTY_TYPE_LOOKUP.VIEW_APPLICATION_ID = 222
AND (HZ_LOC.COUNTRY = TERR.TERRITORY_CODE(+))