VIEW: APPS.OKL_AM_ITEM_LOCATION_UV

Source

View Text - Preformatted

SELECT kle_fa.chr_id chr_id, kle_fa.id cle_id_fin_asset, kle_il.id cle_id_inst_line, kle_ib.id cle_id_inst_base, cii.instance_id instance_id, cii.serial_number ib_serial_number, par.party_id party_id, psi.party_site_id party_site_id, psu.party_site_use_id party_site_use_id, loc.location_id location_id, okhv.contract_number contract_number, kle_fa.name asset_number, kle_fa.item_description asset_description, kle_fa.end_date end_date, cii.instance_number instance_number, cii.active_start_date instance_start_date, cii.active_end_date instance_end_date, par.party_number party_number, par.party_name party_name, psi.party_site_number party_site_number, psi.party_site_name party_site_name, loc.description location_description, Substr (arp_addr_label_pkg.format_address ( NULL, loc.address1, loc.address2, loc.address3, loc.address4, loc.city, loc.county, loc.state, loc.province, loc.postal_code, NULL, loc.country, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'N', 'N', 80, 1, 1), 1, 80) location_address, to_char(null) new_location_address, to_number(null) new_location_id, to_number(null) new_installed_location_id, to_char(null) change_party_flag, to_char(null) new_party_name FROM OKC_K_HEADERS_V OKHV,OKC_K_LINES_V KLE_FA,OKC_LINE_STYLES_B LSE_FA,OKC_K_LINES_B KLE_IL,OKC_LINE_STYLES_B LSE_IL,OKC_K_LINES_B KLE_IB,OKC_LINE_STYLES_B LSE_IB,OKC_K_ITEMS ITE,CSI_ITEM_INSTANCES CII,HZ_PARTY_SITES PSI,HZ_LOCATIONS LOC,HZ_PARTIES PAR,HZ_PARTY_SITE_USES PSU WHERE kle_fa.chr_id = okhv.id AND lse_fa.id = kle_fa.lse_id AND lse_fa.lty_code = 'FREE_FORM1' AND kle_il.cle_id = kle_fa.id AND lse_il.id = kle_il.lse_id AND lse_il.lty_code = 'FREE_FORM2' AND kle_ib.cle_id = kle_il.id AND lse_ib.id = kle_ib.lse_id AND lse_ib.lty_code = 'INST_ITEM' AND ite.cle_id = kle_ib.id AND ite.jtot_object1_code = 'OKX_IB_ITEM' AND cii.instance_id = ite.object1_id1 AND cii.install_location_type_code = 'HZ_PARTY_SITES' AND psi.party_site_id = cii.install_location_id AND loc.location_id = psi.location_id AND par.party_id = psi.party_id AND psu.party_site_id = psi.party_site_id AND psu.site_use_type = 'INSTALL_AT' UNION SELECT KLE_FA.CHR_ID CHR_ID, KLE_FA.ID CLE_ID_FIN_ASSET, KLE_IL.ID CLE_ID_INST_LINE, KLE_IB.ID CLE_ID_INST_BASE, CII.INSTANCE_ID INSTANCE_ID, CII.SERIAL_NUMBER IB_SERIAL_NUMBER, PAR.PARTY_ID PARTY_ID, PSI.PARTY_SITE_ID PARTY_SITE_ID, PSU.PARTY_SITE_USE_ID PARTY_SITE_USE_ID, LOC.LOCATION_ID LOCATION_ID, OKHV.CONTRACT_NUMBER CONTRACT_NUMBER, KLE_FA.NAME ASSET_NUMBER, KLE_FA.ITEM_DESCRIPTION ASSET_DESCRIPTION, KLE_FA.END_DATE END_DATE, CII.INSTANCE_NUMBER INSTANCE_NUMBER, CII.ACTIVE_START_DATE INSTANCE_START_DATE, CII.ACTIVE_END_DATE INSTANCE_END_DATE, PAR.PARTY_NUMBER PARTY_NUMBER, PAR.PARTY_NAME PARTY_NAME, PSI.PARTY_SITE_NUMBER PARTY_SITE_NUMBER, PSI.PARTY_SITE_NAME PARTY_SITE_NAME, LOC.DESCRIPTION LOCATION_DESCRIPTION, SUBSTR (ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS ( NULL, LOC.ADDRESS1, LOC.ADDRESS2, LOC.ADDRESS3, LOC.ADDRESS4, LOC.CITY, LOC.COUNTY, LOC.STATE, LOC.PROVINCE, LOC.POSTAL_CODE, NULL, LOC.COUNTRY, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'N', 'N', 80, 1, 1), 1, 80) LOCATION_ADDRESS, TO_CHAR(NULL) NEW_LOCATION_ADDRESS, TO_NUMBER(NULL) NEW_LOCATION_ID, TO_NUMBER(NULL) NEW_INSTALLED_LOCATION_ID, TO_CHAR(NULL) CHANGE_PARTY_FLAG, TO_CHAR(NULL) NEW_PARTY_NAME FROM OKC_K_HEADERS_V OKHV, OKC_K_LINES_V KLE_FA, OKC_LINE_STYLES_B LSE_FA, OKC_K_LINES_B KLE_IL, OKC_LINE_STYLES_B LSE_IL, OKC_K_LINES_B KLE_IB, OKC_LINE_STYLES_B LSE_IB, OKC_K_ITEMS ITE, CSI_ITEM_INSTANCES CII, HZ_PARTY_SITES PSI, HZ_LOCATIONS LOC, HZ_PARTIES PAR, HZ_PARTY_SITE_USES PSU WHERE KLE_FA.CHR_ID = OKHV.ID AND LSE_FA.ID = KLE_FA.LSE_ID AND LSE_FA.LTY_CODE = 'FREE_FORM1' AND KLE_IL.CLE_ID = KLE_FA.ID AND LSE_IL.ID = KLE_IL.LSE_ID AND LSE_IL.LTY_CODE = 'FREE_FORM2' AND KLE_IB.CLE_ID = KLE_IL.ID AND LSE_IB.ID = KLE_IB.LSE_ID AND LSE_IB.LTY_CODE = 'INST_ITEM' AND ITE.CLE_ID = KLE_IB.ID AND ITE.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM' AND CII.INSTANCE_ID = ITE.OBJECT1_ID1 AND CII.INSTALL_LOCATION_TYPE_CODE = 'HZ_LOCATIONS' AND LOC.LOCATION_ID = CII.INSTALL_LOCATION_ID AND LOC.LOCATION_ID = PSI.LOCATION_ID AND PAR.PARTY_ID = PSI.PARTY_ID AND PSU.PARTY_SITE_ID = PSI.PARTY_SITE_ID AND PSU.SITE_USE_TYPE = 'INSTALL_AT'

