[Home] [Help]
727: x_msg_data IN OUT NOCOPY VARCHAR2,
728: x_record_return_status IN OUT NOCOPY BOOLEAN,
729: x_cum_key_id IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_KEY_ID%TYPE,
730: x_cum_start_date IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_START_DATE%TYPE,
731: x_shipped_quantity IN OUT NOCOPY OE_ORDER_LINES.SHIPPED_QUANTITY%TYPE,
732: x_actual_shipment_date IN OUT NOCOPY OE_ORDER_LINES.ACTUAL_SHIPMENT_DATE%TYPE,
733: x_cum_key_created_flag IN OUT NOCOPY BOOLEAN,
734: x_cum_qty IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY%TYPE,
735: x_as_of_date_cum_qty IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY%TYPE,
728: x_record_return_status IN OUT NOCOPY BOOLEAN,
729: x_cum_key_id IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_KEY_ID%TYPE,
730: x_cum_start_date IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_START_DATE%TYPE,
731: x_shipped_quantity IN OUT NOCOPY OE_ORDER_LINES.SHIPPED_QUANTITY%TYPE,
732: x_actual_shipment_date IN OUT NOCOPY OE_ORDER_LINES.ACTUAL_SHIPMENT_DATE%TYPE,
733: x_cum_key_created_flag IN OUT NOCOPY BOOLEAN,
734: x_cum_qty IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY%TYPE,
735: x_as_of_date_cum_qty IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY%TYPE,
736: x_cum_qty_to_be_accumulated IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY_TO_BE_ACCUMULATED%TYPE,
740: x_use_ship_incl_rule_flag IN OUT NOCOPY VARCHAR2,
741: x_shipment_rule_code IN OUT NOCOPY RLM_CUST_SHIPTO_TERMS.CUM_SHIPMENT_RULE_CODE%TYPE,
742: x_yesterday_time_cutoff IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.LAST_UPDATE_DATE%TYPE,
743: x_last_update_date IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.LAST_UPDATE_DATE%TYPE,
744: x_as_of_date_time IN OUT NOCOPY OE_ORDER_LINES.ACTUAL_SHIPMENT_DATE%TYPE)
745: --
746: IS
747: --
748: x_cum_key_record cum_key_attrib_rec_type;
914: END IF;
915: --
916: SELECT NVL((SUM(shipped_quantity)), 0)
917: INTO x_cum_record.as_of_date_cum_qty
918: FROM OE_ORDER_LINES
919: WHERE veh_cus_item_cum_key_id = x_cum_record.cum_key_id
920: AND actual_shipment_date >= x_cum_record.cum_start_date
921: AND actual_shipment_date <= x_cum_record.as_of_date_time
922: AND source_document_type_id = 5
1031: -- TODO: Verify this logic again
1032: --
1033: SELECT MAX(actual_shipment_date)
1034: INTO v_cutoff_date
1035: FROM oe_order_lines
1036: WHERE veh_cus_item_cum_key_id = x_cum_record.cum_key_id
1037: AND source_document_type_id = 5
1038: AND open_flag = 'N'
1039: AND shipped_quantity IS NOT NULL
1167: BEGIN
1168: --
1169: SELECT NVL(SUM(shipped_quantity), 0)
1170: INTO x_cum_record.as_of_date_cum_qty
1171: FROM OE_ORDER_LINES
1172: WHERE veh_cus_item_cum_key_id = x_cum_record.cum_key_id
1173: AND actual_shipment_date >= x_cum_record.cum_start_date
1174: AND actual_shipment_date <= v_cutoff_date
1175: AND source_document_type_id = 5
1295: PROCEDURE CalculateSupplierCumClient (
1296: -- Parameter definition was changed TCA obsolescence project.
1297: x_customer_id IN HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID%TYPE,
1298: x_customer_item_id IN RLM_CUST_ITEM_CUM_KEYS.CUSTOMER_ITEM_ID%TYPE,
1299: x_inventory_item_id IN OE_ORDER_LINES.INVENTORY_ITEM_ID%TYPE,
1300: x_ship_from_org_id IN RLM_CUST_ITEM_CUM_KEYS.SHIP_FROM_ORG_ID%TYPE,
1301: x_intrmd_ship_to_address_id IN RLM_CUST_ITEM_CUM_KEYS.INTRMD_SHIP_TO_ID%TYPE,
1302: x_ship_to_address_id IN RLM_CUST_ITEM_CUM_KEYS.SHIP_TO_ADDRESS_ID%TYPE,
1303: x_bill_to_address_id IN RLM_CUST_ITEM_CUM_KEYS.BILL_TO_ADDRESS_ID%TYPE,
1307: x_msg_data IN OUT NOCOPY VARCHAR2,
1308: x_record_return_status IN OUT NOCOPY BOOLEAN,
1309: x_cum_key_id IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_KEY_ID%TYPE,
1310: x_cum_start_date IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_START_DATE%TYPE,
1311: x_shipped_quantity IN OUT NOCOPY OE_ORDER_LINES.SHIPPED_QUANTITY%TYPE,
1312: x_actual_shipment_date IN OUT NOCOPY OE_ORDER_LINES.ACTUAL_SHIPMENT_DATE%TYPE,
1313: x_cum_key_created_flag IN OUT NOCOPY BOOLEAN,
1314: x_cum_qty IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY%TYPE,
1315: x_as_of_date_cum_qty IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY%TYPE,
1308: x_record_return_status IN OUT NOCOPY BOOLEAN,
1309: x_cum_key_id IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_KEY_ID%TYPE,
1310: x_cum_start_date IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_START_DATE%TYPE,
1311: x_shipped_quantity IN OUT NOCOPY OE_ORDER_LINES.SHIPPED_QUANTITY%TYPE,
1312: x_actual_shipment_date IN OUT NOCOPY OE_ORDER_LINES.ACTUAL_SHIPMENT_DATE%TYPE,
1313: x_cum_key_created_flag IN OUT NOCOPY BOOLEAN,
1314: x_cum_qty IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY%TYPE,
1315: x_as_of_date_cum_qty IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY%TYPE,
1316: x_cum_qty_to_be_accumulated IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.CUM_QTY_TO_BE_ACCUMULATED%TYPE,
1320: x_use_ship_incl_rule_flag IN OUT NOCOPY VARCHAR2,
1321: x_shipment_rule_code IN OUT NOCOPY RLM_CUST_SHIPTO_TERMS.CUM_SHIPMENT_RULE_CODE%TYPE,
1322: x_yesterday_time_cutoff IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.LAST_UPDATE_DATE%TYPE,
1323: x_last_update_date IN OUT NOCOPY RLM_CUST_ITEM_CUM_KEYS.LAST_UPDATE_DATE%TYPE,
1324: x_as_of_date_time IN OUT NOCOPY OE_ORDER_LINES.ACTUAL_SHIPMENT_DATE%TYPE)
1325:
1326: IS
1327: --
1328: x_cum_key_record cum_key_attrib_rec_type;
1473: SELECT DISTINCT oelines.header_id, oelines.org_id
1474: FROM WSH_DELIVERY_LEGS wleg,
1475: WSH_DELIVERY_ASSIGNMENTS_V wdass,
1476: WSH_DELIVERY_DETAILS wdel,
1477: OE_ORDER_LINES_ALL oelines
1478: WHERE wleg.pick_up_stop_id = x_trip_stop_id
1479: AND wdass.delivery_id = wleg.delivery_id
1480: AND wdel.delivery_detail_id = wdass.delivery_detail_id
1481: AND oelines.shipped_quantity IS NOT NULL
1483: AND wdel.container_flag = 'N' -- 4301944
1484: AND oelines.header_id = wdel.source_header_id -- 4301944
1485: AND oelines.source_document_type_id = 5;
1486: --
1487: --Cursor to hold oe_order_lines rows of data
1488: --
1489: CURSOR c_oe_lines IS
1490: SELECT oelines.line_id,
1491: wleg.delivery_id,
1509: oelines.org_id
1510: FROM WSH_DELIVERY_LEGS wleg,
1511: WSH_DELIVERY_ASSIGNMENTS_V wdass,
1512: WSH_DELIVERY_DETAILS wdel,
1513: OE_ORDER_LINES_ALL oelines
1514: WHERE wleg.pick_up_stop_id = x_trip_stop_id
1515: AND wdass.delivery_id = wleg.delivery_id
1516: AND wdel.delivery_detail_id = wdass.delivery_detail_id
1517: AND oelines.header_id = v_oe_header_id
1622: --
1623: ---------------+
1624: OPEN c_oe_lines;
1625: --
1626: LOOP --LOOP to fetch OE Order Lines
1627: --DEBUGGING
1628: --
1629: IF (l_debug <> -1) THEN
1630: rlm_core_sv.dlog(C_DEBUG, 'Inside c_oe_lines Loop');
2016: --
2017: /* Populate g_oe_line_tbl data structure with the
2018: calculated cum_key_id so it can be used when
2019: oe_order_grp.process order api to update
2020: oe_order_lines table */
2021: --
2022: counter := counter + 1;
2023: -- Bug# 1466909
2024: g_oe_line_tbl(counter) := OE_Order_PUB.G_MISS_LINE_REC;
2102: fnd_file.put_line(fnd_file.log, v_msg_text);
2103: --
2104: END IF; -- v_cum_control_code <> 'NO_CUM'
2105: --
2106: END LOOP; -- END LOOP to fetch OE Order Lines
2107: --
2108: CLOSE c_oe_lines;
2109: ----------------+
2110: --
2116: IF v_upd_indicator = TRUE THEN
2117: --
2118: -----------------------------------------------------------------------+
2119: BEGIN
2120: /* Call OE_Order_GRP.Process_Order procedure to update OE_ORDER_LINES
2121: table by passing the g_oe_line_tbl structure that has been prepared
2122: inside the loop above */
2123: --
2124: --Pass only g_oe_line_tbl. The rest uses default values
3169: oelines.invoice_to_org_id
3170: FROM WSH_DELIVERY_LEGS wleg,
3171: WSH_DELIVERY_ASSIGNMENTS_V wdass,
3172: WSH_DELIVERY_DETAILS wdel,
3173: OE_ORDER_LINES oelines
3174: WHERE wleg.pick_up_stop_id = x_trip_stop_id
3175: AND wdass.delivery_id = wleg.delivery_id
3176: AND wdel.delivery_detail_id = wdass.delivery_detail_id
3177: AND oelines.shipped_quantity IS NOT NULL
4047: rlm_core_sv.dlog(C_DEBUG, 'l_file_val',l_file_val);
4048: END IF;
4049: --
4050: LOOP --{
4051: /* Call OE_Order_GRP.Process_Order procedure to update OE_ORDER_LINES
4052: table by passing the g_oe_line_tbl structure that has been prepared
4053: this loop calls the process_order API once per each header_id,
4054: since the table is sorted by header_id then this loop
4055: calls the process_order once the header_id is changed */
5235: ELSE --{
5236: --
5237: SELECT COUNT(*)
5238: INTO v_shipment_count
5239: FROM oe_order_lines
5240: WHERE (ship_from_org_id = x_ship_from_org_id
5241: OR x_ship_from_org_id IS NULL)
5242: AND (ship_to_org_id = x_ship_to_org_id
5243: OR x_ship_to_org_id IS NULL)
5799: shipped_quantity,
5800: actual_shipment_date,
5801: order_quantity_uom,
5802: org_id
5803: FROM oe_order_lines_all
5804: WHERE (ship_from_org_id = x_ship_from_org_id
5805: OR x_ship_from_org_id IS NULL)
5806: AND (ship_to_org_id = x_ship_to_org_id
5807: OR x_ship_to_org_id IS NULL)
6109: rlm_core_sv.dlog(C_DEBUG, 'l_file_val',l_file_val);
6110: END IF;
6111: --
6112: LOOP --{
6113: /* Call OE_Order_GRP.Process_Order procedure to update OE_ORDER_LINES
6114: table by passing the g_oe_line_tbl structure that has been prepared
6115: this loop calls the process_order API once per each header_id,
6116: since the table is sorted by header_id then this loop
6117: calls the process_order once the header_id is changed */