Product: | CSF - Field Service |
---|---|
Description: | Field service Report Material Lines information |
Implementation/DBA Data: | APPS.CSF_DEBRIEF_MAT_LINES_V |
SELECT CDL.ROWID
, CDL.CHANNEL_CODE
, CDL.DEBRIEF_LINE_ID
, CDL.DEBRIEF_HEADER_ID
, CDL.SERVICE_DATE
, CDL.INVENTORY_ITEM_ID
, CII.INSTANCE_ID
, CII.INSTANCE_NUMBER
, NVL(CDL.ISSUING_INVENTORY_ORG_ID
, CDL.RECEIVING_INVENTORY_ORG_ID)
, NVL(CDL.ISSUING_SUB_INVENTORY_CODE
, CDL.RECEIVING_SUB_INVENTORY_CODE)
, NVL(CDL.ISSUING_LOCATOR_ID
, CDL.RECEIVING_LOCATOR_ID)
, CDL.UOM_CODE
, CDL.PARENT_PRODUCT_ID
, CDL.REMOVED_PRODUCT_ID
, CDL.STATUS_OF_RECEIVED_PART
, CDL.QUANTITY
, CDL.ITEM_REVISION
, CDL.ITEM_LOTNUMBER
, CDL.ITEM_SERIAL_NUMBER
, CDL.CREATED_BY
, CDL.CREATION_DATE
, CDL.LAST_UPDATED_BY
, CDL.LAST_UPDATE_DATE
, CDL.LAST_UPDATE_LOGIN
, CDL.ATTRIBUTE1
, CDL.ATTRIBUTE2
, CDL.ATTRIBUTE3
, CDL.ATTRIBUTE4
, CDL.ATTRIBUTE5
, CDL.ATTRIBUTE6
, CDL.ATTRIBUTE7
, CDL.ATTRIBUTE8
, CDL.ATTRIBUTE9
, CDL.ATTRIBUTE10
, CDL.ATTRIBUTE11
, CDL.ATTRIBUTE12
, CDL.ATTRIBUTE13
, CDL.ATTRIBUTE14
, CDL.ATTRIBUTE15
, CDL.ATTRIBUTE_CATEGORY
, CDL.MATERIAL_REASON_CODE
, CDL.BUSINESS_PROCESS_ID
, CTT.TRANSACTION_TYPE_ID
, CTT.LINE_ORDER_CATEGORY_CODE
, CTT.NAME
, FL.MEANING
, CTT.REVISION_FLAG
, CDL.IB_UPDATE_STATUS
, CDL.IB_UPDATE_MSG_CODE
, CDL.IB_UPDATE_MESSAGE
, CDL.SPARE_UPDATE_STATUS
, CDL.SPARE_UPDATE_MSG_CODE
, CDL.SPARE_UPDATE_MESSAGE
, CDL.CHARGE_UPLOAD_STATUS
, CDL.CHARGE_UPLOAD_MSG_CODE
, CDL.CHARGE_UPLOAD_MESSAGE
, CDL.RETURN_REASON_CODE
, ARC.RETURN_REASON
, CDL.RETURN_DATE
, CDL.ERROR_TEXT FROM CSF_DEBRIEF_LINES CDL
, CS_TRANSACTION_TYPES_VL CTT
, CSI_ITEM_INSTANCES CII
, FND_LOOKUPS FL
, ASO_I_RETURN_CODES_V ARC
, CS_BILLING_TYPE_CATEGORIES CBTC
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
WHERE CTT.TRANSACTION_TYPE_ID = CDL.TRANSACTION_TYPE_ID
AND MSIBK.MATERIAL_BILLABLE_FLAG = CBTC.BILLING_TYPE
AND CBTC.BILLING_CATEGORY = 'M'
AND MSIBK.INVENTORY_ITEM_ID = CDL.INVENTORY_ITEM_ID
AND MSIBK.ORGANIZATION_ID = NVL(CDL.ISSUING_INVENTORY_ORG_ID
, CDL.RECEIVING_INVENTORY_ORG_ID) AND CII.INSTANCE_ID(+)=CDL.INSTANCE_ID
AND FL.LOOKUP_TYPE (+) = 'CSF_MATERIAL_REASON'
AND CDL.MATERIAL_REASON_CODE = FL.LOOKUP_CODE (+)
AND CDL.RETURN_REASON_CODE = ARC.RETURN_REASON_CODE (+)