View Text - HTML Formatted

SELECT KLE_FA.CHR_ID CHR_ID
, KLE_FA.ID CLE_ID_FIN_ASSET
, KLE_IL.ID CLE_ID_INST_LINE
, KLE_IB.ID CLE_ID_INST_BASE
, CII.INSTANCE_ID INSTANCE_ID
, CII.SERIAL_NUMBER IB_SERIAL_NUMBER
, PAR.PARTY_ID PARTY_ID
, PSI.PARTY_SITE_ID PARTY_SITE_ID
, PSU.PARTY_SITE_USE_ID PARTY_SITE_USE_ID
, LOC.LOCATION_ID LOCATION_ID
, OKHV.CONTRACT_NUMBER CONTRACT_NUMBER
, KLE_FA.NAME ASSET_NUMBER
, KLE_FA.ITEM_DESCRIPTION ASSET_DESCRIPTION
, KLE_FA.END_DATE END_DATE
, CII.INSTANCE_NUMBER INSTANCE_NUMBER
, CII.ACTIVE_START_DATE INSTANCE_START_DATE
, CII.ACTIVE_END_DATE INSTANCE_END_DATE
, PAR.PARTY_NUMBER PARTY_NUMBER
, PAR.PARTY_NAME PARTY_NAME
, PSI.PARTY_SITE_NUMBER PARTY_SITE_NUMBER
, PSI.PARTY_SITE_NAME PARTY_SITE_NAME
, LOC.DESCRIPTION LOCATION_DESCRIPTION
, SUBSTR (ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS ( NULL
, LOC.ADDRESS1
, LOC.ADDRESS2
, LOC.ADDRESS3
, LOC.ADDRESS4
, LOC.CITY
, LOC.COUNTY
, LOC.STATE
, LOC.PROVINCE
, LOC.POSTAL_CODE
, NULL
, LOC.COUNTRY
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'N'
, 'N'
, 80
, 1
, 1)
, 1
, 80) LOCATION_ADDRESS
, TO_CHAR(NULL) NEW_LOCATION_ADDRESS
, TO_NUMBER(NULL) NEW_LOCATION_ID
, TO_NUMBER(NULL) NEW_INSTALLED_LOCATION_ID
, TO_CHAR(NULL) CHANGE_PARTY_FLAG
, TO_CHAR(NULL) NEW_PARTY_NAME
FROM OKC_K_HEADERS_V OKHV
, OKC_K_LINES_V KLE_FA
, OKC_LINE_STYLES_B LSE_FA
, OKC_K_LINES_B KLE_IL
, OKC_LINE_STYLES_B LSE_IL
, OKC_K_LINES_B KLE_IB
, OKC_LINE_STYLES_B LSE_IB
, OKC_K_ITEMS ITE
, CSI_ITEM_INSTANCES CII
, HZ_PARTY_SITES PSI
, HZ_LOCATIONS LOC
, HZ_PARTIES PAR
, HZ_PARTY_SITE_USES PSU
WHERE KLE_FA.CHR_ID = OKHV.ID
AND LSE_FA.ID = KLE_FA.LSE_ID
AND LSE_FA.LTY_CODE = 'FREE_FORM1'
AND KLE_IL.CLE_ID = KLE_FA.ID
AND LSE_IL.ID = KLE_IL.LSE_ID
AND LSE_IL.LTY_CODE = 'FREE_FORM2'
AND KLE_IB.CLE_ID = KLE_IL.ID
AND LSE_IB.ID = KLE_IB.LSE_ID
AND LSE_IB.LTY_CODE = 'INST_ITEM'
AND ITE.CLE_ID = KLE_IB.ID
AND ITE.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM'
AND CII.INSTANCE_ID = ITE.OBJECT1_ID1
AND CII.INSTALL_LOCATION_TYPE_CODE = 'HZ_PARTY_SITES'
AND PSI.PARTY_SITE_ID = CII.INSTALL_LOCATION_ID
AND LOC.LOCATION_ID = PSI.LOCATION_ID
AND PAR.PARTY_ID = PSI.PARTY_ID
AND PSU.PARTY_SITE_ID = PSI.PARTY_SITE_ID
AND PSU.SITE_USE_TYPE = 'INSTALL_AT' UNION SELECT KLE_FA.CHR_ID CHR_ID
, KLE_FA.ID CLE_ID_FIN_ASSET
, KLE_IL.ID CLE_ID_INST_LINE
, KLE_IB.ID CLE_ID_INST_BASE
, CII.INSTANCE_ID INSTANCE_ID
, CII.SERIAL_NUMBER IB_SERIAL_NUMBER
, PAR.PARTY_ID PARTY_ID
, PSI.PARTY_SITE_ID PARTY_SITE_ID
, PSU.PARTY_SITE_USE_ID PARTY_SITE_USE_ID
, LOC.LOCATION_ID LOCATION_ID
, OKHV.CONTRACT_NUMBER CONTRACT_NUMBER
, KLE_FA.NAME ASSET_NUMBER
, KLE_FA.ITEM_DESCRIPTION ASSET_DESCRIPTION
, KLE_FA.END_DATE END_DATE
, CII.INSTANCE_NUMBER INSTANCE_NUMBER
, CII.ACTIVE_START_DATE INSTANCE_START_DATE
, CII.ACTIVE_END_DATE INSTANCE_END_DATE
, PAR.PARTY_NUMBER PARTY_NUMBER
, PAR.PARTY_NAME PARTY_NAME
, PSI.PARTY_SITE_NUMBER PARTY_SITE_NUMBER
, PSI.PARTY_SITE_NAME PARTY_SITE_NAME
, LOC.DESCRIPTION LOCATION_DESCRIPTION
, SUBSTR (ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS ( NULL
, LOC.ADDRESS1
, LOC.ADDRESS2
, LOC.ADDRESS3
, LOC.ADDRESS4
, LOC.CITY
, LOC.COUNTY
, LOC.STATE
, LOC.PROVINCE
, LOC.POSTAL_CODE
, NULL
, LOC.COUNTRY
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'N'
, 'N'
, 80
, 1
, 1)
, 1
, 80) LOCATION_ADDRESS
, TO_CHAR(NULL) NEW_LOCATION_ADDRESS
, TO_NUMBER(NULL) NEW_LOCATION_ID
, TO_NUMBER(NULL) NEW_INSTALLED_LOCATION_ID
, TO_CHAR(NULL) CHANGE_PARTY_FLAG
, TO_CHAR(NULL) NEW_PARTY_NAME
FROM OKC_K_HEADERS_V OKHV
, OKC_K_LINES_V KLE_FA
, OKC_LINE_STYLES_B LSE_FA
, OKC_K_LINES_B KLE_IL
, OKC_LINE_STYLES_B LSE_IL
, OKC_K_LINES_B KLE_IB
, OKC_LINE_STYLES_B LSE_IB
, OKC_K_ITEMS ITE
, CSI_ITEM_INSTANCES CII
, HZ_PARTY_SITES PSI
, HZ_LOCATIONS LOC
, HZ_PARTIES PAR
, HZ_PARTY_SITE_USES PSU
WHERE KLE_FA.CHR_ID = OKHV.ID
AND LSE_FA.ID = KLE_FA.LSE_ID
AND LSE_FA.LTY_CODE = 'FREE_FORM1'
AND KLE_IL.CLE_ID = KLE_FA.ID
AND LSE_IL.ID = KLE_IL.LSE_ID
AND LSE_IL.LTY_CODE = 'FREE_FORM2'
AND KLE_IB.CLE_ID = KLE_IL.ID
AND LSE_IB.ID = KLE_IB.LSE_ID
AND LSE_IB.LTY_CODE = 'INST_ITEM'
AND ITE.CLE_ID = KLE_IB.ID
AND ITE.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM'
AND CII.INSTANCE_ID = ITE.OBJECT1_ID1
AND CII.INSTALL_LOCATION_TYPE_CODE = 'HZ_LOCATIONS'
AND LOC.LOCATION_ID = CII.INSTALL_LOCATION_ID
AND LOC.LOCATION_ID = PSI.LOCATION_ID
AND PAR.PARTY_ID = PSI.PARTY_ID
AND PSU.PARTY_SITE_ID = PSI.PARTY_SITE_ID
AND PSU.SITE_USE_TYPE = 'INSTALL_AT'