[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT VISIT_ID,
INVENTORY_ITEM_ID,
REQUESTED_QUANTITY,
REQUESTED_DATE,
SCHEDULED_MATERIAL_ID,
NVL(COMPLETED_QUANTITY,0) COMPLETED_QUANTITY
FROM AHL_SCHEDULE_MATERIALS
WHERE SCHEDULED_MATERIAL_ID = P_SCHEDULE_MATERIAL_ID;
SELECT DEMAND_SOURCE_LINE_DETAIL,
NVL(SUM(MRV.PRIMARY_RESERVATION_QUANTITY),0) RESERVED_QTY
FROM MTL_RESERVATIONS MRV
WHERE DEMAND_SOURCE_LINE_DETAIL = P_SCHEDULE_MATERIAL_ID
AND MRV. EXTERNAL_SOURCE_CODE = 'AHL'
GROUP BY DEMAND_SOURCE_LINE_DETAIL;
SELECT VISIT_ID,
VISIT_TASK_ID,
SCHEDULED_MATERIAL_ID,
REQUESTED_QUANTITY,
NVL(COMPLETED_QUANTITY,0) COMPLETED_QUANTITY
FROM AHL_SCHEDULE_MATERIALS
WHERE SCHEDULED_MATERIAL_ID = P_SCHEDULE_MATERIAL_ID;
SELECT meaning
FROM mfg_lookups
WHERE LOOKUP_TYPE = 'MTL_DEMAND_INTERFACE_ERRORS'
AND LOOKUP_CODE = C_ERROR_CODE;
SELECT DEMAND_SOURCE_LINE_DETAIL,
NVL(SUM(MRV.PRIMARY_RESERVATION_QUANTITY),0) RESERVED_QTY
FROM MTL_RESERVATIONS MRV
WHERE DEMAND_SOURCE_LINE_DETAIL = P_SCHEDULE_MATERIAL_ID
AND MRV. EXTERNAL_SOURCE_CODE = 'AHL'
GROUP BY DEMAND_SOURCE_LINE_DETAIL;
SELECT SCHEDULED_MATERIAL_ID
FROM AHL_SCHEDULE_MATERIALS
WHERE VISIT_ID = L_VISIT_ID;
SELECT requested_quantity
INTO l_req_qty
FROM ahl_schedule_materials ASM
WHERE ASM.scheduled_material_id = p_scheduled_material_id;
SELECT MAX(ALL_RESERVATIONS.AVAILABLE_DATE) MAX_AVAILABLE_DATE,
SUM(ALL_RESERVATIONS.RESERVED_QTY) TOTAL_RESERVED_QTY
INTO l_available_date,
l_reserved_qty
FROM
(
--Inventory Reservation
SELECT sysdate AVAILABLE_DATE,
RESERVATION.reservation_quantity RESERVED_QTY
FROM mtl_reservations RESERVATION
WHERE RESERVATION.supply_source_type_id = 13 /*g_source_type_inv = 13*/
AND RESERVATION.demand_source_line_detail = p_scheduled_material_id
UNION ALL
--ISO
SELECT ORDER_LINE.schedule_arrival_date AVAILABLE_DATE,
RESERVATION.reservation_quantity RESERVED_QTY
FROM po_requisition_headers_all POREQ_HEAD,
po_requisition_lines_all POREQ_LINE,
oe_order_headers_all ORDER_HEAD,
oe_order_lines_all ORDER_LINE,
mtl_reservations RESERVATION
WHERE POREQ_HEAD.requisition_header_id = ORDER_HEAD.source_document_id
AND ORDER_HEAD.header_id = ORDER_LINE.header_id
AND POREQ_HEAD.requisition_header_id = POREQ_LINE.requisition_header_id
AND POREQ_LINE.item_id = RESERVATION.inventory_item_id
AND RESERVATION.supply_source_type_id = 7 /*g_source_type_internal_req = 7*/
AND RESERVATION.demand_source_line_detail = p_scheduled_material_id
UNION ALL
--WO to WO
SELECT WIP.scheduled_completion_date AVAILABLE_DATE,
RESERVATION.reservation_quantity RESERVED_QTY
FROM mtl_reservations RESERVATION,
wip_discrete_jobs WIP
WHERE WIP.wip_entity_id = RESERVATION.supply_source_header_id
AND RESERVATION.supply_source_type_id = 5 /*g_source_type_wip = 5*/
AND RESERVATION.demand_source_line_detail = p_scheduled_material_id
) ALL_RESERVATIONS;
SELECT ASM.inventory_item_id,
ASM.organization_id,
ASM.requested_date,
ASM.aog_flag,
LOC.subinventory_code,
LOC.inventory_location_id,
ITEMS.primary_uom_code
INTO l_inv_item_id,
l_org_id,
l_dmd_req_date,
l_aog,
l_dmd_subinv_code,
l_dmd_loc_id,
l_uom
FROM mtl_system_items_b ITEMS,
ahl_schedule_materials ASM,
ahl_visits_b VST,
mtl_item_locations LOC
WHERE ITEMS.inventory_item_id = ASM.inventory_item_id
AND ITEMS.organization_id = ASM.organization_id
AND ASM.visit_id = VST.visit_id
AND VST.inv_locator_id = LOC.inventory_location_id (+)
AND ASM.scheduled_material_id = p_scheduled_material_id;
SELECT reservation_id
INTO l_old_rsrv_id
FROM MTL_SERIAL_NUMBERS
WHERE inventory_item_id = l_inv_item_id
AND serial_number = p_supply_tbl_type(i).Serial_Number;
inv_mo_line_detail_util.update_row (l_return_status, l_mo_line_dtl_tbl(i));
SELECT transaction_header_id, transaction_quantity
INTO l_trolin_tbl(i).transaction_header_id, l_trolin_tbl(i).quantity_detailed
FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE move_order_line_id = l_trolin_tbl(i).line_id;
l_trolin_tbl(i).last_update_date := SYSDATE;
l_trolin_tbl(i).last_update_login := fnd_global.login_id;
l_trolin_tbl(i).last_updated_by := fnd_global.user_id;
l_trolin_tbl(i).program_update_date := SYSDATE;
inv_trolin_util.update_row (l_trolin_tbl (i));
SELECT MSNT.transaction_temp_id, MSNT.fm_serial_number, MSNT.to_serial_number
INTO l_temp_txn_id, l_fm_serial, l_to_serial
FROM mtl_material_transactions_temp MMTT, mtl_serial_numbers_temp MSNT
WHERE MMTT.transaction_temp_id = MSNT.transaction_temp_id
AND MMTT.move_order_line_id = l_to_line_tbl(i).line_id;
, p_update_reservation => 'F'
);
DELETE FROM mtl_serial_numbers_temp WHERE transaction_temp_id = l_temp_txn_id;
'Rows deleted from mtl_serial_numbers_temp: ' || SQL%ROWCOUNT);
, p_update_reservation => 'F'
, success => l_mark_serial_success
);
'Inserting into mtl_serial_numbers_temp for Serial: '
|| p_supply_tbl_type(i).Serial_Number);
INSERT INTO mtl_serial_numbers_temp(transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, fm_serial_number
, to_serial_number
, group_header_id
, serial_prefix /*Number of serial numbers in range*/ )
VALUES(l_temp_txn_id
, sysdate
, fnd_global.user_id
, sysdate
, fnd_global.user_id
, fnd_global.login_id
, p_supply_tbl_type(i).Serial_Number
, p_supply_tbl_type(i).Serial_Number
, l_temp_txn_id
, '1' );
x_mo_lines_tbl.DELETE;
PROCEDURE UPDATE_REQUIREMENTS_FOR_GOP(
Errbuf OUT NOCOPY VARCHAR2,
Retcode OUT NOCOPY NUMBER
) IS
--Table of varchar2
TYPE varchar2_table_type IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
SELECT ASM.SCHEDULED_MATERIAL_ID,
ASM.ATP_FLAG,
WO.WORKORDER_NAME
FROM AHL_SCHEDULE_MATERIALS ASM,
AHL_WORKORDERS WO,
AHL_VISIT_TASKS_B TSK
WHERE (ASM.REQUESTED_QUANTITY - NVL(ASM.COMPLETED_QUANTITY,0) ) > 0
AND ASM.STATUS = 'ACTIVE'
AND ASM.VISIT_TASK_ID = WO.VISIT_TASK_ID
AND WO.STATUS_CODE NOT IN (4, 5, 7, 12)
AND WO.VISIT_TASK_ID = TSK.VISIT_TASK_ID
AND TSK.TASK_TYPE_CODE <> 'STAGE'
AND ASM.REQUESTED_DATE <= SYSDATE + c_planning_window;
FND_FILE.put_line(FND_FILE.log, 'At the start of the PLSQL procedure UPDATE_REQUIREMENTS_FOR_GOP');
SELECT TO_NUMBER(FND_PROFILE.VALUE('AHL_GOP_ATP_PLANNING_WINDOW'))
INTO l_planning_window
FROM DUAL;
UPDATE ahl_schedule_materials
SET ATP_FLAG = 'N'
WHERE status = 'ACTIVE'
AND requested_date > sysdate + l_planning_window
AND ATP_FLAG = 'Y';
UPDATE ahl_schedule_materials
SET ATP_FLAG = 'Y'
WHERE scheduled_material_id = l_demand_rec.scheduled_material_id;
l_p_sch_mtl_id_tbl.DELETE;
l_wo_name_tbl.DELETE;
l_x_mtl_avl_schld_tbl.DELETE;
fnd_file.put_line(fnd_file.log, 'At the end of PL SQL procedure UPDATE_REQUIREMENTS_FOR_GOP');
p_procedure_name => 'UPDATE_REQUIREMENTS_FOR_GOP',
p_error_text => SUBSTR(SQLERRM,1,500));
END UPDATE_REQUIREMENTS_FOR_GOP;
l_sql_string := 'SELECT wo.workorder_name, asm.scheduled_material_id ' ||
'FROM ahl_schedule_materials asm, ahl_workorders wo ' ||
'WHERE ' ||
'asm.visit_task_id = wo.visit_task_id ' ||
'AND asm.scheduled_material_id IN ( 0';
SELECT MSI.SEGMENT1,
MSI.INTERNAL_ORDER_ENABLED_FLAG
INTO L_INVENTORY_ITEM,
L_ISO_ENABLED_FLAG
FROM AHL_SCHEDULE_MATERIALS ASM,
MTL_SYSTEM_ITEMS_B MSI
WHERE ASM.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND ASM.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND SCHEDULED_MATERIAL_ID = P_SCH_MTL_ID;
L_SOURCE_ORG_TBL.DELETE(I);
L_FM_SERIAL_NUM_TBL.DELETE;
L_TO_SERIAL_NUM_TBL.DELETE;
L_QUANTITY_TBL.DELETE;
L_SOURCE_SUB_INV_TBL.DELETE;
L_SOURCE_LOCATORS_TBL.DELETE;
SELECT inventory_item_id, ORGANIZATION_ID, REQUESTED_DATE
INTO l_inventory_item_id, l_demand_org_id, l_need_by_date
FROM AHL_SCHEDULE_MATERIALS
where SCHEDULED_MATERIAL_ID = P_SCH_MTL_ID;
SELECT primary_uom_code,
description
INTO l_uom_code,
l_item_description
FROM mtl_system_items_b
where INVENTORY_ITEM_ID = L_INVENTORY_ITEM_ID
and rownum = 1;
SELECT OPERATING_UNIT
INTO L_SOURCE_OU
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_ID = P_SOURCE_ORG;
SELECT OPERATING_UNIT
INTO L_DESTINATION_OU
FROM ORG_ORGANIZATION_DEFINITIONS
where ORGANIZATION_ID = l_demand_org_id;
SELECT LOC.LOCATION_ID
INTO L_DESTINATION_LOC_ID
FROM HR_LOCATIONS_ALL LOC,
PO_LOCATION_ASSOCIATIONS_ALL PLOC,
HZ_CUST_ACCOUNTS CUST
WHERE LOC.INVENTORY_ORGANIZATION_ID = l_demand_org_id
AND LOC.LOCATION_ID = PLOC.LOCATION_ID
AND PLOC.ORG_ID = L_DESTINATION_OU
AND PLOC.CUSTOMER_ID = CUST.CUST_ACCOUNT_ID
AND CUST.CUSTOMER_TYPE = 'I'
AND ROWNUM = 1;
SELECT employee_id
INTO l_person_id
FROM fnd_user
where USER_ID = L_USER_ID;
SELECT currency_code
INTO l_currency_code
FROM gl_sets_of_books,
hr_organization_information
WHERE SET_OF_BOOKS_ID = ORG_INFORMATION1
AND organization_id = L_DESTINATION_OU
and ORG_INFORMATION_CONTEXT = 'Accounting Information';
SELECT material_account
INTO l_material_account
FROM mtl_parameters
where ORGANIZATION_ID = l_demand_org_id;
SELECT INTRANSIT_TYPE, INTERNAL_ORDER_REQUIRED_FLAG
INTO L_SHIPPING_NETWORK_CODE, L_ISO_REQ_FLAG
FROM MTL_SHIPPING_NETWORK_VIEW
WHERE FROM_ORGANIZATION_ID = P_SOURCE_ORG
AND TO_ORGANIZATION_ID = L_DEMAND_ORG_ID;
SELECT MILK.SUBINVENTORY_CODE
INTO L_DEMAND_SUBINV_CODE--, L_DEMAND_LOCATOR_ID
FROM AHL_VISITS_B AVB, MTL_ITEM_LOCATIONS_KFV MILK, AHL_SCHEDULE_MATERIALS ASM
WHERE AVB.INV_LOCATOR_ID = MILK.INVENTORY_LOCATION_ID(+)
AND ASM.VISIT_ID = AVB.VISIT_ID
and ASM.SCHEDULED_MATERIAL_ID = P_SCH_MTL_ID;
FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Before inserting into requisitions interface.');
INSERT
INTO po_requisitions_interface_all
(
interface_source_code, -- AHL
destination_type_code, -- INVENTORY
authorization_status, -- APPROVED
PREPARER_ID, -- person id of the user name -- l_person_id
quantity, -- The demand quantity
DESTINATION_ORGANIZATION_ID, -- L_DEMAND_ORG_ID
DESTINATION_SUBINVENTORY, -- L_DEMAND_SUBINV_CODE
deliver_to_location_id, -- destination location id - Location attached through customer
deliver_to_requestor_id, -- l_person_id
source_type_code, -- INVENTORY
category_id, -- null
item_description, -- l_item_descriptions(i)
uom_code, -- l_uom_codes(i)
unit_price, -- null
need_by_date, -- p_need_by_date
wip_entity_id, -- null
wip_operation_seq_num, -- null
charge_account_id, -- l_material_accounts(i),
variance_account_id, -- null
item_id, -- l_inventory_item_ids(i)
wip_resource_seq_num, -- null
suggested_vendor_id, -- null
suggested_vendor_name, -- null
suggested_vendor_site, -- null
suggested_vendor_phone, -- null
suggested_vendor_item_num, -- null
currency_code, -- l_currency_code
project_id, -- null
task_id, -- null
project_accounting_context, -- null
last_updated_by, -- l_user_id
last_update_date, -- sysdate
created_by, -- l_user_id
creation_date, -- sysdate
org_id, -- p_destination_ous(i)
REFERENCE_NUM, -- p_destination_ous(i)
interface_source_line_id, -- p_product_txn_ids(1) -- sechedule_material_id
source_organization_id
) -- p_source_orgs(i)
VALUES
(
'AHL',
'INVENTORY',
'APPROVED',
l_person_id,
p_quantity,
L_DEMAND_ORG_ID,
L_DEMAND_SUBINV_CODE,
l_destination_loc_id,
l_person_id,
'INVENTORY',
NULL,
l_item_description,
l_uom_code,
NULL,
l_need_by_date,
NULL,
NULL,
l_material_account,
NULL,
l_inventory_item_id,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
l_currency_code,
NULL,
NULL,
NULL,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
SYSDATE,
l_destination_ou,
NULL,
l_product_txn_id,
p_source_org
);
SELECT PRHA.SEGMENT1, -- req num
PRHA.REQUISITION_HEADER_ID, -- req hdr id
PRLA.REQUISITION_LINE_ID -- req line id
INTO X_REQUISITION_NUM,
X_REQUISITION_HDR_ID,
X_REQUISITION_LINE_ID
FROM PO_REQUISITION_HEADERS_ALL PRHA,
PO_REQUISITION_LINES_ALL PRLA
WHERE PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID
AND INTERFACE_SOURCE_LINE_ID = L_PRODUCT_TXN_ID;
update PO_REQUISITION_HEADERS_ALL set INTERFACE_SOURCE_LINE_ID = P_SCH_MTL_ID where REQUISITION_HEADER_ID = X_REQUISITION_HDR_ID;
SELECT OOHA.HEADER_ID, -- order header id
OOHA.ORDER_NUMBER, -- order number
OOLA.LINE_ID -- order line id
INTO X_INT_ORDER_HDR_ID,
X_INT_ORDER_NUM,
X_INT_ORDER_LINE_ID
FROM PO_REQUISITION_HEADERS_ALL PRHA,
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA
WHERE PRHA.REQUISITION_HEADER_ID = X_REQUISITION_HDR_ID
AND PRHA.REQUISITION_HEADER_ID = OOLA.SOURCE_DOCUMENT_ID
AND OOHA.HEADER_ID = OOLA.HEADER_ID;
INSERT
INTO AHL_MM_TXN_INTERFACE
(
TRANSACTION_ID,
TXN_SOURCE_OBJECT_ID,
TXN_SOURCE_TYPE,
TXN_STATUS,
RESERVATION_ID,
SCHEDULED_MATERIAL_ID,
SERIAL_NUMBER,
ERROR_MESSAGE,
PROCESSING_DATE
)
VALUES
(
AHL_MM_TXN_INTERFACE_S.NEXTVAL,
X_REQUISITION_HDR_ID,
'ISO',
'S',
X_RESERVATION_ID,
P_SCH_MTL_ID,
P_FM_SERIAL_NUM_TBL(X),
NULL,
SYSDATE
);
UPDATE AHL_MM_TXN_INTERFACE
SET TXN_STATUS = 'E', ERROR_MESSAGE = L_ERROR_MESSAGE
WHERE TRANSACTION_ID BETWEEN (L_MM_TXN_INTERFACE_S_CURVAL - P_QUANTITY + 1) AND L_MM_TXN_INTERFACE_S_CURVAL;
FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'NO_ISO_ENABLED_NETWORK block, Internal Order Enabled flag not selected');
L_SOURCE_SUB_INV_TBL.DELETE(I);
L_SOURCE_LOCATOR_TBL.DELETE(I);
L_CUR_FM_SERIAL_NUM_TBL.DELETE(I);
L_CUR_TO_SERIAL_NUM_TBL.DELETE(I);
L_FM_SERIAL_NUM_TBL.DELETE;
L_TO_SERIAL_NUM_TBL.DELETE;
L_QUANTITY_TBL.DELETE;
select FM_SERIAL_NUMBER, TO_SERIAL_NUMBER from MTL_SERIAL_NUMBERS_TEMP
where transaction_temp_id = p_mmtt_temp_id;
SELECT released_status,requested_quantity,requested_quantity_uom,delivery_detail_id
INTO l_delivery_status,l_requested_qty,l_transaction_uom,l_delivery_detail_id
FROM WSH_DELIVERY_DETAILS
WHERE SOURCE_HEADER_ID = P_ORDER_HEADER_ID
and released_status not in ('S', 'Y', 'C');
SELECT sold_to_org_id,
order_type_id,
source_document_type_id
INTO l_customer_id,
l_order_type_id,
l_document_set_id
FROM oe_order_headers_all
WHERE header_id = p_order_header_id;
SELECT NVL(document_set_id, l_document_set_id),
'I',
NVL(existing_rsvs_only_flag, 'N'),
shipment_priority_code,
p_order_header_id,
l_delivery_detail_id,--NULL,
l_order_type_id,
NULL,
l_customer_id,
NULL,
ship_method_code,
NVL(P_PICK_FROM_SUBINV, PICK_FROM_SUBINVENTORY),
nvl(P_PICK_FROM_LOCATOR, pick_from_locator_id),
default_stage_subinventory,
default_stage_locator_id,
autodetail_pr_flag,
'N',
ship_set_number,
NULL,
NULL,
NULL,
NULL,
NULL,
pick_grouping_rule_id,
pick_sequence_rule_id,
NVL(p_pick_from_org, organization_id),
project_id,
task_id,
include_planned_lines,
autocreate_delivery_flag,
allocation_method,
l_delivery_detail_id
INTO l_batch_rec.document_set_id,
l_batch_rec.backorders_only_flag,
l_batch_rec.existing_rsvs_only_flag,
l_batch_rec.shipment_priority_code,
l_batch_rec.order_header_id,
l_batch_rec.delivery_detail_id,
l_batch_rec.order_type_id,
l_batch_rec.ship_from_location_id,
l_batch_rec.customer_id,
l_batch_rec.ship_to_location_id,
l_batch_rec.ship_method_code,
l_batch_rec.pick_from_subinventory,
l_batch_rec.pick_from_locator_id,
l_batch_rec.default_stage_subinventory,
l_batch_rec.default_stage_locator_id,
l_batch_rec.autodetail_pr_flag,
l_batch_rec.auto_pick_confirm_flag,
l_batch_rec.ship_set_number,
l_batch_rec.inventory_item_id,
l_batch_rec.from_requested_date,
l_batch_rec.to_requested_date,
l_batch_rec.from_scheduled_ship_date,
l_batch_rec.to_scheduled_ship_date,
l_batch_rec.pick_grouping_rule_id,
l_batch_rec.pick_sequence_rule_id,
l_batch_rec.organization_id,
l_batch_rec.project_id,
l_batch_rec.task_id,
l_batch_rec.include_planned_lines,
l_batch_rec.autocreate_delivery_flag,
l_batch_rec.allocation_method,
l_batch_rec.delivery_detail_id
from WSH_PICKING_RULES
WHERE PICKING_RULE_ID = l_picking_rule_id; -- value 628
SELECT mtrh.header_id,mmtt.transaction_temp_id,
mtrl.line_id,wdd.delivery_detail_id
INTO l_move_order_hdr_id,l_mmtt_temp_id,
l_move_order_line_id,G_DELIVERY_DETAIL_ID
FROM wsh_delivery_details wdd,
mtl_txn_request_headers mtrh,
mtl_txn_request_lines mtrl,
mtl_material_transactions_temp mmtt
WHERE wdd.source_header_id = p_order_header_id
AND wdd.move_order_line_id = mtrl.line_id
AND mtrl.header_id = mtrh.header_id
AND MTRL.LINE_ID = MMTT.MOVE_ORDER_LINE_ID(+)
AND wdd.delivery_detail_id = l_delivery_detail_id;
SELECT wdd.move_order_line_id
INTO l_move_order_line_id
FROM wsh_delivery_details wdd
WHERE wdd.source_header_id = p_order_header_id;
SELECT msi.reservable_type,msi.inventory_item_id,
msi.organization_id
INTO l_reservable_flag,l_item_id,
l_org_id
FROM mtl_system_items_b msi,
mtl_txn_request_lines mtrl
WHERE mtrl.inventory_item_id = msi.inventory_item_id
AND mtrl.organization_id = msi.organization_id
AND mtrl.line_id = l_move_order_line_id;
SELECT reservation_id
INTO l_reservation_id
FROM mtl_reservations mr
WHERE mr.demand_source_line_id = l_order_line_id
AND NVL(staged_flag,'N') = 'N';
SELECT fm_serial_number, to_serial_number
BULK COLLECT INTO
l_fm_serial_number, l_to_serial_number
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_mmtt_temp_id;
p_update_reservation => 'T'
);
-- delete all the existing serials from MSNT.
if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Deleting from MSNT for transaction_temp_id='||l_mmtt_temp_id);
DELETE FROM mtl_serial_numbers_temp WHERE transaction_temp_id = l_mmtt_temp_id;
-- mark the newly passed serials. And insert them into MSNT.
if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Marking the newly entered serial ranges');
p_update_reservation => fnd_api.g_true,
success => x_success
);
FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Inserting into msnt='||l_mmtt_temp_id);
INSERT INTO mtl_serial_numbers_temp
(transaction_temp_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
fm_serial_number,
to_serial_number,
group_header_id,
serial_prefix
)
VALUES (l_mmtt_temp_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
p_fm_serial_num_tbl(j),
p_to_serial_num_tbl(j),
l_mmtt_temp_id,
p_quantity_tbl(j)
);
-- if we are doing partial picking, then need to update the existing allocation details.
-- the serial number allocation would have been already addressed above.
if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME, ' allocation details after unmarking and marking');
inv_missing_qty_actions_engine.update_allocation_qty
(x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
p_transaction_temp_id => l_mmtt_temp_id,
p_confirmed_quantity => l_picking_qty,
p_transaction_uom => l_transaction_uom
);
fnd_log.string(G_LEVEL_PROCEDURE, l_api_name,'Error in inv_missing_qty_actions_engine.update_allocation_qty. Msg is '||x_msg_data);
SELECT released_status,delivery_detail_id,picked_quantity
INTO l_delivery_status,l_delivery_detail_id_new,l_picked_qty
FROM wsh_delivery_details
WHERE move_order_line_id = l_move_order_line_id;
L_SQL_STRING VARCHAR2(30000) := 'SELECT distinct AWO.WORKORDER_ID,AWO.OBJECT_VERSION_NUMBER FROM AHL_WORKORDERS AWO,
AHL_SCHEDULE_MATERIALS ASM WHERE AWO.VISIT_TASK_ID = ASM.VISIT_TASK_ID
AND AWO.VISIT_TASK_ID = ASM.VISIT_TASK_ID AND ASM.SCHEDULED_MATERIAL_ID in(';
AHL_PRD_WORKORDER_PVT.UPDATE_WO_AOG_STATUS(P_API_VERSION => P_API_VERSION,
P_INIT_MSG_LIST => P_INIT_MSG_LIST,
P_COMMIT => P_COMMIT,
P_WORKORDER_ID_TBL => P_WORK_ORDER_ID_TBL,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
PROCEDURE UPDATE_AOG(
P_API_VERSION IN NUMBER := 1.0,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
P_AOG_TBL IN AOG_TBL_TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
)
IS
CURSOR CURRENT_AOG_CSR(P_SCHEDULE_MATERIAL_ID NUMBER) IS
SELECT AOG_FLAG FROM AHL_SCHEDULE_MATERIALS WHERE SCHEDULED_MATERIAL_ID = P_SCHEDULE_MATERIAL_ID;
L_API_NAME CONSTANT VARCHAR2(30) := 'UPDATE_AOG';
SAVEPOINT MM_UPDATE_AOG_SP;
FND_LOG.STRING(L_LOG_PROCEDURE, L_DEBUG || '.begin', 'At the start of the PLSQL procedure UPDATE_AOG');
FND_LOG.STRING(L_LOG_PROCEDURE, L_DEBUG, 'Invoking Update Notification for ' || P_AOG_TBL(I).SCHEDULE_MATERIAL_ID);
UPDATE AHL_SCHEDULE_MATERIALS SET
AOG_FLAG = P_AOG_TBL(I).AOG_FLAG,
OBJECT_VERSION_NUMBER = P_AOG_TBL(I).ASM_OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
WHERE SCHEDULED_MATERIAL_ID = P_AOG_TBL(I).SCHEDULE_MATERIAL_ID AND OBJECT_VERSION_NUMBER = P_AOG_TBL(I).ASM_OBJECT_VERSION_NUMBER;
ROLLBACK TO MM_UPDATE_AOG_SP;
ROLLBACK TO MM_UPDATE_AOG_SP;
ROLLBACK TO MM_UPDATE_AOG_SP;
END UPDATE_AOG;
PROCEDURE Insert_Material
(
p_x_mtltfr_rec IN OUT NOCOPY Ahl_Mtltfr_Rec_Type,
p_x_txn_hdr_id IN OUT NOCOPY NUMBER,
p_x_txn_intf_id IN OUT NOCOPY NUMBER,
p_shipping_type IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2 );
x_reservation_id OUT NOCOPY NUMBER -- Only one reservation will be made even for multiple item selection
-- Assumption is that the user will be allowed to select multiple serials/lots of only ONE item
-- and after inter org transfer all the item instances will reside in the same visit sub-inv and locator
)
IS
-- Logging Variable
l_debug_module VARCHAR2(1000) := 'ahl.plsql.AHL_MM_MTL_MGT_PVT.Perform_Inter_Org_Transfer';
SELECT intransit_type
INTO l_shipping_network_code
FROM MTL_SHIPPING_NETWORK_VIEW
WHERE from_organization_id =p_x_mtltfr_tbl(i).source_org_id
AND to_organization_id = p_x_mtltfr_tbl(i).dest_org_id ;
SELECT intf.ERROR_EXPLANATION ,
intf.ERROR_CODE,
kfv.concatenated_segments,
sni.FM_SERIAL_NUMBER
FROM MTL_TRANSACTIONS_INTERFACE INTF,
mtl_system_items_kfv kfv,
MTL_SERIAL_NUMBERS_INTERFACE sni
WHERE intf.TRANSACTION_INTERFACE_ID = p_txn_Id
AND intf.inventory_item_id = kfv.inventory_item_id
AND intf.organization_id = kfv.organization_id
AND intf.TRANSACTION_INTERFACE_ID = sni.transaction_interface_id;
SELECT Mtl_Material_Transactions_S.nextval
INTO l_transaction_header_id
FROM DUAL;
SELECT intransit_type
INTO l_shipping_network_code
FROM MTL_SHIPPING_NETWORK_VIEW
WHERE from_organization_id =p_x_mtltfr_tbl(i).source_org_id
AND to_organization_id = p_x_mtltfr_tbl(i).dest_org_id ;
FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||'Calling Insert_Material for record '||i);
Insert_Material ( p_x_mtltfr_rec => l_x_mtltfr_rec,
p_x_txn_hdr_id => l_transaction_header_id,
p_x_txn_intf_id => l_transaction_id,
p_shipping_type => l_shipping_network_code,
x_return_status => x_return_status );
FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Return status from Insert_Material '||x_return_status);
-- Store the transaction ids generated during each insertion
l_transaction_id_tbl(i) := l_transaction_id;
FND_LOG.string(l_log_error, l_debug_module, G_DEBUG_LINE_NUM||':Error in Insert_Material Procedure for record '||i);
FND_LOG.string(l_log_error, l_debug_module, G_DEBUG_LINE_NUM||':Unexpected Error in Insert_Material Procedure for record '||i);
FND_LOG.string(l_log_error, l_debug_module, G_DEBUG_LINE_NUM||':Error in Insert_Material Procedure');
PROCEDURE Insert_Material
(
p_x_mtltfr_rec IN OUT NOCOPY Ahl_Mtltfr_Rec_Type,
p_x_txn_hdr_id IN OUT NOCOPY NUMBER,
p_x_txn_intf_id IN OUT NOCOPY NUMBER,
p_shipping_type IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
-- Serial Number dff
CURSOR get_serial_dff_attrib(p_inv_item_id IN NUMBER, p_serial_number IN VARCHAR2)
IS
SELECT ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
C_ATTRIBUTE1,
C_ATTRIBUTE2,
C_ATTRIBUTE3,
C_ATTRIBUTE4,
C_ATTRIBUTE5,
C_ATTRIBUTE6,
C_ATTRIBUTE7,
C_ATTRIBUTE8,
C_ATTRIBUTE9,
C_ATTRIBUTE10,
C_ATTRIBUTE11,
C_ATTRIBUTE12,
C_ATTRIBUTE13,
C_ATTRIBUTE14,
C_ATTRIBUTE15,
C_ATTRIBUTE16,
C_ATTRIBUTE17,
C_ATTRIBUTE18,
C_ATTRIBUTE19,
C_ATTRIBUTE20,
D_ATTRIBUTE1,
D_ATTRIBUTE2,
D_ATTRIBUTE3,
D_ATTRIBUTE4,
D_ATTRIBUTE5,
D_ATTRIBUTE6,
D_ATTRIBUTE7,
D_ATTRIBUTE8,
D_ATTRIBUTE9,
D_ATTRIBUTE10,
N_ATTRIBUTE1,
N_ATTRIBUTE2,
N_ATTRIBUTE3,
N_ATTRIBUTE4,
N_ATTRIBUTE5,
N_ATTRIBUTE6,
N_ATTRIBUTE7,
N_ATTRIBUTE8,
N_ATTRIBUTE9,
N_ATTRIBUTE10
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inv_item_id
AND serial_number = p_serial_number;
l_debug_module VARCHAR2(1000) := 'ahl.plsql.AHL_MM_MTL_MGT_PVT.Insert_Material';
FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':In Insert_Material Procedure with shipping network type '||p_shipping_type||
',transaction header id '||p_x_txn_hdr_id);
SELECT Mtl_Material_Transactions_S.nextval INTO p_x_txn_hdr_id FROM DUAL;
SELECT Mtl_Material_Transactions_S.nextval INTO p_x_txn_intf_id FROM DUAL;
--SELECT AHL_SHIPMENT_NUMBER_S.nextval INTO l_shipment_num FROM dual;
SELECT AHL_SHIPMENT_NUMBER_S.nextval INTO l_shipment_num FROM dual;
SELECT AHL_SHIPMENT_NUMBER_S.nextval INTO l_shipment_num FROM dual;
INSERT
INTO MTL_SERIAL_NUMBERS_INTERFACE
(
TRANSACTION_INTERFACE_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
PROCESS_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
C_ATTRIBUTE1,
C_ATTRIBUTE2,
C_ATTRIBUTE3,
C_ATTRIBUTE4,
C_ATTRIBUTE5,
C_ATTRIBUTE6,
C_ATTRIBUTE7,
C_ATTRIBUTE8,
C_ATTRIBUTE9,
C_ATTRIBUTE10,
C_ATTRIBUTE11,
C_ATTRIBUTE12,
C_ATTRIBUTE13,
C_ATTRIBUTE14,
C_ATTRIBUTE15,
C_ATTRIBUTE16,
C_ATTRIBUTE17,
C_ATTRIBUTE18,
C_ATTRIBUTE19,
C_ATTRIBUTE20,
D_ATTRIBUTE1,
D_ATTRIBUTE2,
D_ATTRIBUTE3,
D_ATTRIBUTE4,
D_ATTRIBUTE5,
D_ATTRIBUTE6,
D_ATTRIBUTE7,
D_ATTRIBUTE8,
D_ATTRIBUTE9,
D_ATTRIBUTE10,
N_ATTRIBUTE1,
N_ATTRIBUTE2,
N_ATTRIBUTE3,
N_ATTRIBUTE4,
N_ATTRIBUTE5,
N_ATTRIBUTE6,
N_ATTRIBUTE7,
N_ATTRIBUTE8,
N_ATTRIBUTE9,
N_ATTRIBUTE10
)
VALUES
(
p_x_txn_intf_id,
l_source_code,
l_source_line_id,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
p_x_mtltfr_rec.serial_number,
p_x_mtltfr_rec.serial_number,
l_process_flag,
l_serial_dff_rec.ATTRIBUTE_CATEGORY,
l_serial_dff_rec.ATTRIBUTE1,
l_serial_dff_rec.ATTRIBUTE2,
l_serial_dff_rec.ATTRIBUTE3,
l_serial_dff_rec.ATTRIBUTE4,
l_serial_dff_rec.ATTRIBUTE5,
l_serial_dff_rec.ATTRIBUTE6,
l_serial_dff_rec.ATTRIBUTE7,
l_serial_dff_rec.ATTRIBUTE8,
l_serial_dff_rec.ATTRIBUTE9,
l_serial_dff_rec.ATTRIBUTE10,
l_serial_dff_rec.ATTRIBUTE11,
l_serial_dff_rec.ATTRIBUTE12,
l_serial_dff_rec.ATTRIBUTE13,
l_serial_dff_rec.ATTRIBUTE14,
l_serial_dff_rec.ATTRIBUTE15,
l_serial_dff_rec.C_ATTRIBUTE1,
l_serial_dff_rec.C_ATTRIBUTE2,
l_serial_dff_rec.C_ATTRIBUTE3,
l_serial_dff_rec.C_ATTRIBUTE4,
l_serial_dff_rec.C_ATTRIBUTE5,
l_serial_dff_rec.C_ATTRIBUTE6,
l_serial_dff_rec.C_ATTRIBUTE7,
l_serial_dff_rec.C_ATTRIBUTE8,
l_serial_dff_rec.C_ATTRIBUTE9,
l_serial_dff_rec.C_ATTRIBUTE10,
l_serial_dff_rec.C_ATTRIBUTE11,
l_serial_dff_rec.C_ATTRIBUTE12,
l_serial_dff_rec.C_ATTRIBUTE13,
l_serial_dff_rec.C_ATTRIBUTE14,
l_serial_dff_rec.C_ATTRIBUTE15,
l_serial_dff_rec.C_ATTRIBUTE16,
l_serial_dff_rec.C_ATTRIBUTE17,
l_serial_dff_rec.C_ATTRIBUTE18,
l_serial_dff_rec.C_ATTRIBUTE19,
l_serial_dff_rec.C_ATTRIBUTE20,
l_serial_dff_rec.D_ATTRIBUTE1,
l_serial_dff_rec.D_ATTRIBUTE2,
l_serial_dff_rec.D_ATTRIBUTE3,
l_serial_dff_rec.D_ATTRIBUTE4,
l_serial_dff_rec.D_ATTRIBUTE5,
l_serial_dff_rec.D_ATTRIBUTE6,
l_serial_dff_rec.D_ATTRIBUTE7,
l_serial_dff_rec.D_ATTRIBUTE8,
l_serial_dff_rec.D_ATTRIBUTE9,
l_serial_dff_rec.D_ATTRIBUTE10,
l_serial_dff_rec.N_ATTRIBUTE1,
l_serial_dff_rec.N_ATTRIBUTE2,
l_serial_dff_rec.N_ATTRIBUTE3,
l_serial_dff_rec.N_ATTRIBUTE4,
l_serial_dff_rec.N_ATTRIBUTE5,
l_serial_dff_rec.N_ATTRIBUTE6,
l_serial_dff_rec.N_ATTRIBUTE7,
l_serial_dff_rec.N_ATTRIBUTE8,
l_serial_dff_rec.N_ATTRIBUTE9,
l_serial_dff_rec.N_ATTRIBUTE10
);
FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Inserted in serial interface table');
INSERT
INTO MTL_TRANSACTIONS_INTERFACE
(
TRANSACTION_INTERFACE_ID ,
TRANSACTION_HEADER_ID ,
SOURCE_CODE ,
SOURCE_LINE_ID ,
SOURCE_HEADER_ID,
PROCESS_FLAG ,
VALIDATION_REQUIRED ,
TRANSACTION_MODE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
INVENTORY_ITEM_ID ,
ORGANIZATION_ID ,
TRANSACTION_QUANTITY ,
PRIMARY_QUANTITY ,
TRANSACTION_UOM ,
TRANSACTION_DATE ,
SUBINVENTORY_CODE ,
LOCATOR_ID ,
TRANSACTION_TYPE_ID ,
REVISION ,
TRANSACTION_REFERENCE ,
TRANSFER_SUBINVENTORY,
TRANSFER_LOCATOR,
TRANSFER_ORGANIZATION,
SHIPMENT_NUMBER
)
VALUES
(
p_x_txn_intf_id,
p_x_txn_hdr_id,
l_source_code,
l_source_line_id,
l_source_header_id,
l_process_flag,
l_validation_required ,
l_transaction_mode,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
p_x_mtltfr_rec.inventory_item_id,
p_x_mtltfr_rec.source_org_id,
l_qty,
l_qty,
p_x_mtltfr_rec.uom,
sysdate,
p_x_mtltfr_rec.source_subinv_name,
p_x_mtltfr_rec.source_locator_id,
l_transaction_type_id,
p_x_mtltfr_rec.revision,
'AHL',
p_x_mtltfr_rec.dest_subinv_name,
p_x_mtltfr_rec.dest_locator_id,
p_x_mtltfr_rec.dest_org_id,
l_shipment_number
);
FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Inserted in transaction interface table');
FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Returning from Insert_Material Procedure');
FND_LOG.string(l_log_exception, l_debug_module, G_DEBUG_LINE_NUM||':Exception in Insert_Material');
END Insert_Material;
SELECT SERIAL_NUMBER_CONTROL_CODE,
LOT_CONTROL_CODE,
REVISION_QTY_CONTROL_CODE,
LOCATION_CONTROL_CODE,
primary_uom_code,
concatenated_segments
FROM MTL_SYSTEM_ITEMS_KFV
WHERE ORGANIZATION_ID = p_org_id
AND INVENTORY_ITEM_ID = p_item
AND ENABLED_FLAG = 'Y'
AND ((START_DATE_ACTIVE IS NULL)
OR (START_DATE_ACTIVE <= SYSDATE))
AND ((END_DATE_ACTIVE IS NULL)
OR (END_DATE_ACTIVE >= SYSDATE));
SELECT SERIAL_NUMBER_CONTROL_CODE,
LOT_CONTROL_CODE,
REVISION_QTY_CONTROL_CODE,
LOCATION_CONTROL_CODE,
primary_uom_code,
concatenated_segments
FROM MTL_SYSTEM_ITEMS_KFV
WHERE ORGANIZATION_ID = p_org_id
AND INVENTORY_ITEM_ID = p_item
AND ENABLED_FLAG = 'Y'
AND ((START_DATE_ACTIVE IS NULL)
OR (START_DATE_ACTIVE <= SYSDATE))
AND ((END_DATE_ACTIVE IS NULL)
OR (END_DATE_ACTIVE >= SYSDATE));
SELECT 1
FROM MTL_ITEM_LOCATIONS
WHERE INVENTORY_LOCATION_ID = p_loc_id
AND ORGANIZATION_ID = p_org_id;
SELECT 1
FROM MTL_SERIAL_NUMBERS
WHERE INVENTORY_ITEM_ID = p_item
AND CURRENT_ORGANIZATION_ID = p_org_id
AND SERIAL_NUMBER = p_serial_num
AND RESERVATION_ID IS NOT NULL;
SELECT 1
FROM MTL_SHIPPING_NETWORK_VIEW
WHERE from_organization_id =p_source_org_id
AND to_organization_id = p_dest_org_id ;
SELECT 1
FROM MTL_SHIPPING_NETWORK_VIEW
WHERE from_organization_id =p_source_org_id
AND to_organization_id = p_dest_org_id
AND internal_order_required_flag = 1;
SELECT concatenated_segments
INTO l_concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_x_mtltfr_rec.Inventory_Item_Id
AND organization_id = p_x_mtltfr_rec.dest_org_id;
SELECT organization_name
INTO l_src_organization_name
FROM Org_Organization_definitions
WHERE organization_id = p_x_mtltfr_rec.source_org_id;
SELECT organization_name
INTO l_dest_organization_name
FROM Org_Organization_definitions
WHERE organization_id = p_x_mtltfr_rec.dest_org_id;
SELECT CCA.SOURCE_OBJECT_ID ITEM_INSTANCE_ID,
CC.COUNTER_ID,
CC.COUNTER_TEMPLATE_NAME,
CC.NAME COUNTER_NAME,
CC.UOM_CODE,
NVL((SELECT CCR.NET_READING
FROM CSI_COUNTER_READINGS CCR
WHERE CCR.COUNTER_VALUE_ID = CC.CTR_VAL_MAX_SEQ_NO
AND NVL(CCR.DISABLED_FLAG,'N') = 'N'), 0) NET_READING
FROM CSI_COUNTER_ASSOCIATIONS CCA,
CSI_COUNTERS_VL CC,
AHL_APPLICABLE_INSTANCES APPL_INST
WHERE CCA.COUNTER_ID = CC.COUNTER_ID
AND CCA.SOURCE_OBJECT_CODE = 'CP'
AND CCA.SOURCE_OBJECT_ID = APPL_INST.CSI_ITEM_INSTANCE_ID;
SELECT ASM.SCHEDULED_MATERIAL_ID,
CNTR.COUNTER_ID,
CNTR.NAME COUNTER_NAME,
UOM.UOM_CODE,
BG.COUNTER_VALUE BG_VALUE,
CASE MR.IMPLEMENT_STATUS_CODE
WHEN 'SOFT_LIMIT'
THEN (BG.COUNTER_VALUE - INTV.INTERVAL_VALUE)
ELSE (INTV.INTERVAL_VALUE - BG.COUNTER_VALUE) -- Mandatory or Optional Implement
END MAX_COUNTER_VALUE,
MR.TITLE MR_TITLE,
MR.MR_HEADER_ID MR_HEADER_ID
FROM AHL_SCHEDULE_MATERIALS ASM,
AHL_MR_INTERVALS INTV,
CS_COUNTERS CNTR,
MTL_UNITS_OF_MEASURE_VL UOM,
AHL_BUILD_GOALS BG,
AHL_VISITS_B AVB,
AHL_MR_HEADERS_B MR,
AHL_MR_EFFECTIVITIES EFF,
AHL_MC_PATH_POSITION_NODES NODE,
AHL_MC_HEADERS_B MC,
AHL_UNIT_CONFIG_HEADERS UCH
WHERE INTV.COUNTER_ID = CNTR.COUNTER_ID
AND UOM.UOM_CODE = CNTR.UOM_CODE
AND BG.VISIT_ID = AVB.VISIT_ID
AND BG.UOM_CODE = CNTR.UOM_CODE
AND AVB.item_instance_id = UCH.csi_item_instance_id
AND INTV.MR_EFFECTIVITY_ID = EFF.MR_EFFECTIVITY_ID
AND EFF.MR_HEADER_ID = MR.MR_HEADER_ID
AND ( MR.IMPLEMENT_STATUS_CODE = DECODE(CONSIDER_HARD_LIMIT, 'Y', 'MANDATORY', 'X')
OR MR.IMPLEMENT_STATUS_CODE = DECODE(CONSIDER_HARD_LIMIT, 'Y', 'OPTIONAL_IMPLEMENT', 'X')
OR MR.IMPLEMENT_STATUS_CODE = DECODE(CONSIDER_SOFT_LIMIT, 'Y', 'SOFT_LIMIT', 'X') )
AND MR.SERVICE_TYPE_CODE = 'ASR'
AND MR.MR_STATUS_CODE = 'COMPLETE'
AND NVL(MR.EFFECTIVE_TO,sysdate+1) > (sysdate)
AND EFF.RELATIONSHIP_ID = NODE.PATH_POSITION_ID
-- Filter for MC Position + MC Item combination
AND NVL(EFF.INVENTORY_ITEM_ID, 1) = NVL2(EFF.INVENTORY_ITEM_ID, ASM.INVENTORY_ITEM_ID, 1)
AND ASM.MC_HEADER_ID = MC.MC_HEADER_ID
AND ASM.POSITION_KEY = NODE.POSITION_KEY
AND MC.MC_ID = NODE.MC_ID
AND NVL(NODE.VERSION_NUMBER,MC.VERSION_NUMBER) = MC.VERSION_NUMBER
AND AVB.VISIT_ID = ASM.VISIT_ID
AND NODE.sequence = (SELECT MAX(PPN.sequence)
FROM AHL_MC_PATH_POSITION_NODES PPN
WHERE PPN.PATH_POSITION_ID = NODE.PATH_POSITION_ID)
AND INTV.INTERVAL_VALUE IN (SELECT MIN(THR.INTERVAL_VALUE)
FROM AHL_MR_INTERVALS THR, CS_COUNTERS CS
WHERE THR.MR_EFFECTIVITY_ID = INTV.MR_EFFECTIVITY_ID
AND CS.COUNTER_ID = THR.COUNTER_ID
GROUP BY CS.UOM_CODE)
--PC Node effectivities
AND ( --Unit type PC assoc
NVL2(EFF.pc_node_id,
(SELECT COUNT('X')
FROM ahl_pc_nodes_b B
WHERE B.pc_node_id = EFF.pc_node_id
START WITH B.pc_node_id
IN (select pc_node_id
from ahl_pc_associations unit
where unit.association_type_flag = 'U'
and unit.unit_item_id = UCH.unit_config_header_id)
CONNECT BY B.pc_node_id = PRIOR B.parent_node_id),
1) > 0
OR
--Item type PC assoc
NVL2(EFF.pc_node_id,
(SELECT COUNT('X')
FROM ahl_pc_nodes_b B
WHERE B.pc_node_id = EFF.pc_node_id
START WITH B.pc_node_id
IN (select pc_node_id
from ahl_pc_associations itm, csi_item_instances csi
where itm.association_type_flag = 'I'
and itm.unit_item_id = csi.inventory_item_id
and csi.instance_id = UCH.csi_item_instance_id)
CONNECT BY B.pc_node_id = PRIOR B.parent_node_id),
1) > 0)
-- Fleet based effecctivities
AND NVL2(EFF.fleet_header_id,
Ahl_Ump_ProcessUnit_Pvt.get_Fleet_From_Unit_Asso(UCH.unit_config_header_id,sysdate,null),
1) = NVL(EFF.fleet_header_id, 1)
AND ASM.SCHEDULED_MATERIAL_ID = C_ASM_ID;
' INSERT INTO AHL_APPLICABLE_INSTANCES(CSI_ITEM_INSTANCE_ID, POSITION_ID)' ||
' SELECT UNIQUE INST.instance_id, -1' ||
' FROM mtl_system_items_b ITEMS,' ||
' csi_item_instances INST,' ||
' mtl_serial_numbers SERIALS' ||
' WHERE ITEMS.inventory_item_id = INST.inventory_item_id' ||
' AND ITEMS.organization_id = INST.inv_master_organization_id' ||
' AND INST.serial_number = SERIALS.serial_number' ||
' AND INST.inventory_item_id = SERIALS.inventory_item_id' ||
' AND EXISTS (SELECT 1' ||
' FROM mtl_parameters mp, inv_organization_info_v io' ||
' WHERE mp.master_organization_id = ITEMS.organization_id' ||
' AND mp.organization_Id = io.organization_id' ||
' AND NVL(io.operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id())';
DELETE FROM ahl_applicable_instances;
SELECT ASM.inventory_item_id,
ASM.organization_id,
ASM.mc_header_id,
ASM.position_key,
AHL_UTIL_UC_PKG.GET_UC_HEADER_ID(NVL(TSK.INSTANCE_ID,VST.ITEM_INSTANCE_ID)) UC_HEADER_ID
INTO l_inventory_item_id,
l_org_id,
l_mc_header_id,
l_pos_key,
l_uc_header_id
FROM ahl_schedule_materials ASM,
ahl_visits_b VST,
ahl_visit_tasks_b TSK
WHERE ASM.visit_id = VST.visit_id
AND ASM.visit_task_id = TSK.visit_task_id
AND ASM.scheduled_material_id = p_demand_Record.Scheduled_Material_ID;
SELECT relationship_id
INTO l_relationship_id
FROM AHL_SCHEDULE_MATERIALS
WHERE scheduled_material_id = p_demand_Record.Scheduled_Material_ID;
|| ' (SELECT party_id FROM hz_parties'
|| ' WHERE party_name LIKE '''
|| p_demand_Record.Item_Owner || ''')';
|| ' (SELECT party_id FROM hz_parties'
|| ' WHERE party_name = '''
|| p_demand_Record.Item_Owner || ''')';
|| ' (SELECT UNIQUE NVL(MSN.SERIAL_NUMBER, NVL(WO_RESERV.SERIAL_NUMBER, ISO_RESERV.SERIAL_NUMBER))'
|| ' FROM MTL_RESERVATIONS MR,'
|| ' MTL_SERIAL_NUMBERS MSN,'
|| ' (SELECT CSIII.serial_number,'
|| ' WO.WIP_ENTITY_ID'
|| ' FROM AHL_WORKORDERS WO,'
|| ' AHL_VISIT_TASKS_B ATSK,'
|| ' AHL_VISITS_B AVST,'
|| ' CSI_ITEM_INSTANCES CSIII'
|| ' WHERE WO.visit_task_id = ATSK.visit_task_id'
|| ' AND WO.STATUS_CODE NOT IN (7, 22, 17, 12, 18, 21, 4, 5)'
|| ' AND ATSK.return_to_supply_flag = ''Y'''
|| ' AND ATSK.visit_id = AVST.visit_id'
|| ' AND CSIII.instance_id = NVL(ATSK.instance_id, AVST.item_instance_id)) WO_RESERV,'
|| ' (SELECT CSIII.SERIAL_NUMBER, CSIII.INVENTORY_ITEM_ID, PRHA.REQUISITION_HEADER_ID, AMTI.RESERVATION_ID'
|| ' FROM PO_REQUISITION_HEADERS_ALL PRHA,'
|| ' OE_ORDER_HEADERS_ALL OOHA,'
|| ' MTL_SERIAL_NUMBERS MSN,'
|| ' CSI_ITEM_INSTANCES CSIII,'
|| ' MTL_SERIAL_NUMBERS_TEMP MSNT,'
|| ' WSH_DELIVERY_DETAILS WDD,'
|| ' WSH_SERIAL_NUMBERS WSN,'
|| ' AHL_MM_TXN_INTERFACE AMTI'
|| ' WHERE PRHA.REQUISITION_HEADER_ID = OOHA.SOURCE_DOCUMENT_ID'
|| ' AND OOHA.HEADER_ID = WDD.SOURCE_HEADER_ID'
|| ' AND WDD.RELEASED_STATUS <> ''B'' '
|| ' AND WDD.TRANSACTION_TEMP_ID = MSNT.TRANSACTION_TEMP_ID (+)'
|| ' AND WDD.DELIVERY_DETAIL_ID = WSN.DELIVERY_DETAIL_ID (+)'
|| ' AND CSIII.SERIAL_NUMBER = NVL(WDD.SERIAL_NUMBER, '
|| ' NVL(MSNT.FM_SERIAL_NUMBER, NVL(WSN.FM_SERIAL_NUMBER, AMTI.SERIAL_NUMBER)))'
|| ' AND CSIII.SERIAL_NUMBER = MSN.SERIAL_NUMBER'
|| ' AND AMTI.TXN_SOURCE_TYPE = ''ISO'') ISO_RESERV'
|| ' WHERE MR.DEMAND_SOURCE_TYPE_ID = 5'
|| ' AND MR.EXTERNAL_SOURCE_CODE = ''AHL'' '
|| ' AND MR.inventory_item_id = ITEMS.inventory_item_id'
|| ' AND MR.RESERVATION_ID = MSN.RESERVATION_ID(+)'
|| ' AND NVL2(MSN.RESERVATION_ID, MR.SUPPLY_SOURCE_TYPE_ID, 1) = NVL2(MSN.RESERVATION_ID, 13, 1)'
|| ' AND MR.SUPPLY_SOURCE_HEADER_ID = WO_RESERV.WIP_ENTITY_ID(+)'
|| ' AND NVL2(WO_RESERV.WIP_ENTITY_ID, MR.SUPPLY_SOURCE_TYPE_ID, 1) = NVL2(WO_RESERV.WIP_ENTITY_ID, 5, 1)'
|| ' AND MR.RESERVATION_ID = ISO_RESERV.RESERVATION_ID(+)'
|| ' AND MR.INVENTORY_ITEM_ID = ISO_RESERV.INVENTORY_ITEM_ID(+)'
|| ' AND MR.SUPPLY_SOURCE_HEADER_ID = ISO_RESERV.REQUISITION_HEADER_ID(+)'
|| ' AND NVL2(ISO_RESERV.REQUISITION_HEADER_ID, MR.SUPPLY_SOURCE_TYPE_ID, 1) = NVL2(ISO_RESERV.REQUISITION_HEADER_ID, 7, 1)';
l_base_query := l_base_query || ' AND EXISTS (SELECT 1'
|| ' FROM AHL_WORKORDERS WO, AHL_VISIT_TASKS_B TSK, AHL_VISITS_B VST'
|| ' WHERE WO.visit_task_id = TSK.visit_task_id'
|| ' AND WO.STATUS_CODE NOT IN (7, 22, 17, 12, 18, 21, 4, 5)'
|| ' AND TSK.return_to_supply_flag = ''Y'' '
|| ' AND TSK.visit_id = VST.visit_id'
|| ' AND NVL(TSK.instance_id, VST.item_instance_id) = INST.instance_id';
|| ' (SELECT ORGANIZATION_ID FROM HR_ORGANIZATION_UNITS'
|| ' WHERE NAME LIKE '''
|| p_demand_Record.WO_Org || ''')';
|| ' (SELECT 1'
|| ' FROM MTL_RESERVATIONS RESERV, CSI_ITEM_INSTANCES CSIII'
|| ' WHERE CSIII.instance_id = NVL(TSK.instance_id, VST.item_instance_id)'
|| ' AND RESERV.SUPPLY_SOURCE_HEADER_ID = WO.WIP_ENTITY_ID'
|| ' AND RESERV.SUPPLY_SOURCE_TYPE_ID = 5)';
|| ' (SELECT item_instance_id'
|| ' FROM ahl_visits_b'
|| ' WHERE item_instance_id IS NOT NULL'
|| ' AND status_code IN (''RELEASED'',''PARTIALLY RELEASED'')';
|| ' (SELECT ORGANIZATION_ID FROM HR_ORGANIZATION_UNITS'
|| ' WHERE NAME LIKE '''
|| p_demand_Record.Visit_Org || ''')';
|| ' (SELECT CSI_ITEM_INSTANCE_ID FROM AHL_UNIT_CONFIG_HEADERS'
|| ' WHERE NAME LIKE ''' || p_demand_Record.Visit_Unit || ''')';
|| ' (SELECT CSI_ITEM_INSTANCE_ID FROM AHL_UNIT_CONFIG_HEADERS'
|| ' WHERE NAME = ''' || p_demand_Record.Visit_Unit || ''')';
|| ' SELECT cis.instance_id'
|| ' FROM csi_item_instances cis, csi_ii_relationships cir, base_inst'
|| ' WHERE cis.instance_id = cir.subject_id'
|| ' AND cir.relationship_type_code = ''COMPONENT-OF'''
|| ' AND NVL(cir.active_start_date,sysdate) <= sysdate'
|| ' AND NVL(cir.active_end_date,sysdate) >= sysdate'
|| ' START WITH cir.object_id = base_inst.item_instance_id'
|| ' CONNECT BY cir.object_id = prior cir.subject_id'
|| ' UNION'
|| ' SELECT cis.instance_id'
|| ' FROM csi_item_instances cis, base_inst'
|| ' WHERE cis.instance_id = base_inst.item_instance_id)';
SELECT NVL(MAX(UA.COUNTER_VALUE), 0) COUNTER_VALUE
INTO last_acc_value
FROM AHL_UNIT_ACCOMPLISHMNTS UA,
CSI_COUNTERS_VL CS
WHERE UA.COUNTER_ID = CS.COUNTER_ID
AND UA.UNIT_EFFECTIVITY_ID = l_unit_effectivity_id
AND CS.UOM_CODE = l_eff_mr_tbl(i).UOM_CODE;
DELETE AHL_APPLICABLE_INSTANCES
WHERE CSI_ITEM_INSTANCE_ID = l_appl_inst_tbl(j).ITEM_INSTANCE_ID;
SELECT instance_id FROM mrp_ap_apps_instances;
SELECT primary_uom_code,
concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = c_inv_item_id
AND organization_id = c_org_id;
SELECT Meaning
FROM MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'MTL_DEMAND_INTERFACE_ERRORS'
AND LOOKUP_CODE = C_LOOKUP_CODE;
SELECT mrp_atp_schedule_temp_s.nextval INTO l_session_id FROM dual;
SELECT MRP_ATP_SCHEDULE_TEMP_S.NEXTVAL
INTO l_session_id
FROM dual;
SELECT SCHEDULED_MATERIAL_ID
FROM AHL_SCHEDULE_MATERIALS
WHERE VISIT_ID = L_VISIT_ID;