VIEW: APPS.INL_SHIP_RMA_OVERALL_V

Source

View Text - Preformatted

SELECT lso.ship_header_id, lso.organization_id, lso.location_id, lso.org_id, lso.ship_type_id, lso.ship_type_code, lso.ship_type_name, lso.ship_num, lso.ship_date, lso.ship_status_code, lso.pending_matching_flag, lso.pending_update_flag, lso.simulation_id, lso.ship_line_group_id, lso.ship_line_group_num, lso.ship_line_group_reference, lso.src_type_code, lso.group_party_id, lso.group_party_name, lso.group_party_site_id, lso.group_party_site_name, lso.group_organization_id, lso.group_organization_name, TO_NUMBER (lso.ship_line_id) ship_line_id, TO_NUMBER (lso.ship_line_num) ship_line_num, TO_NUMBER (est_ship_line_id) est_ship_line_id, TO_NUMBER (lso.est_txn_unit_price) est_txn_unit_price, lso.ship_line_src_type_code, TO_NUMBER (lso.ship_line_source_id) ship_line_source_id, lso.ship_line_type_code, lso.ship_line_type_name, TO_NUMBER (lso.item_id) item_id, lso.item, lso.item_revision, lso.item_description, TO_NUMBER (lso.txn_qty) txn_qty, lso.txn_uom_code, lso.primary_uom_code, TO_NUMBER (lso.primary_qty) primary_qty, lso.secondary_uom_code, TO_NUMBER (lso.secondary_qty) secondary_qty, TO_NUMBER (lso.txn_unit_price) txn_unit_price, lso.currency_code, lso.currency_conversion_type, TO_DATE (lso.currency_conversion_date) currency_conversion_date, TO_NUMBER (lso.currency_conversion_rate) currency_conversion_rate, TO_NUMBER (lso.line_amt) line_amt, TO_NUMBER (lso.src_id) src_id, lso.src_type, lso.src_number, lso.src_description, TO_NUMBER (lso.src_org_id) src_org_id, TO_NUMBER (lso.src_party_id) src_party_id, lso.src_party_name, TO_NUMBER (lso.src_party_site_id) src_party_site_id, lso.src_party_site_name, TO_NUMBER (lso.src_vendor_id) src_vendor_id, lso.src_vendor_name, TO_NUMBER (lso.src_vendor_site_id) src_vendor_site_id, lso.src_vendor_site_code, TO_NUMBER (lso.src_organization_id) src_organization_id, TO_NUMBER (lso.src_location_id) src_location_id, TO_NUMBER (lso.src_customer_id) src_customer_id, lso.src_customer_name, lso.src_customer_number, lso.src_customer_site_code, lso.src_cust_item_num, TO_NUMBER (lso.src_release_num) src_release_num, TO_NUMBER (lso.src_release_id) src_release_id, TO_DATE (lso.src_release_date) src_release_date, TO_NUMBER (lso.src_line_id) src_line_id, TO_NUMBER (lso.src_line_num) src_line_num, TO_NUMBER (lso.src_ship_id) src_ship_id, lso.src_ship_num, TO_DATE (lso.src_shipped_date) src_shipped_date, TO_NUMBER (lso.src_qty) src_qty, TO_NUMBER (lso.src_item_id) src_item_id, lso.src_item_revision, lso.src_item_description, lso.src_container_num, lso.src_truck_num, lso.src_bar_code_label, TO_NUMBER (lso.src_deliver_to_person_id) src_deliver_to_person_id, lso.src_vendor_item_num, lso.src_packing_slip, TO_NUMBER (lso.src_employee_id) src_employee_id, lso.src_freight_carrier_code, lso.src_bill_of_lading, TO_NUMBER (lso.src_routing_id) src_routing_id, lso.src_inspection_status_code, TO_DATE (lso.src_transaction_date) src_transaction_date FROM inl_ship_overall_v lso WHERE (lso.ship_line_src_type_code IS NULL OR lso.ship_line_src_type_code = 'RMA') UNION ALL SELECT cfoh.ship_header_id, cfoh.organization_id, cfoh.location_id, cfoh.org_id, cfoh.ship_type_id, cfot.ship_type_code, cfot.ship_type_name, cfoh.ship_num, cfoh.ship_date, cfoh.ship_status_code, cfoh.pending_matching_flag, cfoh.pending_update_flag, cfoh.simulation_id, cfolg.ship_line_group_id, cfolg.ship_line_group_num, cfolg.ship_line_group_reference, cfolg.src_type_code, cfolg.party_id group_party_id, hp.party_name group_party_name, cfolg.party_site_id group_party_site_id, hps.party_site_name group_party_site_name, NULL group_organization_id, NULL group_organization_name, cfol.ship_line_id, cfol.ship_line_num, cfol.parent_ship_line_id est_ship_line_id, sl.txn_unit_price est_txn_unit_price, cfol.ship_line_src_type_code, cfol.ship_line_source_id, cfolt.ship_line_type_code, cfolt.ship_line_type_name, msi.inventory_item_id item_id, msi.concatenated_segments item, ol.item_revision, msi.description item_description, cfol.txn_qty, cfol.txn_uom_code, cfol.primary_uom_code, cfol.primary_qty, cfol.secondary_uom_code, cfol.secondary_qty, cfol.txn_unit_price, cfol.currency_code, cfol.currency_conversion_type, cfol.currency_conversion_date, cfol.currency_conversion_rate, NVL (cfol.txn_qty, 0) * NVL (cfol.txn_unit_price, 0) line_amt, oh.header_id src_id, NULL src_type, TO_CHAR (oh.order_number) src_number, NULL src_description, oh.org_id src_org_id, hp1.party_id src_party_id, hp1.party_name src_party_name, cas.party_site_id src_party_site_id, hps1.party_site_name src_party_site_name, NULL src_vendor_id, NULL src_vendor_name, NULL src_vendor_site_id, NULL src_vendor_site_code, NULL src_organization_id, NULL src_location_id, oec.customer_id src_customer_id, oec.NAME src_customer_name, oec.customer_number src_customer_number, su.site_use_code src_customer_site_code, NULL src_cust_item_num, NULL src_release_num, NULL src_release_id, NULL src_release_date, ol.line_id src_line_id, ol.line_number src_line_num, NULL src_ship_id, NULL src_ship_num, NULL src_shipped_date, ol.ordered_quantity src_qty, rsl.item_id src_item_id, rsl.item_revision src_item_revision, rsl.item_description src_item_description, rsl.container_num src_container_num, rsl.truck_num src_truck_num, rsl.bar_code_label src_bar_code_label, rsl.deliver_to_person_id src_deliver_to_person_id, rsl.vendor_item_num src_vendor_item_num, rsl.packing_slip src_packing_slip, rsl.employee_id src_employee_id, rsh.freight_carrier_code src_freight_carrier_code, rsh.bill_of_lading src_bill_of_lading, rt.routing_header_id src_routing_id, rt.inspection_status_code src_inspection_status_code, rt.transaction_date src_transaction_date FROM rcv_transactions rt, rcv_shipment_headers rsh, rcv_supply rsup, rcv_shipment_lines rsl, hz_cust_site_uses su, hz_cust_acct_sites cas, oe_sold_to_orgs_v oec, hz_party_sites hps1, hz_parties hp1, mtl_system_items_vl msi, inl_ship_line_types_vl cfolt, hz_party_sites hps, hz_parties hp, inl_ship_types_vl cfot, oe_order_headers oh, oe_order_lines ol, inl_ship_headers cfoh, inl_ship_line_groups cfolg, inl_ship_lines sl, inl_adj_ship_lines_v cfol WHERE rt.transaction_id(+) = rsup.rcv_transaction_id AND rsh.shipment_header_id(+) = rsup.shipment_header_id AND rsup.shipment_line_id(+) = rsl.shipment_line_id AND rsl.oe_order_header_id(+) = ol.header_id AND rsl.oe_order_line_id(+) = ol.line_id AND cas.cust_acct_site_id(+) = su.cust_acct_site_id AND su.site_use_id(+) = oh.ship_to_org_id AND oec.organization_id(+) = oh.sold_to_org_id AND cas.party_site_id = hps1.party_site_id(+) AND hp1.party_number(+) = oec.customer_number AND cfot.ship_type_id = cfoh.ship_type_id AND cfolt.ship_line_type_id = cfol.ship_line_type_id AND msi.organization_id = cfoh.organization_id AND msi.inventory_item_id = cfol.inventory_item_id AND hps.party_site_id(+) = cfolg.party_site_id AND hp.party_id = cfolg.party_id AND cfoh.ship_header_id = cfolg.ship_header_id AND cfolg.ship_line_group_id = cfol.ship_line_group_id AND oh.header_id = ol.header_id AND ol.line_id = cfol.ship_line_source_id AND sl.ship_line_id = NVL (cfol.parent_ship_line_id, cfol.ship_line_id) AND cfol.ship_line_src_type_code = 'RMA'

