VIEW: APPS.CSD_PRODUCT_TXNS_IRO_V

Source

View Text - Preformatted

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, */ ottt.name 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_headers_all ooh, /*oe_order_lines_v ool , */ oe_order_lines_all ool , oe_transaction_types_tl ottt, 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(+) AND ooh.order_type_id = ottt.transaction_type_id AND ottt.language = userenv('LANG')

View Text - HTML Formatted

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
, */ OTTT.NAME 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_HEADERS_ALL OOH
, /*OE_ORDER_LINES_V OOL
, */ OE_ORDER_LINES_ALL OOL
, OE_TRANSACTION_TYPES_TL OTTT
, 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(+)
AND OOH.ORDER_TYPE_ID = OTTT.TRANSACTION_TYPE_ID
AND OTTT.LANGUAGE = USERENV('LANG')