/* Formatted on 2013/07/11 18:23 (Formatter Plus v4.8.8) */
DECLARE
l_inventory_item_id NUMBER;
l_cost_type_id NUMBER;
l_organization_id NUMBER;
l_cost_element_id NUMBER;
l_locator_id NUMBER;
v_error VARCHAR2 (4000);
x_item_valid VARCHAR2 (1);
v_main_record_count NUMBER;
v_inv_record_count NUMBER;
v_po_record_count NUMBER;
l_code_combination_id NUMBER;
CURSOR c_onhand_qty
IS
SELECT *
FROM xxoomco_items_onhand_qty_temp
WHERE TO_CHAR (transaction_date, 'RRRR/MM/DD HH24:MI:SS') ! =
'2013/06/30 04:06:01'
AND sno = 1
ORDER BY transaction_date;
BEGIN
FOR i_onhand_qty IN c_onhand_qty
LOOP
BEGIN
SELECT organization_id
INTO l_organization_id
FROM mtl_parameters
WHERE UPPER (organization_code) =
UPPER (i_onhand_qty.organization_code);
DBMS_OUTPUT.put_line ('ORGANIZATION ID:' || l_organization_id);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_error := v_error || 'No Org Exists for ORG Code';
DBMS_OUTPUT.put_line ( 'No Org exists for Org Code: '
|| i_onhand_qty.organization_code
);
WHEN OTHERS
THEN
v_error := v_error || 'No Org Exists for ORG Code';
END;
BEGIN
SELECT inventory_item_id
INTO l_inventory_item_id
FROM mtl_system_items_b
WHERE segment1 || '.' || segment2 = i_onhand_qty.item_number
AND organization_id = 158;
DBMS_OUTPUT.put_line ('Inventory Item Id :' || l_inventory_item_id);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_error := v_error || 'No Item_id Exists for Item Code';
DBMS_OUTPUT.put_line ( 'No Item_id Exists for Item Code: '
|| i_onhand_qty.item_number
);
WHEN OTHERS
THEN
v_error := v_error || 'No Item_id Exists for Item Code';
END;
BEGIN
SELECT code_combination_id
INTO l_code_combination_id
FROM gl_code_combinations
WHERE segment1
|| '.'
|| segment2
|| '.'
|| segment3
|| '.'
|| segment4
|| '.'
|| segment5
|| '.'
|| segment6
|| '.'
|| segment7
|| '.'
|| segment8 = i_onhand_qty.code_combination_val;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( 'Combinatin does not exist: '
|| i_onhand_qty.code_combination_val
);
END;
BEGIN
DBMS_OUTPUT.put_line ('Before Inserted MTL_TRANSACTIONS_INTERFACE');
INSERT INTO apps.mtl_transactions_interface
(source_code, source_line_id,
source_header_id, process_flag, transaction_mode,
last_update_date, last_updated_by, creation_date,
created_by, inventory_item_id, organization_id,
transaction_quantity, transaction_uom,
transaction_date,
subinventory_code, locator_id, transaction_type_id,
distribution_account_id, dst_segment1, dst_segment2,
dst_segment3, dst_segment4, dst_segment5,
dst_segment6, dst_segment7, transaction_cost,
transfer_lpn_id
)
VALUES ('ONHAND_CONV', OOMCO_SRC_LINE_ID_INT_SEQ.NEXTVAL,
xxmtl_src_hdr_id_int_seq.NEXTVAL, 1, 3,
SYSDATE, 1131, SYSDATE,
1131, l_inventory_item_id, 158,
i_onhand_qty.quantity, i_onhand_qty.transaction_uom,
i_onhand_qty.transaction_date,
i_onhand_qty.subinventory_code, NULL, 42,
l_code_combination_id, NULL, --lc_dist_acc_seg1,
NULL, --lc_dist_acc_seg2,
NULL, --lc_dist_acc_seg3,
NULL, --lc_dist_acc_seg4,
NULL, --lc_dist_acc_seg5,
NULL, --lc_dist_acc_seg6,
NULL, --lc_dist_acc_seg7,
i_onhand_qty.unit_cost,
--CRecOnhand.TRANSACTION_COST
NULL --LPN_ID
);
--FND_FILE.PUT_LINE(FND_FILE.LOG,'After Inserted MTL_TRANSACTIONS_INTERFACE');
DBMS_OUTPUT.put_line ('After Inserted MTL_TRANSACTIONS_INTERFACE');
COMMIT;
END;
END LOOP;
DBMS_OUTPUT.put_line ('Main Table Record count:' || v_main_record_count);
DBMS_OUTPUT.put_line ( 'Category inv Table Record count:'
|| v_inv_record_count
);
DBMS_OUTPUT.put_line ('Category PO Record count:' || v_po_record_count);
END;
