[Home] [Help]
728: x_msg_data IN OUT NOCOPY VARCHAR2,
729: x_record_return_status IN OUT NOCOPY BOOLEAN,
730: x_cum_key_id IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_KEY_ID%TYPE,
731: x_cum_start_date IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_START_DATE%TYPE,
732: x_shipped_quantity IN OUT NOCOPY OE_ORDER_LINES.SHIPPED_QUANTITY%TYPE,
733: x_actual_shipment_date IN OUT NOCOPY OE_ORDER_LINES.ACTUAL_SHIPMENT_DATE%TYPE,
734: x_cum_key_created_flag IN OUT NOCOPY BOOLEAN,
735: x_cum_qty IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY%TYPE,
736: x_as_of_date_cum_qty IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY%TYPE,
729: x_record_return_status IN OUT NOCOPY BOOLEAN,
730: x_cum_key_id IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_KEY_ID%TYPE,
731: x_cum_start_date IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_START_DATE%TYPE,
732: x_shipped_quantity IN OUT NOCOPY OE_ORDER_LINES.SHIPPED_QUANTITY%TYPE,
733: x_actual_shipment_date IN OUT NOCOPY OE_ORDER_LINES.ACTUAL_SHIPMENT_DATE%TYPE,
734: x_cum_key_created_flag IN OUT NOCOPY BOOLEAN,
735: x_cum_qty IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY%TYPE,
736: x_as_of_date_cum_qty IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY%TYPE,
737: x_cum_qty_to_be_accumulated IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY_TO_BE_ACCUMULATED%TYPE,
741: x_use_ship_incl_rule_flag IN OUT NOCOPY VARCHAR2,
742: x_shipment_rule_code IN OUT NOCOPY RLM_CUST_SHIPTO_TERMS.CUM_SHIPMENT_RULE_CODE%TYPE,
743: x_yesterday_time_cutoff IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.LAST_UPDATE_DATE%TYPE,
744: x_last_update_date IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.LAST_UPDATE_DATE%TYPE,
745: x_as_of_date_time IN OUT NOCOPY OE_ORDER_LINES.ACTUAL_SHIPMENT_DATE%TYPE)
746: --
747: IS
748: --
749: x_cum_key_record cum_key_attrib_rec_type;
918: END IF;
919: --
920: SELECT NVL((SUM(shipped_quantity)), 0)
921: INTO x_cum_record.as_of_date_cum_qty
922: FROM OE_ORDER_LINES
923: WHERE veh_cus_item_cum_key_id = x_cum_record.cum_key_id
924: AND actual_shipment_date >= x_cum_record.cum_start_date
925: AND actual_shipment_date <= x_cum_record.as_of_date_time
926: AND source_document_type_id = 5
1035: -- TODO: Verify this logic again
1036: --
1037: SELECT MAX(actual_shipment_date)
1038: INTO v_cutoff_date
1039: FROM oe_order_lines
1040: WHERE veh_cus_item_cum_key_id = x_cum_record.cum_key_id
1041: AND source_document_type_id = 5
1042: AND open_flag = 'N'
1043: AND shipped_quantity IS NOT NULL
1176: BEGIN
1177: --
1178: SELECT NVL(SUM(shipped_quantity), 0)
1179: INTO x_cum_record.as_of_date_cum_qty
1180: FROM OE_ORDER_LINES
1181: WHERE veh_cus_item_cum_key_id = x_cum_record.cum_key_id
1182: AND actual_shipment_date >= x_cum_record.cum_start_date
1183: AND actual_shipment_date <= v_cutoff_date
1184: AND source_document_type_id = 5
1304: PROCEDURE CalculateSupplierCumClient (
1305: -- Parameter definition was changed TCA obsolescence project.
1306: x_customer_id IN HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID%TYPE,
1307: x_customer_item_id IN RLM_CUST_ITEM_CUM_KEYS.CUSTOMER_ITEM_ID%TYPE,
1308: x_inventory_item_id IN OE_ORDER_LINES.INVENTORY_ITEM_ID%TYPE,
1309: x_ship_from_org_id IN RLM_CUST_ITEM_CUM_KEYS.SHIP_FROM_ORG_ID%TYPE,
1310: x_intrmd_ship_to_address_id IN RLM_CUST_ITEM_CUM_KEYS.INTRMD_SHIP_TO_ID%TYPE,
1311: x_ship_to_address_id IN RLM_CUST_ITEM_CUM_KEYS.SHIP_TO_ADDRESS_ID%TYPE,
1312: x_bill_to_address_id IN RLM_CUST_ITEM_CUM_KEYS.BILL_TO_ADDRESS_ID%TYPE,
1316: x_msg_data IN OUT NOCOPY VARCHAR2,
1317: x_record_return_status IN OUT NOCOPY BOOLEAN,
1318: x_cum_key_id IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_KEY_ID%TYPE,
1319: x_cum_start_date IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_START_DATE%TYPE,
1320: x_shipped_quantity IN OUT NOCOPY OE_ORDER_LINES.SHIPPED_QUANTITY%TYPE,
1321: x_actual_shipment_date IN OUT NOCOPY OE_ORDER_LINES.ACTUAL_SHIPMENT_DATE%TYPE,
1322: x_cum_key_created_flag IN OUT NOCOPY BOOLEAN,
1323: x_cum_qty IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY%TYPE,
1324: x_as_of_date_cum_qty IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY%TYPE,
1317: x_record_return_status IN OUT NOCOPY BOOLEAN,
1318: x_cum_key_id IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_KEY_ID%TYPE,
1319: x_cum_start_date IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_START_DATE%TYPE,
1320: x_shipped_quantity IN OUT NOCOPY OE_ORDER_LINES.SHIPPED_QUANTITY%TYPE,
1321: x_actual_shipment_date IN OUT NOCOPY OE_ORDER_LINES.ACTUAL_SHIPMENT_DATE%TYPE,
1322: x_cum_key_created_flag IN OUT NOCOPY BOOLEAN,
1323: x_cum_qty IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY%TYPE,
1324: x_as_of_date_cum_qty IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY%TYPE,
1325: x_cum_qty_to_be_accumulated IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY_TO_BE_ACCUMULATED%TYPE,
1329: x_use_ship_incl_rule_flag IN OUT NOCOPY VARCHAR2,
1330: x_shipment_rule_code IN OUT NOCOPY RLM_CUST_SHIPTO_TERMS.CUM_SHIPMENT_RULE_CODE%TYPE,
1331: x_yesterday_time_cutoff IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.LAST_UPDATE_DATE%TYPE,
1332: x_last_update_date IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.LAST_UPDATE_DATE%TYPE,
1333: x_as_of_date_time IN OUT NOCOPY OE_ORDER_LINES.ACTUAL_SHIPMENT_DATE%TYPE)
1334:
1335: IS
1336: --
1337: x_cum_key_record cum_key_attrib_rec_type;
1482: SELECT DISTINCT oelines.header_id, oelines.org_id
1483: FROM WSH_DELIVERY_LEGS wleg,
1484: WSH_DELIVERY_ASSIGNMENTS_V wdass,
1485: WSH_DELIVERY_DETAILS wdel,
1486: OE_ORDER_LINES_ALL oelines
1487: WHERE wleg.pick_up_stop_id = x_trip_stop_id
1488: AND wdass.delivery_id = wleg.delivery_id
1489: AND wdel.delivery_detail_id = wdass.delivery_detail_id
1490: AND oelines.shipped_quantity IS NOT NULL
1492: AND wdel.container_flag = 'N' -- 4301944
1493: AND oelines.header_id = wdel.source_header_id -- 4301944
1494: AND oelines.source_document_type_id = 5;
1495: --
1496: --Cursor to hold oe_order_lines rows of data
1497: --
1498: CURSOR c_oe_lines IS
1499: SELECT oelines.line_id,
1500: wleg.delivery_id,
1518: oelines.org_id
1519: FROM WSH_DELIVERY_LEGS wleg,
1520: WSH_DELIVERY_ASSIGNMENTS_V wdass,
1521: WSH_DELIVERY_DETAILS wdel,
1522: OE_ORDER_LINES_ALL oelines
1523: WHERE wleg.pick_up_stop_id = x_trip_stop_id
1524: AND wdass.delivery_id = wleg.delivery_id
1525: AND wdel.delivery_detail_id = wdass.delivery_detail_id
1526: AND oelines.header_id+0 = v_oe_header_id --Bugfix 14827544
1631: --
1632: ---------------+
1633: OPEN c_oe_lines;
1634: --
1635: LOOP --LOOP to fetch OE Order Lines
1636: --DEBUGGING
1637: --
1638: IF (l_debug <> -1) THEN
1639: rlm_core_sv.dlog(C_DEBUG, 'Inside c_oe_lines Loop');
2026: --
2027: /* Populate g_oe_line_tbl data structure with the
2028: calculated cum_key_id so it can be used when
2029: oe_order_grp.process order api to update
2030: oe_order_lines table */
2031: --
2032: counter := counter + 1;
2033: -- Bug# 1466909
2034: g_oe_line_tbl(counter) := OE_Order_PUB.G_MISS_LINE_REC;
2112: fnd_file.put_line(fnd_file.log, v_msg_text);
2113: --
2114: END IF; -- v_cum_control_code <> 'NO_CUM'
2115: --
2116: END LOOP; -- END LOOP to fetch OE Order Lines
2117: --
2118: CLOSE c_oe_lines;
2119: ----------------+
2120: --
2126: IF v_upd_indicator = TRUE THEN
2127: --
2128: -----------------------------------------------------------------------+
2129: BEGIN
2130: /* Call OE_Order_GRP.Process_Order procedure to update OE_ORDER_LINES
2131: table by passing the g_oe_line_tbl structure that has been prepared
2132: inside the loop above */
2133: --
2134: --Pass only g_oe_line_tbl. The rest uses default values
3179: oelines.invoice_to_org_id
3180: FROM WSH_DELIVERY_LEGS wleg,
3181: WSH_DELIVERY_ASSIGNMENTS_V wdass,
3182: WSH_DELIVERY_DETAILS wdel,
3183: OE_ORDER_LINES oelines
3184: WHERE wleg.pick_up_stop_id = x_trip_stop_id
3185: AND wdass.delivery_id = wleg.delivery_id
3186: AND wdel.delivery_detail_id = wdass.delivery_detail_id
3187: AND oelines.shipped_quantity IS NOT NULL
4057: rlm_core_sv.dlog(C_DEBUG, 'l_file_val',l_file_val);
4058: END IF;
4059: --
4060: LOOP --{
4061: /* Call OE_Order_GRP.Process_Order procedure to update OE_ORDER_LINES
4062: table by passing the g_oe_line_tbl structure that has been prepared
4063: this loop calls the process_order API once per each header_id,
4064: since the table is sorted by header_id then this loop
4065: calls the process_order once the header_id is changed */
5245: ELSE --{
5246: --
5247: SELECT COUNT(*)
5248: INTO v_shipment_count
5249: FROM oe_order_lines
5250: WHERE (ship_from_org_id = x_ship_from_org_id
5251: OR x_ship_from_org_id IS NULL)
5252: AND (ship_to_org_id = x_ship_to_org_id
5253: OR x_ship_to_org_id IS NULL)
5809: shipped_quantity,
5810: actual_shipment_date,
5811: order_quantity_uom,
5812: org_id
5813: FROM oe_order_lines_all
5814: WHERE (ship_from_org_id = x_ship_from_org_id
5815: OR x_ship_from_org_id IS NULL)
5816: AND (ship_to_org_id = x_ship_to_org_id
5817: OR x_ship_to_org_id IS NULL)
6119: rlm_core_sv.dlog(C_DEBUG, 'l_file_val',l_file_val);
6120: END IF;
6121: --
6122: LOOP --{
6123: /* Call OE_Order_GRP.Process_Order procedure to update OE_ORDER_LINES
6124: table by passing the g_oe_line_tbl structure that has been prepared
6125: this loop calls the process_order API once per each header_id,
6126: since the table is sorted by header_id then this loop
6127: calls the process_order once the header_id is changed */