VIEW: APPS.CSF_M_DEBRIEF_PARTS_V

Source

View Text - Preformatted

SELECT CSM_UTIL_PKG.GET_NUMBER(acc.access_id) , lines.DEBRIEF_LINE_ID ,NVL( lines.ISSUING_SUB_INVENTORY_CODE, lines.receiving_sub_inventory_code) , NVL(lines.ISSUING_INVENTORY_ORG_ID, lines.receiving_inventory_org_id) , lines.INVENTORY_ITEM_ID , lines.TXN_BILLING_TYPE_ID , CSM_UTIL_PKG.GET_NUMBER(header.task_assignment_id) , asg_base.get_resource_id as RESOURCE_ID, lines.ITEM_SERIAL_NUMBER , lines.QUANTITY , lines.UOM_CODE , lines.ITEM_REVISION , lines.ITEM_LOTNUMBER , lines.BUSINESS_PROCESS_ID , lines.INSTANCE_ID,CSM_UTIL_PKG.GET_DATE(lines.last_update_date), csm_util_pkg.item_name(msi.concatenated_segments) AS item_name, msi.description AS item_description, msi.serial_number_control_code, lines.transaction_type_id, lines.material_reason_code , CSM_UTIL_PKG.GET_DATE(lines.service_date), lines.attribute1, lines.attribute2, lines.attribute3, lines.attribute4, lines.attribute5, lines.attribute6, lines.attribute7, lines.attribute8, lines.attribute9, lines.attribute10, lines.attribute11, lines.attribute12, lines.attribute13, lines.attribute14, lines.attribute15, lines.attribute_category, lines.return_reason_code, lines.disposition_code, NVL(lines.issuing_locator_id, lines.receiving_locator_id) issuing_locator_id, parent_product_id, removed_product_id, CSM_UTIL_PKG.get_debrief_header_id(header.debrief_header_id ) as debrief_header_id, LINES.RETURN_DATE,CSM_UTIL_PKG.GET_NUMBER(lines.created_by) AS CREATED_BY,CSM_UTIL_PKG.GET_NUMBER(lines.last_updated_by) AS LAST_UPDATED_BY, lines.last_update_date AS SERVER_LAST_UPDATE_DATE, acc.FREE_FORM_ITEM, CSM_UTIL_PKG.GET_NUMBER(lines.DEBRIEF_LINE_NUMBER) AS DEBRIEF_LINE_NUMBER , lines.CARRIER_CODE,lines.USAGE_TYPE,lines.WAYBILL,lines.MATERIAL_TRANSACTION_ID,lines.SHIPPING_METHOD,lines.SHIPPING_NUMBER,lines.RETURN_ORGANIZATION_ID,lines.RETURN_SUBINVENTORY_NAME,lines.STATUS_OF_RECEIVED_PART,lines.CHANNEL_CODE,msi.COMMS_NL_TRACKABLE_FLAG, cii_r.serial_number as RECOVERED_SERIAL_NUMBER, cii_p.serial_number as PARENT_SERIAL_NUMBER FROM CSM_DEBRIEF_LINES_ACC acc , CSF_DEBRIEF_LINES lines , cs_txn_billing_types txbt, CS_BILLING_TYPE_CATEGORIES cbtc, CSF_DEBRIEF_HEADERS header, mtl_system_items_b_kfv msi ,csi_item_instances cii_p,csi_item_instances cii_r WHERE acc.debrief_line_id = lines.debrief_line_id AND acc.user_id = asg_base.get_user_id AND lines.debrief_header_id = header.debrief_header_id AND lines.transaction_type_id = txbt.transaction_type_id AND txbt.billing_type = cbtc.billing_type AND cbtc.billing_category = 'M' AND msi.inventory_item_id = lines.inventory_item_id AND msi.organization_id = nvl(lines.issuing_inventory_org_id, lines.receiving_inventory_org_id) AND msi.material_billable_flag = txbt.billing_type AND lines.parent_product_id=cii_p.instance_id(+) AND lines.removed_product_id=cii_r.instance_id(+) UNION ALL SELECT CSM_UTIL_PKG.GET_NUMBER(acc.access_id) , lines.DEBRIEF_LINE_ID ,NVL( lines.ISSUING_SUB_INVENTORY_CODE, lines.receiving_sub_inventory_code) , NVL(lines.ISSUING_INVENTORY_ORG_ID, lines.receiving_inventory_org_id) , lines.INVENTORY_ITEM_ID , lines.TXN_BILLING_TYPE_ID , CSM_UTIL_PKG.GET_NUMBER(header.task_assignment_id) , asg_base.get_resource_id as RESOURCE_ID, lines.ITEM_SERIAL_NUMBER , lines.QUANTITY , lines.UOM_CODE , lines.ITEM_REVISION , lines.ITEM_LOTNUMBER , lines.BUSINESS_PROCESS_ID , lines.INSTANCE_ID,CSM_UTIL_PKG.GET_DATE(lines.last_update_date), csm_util_pkg.item_name(msi.concatenated_segments) AS item_name, msi.description AS item_description, msi.serial_number_control_code, lines.transaction_type_id, lines.material_reason_code , CSM_UTIL_PKG.GET_DATE(lines.service_date), lines.attribute1, lines.attribute2, lines.attribute3, lines.attribute4, lines.attribute5, lines.attribute6, lines.attribute7, lines.attribute8, lines.attribute9, lines.attribute10, lines.attribute11, lines.attribute12, lines.attribute13, lines.attribute14, lines.attribute15, lines.attribute_category, lines.return_reason_code, lines.disposition_code, NVL(lines.issuing_locator_id, lines.receiving_locator_id) issuing_locator_id, parent_product_id, removed_product_id, CSM_UTIL_PKG.get_debrief_header_id(header.debrief_header_id ) as debrief_header_id, LINES.RETURN_DATE,CSM_UTIL_PKG.GET_NUMBER(lines.created_by) AS CREATED_BY,CSM_UTIL_PKG.GET_NUMBER(lines.last_updated_by) AS LAST_UPDATED_BY, lines.last_update_date AS SERVER_LAST_UPDATE_DATE, acc.FREE_FORM_ITEM, CSM_UTIL_PKG.GET_NUMBER(lines.DEBRIEF_LINE_NUMBER) AS DEBRIEF_LINE_NUMBER , lines.CARRIER_CODE,lines.USAGE_TYPE,lines.WAYBILL,lines.MATERIAL_TRANSACTION_ID,lines.SHIPPING_METHOD,lines.SHIPPING_NUMBER,lines.RETURN_ORGANIZATION_ID,lines.RETURN_SUBINVENTORY_NAME,lines.STATUS_OF_RECEIVED_PART,lines.CHANNEL_CODE,msi.COMMS_NL_TRACKABLE_FLAG, csm_util_pkg.get_varchar('') as RECOVERED_SERIAL_NUMBER, csm_util_pkg.get_varchar('') as PARENT_SERIAL_NUMBER FROM CSM_DEBRIEF_LINES_ACC acc , CSF_DEBRIEF_LINES lines , CSF_DEBRIEF_HEADERS header, mtl_system_items_b_kfv msi WHERE acc.debrief_line_id = lines.debrief_line_id AND acc.user_id = asg_base.get_user_id AND lines.debrief_header_id = header.debrief_header_id AND lines.transaction_type_id is NULL AND lines.USAGE_TYPE IN ('DOA','UNUSED') AND msi.inventory_item_id = lines.inventory_item_id AND msi.organization_id = nvl(lines.issuing_inventory_org_id, lines.receiving_inventory_org_id) AND msi.material_billable_flag = 'M'

