VIEW: APPS.RLM_SCHEDULE_LINE_CUM_V

Source

View Text - Preformatted

SELECT d.start_date_time cum_start_date, b.CUM_KEY_ID, c.schedule_reference_num, a.item_detail_subtype, a.date_type_code, a.start_date_time, a.end_date_time, a.qty_type_code, a.item_detail_quantity, a.uom_code, a.ship_from_org_id, a.ship_to_org_id, a.customer_item_id, b.ORG_ID from rlm_schedule_lines_all a, rlm_cust_item_cum_keys b, rlm_schedule_headers_all c, rlm_schedule_lines_all d WHERE ( trunc(c.sched_generation_date) >= trunc(b.cum_start_date) or b.cum_start_date is null) and a.customer_item_id = b.customer_item_id and a.ship_from_org_id = nvl(b.ship_from_org_id, a.ship_from_org_id) and a.ship_to_address_id = nvl(b.ship_to_address_id, a.ship_to_address_id) and nvl(a.bill_to_address_id,0) = nvl(b.bill_to_address_id, nvl(a.bill_to_address_id,0)) and nvl(a.intrmd_ship_to_id,0) = nvl(b.intrmd_ship_to_id, nvl(a.intrmd_ship_to_id,0)) and nvl(a.cust_po_number,0) = nvl(b.purchase_order_number, nvl(a.cust_po_number,0)) and nvl(a.industry_attribute1,0) = nvl(b.cust_record_year, nvl(a.industry_attribute1,0)) and c.header_id = a.header_id and a.item_detail_type = '3' and d.header_id = a.header_id and d.schedule_item_num = a.schedule_item_num and d.item_detail_type = '4' and d.item_detail_subtype = 'CUM' and ( trunc(d.start_date_time) = trunc(b.cum_start_date) or b.cum_start_date is null) and nvl(d.cust_po_number,0) = nvl(b.purchase_order_number, nvl(d.cust_po_number,0)) and a.ORG_ID = b.ORG_ID and a.ORG_ID = c.ORG_ID and a.ORG_ID = d.ORG_ID and a. item_detail_quantity = ( SELECT max(a1.item_detail_quantity) FROM rlm_schedule_lines_all a1, rlm_cust_item_cum_keys b1, rlm_schedule_headers_all c1, rlm_schedule_lines_all d1 WHERE ( trunc(c1.sched_generation_date) >= trunc(b1.cum_start_date) or b1.cum_start_date is null) and a1.customer_item_id = b1.customer_item_id and a1.ship_from_org_id = nvl(b1.ship_from_org_id, a1.ship_from_org_id) and a1.ship_to_address_id = nvl(b1.ship_to_address_id, a1.ship_to_address_id) and nvl(a1.bill_to_address_id,0) = nvl(b1.bill_to_address_id, nvl(a1.bill_to_address_id,0)) and nvl(a1.intrmd_ship_to_id,0) = nvl(b1.intrmd_ship_to_id, nvl(a1.intrmd_ship_to_id,0)) and nvl(a1.cust_po_number,0) = nvl(b1.purchase_order_number, nvl(a1.cust_po_number,0)) and nvl(a1.industry_attribute1,0) = nvl(b1.cust_record_year, nvl(a1.industry_attribute1,0)) and c1.header_id = a1.header_id and a1.item_detail_type = '3' and d1.header_id = a1.header_id and d1.schedule_item_num = a1.schedule_item_num and d1.item_detail_type = '4' and d1.item_detail_subtype = 'CUM' and ( trunc(d1.start_date_time) = trunc(b1.cum_start_date) or b1.cum_start_date is null) and nvl(d1.cust_po_number,0) = nvl(b.purchase_order_number, nvl(d1.cust_po_number,0)) and b1.cum_key_id = b.cum_key_id and a1.item_detail_subtype = a.item_detail_subtype and a1.ORG_ID = b1.ORG_ID and a1.ORG_ID = c1.ORG_ID and a1.ORG_ID = d1.ORG_ID )

View Text - HTML Formatted