View Text - HTML Formatted

SELECT LSO.SHIP_HEADER_ID
, LSO.ORGANIZATION_ID
, LSO.LOCATION_ID
, LSO.ORG_ID
, LSO.SHIP_TYPE_ID
, LSO.SHIP_TYPE_CODE
, LSO.SHIP_TYPE_NAME
, LSO.SHIP_NUM
, LSO.SHIP_DATE
, LSO.SHIP_STATUS_CODE
, LSO.PENDING_MATCHING_FLAG
, LSO.PENDING_UPDATE_FLAG
, LSO.SIMULATION_ID
, LSO.SHIP_LINE_GROUP_ID
, LSO.SHIP_LINE_GROUP_NUM
, LSO.SHIP_LINE_GROUP_REFERENCE
, LSO.SRC_TYPE_CODE
, LSO.GROUP_PARTY_ID
, LSO.GROUP_PARTY_NAME
, LSO.GROUP_PARTY_SITE_ID
, LSO.GROUP_PARTY_SITE_NAME
, LSO.GROUP_ORGANIZATION_ID
, LSO.GROUP_ORGANIZATION_NAME
, TO_NUMBER (LSO.SHIP_LINE_ID) SHIP_LINE_ID
, TO_NUMBER (LSO.SHIP_LINE_NUM) SHIP_LINE_NUM
, TO_NUMBER (EST_SHIP_LINE_ID) EST_SHIP_LINE_ID
, TO_NUMBER (LSO.EST_TXN_UNIT_PRICE) EST_TXN_UNIT_PRICE
, LSO.SHIP_LINE_SRC_TYPE_CODE
, TO_NUMBER (LSO.SHIP_LINE_SOURCE_ID) SHIP_LINE_SOURCE_ID
, LSO.SHIP_LINE_TYPE_CODE
, LSO.SHIP_LINE_TYPE_NAME
, TO_NUMBER (LSO.ITEM_ID) ITEM_ID
, LSO.ITEM
, LSO.ITEM_REVISION
, LSO.ITEM_DESCRIPTION
, TO_NUMBER (LSO.TXN_QTY) TXN_QTY
, LSO.TXN_UOM_CODE
, LSO.PRIMARY_UOM_CODE
, TO_NUMBER (LSO.PRIMARY_QTY) PRIMARY_QTY
, LSO.SECONDARY_UOM_CODE
, TO_NUMBER (LSO.SECONDARY_QTY) SECONDARY_QTY
, TO_NUMBER (LSO.TXN_UNIT_PRICE) TXN_UNIT_PRICE
, LSO.CURRENCY_CODE
, LSO.CURRENCY_CONVERSION_TYPE
, TO_DATE (LSO.CURRENCY_CONVERSION_DATE) CURRENCY_CONVERSION_DATE
, TO_NUMBER (LSO.CURRENCY_CONVERSION_RATE) CURRENCY_CONVERSION_RATE
, TO_NUMBER (LSO.LINE_AMT) LINE_AMT
, TO_NUMBER (LSO.SRC_ID) SRC_ID
, LSO.SRC_TYPE
, LSO.SRC_NUMBER
, LSO.SRC_DESCRIPTION
, TO_NUMBER (LSO.SRC_ORG_ID) SRC_ORG_ID
, TO_NUMBER (LSO.SRC_PARTY_ID) SRC_PARTY_ID
, LSO.SRC_PARTY_NAME
, TO_NUMBER (LSO.SRC_PARTY_SITE_ID) SRC_PARTY_SITE_ID
, LSO.SRC_PARTY_SITE_NAME
, TO_NUMBER (LSO.SRC_VENDOR_ID) SRC_VENDOR_ID
, LSO.SRC_VENDOR_NAME
, TO_NUMBER (LSO.SRC_VENDOR_SITE_ID) SRC_VENDOR_SITE_ID
, LSO.SRC_VENDOR_SITE_CODE
, TO_NUMBER (LSO.SRC_ORGANIZATION_ID) SRC_ORGANIZATION_ID
, TO_NUMBER (LSO.SRC_LOCATION_ID) SRC_LOCATION_ID
, TO_NUMBER (LSO.SRC_CUSTOMER_ID) SRC_CUSTOMER_ID
, LSO.SRC_CUSTOMER_NAME
, LSO.SRC_CUSTOMER_NUMBER
, LSO.SRC_CUSTOMER_SITE_CODE
, LSO.SRC_CUST_ITEM_NUM
, TO_NUMBER (LSO.SRC_RELEASE_NUM) SRC_RELEASE_NUM
, TO_NUMBER (LSO.SRC_RELEASE_ID) SRC_RELEASE_ID
, TO_DATE (LSO.SRC_RELEASE_DATE) SRC_RELEASE_DATE
, TO_NUMBER (LSO.SRC_LINE_ID) SRC_LINE_ID
, TO_NUMBER (LSO.SRC_LINE_NUM) SRC_LINE_NUM
, TO_NUMBER (LSO.SRC_SHIP_ID) SRC_SHIP_ID
, LSO.SRC_SHIP_NUM
, TO_DATE (LSO.SRC_SHIPPED_DATE) SRC_SHIPPED_DATE
, TO_NUMBER (LSO.SRC_QTY) SRC_QTY
, TO_NUMBER (LSO.SRC_ITEM_ID) SRC_ITEM_ID
, LSO.SRC_ITEM_REVISION
, LSO.SRC_ITEM_DESCRIPTION
, LSO.SRC_CONTAINER_NUM
, LSO.SRC_TRUCK_NUM
, LSO.SRC_BAR_CODE_LABEL
, TO_NUMBER (LSO.SRC_DELIVER_TO_PERSON_ID) SRC_DELIVER_TO_PERSON_ID
, LSO.SRC_VENDOR_ITEM_NUM
, LSO.SRC_PACKING_SLIP
, TO_NUMBER (LSO.SRC_EMPLOYEE_ID) SRC_EMPLOYEE_ID
, LSO.SRC_FREIGHT_CARRIER_CODE
, LSO.SRC_BILL_OF_LADING
, TO_NUMBER (LSO.SRC_ROUTING_ID) SRC_ROUTING_ID
, LSO.SRC_INSPECTION_STATUS_CODE
, TO_DATE (LSO.SRC_TRANSACTION_DATE) SRC_TRANSACTION_DATE
FROM INL_SHIP_OVERALL_V LSO
WHERE (LSO.SHIP_LINE_SRC_TYPE_CODE IS NULL OR LSO.SHIP_LINE_SRC_TYPE_CODE = 'RMA') UNION ALL SELECT CFOH.SHIP_HEADER_ID
, CFOH.ORGANIZATION_ID
, CFOH.LOCATION_ID
, CFOH.ORG_ID
, CFOH.SHIP_TYPE_ID
, CFOT.SHIP_TYPE_CODE
, CFOT.SHIP_TYPE_NAME
, CFOH.SHIP_NUM
, CFOH.SHIP_DATE
, CFOH.SHIP_STATUS_CODE
, CFOH.PENDING_MATCHING_FLAG
, CFOH.PENDING_UPDATE_FLAG
, CFOH.SIMULATION_ID
, CFOLG.SHIP_LINE_GROUP_ID
, CFOLG.SHIP_LINE_GROUP_NUM
, CFOLG.SHIP_LINE_GROUP_REFERENCE
, CFOLG.SRC_TYPE_CODE
, CFOLG.PARTY_ID GROUP_PARTY_ID
, HP.PARTY_NAME GROUP_PARTY_NAME
, CFOLG.PARTY_SITE_ID GROUP_PARTY_SITE_ID
, HPS.PARTY_SITE_NAME GROUP_PARTY_SITE_NAME
, NULL GROUP_ORGANIZATION_ID
, NULL GROUP_ORGANIZATION_NAME
, CFOL.SHIP_LINE_ID
, CFOL.SHIP_LINE_NUM
, CFOL.PARENT_SHIP_LINE_ID EST_SHIP_LINE_ID
, SL.TXN_UNIT_PRICE EST_TXN_UNIT_PRICE
, CFOL.SHIP_LINE_SRC_TYPE_CODE
, CFOL.SHIP_LINE_SOURCE_ID
, CFOLT.SHIP_LINE_TYPE_CODE
, CFOLT.SHIP_LINE_TYPE_NAME
, MSI.INVENTORY_ITEM_ID ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM
, OL.ITEM_REVISION
, MSI.DESCRIPTION ITEM_DESCRIPTION
, CFOL.TXN_QTY
, CFOL.TXN_UOM_CODE
, CFOL.PRIMARY_UOM_CODE
, CFOL.PRIMARY_QTY
, CFOL.SECONDARY_UOM_CODE
, CFOL.SECONDARY_QTY
, CFOL.TXN_UNIT_PRICE
, CFOL.CURRENCY_CODE
, CFOL.CURRENCY_CONVERSION_TYPE
, CFOL.CURRENCY_CONVERSION_DATE
, CFOL.CURRENCY_CONVERSION_RATE
, NVL (CFOL.TXN_QTY
, 0) * NVL (CFOL.TXN_UNIT_PRICE
, 0) LINE_AMT
, OH.HEADER_ID SRC_ID
, NULL SRC_TYPE
, TO_CHAR (OH.ORDER_NUMBER) SRC_NUMBER
, NULL SRC_DESCRIPTION
, OH.ORG_ID SRC_ORG_ID
, HP1.PARTY_ID SRC_PARTY_ID
, HP1.PARTY_NAME SRC_PARTY_NAME
, CAS.PARTY_SITE_ID SRC_PARTY_SITE_ID
, HPS1.PARTY_SITE_NAME SRC_PARTY_SITE_NAME
, NULL SRC_VENDOR_ID
, NULL SRC_VENDOR_NAME
, NULL SRC_VENDOR_SITE_ID
, NULL SRC_VENDOR_SITE_CODE
, NULL SRC_ORGANIZATION_ID
, NULL SRC_LOCATION_ID
, OEC.CUSTOMER_ID SRC_CUSTOMER_ID
, OEC.NAME SRC_CUSTOMER_NAME
, OEC.CUSTOMER_NUMBER SRC_CUSTOMER_NUMBER
, SU.SITE_USE_CODE SRC_CUSTOMER_SITE_CODE
, NULL SRC_CUST_ITEM_NUM
, NULL SRC_RELEASE_NUM
, NULL SRC_RELEASE_ID
, NULL SRC_RELEASE_DATE
, OL.LINE_ID SRC_LINE_ID
, OL.LINE_NUMBER SRC_LINE_NUM
, NULL SRC_SHIP_ID
, NULL SRC_SHIP_NUM
, NULL SRC_SHIPPED_DATE
, OL.ORDERED_QUANTITY SRC_QTY
, RSL.ITEM_ID SRC_ITEM_ID
, RSL.ITEM_REVISION SRC_ITEM_REVISION
, RSL.ITEM_DESCRIPTION SRC_ITEM_DESCRIPTION
, RSL.CONTAINER_NUM SRC_CONTAINER_NUM
, RSL.TRUCK_NUM SRC_TRUCK_NUM
, RSL.BAR_CODE_LABEL SRC_BAR_CODE_LABEL
, RSL.DELIVER_TO_PERSON_ID SRC_DELIVER_TO_PERSON_ID
, RSL.VENDOR_ITEM_NUM SRC_VENDOR_ITEM_NUM
, RSL.PACKING_SLIP SRC_PACKING_SLIP
, RSL.EMPLOYEE_ID SRC_EMPLOYEE_ID
, RSH.FREIGHT_CARRIER_CODE SRC_FREIGHT_CARRIER_CODE
, RSH.BILL_OF_LADING SRC_BILL_OF_LADING
, RT.ROUTING_HEADER_ID SRC_ROUTING_ID
, RT.INSPECTION_STATUS_CODE SRC_INSPECTION_STATUS_CODE
, RT.TRANSACTION_DATE SRC_TRANSACTION_DATE
FROM RCV_TRANSACTIONS RT
, RCV_SHIPMENT_HEADERS RSH
, RCV_SUPPLY RSUP
, RCV_SHIPMENT_LINES RSL
, HZ_CUST_SITE_USES SU
, HZ_CUST_ACCT_SITES CAS
, OE_SOLD_TO_ORGS_V OEC
, HZ_PARTY_SITES HPS1
, HZ_PARTIES HP1
, MTL_SYSTEM_ITEMS_VL MSI
, INL_SHIP_LINE_TYPES_VL CFOLT
, HZ_PARTY_SITES HPS
, HZ_PARTIES HP
, INL_SHIP_TYPES_VL CFOT
, OE_ORDER_HEADERS OH
, OE_ORDER_LINES OL
, INL_SHIP_HEADERS CFOH
, INL_SHIP_LINE_GROUPS CFOLG
, INL_SHIP_LINES SL
, INL_ADJ_SHIP_LINES_V CFOL
WHERE RT.TRANSACTION_ID(+) = RSUP.RCV_TRANSACTION_ID
AND RSH.SHIPMENT_HEADER_ID(+) = RSUP.SHIPMENT_HEADER_ID
AND RSUP.SHIPMENT_LINE_ID(+) = RSL.SHIPMENT_LINE_ID
AND RSL.OE_ORDER_HEADER_ID(+) = OL.HEADER_ID
AND RSL.OE_ORDER_LINE_ID(+) = OL.LINE_ID
AND CAS.CUST_ACCT_SITE_ID(+) = SU.CUST_ACCT_SITE_ID
AND SU.SITE_USE_ID(+) = OH.SHIP_TO_ORG_ID
AND OEC.ORGANIZATION_ID(+) = OH.SOLD_TO_ORG_ID
AND CAS.PARTY_SITE_ID = HPS1.PARTY_SITE_ID(+)
AND HP1.PARTY_NUMBER(+) = OEC.CUSTOMER_NUMBER
AND CFOT.SHIP_TYPE_ID = CFOH.SHIP_TYPE_ID
AND CFOLT.SHIP_LINE_TYPE_ID = CFOL.SHIP_LINE_TYPE_ID
AND MSI.ORGANIZATION_ID = CFOH.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = CFOL.INVENTORY_ITEM_ID
AND HPS.PARTY_SITE_ID(+) = CFOLG.PARTY_SITE_ID
AND HP.PARTY_ID = CFOLG.PARTY_ID
AND CFOH.SHIP_HEADER_ID = CFOLG.SHIP_HEADER_ID
AND CFOLG.SHIP_LINE_GROUP_ID = CFOL.SHIP_LINE_GROUP_ID
AND OH.HEADER_ID = OL.HEADER_ID
AND OL.LINE_ID = CFOL.SHIP_LINE_SOURCE_ID
AND SL.SHIP_LINE_ID = NVL (CFOL.PARENT_SHIP_LINE_ID
, CFOL.SHIP_LINE_ID)
AND CFOL.SHIP_LINE_SRC_TYPE_CODE = 'RMA'