Create Procedure for
Account issue On hand balances
CREATE
OR REPLACE PROCEDURE XXXINV_ACCOUNT_ISSUE_OHB_P (org_code
VARCHAR2) AS
v_transaction_uom MTL_SYSTEM_ITEMS.primary_uom_code%TYPE;
v_org_id NUMBER;
CURSOR cur_account_issue(org_id NUMBER) IS
SELECT inventory_item_id
,organization_id
,locator_id
,subinventory_code
,sum(transaction_quantity) tot
,revision
FROM
MTL_ONHAND_QUANTITIES
WHERE
transaction_quantity>0
AND organization_id =org_id
GROUP BY inventory_item_id
,organization_id
,locator_id
,subinventory_code
,revision;
BEGIN
BEGIN
SELECT organization_id
INTO v_org_id
FROM MTL_PARAMEXXXS
WHERE organization_code=org_code;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE
('Procedure XXXINV_ACCOUNT_ISSUE_OHB_P - organization_id
'||SQLERRM);
END;
FOR v_cur_account_issue IN
cur_account_issue(v_org_id)
BEGIN
SELECT primary_uom_code
INTO v_transaction_uom
FROM MTL_SYSTEM_ITEMS msi
WHERE msi.inventory_item_id
=v_cur_account_issue.inventory_item_id
AND msi.organization_id =v_cur_account_issue.organization_id;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE
('Procedure XXXINV_ACCOUNT_ISSUE_OHB_P - primary_uom_code
'||SQLERRM);
END;
INSERT INTO XXXINV_ACCOUNT_ISSUE_OHB
(organization_id
,transaction_quantity
,transaction_uom
,inventory_item_id
,subinventory_code
,revision
,created_by
,last_updated_by
,last_update_date
,creation_date
,locator_id
)
VALUES
(
v_cur_account_issue.organization_id
,v_cur_account_issue.tot
,v_transaction_uom
,v_cur_account_issue.inventory_item_id
,v_cur_account_issue.subinventory_code
,v_cur_account_issue.revision
,38259
,38259
,SYSDATE
,SYSDATE
,v_cur_account_issue.locator_id
);
END LOOP ;
COMMIT;
END
XXXINV_ACCOUNT_ISSUE_OHB_P;
Process 2: Create Procedure for Unreserve
the item quantity against the sale order
CREATE
OR REPLACE PROCEDURE XXXINV_DEL_RES_P (org_code
VARCHAR2) IS
v_mtl_reservation_record
Inv_Reservation_Global.mtl_reservation_rec_type;
v_serial_number_tbl_type
Inv_Reservation_Global.serial_number_tbl_type;
v_return_status VARCHAR2(30);
v_msg_data VARCHAR2(256);
v_msg_count NUMBER;
v_message VARCHAR2(250);
v_msg_index_out NUMBER;
v_count NUMBER;
v_res_flag VARCHAR2(2);
v_INTERFACE_id NUMBER;
v_org_id NUMBER;
v_user FND_USER.user_id%TYPE;
v_code_comb_id GL_CODE_COMBINATIONS.code_combination_id%TYPE;
v_transaction_uom MTL_SYSTEM_ITEMS.primary_uom_code%TYPE;
CURSOR cur_del_res(org_id NUMBER) IS
SELECT reservation_id
,organization_id
,inventory_item_id
,demand_source_type_id
,demand_source_header_id
,demand_source_line_id
,primary_uom_code
,reservation_uom_code
,reservation_quantity
,primary_reservation_quantity
,supply_source_type_id
FROM
MTL_RESERVATIONS
WHERE
organization_id =org_id;
CURSOR cur_account_stag(org_id NUMBER) IS
SELECT *
FROM
XXXINV_ACCOUNT_ISSUE_OHB
WHERE
transaction_quantity>0
AND
organization_id =org_id;
BEGIN
BEGIN
SELECT organization_id
INTO v_org_id
FROM MTL_PARAMEXXXS
WHERE organization_code=org_code;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE
('Procedure XXXINV_DELETE_RES_P - organization_id'||SQLERRM);
END;
INSERT INTO XXXINV_MTL_RESERVATIONS_BK
(SELECT MTL_RESERVATIONS.*
FROM MTL_RESERVATIONS
WHERE organization_id=v_org_id);
FOR
v_cur_del_res IN cur_del_res(v_org_id)
v_mtl_reservation_record.reservation_id := v_cur_del_res.reservation_id;
v_mtl_reservation_record.organization_id := v_cur_del_res.organization_id;
v_mtl_reservation_record.inventory_item_id := v_cur_del_res.inventory_item_id;
v_mtl_reservation_record.demand_source_type_id :=
v_cur_del_res.demand_source_type_id;
v_mtl_reservation_record.demand_source_header_id :=
v_cur_del_res.demand_source_header_id;
v_mtl_reservation_record.demand_source_line_id :=
v_cur_del_res.demand_source_line_id;
v_mtl_reservation_record.primary_uom_code := v_cur_del_res.primary_uom_code;
v_mtl_reservation_record.reservation_uom_code := v_cur_del_res.reservation_uom_code;
v_mtl_reservation_record.reservation_quantity :=
v_cur_del_res.reservation_quantity;
v_mtl_reservation_record.primary_reservation_quantity:=
v_cur_del_res.primary_reservation_quantity;
v_mtl_reservation_record.supply_source_type_id := v_cur_del_res.supply_source_type_id;
Inv_Reservation_Pub.delete_reservation
(p_api_version_number => 1.0
,x_return_status => v_return_status
,x_msg_count => v_msg_count
,x_msg_data => v_msg_data
,p_rsv_rec => v_mtl_reservation_record
,p_serial_number => v_serial_number_tbl_type
);
Fnd_Msg_Pub.get(p_msg_index => 1
,p_encoded =>
'F'
,p_data =>
v_message
,p_msg_index_out => v_msg_index_out
);
DBMS_OUTPUT.PUT_LINE('AfXXX Deletetion
of Reservation p_msg_index_out'||' '||
v_msg_index_out||'Message'||v_message);
END LOOP ;
COMMIT;
FOR v_cur_account_stag IN
cur_account_stag(v_org_id)
BEGIN
SELECT MTL_MAXXXIAL_TRANSACTIONS_S.NEXTVAL
INTO v_INTERFACE_id
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE
('Procedure XXXINV_DELETE_RES_P - sequence '||SQLERRM);
END;
BEGIN
SELECT code_combination_id
INTO v_code_comb_id
FROM GL_CODE_COMBINATIONS
WHERE segment1='001'
AND segment2 ='0787796'
AND segment3 ='134668'
AND segment4 ='130';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE
('Procedure XXXINV_DELETE_RES_P - code combination id '||SQLERRM);
END;
IF
NVL(v_cur_account_stag.record_status,'N') !='Y' THEN
BEGIN
INSERT INTO MTL_TRANSACTIONS_INTERFACE
(source_code
,source_line_id
,source_header_id
,transaction_INTERFACE_id
,process_flag
,validation_required
,transaction_mode
,lock_flag
,organization_id
,transaction_quantity
,transaction_uom
,transaction_date
,transaction_type_id
,transaction_source_id
,flow_schedule
,scheduled_flag
,inventory_item_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,subinventory_code
,distribution_account_id
,revision
,locator_id
)
VALUES (
'Conversion'
,v_INTERFACE_id
,v_INTERFACE_id
,v_INTERFACE_id
,1
,1
,3
,2
,v_cur_account_stag.organization_id
,-v_cur_account_stag.transaction_quantity
,v_cur_account_stag.transaction_uom
,SYSDATE
,1
,v_code_comb_id
,'Y'
,2
,v_cur_account_stag.inventory_item_id
,SYSDATE
,38259
, SYSDATE
,38259
,v_cur_account_stag.subinventory_code
,v_code_comb_id
,v_cur_account_stag.revision
,v_cur_account_stag.locator_id
);
UPDATE XXXINV_ACCOUNT_ISSUE_OHB
SET record_status ='Y'
WHERE organization_id =v_cur_account_stag.organization_id
AND
inventory_item_id=v_cur_account_stag.inventory_item_id;
EXCEPTION
WHEN OTHERS THEN
UPDATE XXXINV_ACCOUNT_ISSUE_OHB
SET record_status ='E',error_msg ='Procedure XXXINV_DELETE_RES_P
- Error in fetching the data' WHERE
organization_id =v_cur_account_stag.organization_id
AND
inventory_item_id=v_cur_account_stag.inventory_item_id;
END;
END IF;
END LOOP ;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Data
insertion is Failed '||SQLERRM);
END
XXXINV_DELETE_RES_P;
Process
3: Create Procedure for Account
receipt
CREATE
OR REPLACE PROCEDURE XXXINV_ACCOUNT_RECEIPT_OHB_P (org_code
VARCHAR2) AS
CURSOR cur_account_receipt(org_id NUMBER) IS
SELECT *
FROM
XXXINV_ACCOUNT_ISSUE_OHB
WHERE
organization_id = org_id;
v_user FND_USER.user_id%TYPE;
v_transaction_uom MTL_SYSTEM_ITEMS.primary_uom_code%TYPE;
v_code_comb_id GL_CODE_COMBINATIONS.code_combination_id%TYPE;
v_org_id NUMBER;
v_INTERFACE_id NUMBER;
BEGIN
BEGIN
SELECT organization_id
INTO v_org_id
FROM MTL_PARAMEXXXS
WHERE organization_code=org_code;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE
('Procedure XXXINV_ACCOUNT_RECEIPT_OHB_P - organization_id
'||SQLERRM);
END;
FOR
v_cur_account_receipt IN cur_account_receipt(v_org_id)
BEGIN
SELECT primary_uom_code
INTO v_transaction_uom
FROM MTL_SYSTEM_ITEMS msi
WHERE v_cur_account_receipt.inventory_item_id =
msi.inventory_item_id
AND v_cur_account_receipt.organization_id = msi.organization_id;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Procedure XXXINV_ACCOUNT_RECEIPT_OHB_P - primary_uom_code'||SQLERRM);
END;
BEGIN
SELECT MTL_MAXXXIAL_TRANSACTIONS_S.NEXTVAL
INTO v_INTERFACE_id
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Procedure XXXINV_ACCOUNT_RECEIPT_OHB_P
- sequence '||SQLERRM);
END;
BEGIN
SELECT code_combination_id
INTO v_code_comb_id
FROM GL_CODE_COMBINATIONS
WHERE segment1='206'
AND segment2 ='0787796'
AND segment3 ='042709'
AND segment4 ='040';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Procedure XXXINV_ACCOUNT_RECEIPT_OHB_P-code
combination id'||SQLERRM);
END;
INSERT INTO MTL_TRANSACTIONS_INTERFACE (source_code
,source_line_id
,source_header_id
,transaction_INTERFACE_id
,process_flag
,validation_required
,transaction_mode
,lock_flag
,organization_id
,transaction_quantity
,transaction_uom
,transaction_date
,transaction_type_id
,transaction_source_id
,flow_schedule
,scheduled_flag
,inventory_item_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,subinventory_code
,Distribution_account_id
,revision
,locator_id
)
VALUES (
'Conversion'
,v_INTERFACE_id
,v_INTERFACE_id
,v_INTERFACE_id
,1
,1
,3
,2
,v_cur_account_receipt.organization_id
,abs(v_cur_account_receipt.transaction_quantity)
,v_transaction_uom
,SYSDATE
,40
,v_code_comb_id
,'Y'
,2
,v_cur_account_receipt.inventory_item_id
, SYSDATE
,38259
, SYSDATE
,38259
,v_cur_account_receipt.subinventory_code
,v_code_comb_id
,v_cur_account_receipt.revision
,v_cur_account_receipt.locator_id
);
END LOOP ;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Data
insertion is Failed '||SQLERRM);
END XXXINV_ACCOUNT_RECEIPT_OHB_P;
Process
4: Create Procedure for Reserve the item quantity against the sale
order
CREATE
OR REPLACE PROCEDURE XXXINV_CREATE_RES_P (org_code
VARCHAR2) AS
v_org_id NUMBER;
v_batch_number
NUMBER := 1;
CURSOR cur_create_res(org_id NUMBER) IS
SELECT *
FROM XXXINV_MTL_RESERVATIONS_BK
WHERE
organization_id =org_id;
BEGIN
BEGIN
SELECT organization_id
INTO v_org_id
FROM MTL_PARAMEXXXS
WHERE organization_code=org_code;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE
('Procedure XXXINV_CREATE_RES_P - organization_id
'||SQLERRM);
END;
FOR
v_cur_create_res IN cur_create_res(v_org_id)
INSERT INTO MTL_RESERVATIONS_INTERFACE
(reservation_INTERFACE_id
,reservation_batch_id
,requirement_date
,organization_id
,inventory_item_id
,primary_uom_code
,reservation_uom_code
,reservation_quantity
,primary_reservation_quantity
,supply_source_type_id
,row_status_code
,lock_flag
,reservation_action_code
,transaction_mode
,validation_flag
,demand_source_type_id
,last_update_date
,last_updated_by
,created_by
,creation_date
,demand_source_header_id
,demand_source_line_id
,subinventory_code
,demand_source_name
,supply_source_name
,supply_source_header_id
,supply_source_line_id
)
VALUES (
MTL_RESERVATIONS_INTERFACE_S.NEXTVAL
, v_batch_number
,v_cur_create_res.requirement_date
,v_cur_create_res.organization_id
,v_cur_create_res.inventory_item_id
,v_cur_create_res.primary_uom_code
,v_cur_create_res.reservation_uom_code
,v_cur_create_res.reservation_quantity
,v_cur_create_res.primary_reservation_quantity
,v_cur_create_res.supply_source_type_id
,1
,2
,1
,3
,1
,v_cur_create_res.demand_source_type_id
,SYSDATE
,v_cur_create_res.last_updated_by
,v_cur_create_res.created_by
,SYSDATE
,v_cur_create_res.demand_source_header_id
,v_cur_create_res.demand_source_line_id
,v_cur_create_res.subinventory_code
,v_cur_create_res.demand_source_name
,v_cur_create_res.supply_source_name
,v_cur_create_res.supply_source_header_id
,v_cur_create_res.supply_source_line_id
);
v_batch_number
:= v_batch_number + 1;
END LOOP ;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Data
insertion is Failed '||SQLERRM);
END
XXXINV_CREATE_RES_P;
No comments:
Post a Comment