VIEW: APPS.CSD_PRODUCT_TXNS_V

Source

View Text - Preformatted

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(+)

View Text - HTML Formatted

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(+)