VIEW: APPS.RLM_CUST_ITEM_CUM_DETAILS_V

Source

View Text - Preformatted

SELECT 'Adjustment' txn_type, adj.cum_key_id , null ship_from, null ship_to, null deliver_to , null bill_to , adj.transaction_date_time txn_date, adj.transaction_qty txn_quantity, ckeys.cust_uom_code quantity_uom, to_number(null) shipment_number, fuser.user_name Last_adjusted_by, null last_shipped_by, adj.adjustment_reason, adj.adjustment_reference, adj.LAST_UPDATE_DATE, adj.LAST_UPDATED_BY, adj.CREATION_DATE, adj.CREATED_BY, adj.ATTRIBUTE_CATEGORY, adj.ATTRIBUTE1, adj.ATTRIBUTE2, adj.ATTRIBUTE3, adj.ATTRIBUTE4, adj.ATTRIBUTE5, adj.ATTRIBUTE6, adj.ATTRIBUTE7, adj.ATTRIBUTE8, adj.ATTRIBUTE9, adj.ATTRIBUTE10, adj.ATTRIBUTE11, adj.ATTRIBUTE12, adj.ATTRIBUTE13, adj.ATTRIBUTE14, adj.ATTRIBUTE15, adj.TP_ATTRIBUTE_CATEGORY, adj.TP_ATTRIBUTE1, adj.TP_ATTRIBUTE2, adj.TP_ATTRIBUTE3, adj.TP_ATTRIBUTE4, adj.TP_ATTRIBUTE5, adj.TP_ATTRIBUTE6, adj.TP_ATTRIBUTE7, adj.TP_ATTRIBUTE8, adj.TP_ATTRIBUTE9, adj.TP_ATTRIBUTE10, adj.TP_ATTRIBUTE11, adj.TP_ATTRIBUTE12, adj.TP_ATTRIBUTE13, adj.TP_ATTRIBUTE14, adj.TP_ATTRIBUTE15, adj.LAST_UPDATE_LOGIN, adj.REQUEST_ID, adj.PROGRAM_APPLICATION_ID, adj.PROGRAM_ID, adj.PROGRAM_UPDATE_DATE, adj.ORG_ID FROM org_organization_definitions org, rlm_cust_item_cum_keys ckeys, rlm_cust_item_cum_adj_all adj, fnd_user fuser WHERE org.organization_id = ckeys.ship_from_org_id AND ckeys.CUM_KEY_ID = adj.CUM_KEY_ID AND fuser.user_id = adj.last_updated_by AND adj.ORG_ID = ckeys.ORG_ID UNION SELECT DISTINCT 'Shipment' txn_type, lines.VEH_CUS_ITEM_CUM_KEY_ID , org.organization_code ship_from, sto.location ship_to, delto.location deliver_to , billto.location bill_to , lines.ACTUAL_SHIPMENT_DATE txn_date, lines.SHIPPED_QUANTITY txn_quantity, lines.SHIPPING_QUANTITY_UOM quantity_uom, lines.SHIPMENT_NUMBER, null Last_adjusted_by, fuser.user_name Last_shipped_by, null adjustment_reason, null adjustment_reference, lines.LAST_UPDATE_DATE, lines.LAST_UPDATED_BY, lines.CREATION_DATE, lines.CREATED_BY, null ATTRIBUTE_CATEGORY, lines.ATTRIBUTE1, lines.ATTRIBUTE2, lines.ATTRIBUTE3, lines.ATTRIBUTE4, lines.ATTRIBUTE5, lines.ATTRIBUTE6, lines.ATTRIBUTE7, lines.ATTRIBUTE8, lines.ATTRIBUTE9, lines.ATTRIBUTE10, lines.ATTRIBUTE11, lines.ATTRIBUTE12, lines.ATTRIBUTE13, lines.ATTRIBUTE14, lines.ATTRIBUTE15, null TP_ATTRIBUTE_CATEGORY, null TP_ATTRIBUTE1, null TP_ATTRIBUTE2, null TP_ATTRIBUTE3, null TP_ATTRIBUTE4, null TP_ATTRIBUTE5, null TP_ATTRIBUTE6, null TP_ATTRIBUTE7, null TP_ATTRIBUTE8, null TP_ATTRIBUTE9, null TP_ATTRIBUTE10, null TP_ATTRIBUTE11, null TP_ATTRIBUTE12, null TP_ATTRIBUTE13, null TP_ATTRIBUTE14, null TP_ATTRIBUTE15, lines.LAST_UPDATE_LOGIN, lines.REQUEST_ID, lines.PROGRAM_APPLICATION_ID, lines.PROGRAM_ID, lines.PROGRAM_UPDATE_DATE, lines.ORG_ID FROM HZ_CUST_SITE_USES_ALL sto, HZ_CUST_SITE_USES_ALL delto, HZ_CUST_SITE_USES_ALL billto, org_organization_definitions org, rlm_cust_item_cum_keys ckeys, fnd_user fuser, oe_order_lines_all lines WHERE org.organization_id = lines.ship_from_org_id AND org.organization_id = ckeys.ship_from_org_id AND ckeys.CUM_KEY_ID = lines.VEH_CUS_ITEM_CUM_KEY_ID AND sto.site_use_id = lines.ship_to_org_id AND delto.site_use_id(+) = lines.deliver_to_org_id AND billto.site_use_id(+) = lines.invoice_to_org_id AND fuser.user_id = lines.last_updated_by AND sto.ORG_ID = ckeys.ORG_ID AND delto.ORG_ID(+) = lines.ORG_ID AND billto.ORG_ID(+) = lines.ORG_ID

