View: CSD_PRODUCT_TXNS_V

Product:	CSD - Depot Repair
Description:	This View is used for the Receive/Ship tab in the Repair Orders Workbench. This will show all the Charge Lines and related information created for the repair order
Implementation/DBA Data:	APPS.CSD_PRODUCT_TXNS_V

View Text

SELECT DPT.ROWID ROW_ID
, DPT.PRODUCT_TRANSACTION_ID PRODUCT_TRANSACTION_ID
, DPT.REPAIR_LINE_ID REPAIR_LINE_ID
, DRA.REPAIR_NUMBER REPAIR_NUMBER
, EDT.ESTIMATE_DETAIL_ID ESTIMATE_DETAIL_ID
, DPT.ACTION_TYPE ACTION_TYPE
, DPT.ACTION_CODE ACTION_CODE
, DPT.INTERFACE_TO_OM_FLAG INTERFACE_TO_OM_FLAG
, DPT.BOOK_SALES_ORDER_FLAG BOOK_SALES_ORDER_FLAG
, DPT.RELEASE_SALES_ORDER_FLAG RELEASE_SALES_ORDER_FLAG
, DPT.SHIP_SALES_ORDER_FLAG SHIP_SALES_ORDER_FLAG
, DRA.AUTO_PROCESS_RMA AUTO_PROCESS_RMA
, DRA.REPAIR_MODE REPAIR_MODE
, DPT.PROD_TXN_STATUS PROD_TXN_STATUS
, DPT.SUB_INVENTORY SUB_INVENTORY
, DRA.REPAIR_TYPE_ID REPAIR_TYPE_ID
, DPT.SOURCE_INSTANCE_ID SOURCE_INSTANCE_ID
, DPT.NON_SOURCE_INSTANCE_ID NON_SOURCE_INSTANCE_ID
, EDT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, ITEMS.CONCATENATED_SEGMENTS PRODUCT
, ITEMS.DESCRIPTION PRODUCT_DESCRIPTION
, DPT.CREATED_BY CREATED_BY
, DPT.CREATION_DATE CREATION_DATE
, DPT.LAST_UPDATED_BY LAST_UPDATED_BY
, DPT.LAST_UPDATE_DATE LAST_UPDATE_DATE
, DPT.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, DPT.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, EDT.UNIT_OF_MEASURE_CODE UNIT_OF_MEASURE
, UOM.UNIT_OF_MEASURE_TL UNIT_OF_MEASURE_TL
, EDT.QUANTITY_REQUIRED ESTIMATE_QUANTITY
, DRA.QUANTITY REPAIR_QUANTITY
, NVL(DRA.QUANTITY_IN_WIP
, 0) QUANTITY_IN_WIP
, NVL(DRA.QUANTITY_RCVD
, 0) QUANTITY_RCVD
, NVL(DRA.QUANTITY_SHIPPED
, 0) QUANTITY_SHIPPED
, DPT.LOT_NUMBER LOT_NUMBER
, EDT.ITEM_REVISION REVISION
, DRA.APPROVAL_REQUIRED_FLAG APPROVAL_REQUIRED_FLAG
, DRA.APPROVAL_STATUS APPROVAL_STATUS
, DPT.PROD_TXN_STATUS TRANSACTION_STATUS
, DRA.STATUS STATUS
, DRA.DATE_CLOSED DATE_CLOSED
, DRA.PROMISE_DATE PROMISE_DATE
, DPT.CONTEXT CONTEXT
, DPT.ATTRIBUTE1 ATTRIBUTE1
, DPT.ATTRIBUTE2 ATTRIBUTE2
, DPT.ATTRIBUTE3 ATTRIBUTE3
, DPT.ATTRIBUTE4 ATTRIBUTE4
, DPT.ATTRIBUTE5 ATTRIBUTE5
, DPT.ATTRIBUTE6 ATTRIBUTE6
, DPT.ATTRIBUTE7 ATTRIBUTE7
, DPT.ATTRIBUTE8 ATTRIBUTE8
, DPT.ATTRIBUTE9 ATTRIBUTE9
, DPT.ATTRIBUTE10 ATTRIBUTE10
, DPT.ATTRIBUTE11 ATTRIBUTE11
, DPT.ATTRIBUTE12 ATTRIBUTE12
, DPT.ATTRIBUTE13 ATTRIBUTE13
, DPT.ATTRIBUTE14 ATTRIBUTE14
, DPT.ATTRIBUTE15 ATTRIBUTE15
, EDT.PRICE_LIST_HEADER_ID PRICE_LIST_HEADER_ID
, QP.NAME PRICE_LIST
, STT.NAME TRANSACTION_TYPE
, EDT.TXN_BILLING_TYPE_ID TXN_BILLING_TYPE_ID
, EDT.ORDER_HEADER_ID ORDER_HEADER_ID
, EDT.ORDER_LINE_ID ORDER_LINE_ID
, OEH.ORDER_NUMBER ORDER_NUMBER
, EDT.RETURN_REASON_CODE RETURN_REASON_CODE
, DECODE(EDT.LINE_CATEGORY_CODE
, 'RETURN'
, EDT.INSTALLED_CP_RETURN_BY_DATE
, NVL(EDT.NEW_CP_RETURN_BY_DATE
, EDT.INSTALLED_CP_RETURN_BY_DATE) ) RETURN_BY_DATE
, OKH.CONTRACT_NUMBER CONTRACT_NUMBER
, EDT.CONTRACT_LINE_ID CONTRACT_LINE_ID
, EDT.INVOICE_TO_ORG_ID INVOICE_TO_ORG_ID
, SUBSTR(HL1.ADDRESS1||' '||HL1.ADDRESS2||' '||HL1.ADDRESS3||' '||HL1.ADDRESS4 ||' '|| HL1.CITY||' '||HL1.STATE||' '||HL1.COUNTRY||' '||HL1.POSTAL_CODE
, 1
, 440) BILL_TO_ADDRESS
, EDT.SHIP_TO_ORG_ID SHIP_TO_ORG_ID
, SUBSTR(HL2.ADDRESS1||' '||HL2.ADDRESS2||' '||HL2.ADDRESS3||' '||HL2.ADDRESS4 ||' '||HL2.CITY||' '||HL2.STATE||' '||HL2.COUNTRY||' '||HL2.POSTAL_CODE
, 1
, 440) SHIP_TO_ADDRESS
, EDT.SELLING_PRICE SELLING_PRICE
, EDT.NO_CHARGE_FLAG NO_CHARGE_FLAG
, EDT.BUSINESS_PROCESS_ID BUSINESS_PROCESS_ID
, ITEMS.LOT_CONTROL_CODE LOT_CONTROL_CODE
, ITEMS.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE
, DECODE(EDT.ADD_TO_ORDER_FLAG
, 'Y'
, 'N'
, 'Y') NEW_ORDER_FLAG
, EDT.AFTER_WARRANTY_COST CHARGE
, DECODE (DPT.ACTION_TYPE
, 'RMA_THIRD_PTY'
, POH.SEGMENT1
, 'SHIP_THIRD_PTY'
, POH.SEGMENT1
, EDT.PURCHASE_ORDER_NUM) PO_NUMBER
, DPT.SOURCE_SERIAL_NUMBER SOURCE_SERIAL_NUMBER
, DPT.NON_SOURCE_SERIAL_NUMBER NON_SOURCE_SERIAL_NUMBER
, SRCP.INSTANCE_NUMBER SOURCE_INSTANCE_NUMBER
, NONSRCP.INSTANCE_NUMBER NON_SOURCE_INSTANCE_NUMBER
, DPT.REQ_HEADER_ID REQ_HEADER_ID
, DPT.REQ_LINE_ID REQ_LINE_ID
, DPT.QUANTITY_RECEIVED PRD_TXN_QUANTITY_RECEIVED
, DPT.QUANTITY_SHIPPED PRD_TXN_QUANTITY_SHIPPED
, DPT.LOCATOR_ID LOCATOR_ID
, DPT.LOT_NUMBER_RCVD LOT_NUMBER_RCVD
, DPT.SUB_INVENTORY_RCVD SUB_INVENTORY_RCVD
, DPT.SOURCE_SERIAL_NUMBER SERIAL_NUMBER
, SRCP.INSTANCE_NUMBER IB_REF_NUMBER
, OEL.LINE_NUMBER ORDER_LINE_NUMBER
, DPT.PICKING_RULE_ID
, PCK.NAME PICKING_RULE_NAME
, EDT.CONTRACT_ID CONTRACT_ID
, EDT.TRANSACTION_INVENTORY_ORG INVENTORY_ORG_ID
, MTP.ORGANIZATION_CODE INVENTORY_ORG_CODE
, OEL.SHIP_FROM_ORG_ID ORDER_INVENTORY_ORG_ID
, DPT.PROJECT_ID PROJECT_ID
, DPT.TASK_ID TASK_ID
, DPT.UNIT_NUMBER UNIT_NUMBER
, DECODE(HCA.ACCOUNT_NUMBER
, NULL
, ''
, (HCA.ACCOUNT_NUMBER || ' - ' || HZ.PARTY_NAME)) BILL_TO_ACCOUNT_INFO
, EDT.INVOICE_TO_ACCOUNT_ID BILL_TO_ACCOUNT_ID
, EDT.BILL_TO_PARTY_ID
, EDT.SHIP_TO_ACCOUNT_ID
, EDT.SHIP_TO_PARTY_ID
, DPT.INTERNAL_PO_HEADER_ID
FROM CSD_PRODUCT_TRANSACTIONS DPT
, CS_ESTIMATE_DETAILS EDT
, CSD_REPAIRS DRA
, CSI_ITEM_INSTANCES SRCP
, CSI_ITEM_INSTANCES NONSRCP
, MTL_SYSTEM_ITEMS_VL ITEMS
, MTL_UNITS_OF_MEASURE_VL UOM
, OE_ORDER_HEADERS_ALL OEH
, OE_ORDER_LINES_ALL OEL
, QP_LIST_HEADERS_TL QP
, OKC_K_HEADERS_B OKH
, /* OKC_K_LINES_B OKL
, */ HZ_PARTY_SITES HP1
, HZ_PARTY_SITES HP2
, HZ_LOCATIONS HL1
, HZ_LOCATIONS HL2
, CS_TRANSACTION_TYPES_VL STT
, CS_TXN_BILLING_TYPES SBT
, WSH_PICKING_RULES PCK
, MTL_PARAMETERS MTP
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HZ
, PO_HEADERS_ALL POH
WHERE DPT.ESTIMATE_DETAIL_ID = EDT.ESTIMATE_DETAIL_ID
AND EDT.SOURCE_CODE = 'DR'
AND DPT.REPAIR_LINE_ID = DRA.REPAIR_LINE_ID
AND DPT.SOURCE_INSTANCE_ID =SRCP.INSTANCE_ID(+)
AND DPT.NON_SOURCE_INSTANCE_ID = NONSRCP.INSTANCE_ID(+)
AND EDT.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND EDT.UNIT_OF_MEASURE_CODE = UOM.UOM_CODE
AND EDT.PRICE_LIST_HEADER_ID = QP.LIST_HEADER_ID(+)
AND QP.LANGUAGE(+) = USERENV('LANG')
AND EDT.CONTRACT_ID = OKH.ID(+)
AND OKH.START_DATE(+) IS NOT NULL
AND OKH.END_DATE(+) IS NOT NULL
AND SBT.TXN_BILLING_TYPE_ID = EDT.TXN_BILLING_TYPE_ID
AND SBT.TRANSACTION_TYPE_ID = STT.TRANSACTION_TYPE_ID
AND EDT.INVOICE_TO_ORG_ID = HP1.PARTY_SITE_ID(+)
AND EDT.SHIP_TO_ORG_ID = HP2.PARTY_SITE_ID(+)
AND HP1.LOCATION_ID = HL1.LOCATION_ID(+)
AND HP2.LOCATION_ID = HL2.LOCATION_ID(+)
AND EDT.ORDER_HEADER_ID = OEH.HEADER_ID(+)
AND EDT.ORDER_LINE_ID = OEL.LINE_ID(+)
AND DPT.PICKING_RULE_ID = PCK.PICKING_RULE_ID(+)
AND MTP.ORGANIZATION_ID(+) = EDT.TRANSACTION_INVENTORY_ORG
AND EDT.BILL_TO_PARTY_ID = HZ.PARTY_ID (+)
AND EDT.INVOICE_TO_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID(+)
AND EDT.BILL_TO_PARTY_ID = HCA.PARTY_ID(+)
AND DPT.INTERNAL_PO_HEADER_ID = POH.PO_HEADER_ID(+)

Columns