CREATE TABLE PURE_BOM_UPDATE_STG
(
ORGANIZATION_NAME VARCHAR2(100 BYTE),
BOM_CODE VARCHAR2(200 BYTE),
BOM_DESCRIPTION VARCHAR2(500 BYTE),
ALTERNATIVE_BOM VARCHAR2(100 BYTE),
COMPONENT_CODE VARCHAR2(250 BYTE),
COMPONENT_DESCRIPTION VARCHAR2(500 BYTE),
OPERATION_SEQ_NUM NUMBER,
ITEM_SEQUENCE NUMBER,
COMPONENT_QUANTITY NUMBER,
SUPPLY_SUBINVENTORY VARCHAR2(500 BYTE),
STATUS_MESSAGE VARCHAR2(3000 BYTE),
STATUS_FLAG VARCHAR2(10 BYTE),
PROCESS_FLAG VARCHAR2(10 BYTE)
)
CREATE OR REPLACE PROCEDURE pure_bom_update_api
IS
---DECLARE
l_bom_header_rec bom_bo_pub.bom_head_rec_type
:= bom_bo_pub.g_miss_bom_header_rec;
l_bom_revision_tbl bom_bo_pub.bom_revision_tbl_type
:= bom_bo_pub.g_miss_bom_revision_tbl;
l_bom_component_tbl bom_bo_pub.bom_comps_tbl_type
:= bom_bo_pub.g_miss_bom_component_tbl;
l_bom_ref_designator_tbl bom_bo_pub.bom_ref_designator_tbl_type
:= bom_bo_pub.g_miss_bom_ref_designator_tbl;
l_bom_sub_component_tbl bom_bo_pub.bom_sub_component_tbl_type
:= bom_bo_pub.g_miss_bom_sub_component_tbl;
l_error_message_list error_handler.error_tbl_type;
l_x_bom_header_rec bom_bo_pub.bom_head_rec_type;
l_x_bom_revision_tbl bom_bo_pub.bom_revision_tbl_type;
l_x_bom_component_tbl bom_bo_pub.bom_comps_tbl_type;
l_x_bom_ref_designator_tbl bom_bo_pub.bom_ref_designator_tbl_type;
l_x_bom_sub_component_tbl bom_bo_pub.bom_sub_component_tbl_type;
l_x_return_status VARCHAR2 (2000);
l_x_msg_count NUMBER;
i NUMBER := 1;
l_count NUMBER;
l_status_flag VARCHAR2 (10) := NULL;
l_error_message VARCHAR2 (20000) := NULL;
l_process_flag VARCHAR2 (10);
l_organization_id NUMBER;
l_start_eff_date DATE;
l_user_id NUMBER := -1;
l_resp_id NUMBER := -1;
l_application_id NUMBER := -1;
l_user_name VARCHAR2 (1000) := 'PURE';
l_resp_name VARCHAR2 (1000) := 'PURE_BOM';
l_error_table error_handler.error_tbl_type;
l_return_status VARCHAR2 (10) := NULL;
l_msg_count NUMBER := 0;
l_assembly_item_id NUMBER := NULL;
l_organization_code VARCHAR2 (100) := NULL;
l_component_item_id NUMBER := NULL;
l_wip_supply_type VARCHAR2 (50) := NULL;
---NUMBER;
l_dis_date DATE;
l_bill_sequence_id NUMBER := NULL;
CURSOR c_process
IS
SELECT cust.process_flag, ood.organization_code,
ood.organization_name, ood.organization_id,
bom.assembly_item_id, msi.segment1 bom_code, msi.description,
bic.item_num, bic.operation_seq_num, bic.component_item_id,
msi1.segment1 component_code, ml.meaning supply_type,
bic.wip_supply_type, bic.effectivity_date, bic.disable_date,
cust.alternative_bom
FROM mtl_system_items_b msi,
org_organization_definitions ood,
bom_bill_of_materials bom,
bom_inventory_components bic,
mtl_system_items_b msi1,
mfg_lookups ml,
pure_bom_update_stg cust
WHERE bom.assembly_item_id = msi.inventory_item_id
AND bom.bill_sequence_id = bic.bill_sequence_id
AND msi.organization_id = ood.organization_id
AND bom.organization_id = ood.organization_id
AND msi1.inventory_item_status_code = 'Active'
AND bic.component_item_id = msi1.inventory_item_id
AND msi1.organization_id = ood.organization_id
AND ml.lookup_code(+) = bic.wip_supply_type
AND ml.lookup_type(+) = 'WIP_SUPPLY'
AND ood.organization_name = cust.organization_name
AND msi.segment1 = cust.bom_code
AND msi1.segment1 = cust.component_code
AND bic.operation_seq_num = cust.operation_seq_num
AND item_sequence = item_num
AND NVL (bom.alternate_bom_designator, 'x') =
NVL (cust.alternative_bom, 'x')
---AND cust.status_flag <> 'C'
AND cust.bom_code IN
('MGCRHDBR00200020', 'MGCRHDBR00200045', 'MGCRHDCN00001008')
ORDER BY bom_code ASC;
-- AND process_flag NOT IN ('C', NULL);
BEGIN
DBMS_OUTPUT.ENABLE (1000000);
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = l_user_name;
-- Get the application_id and responsibility_id
SELECT application_id, responsibility_id
INTO l_application_id, l_resp_id
FROM fnd_responsibility
WHERE responsibility_key = l_resp_name;
-- intiialize applications information
fnd_global.apps_initialize (l_user_id, l_resp_id, l_application_id);
--- i := 0;
FOR j IN c_process
LOOP
i := 1;
l_error_message := NULL; --- startt C_PROCESS
IF j.disable_date IS NOT NULL
THEN
UPDATE pure_bom_update_stg
SET status_flag = 'C', ---l_x_return_status,
status_message = 'Already end dated ',
process_flag = 'C'
WHERE organization_name = j.organization_name
AND bom_code = j.bom_code
AND component_code = j.component_code
AND operation_seq_num = j.operation_seq_num
AND item_sequence = j.item_num;
---dbms_output.put_line(disabled component )||;
COMMIT;
END IF;
-- BEGIN
-- SELECT COUNT (*)
-- INTO l_count
-- FROM bom_inventory_components
-- WHERE component_item_id = j.component_item_id
-- ---and ORGANIZATION_ID=j.ORGANIZATION_ID
-- AND item_num = j.item_num
-- AND operation_seq_num = j.operation_seq_num;
-- IF l_count =0
-- THEN
-- UPDATE pure_bom_update_stg
-- SET status_flag = 'E', ---l_x_return_status,
-- status_message = 'Component doesnot exist ',
-- process_flag = 'I'
-- WHERE organization_name = j.organization_name
-- AND bom_code = j.bom_code
-- AND component_code = j.component_code
-- AND operation_seq_num = j.operation_seq_num
-- AND item_sequence = j.item_num;
-- COMMIT;
-- END IF;
-- END;
DBMS_OUTPUT.put_line ('l_start_eff_date ' || l_start_eff_date);
DBMS_OUTPUT.put_line ('j.wip_supply_type ' || l_wip_supply_type);
l_bom_component_tbl (i) := bom_bo_pub.g_miss_bom_component_rec;
l_bom_component_tbl (i).transaction_type := 'UPDATE';
l_bom_component_tbl (i).organization_code := j.organization_code;
l_bom_component_tbl (i).assembly_item_name := j.bom_code;
l_bom_component_tbl (i).start_effective_date := j.effectivity_date;
--- TO_DATE (j.effectivity_date, 'DD-MON-YYYY HH24:MI:SS');
l_bom_component_tbl (i).disable_date := SYSDATE;
--- TO_DATE (SYSDATE, 'DD-MON-YYYY HH24:MI:SS');
l_bom_component_tbl (i).component_item_name := j.component_code;
l_bom_component_tbl (i).item_sequence_number := j.item_num;
l_bom_component_tbl (i).operation_sequence_number := j.operation_seq_num;
l_bom_component_tbl (i).return_status := NULL;
l_bom_component_tbl (i).wip_supply_type := j.wip_supply_type;
--- j.supply_type;
l_bom_component_tbl (i).alternate_bom_code :=
NVL (j.alternative_bom, NULL);
IF j.disable_date IS NULL
THEN
BEGIN
DBMS_OUTPUT.put_line ('Calling API...');
DBMS_OUTPUT.put_line (' j.organization_code'
|| l_organization_code
);
DBMS_OUTPUT.put_line (' j.bom_code' || j.bom_code);
DBMS_OUTPUT.put_line (' l_start_eff_date' || j.effectivity_date);
DBMS_OUTPUT.put_line (' j.component_code ' || j.component_code);
DBMS_OUTPUT.put_line (' j.item_sequence' || j.item_num);
DBMS_OUTPUT.put_line (' j.operation_seq_num'
|| j.operation_seq_num
);
DBMS_OUTPUT.put_line (' wip_supply_type' || j.wip_supply_type);
--- IF j.disable_date IS NOT NULL
---THEN
---bms_output.put_line(' j.bom_code' || j.bom_code);
DBMS_OUTPUT.put_line ('Line count....' || i || j.bom_code);
error_handler.initialize;
bom_bo_pub.process_bom
(p_bo_identifier => 'BOM',
p_api_version_number => 1.0,
p_init_msg_list => TRUE,
p_bom_header_rec => l_bom_header_rec,
p_bom_revision_tbl => l_bom_revision_tbl,
p_bom_component_tbl => l_bom_component_tbl,
p_bom_ref_designator_tbl => l_bom_ref_designator_tbl,
p_bom_sub_component_tbl => l_bom_sub_component_tbl,
x_bom_header_rec => l_x_bom_header_rec,
x_bom_revision_tbl => l_x_bom_revision_tbl,
x_bom_component_tbl => l_x_bom_component_tbl,
x_bom_ref_designator_tbl => l_x_bom_ref_designator_tbl,
x_bom_sub_component_tbl => l_x_bom_sub_component_tbl,
x_return_status => l_x_return_status,
x_msg_count => l_x_msg_count,
p_debug => 'N',
p_output_dir => '',
p_debug_filename => ''
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
l_error_message || 'Error after processing ' || SQLERRM;
----- DBMS_OUTPUT.put_line ('1' || l_error_message);
UPDATE pure_bom_update_stg
SET status_flag = 'E', ---l_x_return_status,
status_message = l_error_message,
process_flag = 'I'
WHERE organization_name = j.organization_name
AND bom_code = j.bom_code
AND component_code = j.component_code
AND operation_seq_num = j.operation_seq_num
AND item_sequence = j.item_num;
--AND ROWID = j.ROWID;
COMMIT;
END;
END IF;
error_handler.get_message_list (l_error_message_list);
DBMS_OUTPUT.put_line ('Return Status = ' || l_x_return_status);
DBMS_OUTPUT.put_line ('Message Count = ' || l_x_msg_count);
IF l_x_return_status <> 'S'
THEN
----DBMS_OUTPUT.put_line ('3' || l_error_message);
FOR k IN 1 .. l_x_msg_count
LOOP
----- DBMS_OUTPUT.put_line ('4' || l_error_message);
DBMS_OUTPUT.put_line
( TO_CHAR (k)
|| ' MESSAGE TEXT '
|| SUBSTR
(l_error_message_list (k).MESSAGE_TEXT,
1,
250
)
);
DBMS_OUTPUT.put_line ( TO_CHAR (k)
|| ' MESSAGE TYPE '
|| l_error_message_list (k).MESSAGE_TYPE
);
l_error_message :=
l_error_message
|| TO_CHAR (k)
|| ' MESSAGE TEXT '
|| SUBSTR (l_error_message_list (k).MESSAGE_TEXT, 1, 250);
ROLLBACK;
UPDATE pure_bom_update_stg
SET status_flag = l_x_return_status,
status_message = l_error_message,
process_flag = 'I'
WHERE organization_name = j.organization_name
AND bom_code = j.bom_code
AND component_code = j.component_code
AND operation_seq_num = j.operation_seq_num
AND item_sequence = j.item_num;
COMMIT;
END LOOP;
DBMS_OUTPUT.put_line (l_error_message);
ELSE
IF l_x_return_status = 'S'
THEN
UPDATE pure_bom_update_stg
SET status_flag = 'C', ----l_x_return_status,
status_message = 'END DATED',
process_flag = 'C'
WHERE organization_name = j.organization_name
AND bom_code = j.bom_code
AND component_code = j.component_code
AND operation_seq_num = j.operation_seq_num
AND item_sequence = j.item_num;
COMMIT;
END IF;
END IF;
END LOOP; ---END C_PROCESS
END;
SELECT 1
FROM DUAL
