View: CSD_PRODUCT_TXNS_IRO_V

Product:	CSD - Depot Repair
Description:
Implementation/DBA Data:	APPS.CSD_PRODUCT_TXNS_IRO_V

View Text

SELECT PTXN.ROWID ROW_ID
, PTXN.PRODUCT_TRANSACTION_ID PRODUCT_TRANSACTION_ID
, CR.REPAIR_LINE_ID REPAIR_LINE_ID
, CR.REPAIR_TYPE_ID REPAIR_TYPE_ID
, CR.REPAIR_NUMBER REPAIR_NUMBER
, OOL.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, OOL.ORDER_QUANTITY_UOM UNIT_OF_MEASURE
, CR.QUANTITY RO_QUANTITY
, CR.SUPERCESSION_INV_ITEM_ID SUPERCESSION_INV_ITEM_ID
, CR.INCIDENT_ID INCIDENT_ID
, CR.CUSTOMER_PRODUCT_ID CUSTOMER_PRODUCT_ID
, CR.INSTANCE_ID INSTANCE_ID
, CR.CONTRACT_LINE_ID CONTRACT_LINE_ID
, CR.STATUS RO_STATUS
, CR.APPROVAL_REQUIRED_FLAG APPROVAL_REQUIRED_FLAG
, CR.APPROVAL_STATUS APPROVAL_STATUS
, CR.SERIAL_NUMBER RO_SERIAL_NUMBER
, CR.QUANTITY_SHIPPED RO_QUANTITY_SHIPPED
, CR.QUANTITY_RCVD RO_QUANTITY_RCVD
, CR.REPAIR_MODE REPAIR_MODE
, CR.ITEM_REVISION RO_ITEM_REVISION
, CR.CURRENCY_CODE CURRENCY_CODE
, ITEM.LOT_CONTROL_CODE LOT_CONTROL_CODE
, ITEM.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE
, ITEM.CONCATENATED_SEGMENTS ITEM
, ITEM.DESCRIPTION ITEM_DESC
, PTXN.ACTION_TYPE ACTION_TYPE
, PTXN.ACTION_CODE ACTION_CODE
, PTXN.INTERFACE_TO_OM_FLAG INTERFACE_TO_OM_FLAG
, PTXN.BOOK_SALES_ORDER_FLAG BOOK_SALES_ORDER_FLAG
, PTXN.RELEASE_SALES_ORDER_FLAG RELEASE_SALES_ORDER_FLAG
, PTXN.SHIP_SALES_ORDER_FLAG SHIP_SALES_ORDER_FLAG
, NVL( PTXN.SUB_INVENTORY
, PRL.SOURCE_SUBINVENTORY) SUB_INVENTORY
, DECODE(PTXN.PROD_TXN_STATUS
, 'SHIPPED'
, PTXN.LOT_NUMBER
, 'RECEIVED'
, PTXN.LOT_NUMBER_RCVD
, NULL ) LOT_NUMBER
, NVL(PRL.DESTINATION_SUBINVENTORY
, PTXN.SUB_INVENTORY_RCVD) SUB_INVENTORY_RCVD
, PTXN.LOT_NUMBER_RCVD LOT_NUMBER_RCVD
, PTXN.CREATED_BY CREATED_BY
, PTXN.CREATION_DATE CREATION_DATE
, PTXN.LAST_UPDATED_BY LAST_UPDATED_BY
, PTXN.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PTXN.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, PTXN.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, PTXN.SECURITY_GROUP_ID SECURITY_GROUP_ID
, PTXN.PROD_TXN_STATUS PROD_TXN_STATUS
, PTXN.PROD_TXN_CODE PROD_TXN_CODE
, PTXN.REQ_HEADER_ID REQ_HEADER_ID
, PTXN.REQ_LINE_ID REQ_LINE_ID
, PTXN.ORDER_HEADER_ID ORDER_HEADER_ID
, PTXN.ORDER_LINE_ID ORDER_LINE_ID
, PTXN.QUANTITY_RECEIVED PRDTXN_QUANTITY_RECEIVED
, PTXN.QUANTITY_SHIPPED PRDTXN_QUANTITY_SHIPPED
, PRH.SEGMENT1 REQUISITION_NUMBER
, PTXN.SOURCE_SERIAL_NUMBER SOURCE_SERIAL_NUMBER
, PTXN.SOURCE_INSTANCE_ID SOURCE_INSTANCE_ID
, PTXN.NON_SOURCE_SERIAL_NUMBER NON_SOURCE_SERIAL_NUMBER
, PTXN.NON_SOURCE_INSTANCE_ID NON_SOURCE_INSTANCE_ID
, SRCII.INSTANCE_NUMBER SOURCE_INSTANCE_NUMBER
, NONSRCII.INSTANCE_NUMBER NON_SOURCE_INSTANCE_NUMBER
, PTXN.LOCATOR_ID LOCATOR_ID
, OOH.ORDER_NUMBER INTERNAL_SO_NUMBER
, DECODE(ITEM.SERIAL_NUMBER_CONTROL_CODE
, 1
, OOL.ORDERED_QUANTITY
, 1) ORDERED_QUANTITY
, PRL.UNIT_MEAS_LOOKUP_CODE UOM
, PRL.SOURCE_ORGANIZATION_ID SOURCE_ORGANIZATION_ID
, PRL.SOURCE_SUBINVENTORY SOURCE_SUBINVENTORY
, PRL.DESTINATION_ORGANIZATION_ID DESTINATION_ORGANIZATION_ID
, PRL.DESTINATION_SUBINVENTORY DESTINATION_SUBINVENTORY
, TO_CHAR(OOL.LINE_NUMBER) || '.' || TO_CHAR( OOL.SHIPMENT_NUMBER) SO_LINE_NUMBER
, OOL.LINE_TYPE_ID SO_LINE_TYPE_ID
, HOUIN.NAME SOURCE_INV_ORG_NAME
, HOUOUT.NAME DESTINATION_INV_ORG_NAME
, NVL(CPS.ADDRESS1 || '
, '
, '') || NVL(CPS.ADDRESS2 || '
, '
, '')|| NVL(CPS.CITY || '
, '
, '') || NVL(CPS.STATE ||'
, '
, '')|| NVL(CPS.POSTAL_CODE || '
, '
, '') SHIP_TO_ADDRESS
, PTXN.CONTEXT CONTEXT
, PTXN.ATTRIBUTE1 ATTRIBUTE1
, PTXN.ATTRIBUTE2 ATTRIBUTE2
, PTXN.ATTRIBUTE3 ATTRIBUTE3
, PTXN.ATTRIBUTE4 ATTRIBUTE4
, PTXN.ATTRIBUTE5 ATTRIBUTE5
, PTXN.ATTRIBUTE6 ATTRIBUTE6
, PTXN.ATTRIBUTE7 ATTRIBUTE7
, PTXN.ATTRIBUTE8 ATTRIBUTE8
, PTXN.ATTRIBUTE9 ATTRIBUTE9
, PTXN.ATTRIBUTE10 ATTRIBUTE10
, PTXN.ATTRIBUTE11 ATTRIBUTE11
, PTXN.ATTRIBUTE12 ATTRIBUTE12
, PTXN.ATTRIBUTE13 ATTRIBUTE13
, PTXN.ATTRIBUTE14 ATTRIBUTE14
, PTXN.ATTRIBUTE15 ATTRIBUTE15
, ACT_TYPE.MEANING
, ACT_CODE.MEANING
, PROD_TXN_STATUS.MEANING
, OOH.ORDER_TYPE
, PTXN.PICKING_RULE_ID
, PCK.NAME PICKING_RULE_NAME
FROM CSD_PRODUCT_TRANSACTIONS PTXN
, CSD_REPAIRS CR
, CSD_REPAIR_TYPES_B CT
, PO_REQUISITION_HEADERS_ALL PRH
, PO_REQUISITION_LINES_ALL PRL
, OE_ORDER_HEADERS_V OOH
, OE_ORDER_LINES_V OOL
, MTL_SYSTEM_ITEMS_KFV ITEM
, HR_ALL_ORGANIZATION_UNITS HOUIN
, HR_ALL_ORGANIZATION_UNITS HOUOUT
, HZ_CUST_ACCT_SITES_ALL HCAS
, HZ_CUST_SITE_USES_ALL HCSU
, CSD_PARTY_SITES_V CPS
, CSI_ITEM_INSTANCES SRCII
, CSI_ITEM_INSTANCES NONSRCII
, FND_LOOKUPS ACT_TYPE
, FND_LOOKUPS ACT_CODE
, FND_LOOKUPS PROD_TXN_STATUS
, WSH_PICKING_RULES PCK
WHERE PTXN.ESTIMATE_DETAIL_ID IS NULL
AND PTXN.REPAIR_LINE_ID = CR.REPAIR_LINE_ID
AND CR.REPAIR_TYPE_ID = CT.REPAIR_TYPE_ID
AND CT.REPAIR_TYPE_REF = 'RF'
AND CT.INTERNAL_ORDER_FLAG = 'Y'
AND ACT_TYPE.LOOKUP_TYPE = 'CSD_PROD_ACTION_TYPE'
AND ACT_TYPE.LOOKUP_CODE = PTXN.ACTION_TYPE
AND ACT_CODE.LOOKUP_TYPE = 'CSD_PRODUCT_ACTION_CODE'
AND ACT_CODE.LOOKUP_CODE = PTXN.ACTION_CODE
AND PROD_TXN_STATUS.LOOKUP_TYPE = 'CSD_PRODUCT_TXN_STATUS'
AND PROD_TXN_STATUS.LOOKUP_CODE = PTXN.PROD_TXN_STATUS
AND PTXN.REQ_HEADER_ID = PRH.REQUISITION_HEADER_ID
AND PTXN.REQ_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND PTXN.REQ_LINE_ID = PRL.REQUISITION_LINE_ID
AND PTXN.ORDER_HEADER_ID = OOH.HEADER_ID
AND PTXN.ORDER_HEADER_ID = OOL.HEADER_ID
AND PTXN.ORDER_LINE_ID = OOL.LINE_ID
AND OOL.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
AND OOL.SHIP_FROM_ORG_ID = ITEM.ORGANIZATION_ID
AND HOUIN.ORGANIZATION_ID = PRL.SOURCE_ORGANIZATION_ID
AND HOUOUT.ORGANIZATION_ID = PRL.DESTINATION_ORGANIZATION_ID
AND HCSU.SITE_USE_ID = NVL(OOH.SHIP_TO_ORG_ID
, OOL.SHIP_TO_ORG_ID)
AND HCAS.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID
AND CPS.PARTY_SITE_ID = HCAS.PARTY_SITE_ID
AND CPS.SITE_USE_TYPE = 'SHIP_TO'
AND CPS.SITE_STATUS = 'A'
AND CPS.SITE_USE_STATUS = 'A'
AND PTXN.SOURCE_INSTANCE_ID = SRCII.INSTANCE_ID(+)
AND PTXN.NON_SOURCE_INSTANCE_ID = NONSRCII.INSTANCE_ID(+)
AND PTXN.PICKING_RULE_ID = PCK.PICKING_RULE_ID(+)

Columns