View Text - HTML Formatted

SELECT 'ADJUSTMENT' TXN_TYPE
, ADJ.CUM_KEY_ID
, NULL SHIP_FROM
, NULL SHIP_TO
, NULL DELIVER_TO
, NULL BILL_TO
, ADJ.TRANSACTION_DATE_TIME TXN_DATE
, ADJ.TRANSACTION_QTY TXN_QUANTITY
, CKEYS.CUST_UOM_CODE QUANTITY_UOM
, TO_NUMBER(NULL) SHIPMENT_NUMBER
, FUSER.USER_NAME LAST_ADJUSTED_BY
, NULL LAST_SHIPPED_BY
, ADJ.ADJUSTMENT_REASON
, ADJ.ADJUSTMENT_REFERENCE
, ADJ.LAST_UPDATE_DATE
, ADJ.LAST_UPDATED_BY
, ADJ.CREATION_DATE
, ADJ.CREATED_BY
, ADJ.ATTRIBUTE_CATEGORY
, ADJ.ATTRIBUTE1
, ADJ.ATTRIBUTE2
, ADJ.ATTRIBUTE3
, ADJ.ATTRIBUTE4
, ADJ.ATTRIBUTE5
, ADJ.ATTRIBUTE6
, ADJ.ATTRIBUTE7
, ADJ.ATTRIBUTE8
, ADJ.ATTRIBUTE9
, ADJ.ATTRIBUTE10
, ADJ.ATTRIBUTE11
, ADJ.ATTRIBUTE12
, ADJ.ATTRIBUTE13
, ADJ.ATTRIBUTE14
, ADJ.ATTRIBUTE15
, ADJ.TP_ATTRIBUTE_CATEGORY
, ADJ.TP_ATTRIBUTE1
, ADJ.TP_ATTRIBUTE2
, ADJ.TP_ATTRIBUTE3
, ADJ.TP_ATTRIBUTE4
, ADJ.TP_ATTRIBUTE5
, ADJ.TP_ATTRIBUTE6
, ADJ.TP_ATTRIBUTE7
, ADJ.TP_ATTRIBUTE8
, ADJ.TP_ATTRIBUTE9
, ADJ.TP_ATTRIBUTE10
, ADJ.TP_ATTRIBUTE11
, ADJ.TP_ATTRIBUTE12
, ADJ.TP_ATTRIBUTE13
, ADJ.TP_ATTRIBUTE14
, ADJ.TP_ATTRIBUTE15
, ADJ.LAST_UPDATE_LOGIN
, ADJ.REQUEST_ID
, ADJ.PROGRAM_APPLICATION_ID
, ADJ.PROGRAM_ID
, ADJ.PROGRAM_UPDATE_DATE
, ADJ.ORG_ID
FROM ORG_ORGANIZATION_DEFINITIONS ORG
, RLM_CUST_ITEM_CUM_KEYS CKEYS
, RLM_CUST_ITEM_CUM_ADJ_ALL ADJ
, FND_USER FUSER
WHERE ORG.ORGANIZATION_ID = CKEYS.SHIP_FROM_ORG_ID
AND CKEYS.CUM_KEY_ID = ADJ.CUM_KEY_ID
AND FUSER.USER_ID = ADJ.LAST_UPDATED_BY
AND ADJ.ORG_ID = CKEYS.ORG_ID UNION SELECT DISTINCT 'SHIPMENT' TXN_TYPE
, LINES.VEH_CUS_ITEM_CUM_KEY_ID
, ORG.ORGANIZATION_CODE SHIP_FROM
, STO.LOCATION SHIP_TO
, DELTO.LOCATION DELIVER_TO
, BILLTO.LOCATION BILL_TO
, LINES.ACTUAL_SHIPMENT_DATE TXN_DATE
, LINES.SHIPPED_QUANTITY TXN_QUANTITY
, LINES.SHIPPING_QUANTITY_UOM QUANTITY_UOM
, LINES.SHIPMENT_NUMBER
, NULL LAST_ADJUSTED_BY
, FUSER.USER_NAME LAST_SHIPPED_BY
, NULL ADJUSTMENT_REASON
, NULL ADJUSTMENT_REFERENCE
, LINES.LAST_UPDATE_DATE
, LINES.LAST_UPDATED_BY
, LINES.CREATION_DATE
, LINES.CREATED_BY
, NULL ATTRIBUTE_CATEGORY
, LINES.ATTRIBUTE1
, LINES.ATTRIBUTE2
, LINES.ATTRIBUTE3
, LINES.ATTRIBUTE4
, LINES.ATTRIBUTE5
, LINES.ATTRIBUTE6
, LINES.ATTRIBUTE7
, LINES.ATTRIBUTE8
, LINES.ATTRIBUTE9
, LINES.ATTRIBUTE10
, LINES.ATTRIBUTE11
, LINES.ATTRIBUTE12
, LINES.ATTRIBUTE13
, LINES.ATTRIBUTE14
, LINES.ATTRIBUTE15
, NULL TP_ATTRIBUTE_CATEGORY
, NULL TP_ATTRIBUTE1
, NULL TP_ATTRIBUTE2
, NULL TP_ATTRIBUTE3
, NULL TP_ATTRIBUTE4
, NULL TP_ATTRIBUTE5
, NULL TP_ATTRIBUTE6
, NULL TP_ATTRIBUTE7
, NULL TP_ATTRIBUTE8
, NULL TP_ATTRIBUTE9
, NULL TP_ATTRIBUTE10
, NULL TP_ATTRIBUTE11
, NULL TP_ATTRIBUTE12
, NULL TP_ATTRIBUTE13
, NULL TP_ATTRIBUTE14
, NULL TP_ATTRIBUTE15
, LINES.LAST_UPDATE_LOGIN
, LINES.REQUEST_ID
, LINES.PROGRAM_APPLICATION_ID
, LINES.PROGRAM_ID
, LINES.PROGRAM_UPDATE_DATE
, LINES.ORG_ID
FROM HZ_CUST_SITE_USES_ALL STO
, HZ_CUST_SITE_USES_ALL DELTO
, HZ_CUST_SITE_USES_ALL BILLTO
, ORG_ORGANIZATION_DEFINITIONS ORG
, RLM_CUST_ITEM_CUM_KEYS CKEYS
, FND_USER FUSER
, OE_ORDER_LINES_ALL LINES
WHERE ORG.ORGANIZATION_ID = LINES.SHIP_FROM_ORG_ID
AND ORG.ORGANIZATION_ID = CKEYS.SHIP_FROM_ORG_ID
AND CKEYS.CUM_KEY_ID = LINES.VEH_CUS_ITEM_CUM_KEY_ID
AND STO.SITE_USE_ID = LINES.SHIP_TO_ORG_ID
AND DELTO.SITE_USE_ID(+) = LINES.DELIVER_TO_ORG_ID
AND BILLTO.SITE_USE_ID(+) = LINES.INVOICE_TO_ORG_ID
AND FUSER.USER_ID = LINES.LAST_UPDATED_BY
AND STO.ORG_ID = CKEYS.ORG_ID
AND DELTO.ORG_ID(+) = LINES.ORG_ID
AND BILLTO.ORG_ID(+) = LINES.ORG_ID