I am using XX_BOM_ROUTING_STG staging table where I have uploaded Routing data through SQL loader. I am sharing you two procedures.
In the First procedure , I will share you the validation script so that you can
validate the Date for your Staging table like the values you are uploading
is available in Oracle apps
or not.
In the Second Procedure I will share your the Execution script to upload
validated date in Routing Interface Tables.
Second Procedure (After Validation Upload Routing Data in Oracle)
PROCEDURE xxdata_upload_routings
IS
---------Cursor
For Routing Header----------------
org_id NUMBER;
item_id NUMBER;
v_error_msg VARCHAR2 (32767);
CURSOR csr_routings_header
IS organization_code, item_number, completion_subinventory,
completion_locator_id, process_revision, alternate_name,
entry_id
FROM XXdata_BOM_ROUTING
WHERE NVL (VAILIDATE_FLAG_PROCESS, 'E') = 'P'
and nvl(FLAG_PROCESS,'NULL')='NULL'
GROUP BY organization_code,
item_number,
completion_subinventory,
completion_locator_id,
PROCESS_REVISION,
ALTERNATE_NAME;
---------Cursor
For Operations -------------------
CURSOR csr_routings_operation (
v_organization_code VARCHAR2,
v_item_number VARCHAR2,
v_completion_subinventory VARCHAR2,
v_completion_locator_id NUMBER,
v_process_revision VARCHAR2,
v_alternate_name VARCHAR2
)
IS
SELECT routing_seq_num, operation_code, operation_desc,
department_code, reference_flag, effectivity_date,
include_in_rollup, entry_id
FROM xxdata_lbom_routing
WHERE NVL(VAILIDATE_FLAG_PROCESS,'E') ='P'
AND NVL(FLAG_PROCESS,'NULL') ='NULL'
GROUP BY routing_seq_num,
operation_code,
operation_desc,
department_code,
reference_flag,
EFFECTIVITY_DATE,
INCLUDE_IN_ROLLUP;
---------Cursor
For Resources --------------------
CURSOR csr_routings_resource (
v_organization_code VARCHAR2,
v_item_number VARCHAR2,
v_completion_subinventory VARCHAR2,
v_completion_locator_id VARCHAR2,
v_process_revision VARCHAR2,
v_alternate_name VARCHAR2,
v_routing_seq_num NUMBER,
v_operation_code VARCHAR2,
v_department_code VARCHAR2
)
IS
SELECT resource_seq_num, resource_code, resource_desc, USAGE,
schedule_flag, schedule_seq_num, assigned_units, entry_id
FROM xxdata_lbom_routing
WHERE NVL(VAILIDATE_FLAG_PROCESS,'E') ='P'
AND NVL(FLAG_PROCESS,'NULL') ='NULL'
GROUP BY resource_seq_num,
resource_code,
resource_desc,
USAGE,
schedule_flag,
SCHEDULE_SEQ_NUM,
ASSIGNED_UNITS;
CURSOR csr_org_id (org_code VARCHAR2)
IS
SELECT a.organization_id
FROM mtl_parameters a, org_organization_definitions
b
WHERE a.master_organization_id = b.organization_id
AND a.organization_code = org_code;
CURSOR csr_item_id (org_id NUMBER, item_number VARCHAR2)
IS
SELECT inventory_item_id
FROM mtl_system_items_b
WHERE organization_id = org_id AND segment1 = item_number;
CURSOR cur_operation_detail (org_id NUMBER, v_operation_code VARCHAR2)
IS
SELECT bso.department_id, bso.standard_operation_id,
bso.operation_type, bso.minimum_transfer_quantity,
bso.count_point_type, bso.backflush_flag,
bso.operation_yield_enabled
FROM bom_standard_operations
bso
WHERE bso.operation_code = v_operation_code
AND bso.organization_id = org_id;
dept_id NUMBER;
v_standard_operation_id NUMBER;
v_operation_type NUMBER;
v_minimum_transfer_quantity NUMBER;
v_count_point_type NUMBER;
v_backflush_flag NUMBER;
v_option_dependent_flag NUMBER;
v_operation_yield_enabled NUMBER;
--------------------------------------------------
BEGIN
-----------------For
Header Insertion -----------------
BEGIN
FOR l_routing_rec IN csr_routings_header
LOOP
OPEN csr_org_id (l_routing_rec.organization_code);
FETCH csr_org_id
INTO org_id;
IF csr_org_id%NOTFOUND
THEN
CLOSE csr_org_id;
END IF;
CLOSE csr_org_id;
OPEN csr_item_id (org_id, l_routing_rec.item_number);
FETCH csr_item_id
INTO item_id;
IF csr_item_id%NOTFOUND
THEN
CLOSE csr_item_id;
END IF;
CLOSE csr_item_id;
------------------------Insert
header -----------------
INSERT INTO bom_op_routings_interface
(organization_id, assembly_item_id,
organization_code,
assembly_item_number, routing_type,
completion_subinventory,
completion_locator_id,
process_revision,
alternate_routing_designator,
common_routing_sequence_id, common_item_number,
attribute15, process_flag, transaction_type
)
VALUES (org_id, item_id,
l_routing_rec.organization_code,
l_routing_rec.item_number, 1,
l_routing_rec.completion_subinventory,
l_routing_rec.completion_locator_id,
l_routing_rec.process_revision,
l_routing_rec.alternate_name,
NULL, NULL,
'Loaded
By data_loading Team', 1, 'CREATE'
);
-----------------Inner Loop for Routings
Operation -------------
BEGIN
FOR l_operation IN
csr_routings_operation
(l_routing_rec.organization_code,
l_routing_rec.item_number,
l_routing_rec.completion_subinventory,
l_routing_rec.completion_locator_id,
l_routing_rec.process_revision,
l_routing_rec.alternate_name
)
LOOP
----------Refresh
Vairiable -------------------
BEGIN
v_standard_operation_id := NULL;
v_operation_type := NULL;
v_minimum_transfer_quantity := NULL;
v_count_point_type := NULL;
v_backflush_flag := NULL;
v_option_dependent_flag := NULL;
v_operation_yield_enabled := NULL;
END;
-------------iNSERT RECORD FOR OPERATION
BEGIN
OPEN cur_operation_detail (org_id,
l_operation.operation_code
);
FETCH cur_operation_detail
INTO dept_id, v_standard_operation_id, v_operation_type,
v_minimum_transfer_quantity, v_count_point_type,
v_backflush_flag, v_operation_yield_enabled;
CLOSE cur_operation_detail;
BEGIN
INSERT INTO bom_op_sequences_interface
(organization_id, assembly_item_id,
organization_code,
assembly_item_number,
operation_seq_num,
department_code,
operation_code,
reference_flag,
effectivity_date,
count_point_type, backflush_flag,
operation_type,
minimum_transfer_quantity,
include_in_rollup,
alternate_routing_designator,
attribute15, process_flag,
transaction_type
)
VALUES (org_id, item_id,
l_routing_rec.organization_code,
l_routing_rec.item_number,
l_operation.routing_seq_num,
l_operation.department_code,
l_operation.operation_code,
DECODE (NVL (l_operation.reference_flag,
'N'
),
'N', 2,
1
),
NVL (l_operation.effectivity_date,
SYSDATE
),
v_count_point_type, v_backflush_flag,
v_operation_type,
v_minimum_transfer_quantity,
DECODE
(NVL (l_operation.include_in_rollup,
'N'
),
'N', 2,
1
),
l_routing_rec.alternate_name,
'Loaded By ', 1,
'CREATE'
);
EXCEPTION
WHEN OTHERS
THEN
v_error_msg := SQLERRM;
DELETE FROM bom_op_routings_interface
WHERE assembly_item_number =
l_routing_rec.item_number
AND organization_code =
l_routing_rec.organization_code;
UPDATE xxdata_lbom_routing
SET flag_process = 'E',
error_message = v_error_msg
WHERE organization_code =
l_routing_rec.organization_code
AND item_number = l_routing_rec.item_number
AND operation_code = l_operation.operation_code
AND department_code =
l_operation.department_code
AND routing_seq_num =
l_operation.routing_seq_num;
COMMIT;
END;
END;
---------------------Loop For Resources ------------------------
BEGIN
FOR l_resource IN
csr_routings_resource
(l_routing_rec.organization_code,
l_routing_rec.item_number,
l_routing_rec.completion_subinventory,
l_routing_rec.completion_locator_id,
l_routing_rec.process_revision,
l_routing_rec.alternate_name,
l_operation.routing_seq_num,
l_operation.operation_code,
l_operation.department_code
)
LOOP
IF l_resource.resource_code IS NOT NULL
THEN
BEGIN
INSERT INTO bom_op_resources_interface
(organization_id,
assembly_item_id,
organization_code,
assembly_item_number,
operation_seq_num,
operation_type,
resource_seq_num,
resource_code, activity,
usage_rate_or_amount,
schedule_flag,
schedule_seq_num,
assigned_units, principle_flag,
alternate_routing_designator,
effectivity_date,
attribute15, process_flag,
transaction_type
)
VALUES (org_id,
item_id,
l_routing_rec.organization_code,
l_routing_rec.item_number,
l_operation.routing_seq_num,
v_operation_type,
l_resource.resource_seq_num,
l_resource.resource_code, NULL,
l_resource.USAGE,
DECODE
(NVL (l_resource.schedule_flag,
'N'
),
'N', 2,
1
),
l_resource.schedule_seq_num,
l_resource.assigned_units, NULL,
l_routing_rec.alternate_name,
NVL (l_operation.effectivity_date,
SYSDATE
),
'Loaded By ', 1,
'CREATE'
);
UPDATE xxdata_lbom_routing
SET flag_process = 'P'
WHERE entry_id = l_resource.entry_id;
EXCEPTION
WHEN OTHERS
THEN
v_error_msg := SQLERRM;
UPDATE xxdata_lbom_routing
SET flag_process = 'E',
error_message = v_error_msg
WHERE entry_id = l_resource.entry_id;
END;
END IF;
END LOOP;
COMMIT;
END;
END LOOP;
END;
COMMIT;
END LOOP;
END;
END xxdata_upload_routings;
No comments:
Post a Comment