SELECT D.START_DATE_TIME CUM_START_DATE
, B.CUM_KEY_ID
, C.SCHEDULE_REFERENCE_NUM
, A.ITEM_DETAIL_SUBTYPE
, A.DATE_TYPE_CODE
, A.START_DATE_TIME
, A.END_DATE_TIME
, A.QTY_TYPE_CODE
, A.ITEM_DETAIL_QUANTITY
, A.UOM_CODE
, A.SHIP_FROM_ORG_ID
, A.SHIP_TO_ORG_ID
, A.CUSTOMER_ITEM_ID
, B.ORG_ID
FROM RLM_SCHEDULE_LINES_ALL A
, RLM_CUST_ITEM_CUM_KEYS B
, RLM_SCHEDULE_HEADERS_ALL C
, RLM_SCHEDULE_LINES_ALL D
WHERE ( TRUNC(C.SCHED_GENERATION_DATE) >= TRUNC(B.CUM_START_DATE) OR B.CUM_START_DATE IS NULL)
AND A.CUSTOMER_ITEM_ID = B.CUSTOMER_ITEM_ID
AND A.SHIP_FROM_ORG_ID = NVL(B.SHIP_FROM_ORG_ID
, A.SHIP_FROM_ORG_ID)
AND A.SHIP_TO_ADDRESS_ID = NVL(B.SHIP_TO_ADDRESS_ID
, A.SHIP_TO_ADDRESS_ID)
AND NVL(A.BILL_TO_ADDRESS_ID
, 0) = NVL(B.BILL_TO_ADDRESS_ID
, NVL(A.BILL_TO_ADDRESS_ID
, 0))
AND NVL(A.INTRMD_SHIP_TO_ID
, 0) = NVL(B.INTRMD_SHIP_TO_ID
, NVL(A.INTRMD_SHIP_TO_ID
, 0))
AND NVL(A.CUST_PO_NUMBER
, 0) = NVL(B.PURCHASE_ORDER_NUMBER
, NVL(A.CUST_PO_NUMBER
, 0))
AND NVL(A.INDUSTRY_ATTRIBUTE1
, 0) = NVL(B.CUST_RECORD_YEAR
, NVL(A.INDUSTRY_ATTRIBUTE1
, 0))
AND C.HEADER_ID = A.HEADER_ID
AND A.ITEM_DETAIL_TYPE = '3'
AND D.HEADER_ID = A.HEADER_ID
AND D.SCHEDULE_ITEM_NUM = A.SCHEDULE_ITEM_NUM
AND D.ITEM_DETAIL_TYPE = '4'
AND D.ITEM_DETAIL_SUBTYPE = 'CUM'
AND ( TRUNC(D.START_DATE_TIME) = TRUNC(B.CUM_START_DATE) OR B.CUM_START_DATE IS NULL)
AND NVL(D.CUST_PO_NUMBER
, 0) = NVL(B.PURCHASE_ORDER_NUMBER
, NVL(D.CUST_PO_NUMBER
, 0))
AND A.ORG_ID = B.ORG_ID
AND A.ORG_ID = C.ORG_ID
AND A.ORG_ID = D.ORG_ID
AND A. ITEM_DETAIL_QUANTITY = ( SELECT MAX(A1.ITEM_DETAIL_QUANTITY)
FROM RLM_SCHEDULE_LINES_ALL A1
, RLM_CUST_ITEM_CUM_KEYS B1
, RLM_SCHEDULE_HEADERS_ALL C1
, RLM_SCHEDULE_LINES_ALL D1
WHERE ( TRUNC(C1.SCHED_GENERATION_DATE) >= TRUNC(B1.CUM_START_DATE) OR B1.CUM_START_DATE IS NULL)
AND A1.CUSTOMER_ITEM_ID = B1.CUSTOMER_ITEM_ID
AND A1.SHIP_FROM_ORG_ID = NVL(B1.SHIP_FROM_ORG_ID
, A1.SHIP_FROM_ORG_ID)
AND A1.SHIP_TO_ADDRESS_ID = NVL(B1.SHIP_TO_ADDRESS_ID
, A1.SHIP_TO_ADDRESS_ID)
AND NVL(A1.BILL_TO_ADDRESS_ID
, 0) = NVL(B1.BILL_TO_ADDRESS_ID
, NVL(A1.BILL_TO_ADDRESS_ID
, 0))
AND NVL(A1.INTRMD_SHIP_TO_ID
, 0) = NVL(B1.INTRMD_SHIP_TO_ID
, NVL(A1.INTRMD_SHIP_TO_ID
, 0))
AND NVL(A1.CUST_PO_NUMBER
, 0) = NVL(B1.PURCHASE_ORDER_NUMBER
, NVL(A1.CUST_PO_NUMBER
, 0))
AND NVL(A1.INDUSTRY_ATTRIBUTE1
, 0) = NVL(B1.CUST_RECORD_YEAR
, NVL(A1.INDUSTRY_ATTRIBUTE1
, 0))
AND C1.HEADER_ID = A1.HEADER_ID
AND A1.ITEM_DETAIL_TYPE = '3'
AND D1.HEADER_ID = A1.HEADER_ID
AND D1.SCHEDULE_ITEM_NUM = A1.SCHEDULE_ITEM_NUM
AND D1.ITEM_DETAIL_TYPE = '4'
AND D1.ITEM_DETAIL_SUBTYPE = 'CUM'
AND ( TRUNC(D1.START_DATE_TIME) = TRUNC(B1.CUM_START_DATE) OR B1.CUM_START_DATE IS NULL)
AND NVL(D1.CUST_PO_NUMBER
, 0) = NVL(B.PURCHASE_ORDER_NUMBER
, NVL(D1.CUST_PO_NUMBER
, 0))
AND B1.CUM_KEY_ID = B.CUM_KEY_ID
AND A1.ITEM_DETAIL_SUBTYPE = A.ITEM_DETAIL_SUBTYPE
AND A1.ORG_ID = B1.ORG_ID
AND A1.ORG_ID = C1.ORG_ID
AND A1.ORG_ID = D1.ORG_ID )