Part 2 : BOM Routing Interface in Oracle Apps (Interface Script to Upload data in Oracle Apps)

In this post , I will share you the PLSQL code for BOM Routing Interface.

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;