[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT msi.serial_number_control_code, msi.lot_control_code, msi.primary_uom_code,
msi.inventory_item_id,mis.subinventory_code,cpt.rcv_into_org,cpt.rcv_into_ou,
msi.primary_unit_of_measure
INTO l_serial_control_flag,l_lot_control_flag,l_primary_uom,
l_inventory_item_id,l_subinventory,l_to_org_id,l_destn_ou,l_uom
FROM mtl_system_items_b msi, mtl_item_sub_defaults mis,csd_product_transactions cpt
WHERE cpt.product_transaction_id = p_product_txn_id
AND cpt.rcv_into_org = msi.organization_id
AND cpt.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = mis.organization_id(+)
AND msi.inventory_item_id = mis.inventory_item_id(+);
SELECT cpt.ship_from_org,prl.requisition_line_id,prl.deliver_to_location_id,
NVL(p_receiving_subinv,l_subinventory)
INTO l_from_org,l_requisition_line_id,l_deliver_to_location_id,l_subinventory
FROM csd_product_transactions cpt, po_requisition_lines_all prl
WHERE cpt.product_transaction_id = p_product_txn_id
AND cpt.req_header_id = prl.requisition_header_id
AND NVL(cpt.req_line_id,prl.requisition_line_id) = prl.requisition_line_id;
SELECT rsh.shipment_header_id, rsl.shipment_line_id,rsh.shipment_num,rsh.shipped_date,rsl.quantity_shipped
BULK COLLECT INTO l_shipment_header_id,l_shipment_line_id,l_shipment_num,l_shipped_date,l_quantity
FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl
WHERE rsl.requisition_line_id = l_requisition_line_id
AND rsh.shipment_header_id = rsl.shipment_header_id
AND nvl(rsl.quantity_received,0) = 0
AND rsl.mmt_transaction_id IN (SELECT mmt.transaction_id
FROM csd_product_transactions cpt,mtl_material_transactions mmt
WHERE cpt.delivery_detail_id = mmt.picking_line_id
AND cpt.product_transaction_id = p_product_txn_id
UNION ALL
SELECT mmt.transaction_id
FROM wsh_delivery_details wdd, csd_product_transactions cpt,mtl_material_transactions mmt
WHERE cpt.delivery_detail_id = wdd.split_from_delivery_detail_id
AND cpt.order_header_id = wdd.source_header_id
AND wdd.source_code = 'OE'
AND cpt.product_transaction_id = p_product_txn_id
AND wdd.delivery_detail_id = mmt.picking_line_id
AND NOT EXISTS(
SELECT 'exists'
FROM csd_product_transactions cpt1
WHERE wdd.delivery_detail_id = cpt1.delivery_detail_id
AND cpt.order_header_id = cpt1.order_header_id
)
);
Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Inserting into rcv_headers_interface');
SELECT rcv_interface_groups_s.NEXTVAL INTO x_request_group_id FROM dual;
INSERT INTO rcv_headers_interface (
header_interface_id,
group_id,
ship_to_organization_id,
expected_receipt_date, last_update_date,
last_updated_by, last_update_login, creation_date,
created_by, validation_flag, processing_status_code,
receipt_source_code, transaction_type,
shipped_Date,
shipment_num)
VALUES (rcv_headers_interface_s.NEXTVAL,
x_request_group_id,
l_to_org_id,
SYSDATE,SYSDATE,
fnd_global.user_id,fnd_global.login_id,SYSDATE,
fnd_global.user_id,l_validation_flag,l_process_sts_pending,
'INTERNAL ORDER',l_txn_type_new,
l_shipped_date(k),
l_shipment_num(k))
RETURNING header_interface_id
INTO l_hdr_interface_id(k);
Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Inserting into rcv_transactions_interface');
-- 3. insert into rcv transactions interface table.
l_intf_txn_id.EXTEND(l_shipment_header_id.COUNT);
INSERT INTO rcv_transactions_interface
(interface_transaction_id,
header_interface_id,
GROUP_ID,
transaction_date,
quantity,
unit_of_measure,
item_id,
item_revision,
to_organization_id,
ship_to_location_id,
subinventory,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
validation_flag,
source_document_code,
interface_source_code,
auto_transact_code,
receipt_source_code,
transaction_type,
processing_status_code,
processing_mode_code,
transaction_status_code,
category_id,
uom_code,
employee_id,
primary_quantity,
primary_unit_of_measure,
routing_header_id,
routing_step_id,
inspection_status_code,
destination_type_code,
expected_receipt_date,
destination_context,
use_mtl_lot,
use_mtl_serial,
source_doc_quantity,
source_doc_unit_of_measure,
requisition_line_id,
shipped_date,
shipment_num,
from_organization_id,
locator_id,
deliver_to_location_id,
shipment_header_id,
shipment_line_id,
org_id
)
VALUES (rcv_transactions_interface_s.NEXTVAL,
l_hdr_interface_id(i),
x_request_group_id,
SYSDATE,
l_quantity(i),
l_uom,
l_inventory_item_id,
null,
l_to_org_id,
null,
l_subinventory,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
'Y'
, 'REQ'
, 'RCV'
, 'DELIVER'
, 'INTERNAL ORDER'
, 'RECEIVE'
, 'PENDING'
, 'ONLINE'
, 'PENDING'
, null
, l_primary_uom
, l_emp_id
,l_quantity(i)
,l_uom
, 1
, 1
, 'NOT INSPECTED'
, 'INVENTORY'
, SYSDATE
, 'INVENTORY'
, l_lot_control_flag
, l_serial_control_flag
, l_quantity(i)
, l_uom
, l_requisition_line_id
, l_shipped_date(i)
, l_shipment_num(i)
, l_from_org
, null
, l_deliver_to_location_id
, l_shipment_header_id(i)
, l_shipment_line_id(i)
, l_destn_ou
)
RETURNING interface_transaction_id
INTO l_intf_txn_id(i);
SELECT wsn.fm_serial_number,
wsn.to_serial_number
BULK COLLECT INTO
l_fm_serial_num_tbl,
l_to_serial_num_tbl
FROM wsh_delivery_details wdd,
wsh_serial_numbers wsn,
wsh_delivery_assignments wda
WHERE wda.delivery_id = to_number(l_shipment_num(i))
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.source_code = 'OE'
AND wdd.delivery_detail_id = wsn.delivery_detail_id;
-- insert into mtl serial number interface.
IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Inserting into mtl_serial_numbers_interface total of '||l_fm_serial_num_tbl.COUNT||' records');
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,
product_transaction_id,
product_code
)
VALUES(l_intf_txn_id(i),'CSD',
1,SYSDATE,fnd_global.user_id,
SYSDATE,fnd_global.user_id,fnd_global.login_id,
l_fm_serial_num_tbl(j),
l_to_serial_num_tbl(j),
1,
l_intf_txn_id(i),
'RCV');
INSERT INTO csd_product_transactions
(product_transaction_id,
repair_line_id,
req_header_id,
order_header_id,
order_line_id,
exp_quantity,
inventory_item_id,
ship_from_ou,
ship_from_org,
rcv_into_ou,
rcv_into_org,
creation_date,
last_update_date,
last_update_login,
created_by,
last_updated_by,
quantity_available,
delivery_detail_id,
object_version_number,
req_line_id
)
SELECT csd_product_transactions_s1.nextval,
repair_line_id,
req_header_id,
order_header_id,
order_line_id,
exp_quantity,
inventory_item_id,
ship_from_ou,
ship_from_org,
rcv_into_ou,
rcv_into_org,
sysdate creation_date,
sysdate last_update_date,
fnd_global.login_id last_update_login,
fnd_global.user_id created_by,
fnd_global.user_id last_updated_by,
(NVL(quantity_available,exp_quantity) - quantity_picked) available_quantity,
p_delivery_detail_id,
to_number('1'),
req_line_id
FROM csd_product_transactions
WHERE order_header_id = p_order_header_id
AND delivery_detail_id = p_delivery_detail_id_new;
UPDATE csd_product_transactions SET quantity_available = 0
WHERE delivery_detail_id = p_delivery_detail_id_new;
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_information3 --org_information1
AND organization_id = p_destination_ous(1)
AND org_information_context = 'Operating Unit Information'; --'Accounting Information';
SELECT NVL(p_quantitys(i),cpt.exp_quantity),cpt.inventory_item_id,msi.description,msi.primary_uom_code
INTO l_quantitys(i),l_inventory_item_ids(i),l_item_descriptions(i),l_uom_codes(i)
FROM csd_product_transactions cpt,mtl_system_items_b msi
WHERE cpt.product_transaction_id = p_product_txn_ids(i)
AND cpt.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = p_destination_orgs(i);
SELECT material_account
INTO l_material_accounts(i)
FROM mtl_parameters
WHERE organization_id = p_destination_orgs(i);
-- step 4. insert the records into requisitions interface.
IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Before inserting into requisitions interface.');
INSERT INTO po_requisitions_interface_all (
interface_source_code,
destination_type_code,
authorization_status,
preparer_id, -- person id of the user name
quantity,
destination_organization_id,
deliver_to_location_id,
deliver_to_requestor_id,
source_type_code,
category_id,
item_description,
uom_code,
unit_price,
need_by_date,
wip_entity_id,
wip_operation_seq_num,
charge_account_id,
variance_account_id,
item_id,
wip_resource_seq_num,
suggested_vendor_id,
suggested_vendor_name,
suggested_vendor_site,
suggested_vendor_phone,
suggested_vendor_item_num,
currency_code,
project_id,
task_id,
project_accounting_context,
last_updated_by,
last_update_date,
created_by,
creation_date,
org_id,
reference_num,
interface_source_line_id,
source_organization_id,
source_subinventory,
destination_subinventory)
VALUES (
'CSD',
'INVENTORY',
'APPROVED',
l_person_id,
l_quantitys(i),
p_destination_orgs(i),
p_destination_loc_ids(i),
l_person_id,
'INVENTORY',
null,
l_item_descriptions(i),
l_uom_codes(i),
null,
p_need_by_date,
null,
null,
l_material_accounts(i),
null,
l_inventory_item_ids(i),
null,
null,
null,
null,
null,
null,
l_currency_code,
null,
null,
null,
l_user_id,
sysdate,
l_user_id,
sysdate,
p_destination_ous(i),
null,
p_product_txn_ids(1),
p_source_orgs(i),
l_source_subinvs(i),
l_destination_subinvs(i)
);
SELECT segment1,requisition_header_id
INTO x_requisition,x_requisition_id
FROM po_requisition_headers_all
WHERE interface_source_line_id = p_product_txn_ids(1);
-- update the csd_product_transactions.
-- Create Internal Order po program.
-- order import program.
-- update csd_product_transactions.
-- Step 1. Create internal requisition.
IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling CSD_INTERNAL_ORDERS_PVT.create_internal_requisition');
-- update the csd_product_transactions.
-- Create Internal Order po program.
-- order import program.
-- update csd_product_transactions.
-- Step 1. Create internal requisition.
IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling CSD_INTERNAL_ORDERS_PVT.create_internal_requisition');
-- Step 2. Update the product transaction table with the requisition id.
IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Requisition created.Update prod txn tbl with req id='||x_requisition_id);
UPDATE csd_product_transactions SET req_header_id = x_requisition_id
WHERE product_transaction_id IN (SELECT * FROM TABLE(CAST(p_product_txn_ids AS JTF_NUMBER_TABLE)));
-- Step 6. Get the ISO order header id and line id and update csd_product_transactions.
IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Getting ISO created and updating prod txn tbl.');
FOR i IN (SELECT ooh.header_id,
ool.line_id,
ool.inventory_item_id,
pol.requisition_line_id,
wdd.delivery_detail_id
FROM oe_order_headers_all ooh,
oe_order_lines_all ool,
po_requisition_lines_all pol,
wsh_delivery_details wdd
WHERE ooh.orig_sys_document_ref = x_requisition_number
AND ooh.source_document_id = x_requisition_id
AND ooh.header_id = ool.header_id
AND pol.requisition_header_id = x_requisition_id
AND pol.requisition_line_id = ool.source_document_line_id
AND wdd.source_header_id = ooh.header_id
AND wdd.source_line_id = ool.line_id
AND wdd.source_code = 'OE'
)
LOOP
UPDATE csd_product_transactions SET req_line_id = i.requisition_line_id,order_header_id = i.header_id,
order_line_id = i.line_id,delivery_detail_id = i.delivery_detail_id
WHERE req_header_id = x_requisition_id
AND inventory_item_id = i.inventory_item_id;
SELECT line_id
INTO l_order_rec.order_line_id
FROM oe_order_lines_all
WHERE split_from_line_id = p_order_line_id
AND header_id = p_order_header_id;
SELECT wdd.released_status,wdd.picked_quantity,wdd.delivery_detail_id,NVL(cpt.quantity_available,cpt.exp_quantity)
INTO l_dummy,l_picked_quantity,l_delivery_detail_id,l_requested_quantity
FROM wsh_delivery_details wdd,
csd_product_transactions cpt
WHERE cpt.product_transaction_id = p_product_txn_id
AND cpt.delivery_detail_id = wdd.delivery_detail_id;
SELECT delivery_detail_id,picked_quantity
INTO l_delivery_detail_id_new,l_picked_quantity
FROM wsh_delivery_details wdd
WHERE wdd.split_from_delivery_detail_id = l_delivery_detail_id
AND released_status = 'Y';
UPDATE csd_product_transactions SET delivery_detail_id = l_delivery_detail_id_new,quantity_picked = l_picked_quantity
WHERE order_header_id = p_order_header_id
AND delivery_detail_id = l_delivery_detail_id;
UPDATE csd_product_transactions SET quantity_available = 0
WHERE order_header_id = p_order_header_id
AND delivery_detail_id = l_delivery_detail_id;
SELECT delivery_detail_id
INTO l_delivery_detail_id_new
FROM wsh_delivery_details wdd
WHERE wdd.split_from_delivery_detail_id = l_delivery_detail_id
AND released_status = 'B';
UPDATE csd_product_transactions SET quantity_picked = l_picked_quantity
WHERE order_header_id = p_order_header_id
AND delivery_detail_id = l_delivery_detail_id;
UPDATE csd_product_transactions SET quantity_available = 0, quantity_picked = l_picked_quantity
WHERE order_header_id = p_order_header_id
AND delivery_detail_id = l_delivery_detail_id;
-- update the product transaction record. Mark it as picked.
IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Updating ISO as released.');
/*UPDATE csd_product_transactions SET release_sales_order_flag = 'Y',
prod_txn_status = 'RELEASED'
WHERE product_transaction_id = p_product_txn_id;*/
UPDATE csd_product_transactions SET release_sales_order_flag = 'Y',
prod_txn_status = 'RELEASED'
WHERE order_header_id = p_order_header_id
AND quantity_picked IS NOT NULL;
SELECT msi.serial_number_control_code,
msi.revision_qty_control_code,
msi.lot_control_code,
msi.concatenated_segments
INTO l_serial_control_flag,
l_rev_control_flag,
l_lot_control_flag,
l_item_name
FROM mtl_system_items_kfv msi, csd_product_transactions cpt
WHERE cpt.product_transaction_id = p_product_txn_id
AND cpt.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = fnd_profile.value('CSD_DEF_REP_INV_ORG');
SELECT wdd.delivery_detail_id,wda.delivery_id
INTO l_delivery_detail_id,p_delivery_id
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd,
csd_product_transactions cpt
WHERE cpt.product_transaction_id = p_product_txn_id
AND wdd.delivery_detail_id = cpt.delivery_detail_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.released_status = 'Y';
SELECT wdd.delivery_detail_id,wda.delivery_id
INTO l_delivery_detail_id,p_delivery_id
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd,
csd_product_transactions cpt
WHERE cpt.product_transaction_id = p_product_txn_id
AND wdd.split_from_delivery_detail_id = cpt.delivery_detail_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.released_status = 'Y'
AND wdd.source_code = 'OE';
-- call the update attributes API to update the shipping attributes.
changed_attributes(1).delivery_detail_id := l_delivery_detail_id;
fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Calling WSH_DELIVERY_DETAILS_PUB.Update_Shipping_Attributes');
WSH_DELIVERY_DETAILS_PUB.Update_Shipping_Attributes(
p_api_version_number => 1.0,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_changed_attributes => changed_attributes,
p_source_code => source_code,
p_serial_range_tab => l_serial_num_range_tab);
fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Update shipping attributes failed '||x_msg_data);
--dbms_output.put_line('Update shipping attributes failed '||x_msg_data);
fnd_message.set_name('CSD','CSD_UPDATE_SHIPPING_FAILED');
select organization_id
into l_delivery_org_id
from wsh_delivery_details
where delivery_detail_id = l_delivery_detail_id;
SELECT 'N'
INTO l_shipped_flag
FROM wsh_delivery_details wdd
WHERE wdd.delivery_detail_id = l_delivery_detail_id
AND wdd.released_status <> 'C';
-- update the csd_product_transactions table with the shipped quantity information.
IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Updating quantity shipped in prod txn tbl');
SELECT shipped_quantity
INTO l_quantity_shipped
FROM wsh_delivery_details
WHERE delivery_detail_id = l_delivery_detail_id;
UPDATE csd_product_transactions SET quantity_shipped =
(l_quantity_shipped + nvl(quantity_shipped,0) )
WHERE order_header_id = p_order_header_id
AND delivery_detail_id = (SELECT delivery_detail_id
FROM csd_product_transactions
WHERE product_transaction_id = p_product_txn_id
); --p_product_txn_id;
SELECT serial_number
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inv_item_id
AND current_organization_id = p_current_org_id
AND current_status = 3 -- resides in stores.
AND current_subinventory_code = nvl(p_subinventory,current_subinventory_code)
AND serial_number IN (SELECT * FROM TABLE(CAST(p_sn_range_tbl as JTF_VARCHAR2_TABLE_100)));
SELECT nvl(quantity_shipped,0),delivery_detail_id
INTO l_dummy,l_delivery_detail_id
FROM csd_product_transactions
WHERE product_transaction_id = p_product_txn_id
AND order_header_id = p_order_header_id
AND order_line_id = p_order_line_id;
SELECT SUM(rt.quantity)
INTO l_received_quantity
FROM csd_product_transactions cpt,
po_requisition_lines_all prl,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
rcv_transactions rt
WHERE cpt.product_transaction_id = p_product_txn_id
AND cpt.req_header_id = prl.requisition_header_id
AND rsl.requisition_line_id = prl.requisition_line_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rt.transaction_type = 'RECEIVE'
AND rt.shipment_header_id = rsh.shipment_header_id
AND rsl.mmt_transaction_id IN (SELECT mmt.transaction_id
FROM csd_product_transactions cpt,mtl_material_transactions mmt
WHERE cpt.delivery_detail_id = mmt.picking_line_id
AND cpt.product_transaction_id = p_product_txn_id
UNION ALL
SELECT mmt.transaction_id
FROM wsh_delivery_details wdd, csd_product_transactions cpt,mtl_material_transactions mmt
WHERE cpt.delivery_detail_id = wdd.split_from_delivery_detail_id
AND cpt.order_header_id = wdd.source_header_id
AND wdd.source_code = 'OE'
AND cpt.product_transaction_id = p_product_txn_id
AND wdd.delivery_detail_id = mmt.picking_line_id
AND NOT EXISTS(
SELECT 'exists'
FROM csd_product_transactions cpt1
WHERE wdd.delivery_detail_id = cpt1.delivery_detail_id
AND cpt.order_header_id = cpt1.order_header_id
)
);
-- update the received quantity on product transactions.
IF (l_dummy - l_received_quantity) >= 0
THEN
UPDATE csd_product_transactions SET quantity_received = l_received_quantity
WHERE order_header_id = p_order_header_id
AND delivery_detail_id = l_delivery_detail_id; --p_product_txn_id;
-- update the quantity received field in csd_product_transactions with receipt quantity.
IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Updating csd_product_transactions.received_quantity');
SELECT SUM(quantity)
INTO l_received_quantity
FROM rcv_transactions
WHERE group_id = x_request_group_id
AND transaction_type = 'RECEIVE';
UPDATE csd_product_transactions SET quantity_received = (
l_received_quantity + nvl(quantity_received,0))
WHERE order_header_id = p_order_header_id
AND delivery_detail_id = l_delivery_detail_id; --product_transaction_id = p_product_txn_id;
-- update the product transaction status.
UPDATE csd_product_transactions cpt
SET prod_txn_status =
(SELECT 'RECEIVED'
FROM csd_product_transactions
WHERE product_transaction_id = cpt.product_transaction_id
AND NVL(quantity_shipped,0) = exp_quantity
UNION ALL
SELECT 'RECEIVED'
FROM csd_product_transactions
WHERE product_transaction_id = cpt.product_transaction_id
AND NVL(quantity_shipped,0) < exp_quantity
AND quantity_shipped > 0
)
WHERE order_header_id = p_order_header_id
AND delivery_detail_id = l_delivery_detail_id; --product_transaction_id = p_product_txn_id;
SELECT wdd.released_status,wdd.requested_quantity,wdd.requested_quantity_uom,wdd.delivery_detail_id
INTO l_delivery_status,l_requested_qty,l_transaction_uom,l_delivery_detail_id
FROM wsh_delivery_details wdd,csd_product_transactions cpt
WHERE wdd.delivery_detail_id = cpt.delivery_detail_id
AND product_transaction_id = p_product_txn_id
AND wdd.source_code = 'OE';
UPDATE csd_product_transactions SET quantity_available = l_requested_qty
WHERE delivery_detail_id = l_delivery_detail_id;
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_picking_subinv, pick_from_subinventory),
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 = p_picking_rule_id;
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,
csd_product_transactions cpt
WHERE wdd.delivery_detail_id = cpt.delivery_detail_id
AND cpt.product_transaction_id = p_product_txn_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(+);
SELECT wdd.move_order_line_id
INTO l_move_order_line_id
FROM wsh_delivery_details wdd,
csd_product_transactions cpt
WHERE wdd.delivery_detail_id = cpt.delivery_detail_id
AND cpt.product_transaction_id = p_product_txn_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 (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
fnd_log.string(G_LEVEL_PROCEDURE, lc_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 (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Marking the newly entered serial ranges');
p_update_reservation => fnd_api.g_true,
success => x_success
);
fnd_log.string(G_LEVEL_PROCEDURE, lc_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_picking_qty < l_requested_qty
THEN
IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Updating the existing allocations for temp id ='||l_mmtt_temp_id);
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, lc_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;
UPDATE csd_product_transactions SET quantity_picked = l_picked_qty,
delivery_detail_id = l_delivery_detail_id_new
WHERE order_header_id = p_order_header_id
AND delivery_detail_id = l_delivery_detail_id;
SELECT 'Y'
INTO l_dummy
FROM csd_product_transactions
WHERE order_header_id = p_order_header_id
AND product_transaction_id = p_product_txn_id
AND quantity_picked < quantity_available
AND quantity_picked IS NOT NULL;
-- not a partial pick. The quantity available to pick needs to be updated to 0.
IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Successfully picked the available quantity. Updating the quantity available to zero for this line');
UPDATE csd_product_transactions SET quantity_available = 0
WHERE delivery_detail_id = l_delivery_detail_id_new;
/* Called from: RepairOrdersVO of search and update repair orders */
/* Input param: p_repair_line_id repair line id. */
/* Return value: returns all the internal orders for a particular repair order. Returns null if no */
/* internal orders are found */
/*-----------------------------------------------------------------------------------------------------------*/
FUNCTION GET_ALL_INTERNAL_ORDERS(p_repair_line_id IN NUMBER) RETURN VARCHAR2
IS
l_internal_orders_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
SELECT DISTINCT order_number
BULK COLLECT INTO l_internal_orders_tbl
FROM csd_product_transactions cpt,
oe_order_headers_all oeh
WHERE cpt.repair_line_id = p_repair_line_id
AND cpt.order_header_id = oeh.header_id
AND cpt.action_type IS NULL;