View Text - HTML Formatted

SELECT CSM_UTIL_PKG.GET_NUMBER(ACC.ACCESS_ID)
, LINES.DEBRIEF_LINE_ID
, NVL( LINES.ISSUING_SUB_INVENTORY_CODE
, LINES.RECEIVING_SUB_INVENTORY_CODE)
, NVL(LINES.ISSUING_INVENTORY_ORG_ID
, LINES.RECEIVING_INVENTORY_ORG_ID)
, LINES.INVENTORY_ITEM_ID
, LINES.TXN_BILLING_TYPE_ID
, CSM_UTIL_PKG.GET_NUMBER(HEADER.TASK_ASSIGNMENT_ID)
, ASG_BASE.GET_RESOURCE_ID AS RESOURCE_ID
, LINES.ITEM_SERIAL_NUMBER
, LINES.QUANTITY
, LINES.UOM_CODE
, LINES.ITEM_REVISION
, LINES.ITEM_LOTNUMBER
, LINES.BUSINESS_PROCESS_ID
, LINES.INSTANCE_ID
, CSM_UTIL_PKG.GET_DATE(LINES.LAST_UPDATE_DATE)
, CSM_UTIL_PKG.ITEM_NAME(MSI.CONCATENATED_SEGMENTS) AS ITEM_NAME
, MSI.DESCRIPTION AS ITEM_DESCRIPTION
, MSI.SERIAL_NUMBER_CONTROL_CODE
, LINES.TRANSACTION_TYPE_ID
, LINES.MATERIAL_REASON_CODE
, CSM_UTIL_PKG.GET_DATE(LINES.SERVICE_DATE)
, LINES.ATTRIBUTE1
, LINES.ATTRIBUTE2
, LINES.ATTRIBUTE3
, LINES.ATTRIBUTE4
, LINES.ATTRIBUTE5
, LINES.ATTRIBUTE6
, LINES.ATTRIBUTE7
, LINES.ATTRIBUTE8
, LINES.ATTRIBUTE9
, LINES.ATTRIBUTE10
, LINES.ATTRIBUTE11
, LINES.ATTRIBUTE12
, LINES.ATTRIBUTE13
, LINES.ATTRIBUTE14
, LINES.ATTRIBUTE15
, LINES.ATTRIBUTE_CATEGORY
, LINES.RETURN_REASON_CODE
, LINES.DISPOSITION_CODE
, NVL(LINES.ISSUING_LOCATOR_ID
, LINES.RECEIVING_LOCATOR_ID) ISSUING_LOCATOR_ID
, PARENT_PRODUCT_ID
, REMOVED_PRODUCT_ID
, CSM_UTIL_PKG.GET_DEBRIEF_HEADER_ID(HEADER.DEBRIEF_HEADER_ID ) AS DEBRIEF_HEADER_ID
, LINES.RETURN_DATE
, CSM_UTIL_PKG.GET_NUMBER(LINES.CREATED_BY) AS CREATED_BY
, CSM_UTIL_PKG.GET_NUMBER(LINES.LAST_UPDATED_BY) AS LAST_UPDATED_BY
, LINES.LAST_UPDATE_DATE AS SERVER_LAST_UPDATE_DATE
, ACC.FREE_FORM_ITEM
, CSM_UTIL_PKG.GET_NUMBER(LINES.DEBRIEF_LINE_NUMBER) AS DEBRIEF_LINE_NUMBER
, LINES.CARRIER_CODE
, LINES.USAGE_TYPE
, LINES.WAYBILL
, LINES.MATERIAL_TRANSACTION_ID
, LINES.SHIPPING_METHOD
, LINES.SHIPPING_NUMBER
, LINES.RETURN_ORGANIZATION_ID
, LINES.RETURN_SUBINVENTORY_NAME
, LINES.STATUS_OF_RECEIVED_PART
, LINES.CHANNEL_CODE
, MSI.COMMS_NL_TRACKABLE_FLAG
, CII_R.SERIAL_NUMBER AS RECOVERED_SERIAL_NUMBER
, CII_P.SERIAL_NUMBER AS PARENT_SERIAL_NUMBER
FROM CSM_DEBRIEF_LINES_ACC ACC
, CSF_DEBRIEF_LINES LINES
, CS_TXN_BILLING_TYPES TXBT
, CS_BILLING_TYPE_CATEGORIES CBTC
, CSF_DEBRIEF_HEADERS HEADER
, MTL_SYSTEM_ITEMS_B_KFV MSI
, CSI_ITEM_INSTANCES CII_P
, CSI_ITEM_INSTANCES CII_R
WHERE ACC.DEBRIEF_LINE_ID = LINES.DEBRIEF_LINE_ID
AND ACC.USER_ID = ASG_BASE.GET_USER_ID
AND LINES.DEBRIEF_HEADER_ID = HEADER.DEBRIEF_HEADER_ID
AND LINES.TRANSACTION_TYPE_ID = TXBT.TRANSACTION_TYPE_ID
AND TXBT.BILLING_TYPE = CBTC.BILLING_TYPE
AND CBTC.BILLING_CATEGORY = 'M'
AND MSI.INVENTORY_ITEM_ID = LINES.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = NVL(LINES.ISSUING_INVENTORY_ORG_ID
, LINES.RECEIVING_INVENTORY_ORG_ID)
AND MSI.MATERIAL_BILLABLE_FLAG = TXBT.BILLING_TYPE
AND LINES.PARENT_PRODUCT_ID=CII_P.INSTANCE_ID(+)
AND LINES.REMOVED_PRODUCT_ID=CII_R.INSTANCE_ID(+) UNION ALL SELECT CSM_UTIL_PKG.GET_NUMBER(ACC.ACCESS_ID)
, LINES.DEBRIEF_LINE_ID
, NVL( LINES.ISSUING_SUB_INVENTORY_CODE
, LINES.RECEIVING_SUB_INVENTORY_CODE)
, NVL(LINES.ISSUING_INVENTORY_ORG_ID
, LINES.RECEIVING_INVENTORY_ORG_ID)
, LINES.INVENTORY_ITEM_ID
, LINES.TXN_BILLING_TYPE_ID
, CSM_UTIL_PKG.GET_NUMBER(HEADER.TASK_ASSIGNMENT_ID)
, ASG_BASE.GET_RESOURCE_ID AS RESOURCE_ID
, LINES.ITEM_SERIAL_NUMBER
, LINES.QUANTITY
, LINES.UOM_CODE
, LINES.ITEM_REVISION
, LINES.ITEM_LOTNUMBER
, LINES.BUSINESS_PROCESS_ID
, LINES.INSTANCE_ID
, CSM_UTIL_PKG.GET_DATE(LINES.LAST_UPDATE_DATE)
, CSM_UTIL_PKG.ITEM_NAME(MSI.CONCATENATED_SEGMENTS) AS ITEM_NAME
, MSI.DESCRIPTION AS ITEM_DESCRIPTION
, MSI.SERIAL_NUMBER_CONTROL_CODE
, LINES.TRANSACTION_TYPE_ID
, LINES.MATERIAL_REASON_CODE
, CSM_UTIL_PKG.GET_DATE(LINES.SERVICE_DATE)
, LINES.ATTRIBUTE1
, LINES.ATTRIBUTE2
, LINES.ATTRIBUTE3
, LINES.ATTRIBUTE4
, LINES.ATTRIBUTE5
, LINES.ATTRIBUTE6
, LINES.ATTRIBUTE7
, LINES.ATTRIBUTE8
, LINES.ATTRIBUTE9
, LINES.ATTRIBUTE10
, LINES.ATTRIBUTE11
, LINES.ATTRIBUTE12
, LINES.ATTRIBUTE13
, LINES.ATTRIBUTE14
, LINES.ATTRIBUTE15
, LINES.ATTRIBUTE_CATEGORY
, LINES.RETURN_REASON_CODE
, LINES.DISPOSITION_CODE
, NVL(LINES.ISSUING_LOCATOR_ID
, LINES.RECEIVING_LOCATOR_ID) ISSUING_LOCATOR_ID
, PARENT_PRODUCT_ID
, REMOVED_PRODUCT_ID
, CSM_UTIL_PKG.GET_DEBRIEF_HEADER_ID(HEADER.DEBRIEF_HEADER_ID ) AS DEBRIEF_HEADER_ID
, LINES.RETURN_DATE
, CSM_UTIL_PKG.GET_NUMBER(LINES.CREATED_BY) AS CREATED_BY
, CSM_UTIL_PKG.GET_NUMBER(LINES.LAST_UPDATED_BY) AS LAST_UPDATED_BY
, LINES.LAST_UPDATE_DATE AS SERVER_LAST_UPDATE_DATE
, ACC.FREE_FORM_ITEM
, CSM_UTIL_PKG.GET_NUMBER(LINES.DEBRIEF_LINE_NUMBER) AS DEBRIEF_LINE_NUMBER
, LINES.CARRIER_CODE
, LINES.USAGE_TYPE
, LINES.WAYBILL
, LINES.MATERIAL_TRANSACTION_ID
, LINES.SHIPPING_METHOD
, LINES.SHIPPING_NUMBER
, LINES.RETURN_ORGANIZATION_ID
, LINES.RETURN_SUBINVENTORY_NAME
, LINES.STATUS_OF_RECEIVED_PART
, LINES.CHANNEL_CODE
, MSI.COMMS_NL_TRACKABLE_FLAG
, CSM_UTIL_PKG.GET_VARCHAR('') AS RECOVERED_SERIAL_NUMBER
, CSM_UTIL_PKG.GET_VARCHAR('') AS PARENT_SERIAL_NUMBER
FROM CSM_DEBRIEF_LINES_ACC ACC
, CSF_DEBRIEF_LINES LINES
, CSF_DEBRIEF_HEADERS HEADER
, MTL_SYSTEM_ITEMS_B_KFV MSI
WHERE ACC.DEBRIEF_LINE_ID = LINES.DEBRIEF_LINE_ID
AND ACC.USER_ID = ASG_BASE.GET_USER_ID
AND LINES.DEBRIEF_HEADER_ID = HEADER.DEBRIEF_HEADER_ID
AND LINES.TRANSACTION_TYPE_ID IS NULL
AND LINES.USAGE_TYPE IN ('DOA'
, 'UNUSED')
AND MSI.INVENTORY_ITEM_ID = LINES.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = NVL(LINES.ISSUING_INVENTORY_ORG_ID
, LINES.RECEIVING_INVENTORY_ORG_ID)
AND MSI.MATERIAL_BILLABLE_FLAG = 'M'