VIEW: APPS.OKL_AM_ASSET_SALE_UV

Source

View Text - Preformatted

SELECT ar.id art_id , ar.kle_id kle_id , h.order_number order_number , h.ordered_date ordered_date , l.line_number line_number , l.header_id header_id , l.inventory_item_id inventory_item_id , i.concatenated_segments item_name , i.description item_description , l.sold_to_org_id customer_id , c.party_name customer_name , c.party_number customer_number , l.unit_selling_price unit_selling_price , l.unit_list_price unit_list_price , l.tax_value tax_value , l.actual_shipment_date actual_shipment_date , l.ordered_quantity ordered_quantity , l.booked_flag booked_flag , l.ship_to_org_id ship_site_id , l.ship_to_contact_id ship_to_contact_id , loc1.address1 ship_to_address1 , loc1.address2 ship_to_address2 , loc1.address3 ship_to_address3 , loc1.address4 ship_to_address4 , loc1.city ship_to_city , loc1.postal_code ship_to_postal_code , loc1.state ship_to_state , loc1.province ship_to_province , loc1.county ship_to_county , loc1.country ship_to_country , Substr (arp_addr_label_pkg.format_address ( NULL, loc1.address1, loc1.address2, loc1.address3, loc1.address4, loc1.city, loc1.county, loc1.state, loc1.province, loc1.postal_code, NULL, loc1.country, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'N', 'N', 80, 1, 1), 1, 80) ship_to_full_address, al.name asset_number , al.item_description asset_description , al.sts_code status , al.oec fa_cost , kh.contract_number contract_number, h.flow_status_code order_status, ar.rmr_id rmr_id, i.item_catalog_group_id, t.team_name remarketer_name, ar.org_id org_id FROM OE_ORDER_HEADERS_ALL H,OE_ORDER_LINES_ALL L,MTL_SYSTEM_ITEMS_B_KFV I,HZ_PARTIES C,HZ_CUST_ACCOUNTS CA,HZ_CUST_SITE_USES_ALL S1,HZ_CUST_ACCT_SITES_ALL A1,HZ_PARTY_SITES PS1,HZ_LOCATIONS LOC1,OKL_ASSET_RETURNS_ALL_B AR,OKL_K_LINES_FULL_V AL,OKC_K_HEADERS_B KH,JTF_RS_TEAMS_TL T WHERE h.header_id = l.header_id and l.inventory_item_id = i.inventory_item_id and l.ship_from_org_id = i.organization_id and ca.cust_account_id = l.sold_to_org_id and ca.party_id = c.party_id and s1.site_use_id = l.ship_to_org_id and a1.cust_acct_site_id = s1.cust_acct_site_id and ps1.party_site_id = a1.party_site_id and loc1.location_id = ps1.location_id and l.inventory_item_id = ar.imr_id and ar.kle_id = al.id and al.chr_id = kh.id and ar.rmr_id = t.team_id (+) and t.language (+) = userenv ('LANG')

View Text - HTML Formatted

SELECT AR.ID ART_ID
, AR.KLE_ID KLE_ID
, H.ORDER_NUMBER ORDER_NUMBER
, H.ORDERED_DATE ORDERED_DATE
, L.LINE_NUMBER LINE_NUMBER
, L.HEADER_ID HEADER_ID
, L.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, I.CONCATENATED_SEGMENTS ITEM_NAME
, I.DESCRIPTION ITEM_DESCRIPTION
, L.SOLD_TO_ORG_ID CUSTOMER_ID
, C.PARTY_NAME CUSTOMER_NAME
, C.PARTY_NUMBER CUSTOMER_NUMBER
, L.UNIT_SELLING_PRICE UNIT_SELLING_PRICE
, L.UNIT_LIST_PRICE UNIT_LIST_PRICE
, L.TAX_VALUE TAX_VALUE
, L.ACTUAL_SHIPMENT_DATE ACTUAL_SHIPMENT_DATE
, L.ORDERED_QUANTITY ORDERED_QUANTITY
, L.BOOKED_FLAG BOOKED_FLAG
, L.SHIP_TO_ORG_ID SHIP_SITE_ID
, L.SHIP_TO_CONTACT_ID SHIP_TO_CONTACT_ID
, LOC1.ADDRESS1 SHIP_TO_ADDRESS1
, LOC1.ADDRESS2 SHIP_TO_ADDRESS2
, LOC1.ADDRESS3 SHIP_TO_ADDRESS3
, LOC1.ADDRESS4 SHIP_TO_ADDRESS4
, LOC1.CITY SHIP_TO_CITY
, LOC1.POSTAL_CODE SHIP_TO_POSTAL_CODE
, LOC1.STATE SHIP_TO_STATE
, LOC1.PROVINCE SHIP_TO_PROVINCE
, LOC1.COUNTY SHIP_TO_COUNTY
, LOC1.COUNTRY SHIP_TO_COUNTRY
, SUBSTR (ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS ( NULL
, LOC1.ADDRESS1
, LOC1.ADDRESS2
, LOC1.ADDRESS3
, LOC1.ADDRESS4
, LOC1.CITY
, LOC1.COUNTY
, LOC1.STATE
, LOC1.PROVINCE
, LOC1.POSTAL_CODE
, NULL
, LOC1.COUNTRY
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'N'
, 'N'
, 80
, 1
, 1)
, 1
, 80) SHIP_TO_FULL_ADDRESS
, AL.NAME ASSET_NUMBER
, AL.ITEM_DESCRIPTION ASSET_DESCRIPTION
, AL.STS_CODE STATUS
, AL.OEC FA_COST
, KH.CONTRACT_NUMBER CONTRACT_NUMBER
, H.FLOW_STATUS_CODE ORDER_STATUS
, AR.RMR_ID RMR_ID
, I.ITEM_CATALOG_GROUP_ID
, T.TEAM_NAME REMARKETER_NAME
, AR.ORG_ID ORG_ID
FROM OE_ORDER_HEADERS_ALL H
, OE_ORDER_LINES_ALL L
, MTL_SYSTEM_ITEMS_B_KFV I
, HZ_PARTIES C
, HZ_CUST_ACCOUNTS CA
, HZ_CUST_SITE_USES_ALL S1
, HZ_CUST_ACCT_SITES_ALL A1
, HZ_PARTY_SITES PS1
, HZ_LOCATIONS LOC1
, OKL_ASSET_RETURNS_ALL_B AR
, OKL_K_LINES_FULL_V AL
, OKC_K_HEADERS_B KH
, JTF_RS_TEAMS_TL T
WHERE H.HEADER_ID = L.HEADER_ID
AND L.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND L.SHIP_FROM_ORG_ID = I.ORGANIZATION_ID
AND CA.CUST_ACCOUNT_ID = L.SOLD_TO_ORG_ID
AND CA.PARTY_ID = C.PARTY_ID
AND S1.SITE_USE_ID = L.SHIP_TO_ORG_ID
AND A1.CUST_ACCT_SITE_ID = S1.CUST_ACCT_SITE_ID
AND PS1.PARTY_SITE_ID = A1.PARTY_SITE_ID
AND LOC1.LOCATION_ID = PS1.LOCATION_ID
AND L.INVENTORY_ITEM_ID = AR.IMR_ID
AND AR.KLE_ID = AL.ID
AND AL.CHR_ID = KH.ID
AND AR.RMR_ID = T.TEAM_ID (+)
AND T.LANGUAGE (+) = USERENV ('LANG')