[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
fetched. If header_id changes, then update GROUPING_RULE_ID for the header
and continue the FOR loop until header_id changes again. */
/* Bug #2060360
* Added NVL for from_subinventory_code to allocate lines
* where from sub is not specified.
*/
--bug 2307649
--type codes have changed, so we need to handle situation
-- where p_move_order_type = 99 (all lines)
-- kkoothan Bug Fix:2352405
-- Added one more column to_account_id
-- in the cursor below which is later used to update
-- the distribution_account_id of MMTT
/* Restructured the Following Cursor SQL as part of
Performance Fix: 2853526.
Removed NVLs around from and to Subinventory Codes and
used base tables mtl_txn_request_headers and
mtl_txn_request_lines instead of the View mtl_txn_request_lines_v*/
/* FP-J PAR Replenishment Counts: Introduced 3 more columns to be fetched
viz., header_id, project_id and task_id. Also, the cursor is now ordered by
mtrl.header_id so that update of GROUPING_RULE_ID of mtrh (for header_id) is
done efficiently knowing the fact that the cursor may fetch multiple lines
from same header and across headers. column header_id is used to update GROUPINNG_RULE_ID
of MTRH, project_id and task_id are used as input parameters for the new call
INV_PR_PICK_SLIP_NUMBER.GET_PICK_SLIP_NUMBER() to generate the pick slip number.*/
CURSOR c_move_order_lines IS
SELECT mtrl.line_id
, mtrl.inventory_item_id
, mtrh.move_order_type
, mtrl.to_account_id
, mtrl.header_id
, mtrl.project_id
, mtrl.task_id
FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh
WHERE mtrl.line_status IN(3, 7)
AND mtrl.header_id = mtrh.header_id
AND mtrl.organization_id = p_org_id
AND(
(p_move_order_type IN(1, 2)
AND mtrh.move_order_type = p_move_order_type)
OR(p_move_order_type = 99
AND mtrh.move_order_type IN(1, 2))
)
AND mtrl.quantity > NVL(mtrl.quantity_detailed, 0)
AND mtrh.request_number = NVL(p_transfer_order, mtrh.request_number)
AND mtrl.created_by = NVL(p_requested_by, mtrl.created_by)
AND(p_source_subinv IS NULL
OR mtrl.from_subinventory_code = p_source_subinv)
AND(p_dest_subinv IS NULL
OR mtrl.to_subinventory_code = p_dest_subinv)
ORDER BY mtrl.header_id;
SELECT transaction_temp_id
, subinventory_code
, locator_id
, transfer_subinventory
, transfer_to_location
, revision
FROM mtl_material_transactions_temp
WHERE move_order_line_id = v_line_id
AND pick_slip_number IS NULL;
/* FP-J PAR Replenishment Count: Code block to update GROUPING_RULE_ID of mtrh
only once per header_id change in the set of mtrls fetched in the cursor.
This approach works because cursor is ordered by mtrl.header_id.
Implied inline branching between I and J here is to check whether
p_pick_slip_group_rule_id IS NULL or not. In FP-I, concurrent program
cannot pass p_pick_slip_group_rule_id(hence default NULL). */
IF move_ord_rec.header_id <> mtrh_header_change_track
AND p_pick_slip_group_rule_id IS NOT NULL THEN
mtrh_header_change_track := move_ord_rec.header_id;
UPDATE mtl_txn_request_headers
SET grouping_rule_id = p_pick_slip_group_rule_id
WHERE header_id = move_ord_rec.header_id;
inv_pr_pick_slip_number.delete_wip_ps_tbl;
SELECT serial_number_control_code
INTO serial_control_code
FROM mtl_system_items
WHERE inventory_item_id = move_ord_rec.inventory_item_id
AND organization_id = p_org_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO v_header_id
FROM DUAL;
UPDATE mtl_txn_request_lines
SET quantity_detailed = (nvl(quantity_delivered,0) + v_detailed_qty) -- against bug : 4155230
,secondary_quantity_detailed = DECODE(v_secondary_detailed_qty,0,NULL,v_secondary_detailed_qty)
WHERE line_id = v_line_id
AND organization_id = p_org_id;
UPDATE mtl_material_transactions_temp
SET pick_slip_number = v_pick_slip_no
WHERE transaction_temp_id = v_mmtt.transaction_temp_id;
UPDATE mtl_material_transactions_temp
SET distribution_account_id = move_ord_rec.to_account_id
WHERE move_order_line_id = v_line_id;
SELECT COUNT(*)
INTO l_count_item_category_set
FROM mtl_item_categories
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND category_set_id = l_prof_category_set_id;
SELECT NVL(LCM_ENABLED_FLAG, 'N')
INTO v_lcm_enabled_flag
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = p_ship_to_org_id;
SELECT NVL(DROP_SHIP_FLAG, 'N')
INTO v_drop_ship_flag
FROM PO_LINE_LOCATIONS_ALL
WHERE LINE_LOCATION_ID = p_po_line_location_id;
SELECT 'N' INTO v_all_expense_flag
FROM dual
WHERE EXISTS ( SELECT 1
FROM po_distributions_all pod
WHERE pod.destination_type_code <> 'EXPENSE'
AND pod.line_location_id = p_po_line_location_id);
SELECT STOCK_ENABLED_FLAG,INVENTORY_ASSET_FLAG
INTO v_stock_enabled_flag,v_inv_asset_flag
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = p_ship_to_org_id;
SELECT 'N'
INTO v_emp_flag
FROM po_vendors
WHERE vendor_type_lookup_code = 'EMPLOYEE'
AND vendor_id = p_vendor_id;
SELECT nvl(ORG_ID,-1)
INTO v_vs_ou_id
FROM PO_VENDOR_SITES_ALL
WHERE VENDOR_SITE_ID = p_vendor_site_id;
SELECT To_number(hoi2.org_information3)
INTO v_org_ou_id
FROM hr_organization_information hoi2
WHERE organization_id = p_ship_to_org_id
AND ( hoi2.org_information_context || '' ) = 'Accounting Information';
SELECT a.application_column_name
FROM fnd_id_flex_segments_vl a
WHERE a.application_id = 401
AND a.id_flex_code = flex_code
AND a.id_flex_num = (SELECT id_flex_num
FROM fnd_id_flex_structures
WHERE id_flex_code = flex_code)
AND a.enabled_flag = 'Y'
AND a.display_flag = 'Y'
ORDER BY a.segment_num;
SELECT id_flex_num
INTO v_flex_num
FROM fnd_id_flex_structures
WHERE id_flex_code = 'MTLL';
SELECT project_reference_enabled
INTO v_proj_ref_enabled
FROM mtl_parameters
WHERE organization_id = x_org_id;
SELECT DISTINCT project_number
INTO v_proj_name
FROM mtl_project_v
WHERE project_id = (SELECT NVL(TO_NUMBER(segment19), 0)
FROM mtl_item_locations
WHERE inventory_location_id = x_loc_id
AND organization_id = x_org_id);
SELECT DISTINCT a.task_number
INTO v_task_name
FROM mtl_task_v a
WHERE a.task_id = (SELECT NVL(TO_NUMBER(segment20), 0)
FROM mtl_item_locations
WHERE inventory_location_id = x_loc_id
AND organization_id = x_org_id)
AND a.project_id = (SELECT NVL(TO_NUMBER(segment19), a.project_id)
FROM mtl_item_locations
WHERE inventory_location_id = x_loc_id
AND organization_id = x_org_id);
'select ' || v_loc_str || ' from mtl_item_locations where inventory_location_id = :loc_id ' || ' and organization_id = :org_id';
SELECT NVL(mp.wms_enabled_flag,'N')
INTO l_wms_enabled_flag
FROM MTL_PARAMETERS mp
WHERE mp.organization_id=v_org_id ;
SELECT NVL(ccicv.item_cost, 0)
INTO v_item_cost
FROM cst_cg_item_costs_view ccicv, mtl_parameters mp
WHERE v_locator_id IS NULL
AND ccicv.organization_id = v_org_id
AND ccicv.inventory_item_id = v_item_id
AND ccicv.organization_id = mp.organization_id
AND ccicv.cost_group_id = DECODE(mp.primary_cost_method, 1, 1, NVL(mp.default_cost_group_id, 1))
UNION ALL
SELECT NVL(ccicv.item_cost, 0)
FROM mtl_item_locations mil, cst_cg_item_costs_view ccicv, mtl_parameters mp
WHERE v_locator_id IS NOT NULL
AND mil.organization_id = v_org_id
AND mil.inventory_location_id = v_locator_id
AND mil.project_id IS NULL
AND ccicv.organization_id = mil.organization_id
AND ccicv.inventory_item_id = v_item_id
AND ccicv.organization_id = mp.organization_id
AND ccicv.cost_group_id = DECODE(mp.primary_cost_method, 1, 1, DECODE(l_wms_enabled_flag,'Y',ccicv.cost_group_id, NVL(mp.default_cost_group_id, 1)))
UNION ALL
SELECT NVL(ccicv.item_cost, 0)
FROM mtl_item_locations mil, mrp_project_parameters mrp, cst_cg_item_costs_view ccicv, mtl_parameters mp
WHERE v_locator_id IS NOT NULL
AND mil.organization_id = v_org_id
AND mil.inventory_location_id = v_locator_id
AND mil.project_id IS NOT NULL
AND mrp.organization_id = mil.organization_id
AND mrp.project_id = mil.project_id
AND ccicv.organization_id = mil.organization_id
AND ccicv.inventory_item_id = v_item_id
AND ccicv.organization_id = mp.organization_id
AND ccicv.cost_group_id = DECODE(mp.primary_cost_method, 1, 1, NVL(mrp.costing_group_id, 1));