What is maximum number of levels in BOM?

Maximum bill can be defined is 60.

Difference between Discrete and Process manufacturing?

Process	Discrete
Use formulations or recipes.	Use Bills of Materials (BOMs).
A process manufacturer blends in a batch.	A discrete manufacturer assembles along a routing.
Variable Ingredients, by and Co- products	Standard parts, Components
Attribute driven	Part Number driven
UOM are Material Specific	UOM are Each & Piece
Need Lot, Grades, Potency, Shelf- life	Need Serial Numbers, ECN’s
Mixes, Blends, Transforms	Builds, Assembles, Fabricates
Makes “STUFF”	Make “THINGS”

Explain Discrete, Flow and Process manufacturing?

Discrete manufacturing: is distinguished by the production of distinct items that use bills of material and routings to determine costs and lead times.

Examples: Automobile manufacturing, computer manufacturing, dishwasher and washing machine manufacturing, etc.

Flow manufacturing: is also called Lean Manufacturing or JIT(Just-In-Time) and uses the principles of demand flow manufacturing to produce product is required, for customer orders, quickly and to meet the demand exactly on-time.

Usually, in order to use Flow a company needs to have production facilities that subscribe to the principals of Lean Manufacturing. It is necessary to design production facilities so that production lines, resources, machines, and labor are making only what is needed for immediate customer orders.

Process manufacturing: is distinguished by a production approach that has minimal interruptions in actual

processing in any one production run, or between production runs of similar products. This approach produces multiple unique products in relatively small batches flowing through different production operations throughout the factory.

They are normally producing a Product that is "Homogeneous" and equally divided for the convenience of packaging.

Typical examples are Food, pharmaceutical and other batched-based manufacturers such as refineries, wineries, etc.

API to Create Routing in oracle apps (BOM_RTG_PUB.PROCESS_RTG)

 CREATE TABLE APPS.GEPS_MAINTENANCE_ROUTES
(
  ORGANIZATION_CODE             VARCHAR2(3 BYTE),
  ASSEMBLY_ITEM_NAME            VARCHAR2(40 BYTE),
  REVISION                      VARCHAR2(40 BYTE),
  ROUTING_DATE                  VARCHAR2(100 BYTE),
  OPERATION_SEQUENCE_NUMBER     VARCHAR2(500 BYTE),
  OPERATION_CODE                VARCHAR2(500 BYTE),
  REFERENCED                    VARCHAR2(40 BYTE),
  DEPARTMENT_CODE               VARCHAR2(10 BYTE),
  RESOURCE_SEQUENCE_NUMBER      NUMBER,
  RESOURCE_CODE                 VARCHAR2(10 BYTE),
  BASIS_TYPE                    VARCHAR2(10 BYTE),
  USAGE_RATE_OR_AMOUNT          NUMBER,
  USAGE_RATE_OR_AMOUNT_INVERSE  NUMBER,
  ASSIGNED_UNITS                NUMBER,
  LONG_DESCRIPTION              VARCHAR2(4000 BYTE),
  OPERATION_DESCRIPTION         VARCHAR2(4000 BYTE),
  PROCESS_FLAG                  VARCHAR2(10 BYTE),
  ERROR_MESSAGE                 VARCHAR2(4000 BYTE),
  ERROR_MESSAGE_TYPE            VARCHAR2(100 BYTE),
  TRANSACTION_TYPE              VARCHAR2(20 BYTE),
  TRANS_FLAG                    VARCHAR2(100 BYTE)
)




CREATE OR REPLACE PROCEDURE APPS.GEPS_MAINTENANCE_ROUTES_PRC
(errbuf OUT VARCHAR2,
RETCODE OUT NUMBER)
AS
P_HEADER_REC Bom_Rtg_Pub.Rtg_Header_Rec_Type := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
P_OPERATION_REC Bom_Rtg_Pub.Operation_Tbl_Type := Bom_Rtg_Pub.G_MISS_OPERATION_TBL;
P_RESOURCE_REC Bom_Rtg_Pub.Op_Resource_Tbl_Type := Bom_Rtg_Pub.G_MISS_OP_RESOURCE_TBL;
p_RETURN_STATUS VARCHAR2 (240);
p_MSG_COUNT NUMBER:=0;
l_ret_text VARCHAR2 (4000);
p_Message_List Error_Handler.Error_Tbl_Type;
i NUMBER;
j NUMBER;
x_rtg_header_rec Bom_Rtg_Pub.Rtg_Header_Rec_Type;
x_rtg_revision_tbl Bom_Rtg_Pub.Rtg_Revision_Tbl_Type;
x_operation_tbl Bom_Rtg_Pub.Operation_Tbl_Type;
x_op_resource_tbl Bom_Rtg_Pub.Op_Resource_Tbl_Type;
x_sub_resource_tbl Bom_Rtg_Pub.Sub_Resource_Tbl_Type;
x_op_network_tbl Bom_Rtg_Pub.Op_Network_Tbl_Type;
l_hdritem_cnt                number;

CURSOR C1_ENG IS
SELECT DISTINCT t.assembly_item_name,
t.organization_code
--t.transaction_type
FROM APPS.GEPS_MAINTENANCE_ROUTES t
WHERE PROCESS_FLAG = 'P';

CURSOR C1_OPR (p_ASSEMBLY_ITEM VARCHAR2, p_org_code VARCHAR2) IS
SELECT DISTINCT t.assembly_item_name,
t.organization_code,
t.operation_sequence_number,
t.operation_code,
t.referenced,
t.department_code,
t.transaction_type,
LTRIM (RTRIM (T.LONG_DESCRIPTION)) LONG_DESCRIPTION,
LTRIM (RTRIM (t.OPERATION_DESCRIPTION)) OPERATION_DESCRIPTION
FROM  APPS.GEPS_MAINTENANCE_ROUTES t
WHERE t.assembly_item_name = p_ASSEMBLY_ITEM
AND t.organization_code = p_org_code
and t.operation_sequence_number is not null
AND PROCESS_FLAG = 'P'
ORDER BY t.operation_sequence_number;

CURSOR C1_RES (p_ASSEMBLY_ITEM VARCHAR2, p_org_code VARCHAR2, p_seq_number NUMBER) IS
SELECT DISTINCT t.assembly_item_name,
t.organization_code,
t.operation_sequence_number,
t.operation_code,
t.referenced,
t.department_code,
t.resource_sequence_number,
t.resource_code,
t.basis_type,
t.usage_rate_or_amount,
t.usage_rate_or_amount_inverse,
t.ASSIGNED_UNITS,
t.transaction_type
FROM  APPS.GEPS_MAINTENANCE_ROUTES t
WHERE t.assembly_item_name = p_ASSEMBLY_ITEM
AND t.organization_code = p_org_code
AND t.operation_sequence_number = p_seq_number
and t.resource_sequence_number is not null
AND PROCESS_FLAG in( 'S')
ORDER BY t.resource_sequence_number;

l_operation_sequence_id number;

BEGIN

   FND_FILE.PUT_LINE (FND_FILE.LOG,'Starting of GEPS_MAINTENANCE_ROUTES_PRC Procedure');
   --FND_GLOBAL.apps_initialize (1001255, 50326, 700, 0);
   FND_FILE.PUT_LINE (FND_FILE.LOG, 'Initializing Apps');
   FND_GLOBAL.Apps_Initialize (FND_GLOBAL.USER_ID,
                               FND_GLOBAL.RESP_ID,
                               FND_GLOBAL.RESP_APPL_ID);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'P_MSG_COUNT' ||P_MSG_COUNT);


--FND_GLOBAL.APPS_INITIALIZE (1117, 23118, 426); --user_id, resp_id, resp_appl_id
 FOR  C_REC IN C1_ENG
 LOOP
      FND_FILE.PUT_LINE (FND_FILE.LOG, 'loop start');
      FND_FILE.PUT_LINE (FND_FILE.LOG, ' C_REC.ASSEMBLY_ITEM_NAME:' ||  C_REC.ASSEMBLY_ITEM_NAME);
      FND_FILE.PUT_LINE (FND_FILE.LOG, 'C_REC.ORGANIZATION_CODE:' ||C_REC.ORGANIZATION_CODE);
--FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_REC.ASSEMBLY_ITEM_NAME);
--DBMS_OUTPUT.PUT_LINE (C_REC.ASSEMBLY_ITEM_NAME);

/*BEGIN
SELECT   count(msi.segment1)
INTO l_hdritem_cnt
  FROM   BOM_OPERATIONAL_ROUTINGS bor,
         mtl_system_items_b msi,
         org_organization_definitions ood
 WHERE       msi.inventory_item_id = bor.ASSEMBLY_ITEM_ID
         AND msi.ORGANIZATION_ID = bor.ORGANIZATION_ID
         AND msi.organization_id = ood.organization_id
         AND bor.organization_id = ood.organization_id
         AND ood.organization_code = C_REC.ORGANIZATION_CODE
         AND msi.segment1 =  C_REC.ASSEMBLY_ITEM_NAME;
EXCEPTION
         WHEN OTHERS
         THEN
            l_hdritem_cnt := 0;
END;

FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_hdritem_cnt:' || l_hdritem_cnt);

P_HEADER_REC := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;*/


--P_HEADER_REC.Transaction_Type: = 'CREATE'; -
P_HEADER_REC.Return_Status := NULL;
i := 0;
--j := 0;
--P_OPERATION_REC.DELETE;
--P_RESOURCE_REC.DELETE;

 FOR C_REC2 IN C1_OPR (C_REC.ASSEMBLY_ITEM_NAME, C_REC.ORGANIZATION_CODE)
 LOOP

 FND_FILE.PUT_LINE (FND_FILE.LOG, 'C1_OPR Loop Start .....' );

x_rtg_header_rec := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
x_rtg_revision_tbl.delete;
x_operation_tbl.delete;
x_op_resource_tbl.delete;
x_sub_resource_tbl.delete;
x_op_network_tbl.delete;
p_message_list.delete;
P_OPERATION_REC.DELETE;
i := 1;

--P_OPERATION_REC (i) .OPERATION_SEQUENCE_NUMBER :=null;

BEGIN
SELECT   count(msi.segment1)
INTO l_hdritem_cnt
  FROM   BOM_OPERATIONAL_ROUTINGS bor,
         mtl_system_items_b msi,
         org_organization_definitions ood
 WHERE       msi.inventory_item_id = bor.ASSEMBLY_ITEM_ID
         AND msi.ORGANIZATION_ID = bor.ORGANIZATION_ID
         AND msi.organization_id = ood.organization_id
         AND bor.organization_id = ood.organization_id
         AND ood.organization_code = C_REC.ORGANIZATION_CODE
         AND msi.segment1 =  C_REC.ASSEMBLY_ITEM_NAME;
EXCEPTION
         WHEN OTHERS
         THEN
            l_hdritem_cnt := 0;
END;

FND_FILE.PUT_LINE (FND_FILE.LOG, 'C1_OPR Loop l_hdritem_cnt:' || l_hdritem_cnt);

P_HEADER_REC := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;

IF l_hdritem_cnt < 1 then
P_HEADER_REC.ASSEMBLY_ITEM_NAME := C_REC.ASSEMBLY_ITEM_NAME;
P_HEADER_REC.ORGANIZATION_CODE := C_REC.ORGANIZATION_CODE;
P_HEADER_REC.Eng_Routing_Flag := 2; -- 2 for manufacturing 1- for Engineering
--P_HEADER_REC.alternate_routing_code := 1;
--P_HEADER_REC.cfm_routing_flag :=1;
P_HEADER_REC.transaction_type := 'CREATE';

ELSE
P_HEADER_REC.ASSEMBLY_ITEM_NAME := NULL;
P_HEADER_REC.ORGANIZATION_CODE :=NULL;
P_HEADER_REC.Eng_Routing_Flag := NULL; -- 2 for manufacturing 1- for Engineering
P_HEADER_REC.transaction_type := NULL;

END IF;

     FND_FILE.PUT_LINE (FND_FILE.LOG, ' P_HEADER_REC.Eng_Routing_Flag:' ||P_HEADER_REC.Eng_Routing_Flag);
     FND_FILE.PUT_LINE (FND_FILE.LOG, ' i:' ||i);
     FND_FILE.PUT_LINE (FND_FILE.LOG, ' C_REC2.OPERATION_SEQUENCE_NUMBER:' ||C_REC2.OPERATION_SEQUENCE_NUMBER);
     FND_FILE.PUT_LINE (FND_FILE.LOG, 'C_REC2.OPERATION_CODE:' ||C_REC2.OPERATION_CODE);
     FND_FILE.PUT_LINE (FND_FILE.LOG, 'C_REC2.REFERENCED:' ||C_REC2.REFERENCED);
     FND_FILE.PUT_LINE (FND_FILE.LOG, 'C_REC2.DEPARTMENT_CODE:' ||C_REC2.DEPARTMENT_CODE);
     FND_FILE.PUT_LINE (FND_FILE.LOG, 'C_REC2.LONG_DESCRIPTION:' ||C_REC2.LONG_DESCRIPTION);
     FND_FILE.PUT_LINE (FND_FILE.LOG, 'C_REC2.OPERATION_DESCRIPTION:' ||C_REC2.OPERATION_DESCRIPTION);



P_OPERATION_REC (i) .ASSEMBLY_ITEM_NAME := C_REC2.ASSEMBLY_ITEM_NAME;
P_OPERATION_REC (i) .ORGANIZATION_CODE := C_REC2.ORGANIZATION_CODE;
-- FND_FILE.PUT_LINE (FND_FILE.LOG, ' P_OPERATION_REC (i) .OPERATION_SEQUENCE_NUMBER11:' ||P_OPERATION_REC (i) .OPERATION_SEQUENCE_NUMBER);
P_OPERATION_REC (i) .OPERATION_SEQUENCE_NUMBER := C_REC2.OPERATION_SEQUENCE_NUMBER;
-- FND_FILE.PUT_LINE (FND_FILE.LOG, ' P_OPERATION_REC (i) .OPERATION_SEQUENCE_NUMBER22:' ||P_OPERATION_REC (i) .OPERATION_SEQUENCE_NUMBER);
P_OPERATION_REC (i) .STANDARD_OPERATION_CODE := C_REC2.OPERATION_CODE;
if (C_REC2.REFERENCED ='Yes' or C_REC2.REFERENCED ='YES') then
P_OPERATION_REC (i) .REFERENCE_FLAG := 1;
else
P_OPERATION_REC (i) .REFERENCE_FLAG := 2;
end if;
P_OPERATION_REC (i) .DEPARTMENT_CODE := C_REC2.DEPARTMENT_CODE;
P_OPERATION_REC (i) .LONG_DESCRIPTION := C_REC2.LONG_DESCRIPTION; --Ana Tabloda long description diye alan bir aç
P_OPERATION_REC (i) .OPERATION_DESCRIPTION := C_REC2.OPERATION_DESCRIPTION;
P_OPERATION_REC (i) .Operation_Type := 1;

      IF C_REC2.TRANSACTION_TYPE LIKE '%CREATE%'
      THEN
        P_OPERATION_REC (i).transaction_type := 'CREATE';
    P_OPERATION_REC (i) .Start_Effective_Date := SYSDATE;
    P_OPERATION_REC (i) .Disable_Date := null;

    /*IF i <= 1 then
    P_OPERATION_REC (i).transaction_type := 'CREATE';
    else
    P_OPERATION_REC (i).transaction_type := 'UPDATE';
    end if;*/
         FND_FILE.PUT_LINE (            FND_FILE.LOG, 'sss1.1-C_REC2. ' ||P_OPERATION_REC (i).transaction_type);
      ELSE
         P_OPERATION_REC (i).transaction_type := 'UPDATE';
     BEGIN
         SELECT  bos.EFFECTIVITY_DATE
         INTO P_OPERATION_REC (i) .Start_Effective_Date
          FROM   BOM_OPERATIONAL_ROUTINGS_V bor,
             mtl_system_items_b msi,
             BOM_OPERATION_SEQUENCES_V bos,
              org_organization_definitions ood
         WHERE       msi.inventory_item_id = bor.ASSEMBLY_ITEM_ID
             AND msi.ORGANIZATION_ID = bor.ORGANIZATION_ID
             AND msi.ORGANIZATION_ID = ood.ORGANIZATION_ID
             AND bor.ORGANIZATION_ID = ood.ORGANIZATION_ID
             AND bor.ROUTING_SEQUENCE_ID = bos.ROUTING_SEQUENCE_ID
             AND ood.ORGANIZATION_CODE = C_REC2.ORGANIZATION_CODE
             and msi.segment1 = C_REC2.ASSEMBLY_ITEM_NAME
             and bos.OPERATION_SEQ_NUM =C_REC2.OPERATION_SEQUENCE_NUMBER;
        P_OPERATION_REC (i) .Disable_Date := null;
     EXCEPTION WHEN OTHERS THEN
            P_OPERATION_REC (i) .Start_Effective_Date := SYSDATE;
            P_OPERATION_REC (i) .Disable_Date := null;
     END;

      --   FND_FILE.PUT_LINE (FND_FILE.LOG, 'sss1.1-C_REC2. ' || P_OPERATION_REC (i).transaction_type );
      END IF;
     BEGIN
         SELECT  bos.EFFECTIVITY_DATE
         INTO P_OPERATION_REC (i) .Start_Effective_Date
          FROM   BOM_OPERATIONAL_ROUTINGS_V bor,
             mtl_system_items_b msi,
             BOM_OPERATION_SEQUENCES_V bos,
              org_organization_definitions ood
         WHERE       msi.inventory_item_id = bor.ASSEMBLY_ITEM_ID
             AND msi.ORGANIZATION_ID = bor.ORGANIZATION_ID
             AND msi.ORGANIZATION_ID = ood.ORGANIZATION_ID
             AND bor.ORGANIZATION_ID = ood.ORGANIZATION_ID
             AND bor.ROUTING_SEQUENCE_ID = bos.ROUTING_SEQUENCE_ID
             AND ood.ORGANIZATION_CODE = C_REC2.ORGANIZATION_CODE
             and msi.segment1 = C_REC2.ASSEMBLY_ITEM_NAME
             and bos.OPERATION_SEQ_NUM =C_REC2.OPERATION_SEQUENCE_NUMBER;
        P_OPERATION_REC (i) .Disable_Date := null;
     EXCEPTION WHEN OTHERS THEN
            P_OPERATION_REC (i) .Start_Effective_Date := SYSDATE;
            P_OPERATION_REC (i) .Disable_Date := null;
     END;

        -- FND_FILE.PUT_LINE (FND_FILE.LOG, 'sss1.1-C_REC2. ' || P_OPERATION_REC (i).transaction_type );

--x_rtg_header_rec := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;


P_RETURN_STATUS :=null;
P_MSG_COUNT :=0;
l_ret_text :=null;

--FND_FILE.PUT_LINE (FND_FILE.LOG, 'P_RETURN_STATUS00' ||P_RETURN_STATUS);
--FND_FILE.PUT_LINE (FND_FILE.LOG, 'P_MSG_COUNT00' ||P_MSG_COUNT);
--FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_ret_text00' ||l_ret_text);

begin
APPS.BOM_RTG_PUB.PROCESS_RTG (p_init_msg_list  => FALSE,
p_rtg_header_rec  => P_HEADER_REC,
p_operation_tbl => P_OPERATION_REC,
--p_op_resource_tbl => P_RESOURCE_REC,
x_rtg_header_rec => x_rtg_header_rec,
x_rtg_revision_tbl => x_rtg_revision_tbl,
x_operation_tbl => x_operation_tbl,
x_op_resource_tbl => x_op_resource_tbl,
x_sub_resource_tbl => x_sub_resource_tbl,
x_op_network_tbl => x_op_network_tbl,
x_return_status => P_RETURN_STATUS,
X_MSG_COUNT => P_MSG_COUNT);
exception when others then
FND_FILE.PUT_LINE (FND_FILE.LOG, 'OPERATION exception' ||sqlerrm);
FND_FILE.PUT_LINE (FND_FILE.LOG, 'OPERATION P_RETURN_STATUSEXP' ||P_RETURN_STATUS);
FND_FILE.PUT_LINE (FND_FILE.LOG, 'OPERATION P_MSG_COUNTEXP' ||P_MSG_COUNT);
FND_FILE.PUT_LINE (FND_FILE.LOG, 'OPERATION l_ret_textEXP' ||l_ret_text);
end;


--FND_FILE.PUT_LINE (FND_FILE.LOG, 'P_RETURN_STATUS' ||P_RETURN_STATUS);
--FND_FILE.PUT_LINE (FND_FILE.LOG, 'P_MSG_COUNT' ||P_MSG_COUNT);
--FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_ret_text' ||l_ret_text);

IF P_RETURN_STATUS <> 'S' THEN
error_handler.Get_Message_List (p_message_list);
l_ret_text := null;

FOR i IN 1 .. p_message_list.COUNT LOOP

IF i = 1 THEN
--if l_ret_text is null then
FND_FILE.PUT_LINE (FND_FILE.LOG, 'p_Message_List (i) .message_text,' ||p_Message_List (i) .message_text);

l_ret_text := substr(p_Message_List (i) .message_text,1,250);

ELSE
FND_FILE.PUT_LINE (FND_FILE.LOG, 'p_Message_List (i) .message_textelse' ||p_Message_List (i) .message_text);

l_ret_text := l_ret_text || '~'|| substr(p_Message_List (i) .message_text,1,250);
END IF;

END LOOP;

ROLLBACK;


FND_FILE.PUT_LINE (FND_FILE.LOG, 'ERROR!!!!' ||c_rec.assembly_item_name||'-'|| c_rec.organization_code||'-'||l_ret_text);


UPDATE APPS.GEPS_MAINTENANCE_ROUTES xxinn
SET XXINN.ERROR_MESSAGE = l_ret_text,
PROCESS_FLAG ='E'
WHERE XXINN.ASSEMBLY_ITEM_NAME = c_rec.assembly_item_name
AND XXINN.ORGANIZATION_CODE = c_rec.organization_code
AND xxinn.operation_sequence_number = C_REC2.OPERATION_SEQUENCE_NUMBER;

commit;

ELSE


FND_FILE.PUT_LINE (FND_FILE.LOG, 'The process is successful ..' ||c_rec.assembly_item_name||'-'|| c_rec.organization_code);

UPDATE APPS.GEPS_MAINTENANCE_ROUTES xxinn
SET PROCESS_FLAG ='S'
WHERE XXINN.ASSEMBLY_ITEM_NAME = c_rec.assembly_item_name
AND XXINN.ORGANIZATION_CODE = c_rec.organization_code
AND xxinn.operation_sequence_number = C_REC2.OPERATION_SEQUENCE_NUMBER;
commit;

END IF;

--dbms_lock.sleep(60);

begin
select bos.operation_sequence_id
into l_operation_sequence_id from
BOM_OPERATIONAL_ROUTINGS_V bor,
BOM_OPERATION_SEQUENCES_V bos,
org_organization_definitions ood,
mtl_system_items_b msi
where   msi.inventory_item_id = bor.ASSEMBLY_ITEM_ID
and bor.ROUTING_SEQUENCE_ID = bos.ROUTING_SEQUENCE_ID
AND msi.ORGANIZATION_ID = bor.ORGANIZATION_ID
         AND msi.ORGANIZATION_ID = ood.ORGANIZATION_ID
         AND bor.ORGANIZATION_ID = ood.ORGANIZATION_ID
         and msi.segment1 =C_REC.ASSEMBLY_ITEM_NAME
         and ood.ORGANIZATION_code =C_REC.ORGANIZATION_CODE
     and bos.OPERATION_SEQ_NUM =C_REC2.OPERATION_SEQUENCE_NUMBER;
end;

FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_operation_sequence_id.' || l_operation_sequence_id );
j := 0;

-- FND_FILE.PUT_LINE (FND_FILE.LOG, 'before C1_RES Loop Start .....'||C_REC.ASSEMBLY_ITEM_NAME||'-'||C_REC.ORGANIZATION_CODE||'-'|| C_REC2.OPERATION_SEQUENCE_NUMBER);
 FOR C_rec3 IN C1_RES (C_REC.ASSEMBLY_ITEM_NAME,
C_REC.ORGANIZATION_CODE,
C_REC2.OPERATION_SEQUENCE_NUMBER) LOOP

FND_FILE.PUT_LINE (FND_FILE.LOG, 'C1_RES Loop Start .....' );


/*x_rtg_header_rec := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
x_rtg_revision_tbl.delete;
x_operation_tbl.delete;
x_op_resource_tbl.delete;
x_sub_resource_tbl.delete;
x_op_network_tbl.delete;
p_message_list.delete;
P_OPERATION_REC.DELETE;*/
P_RESOURCE_REC.DELETE;

--j := j + 1;
j:=1;


BEGIN
SELECT   count(msi.segment1)
INTO l_hdritem_cnt
  FROM   BOM_OPERATIONAL_ROUTINGS bor,
         mtl_system_items_b msi,
         org_organization_definitions ood
 WHERE       msi.inventory_item_id = bor.ASSEMBLY_ITEM_ID
         AND msi.ORGANIZATION_ID = bor.ORGANIZATION_ID
         AND msi.organization_id = ood.organization_id
         AND bor.organization_id = ood.organization_id
         AND ood.organization_code = C_REC.ORGANIZATION_CODE
         AND msi.segment1 =  C_REC.ASSEMBLY_ITEM_NAME;
EXCEPTION
         WHEN OTHERS
         THEN
            l_hdritem_cnt := 0;
END;

FND_FILE.PUT_LINE (FND_FILE.LOG, 'C1_RES Loop l_hdritem_cnt:' || l_hdritem_cnt);

P_HEADER_REC := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;

IF l_hdritem_cnt < 1 then
P_HEADER_REC.ASSEMBLY_ITEM_NAME := C_REC.ASSEMBLY_ITEM_NAME;
P_HEADER_REC.ORGANIZATION_CODE := C_REC.ORGANIZATION_CODE;
P_HEADER_REC.Eng_Routing_Flag := 2; -- 2 for manufacturing 1- for Engineering
--P_HEADER_REC.alternate_routing_code := 1;
--P_HEADER_REC.cfm_routing_flag :=1;
P_HEADER_REC.transaction_type := 'CREATE';

ELSE
P_HEADER_REC.ASSEMBLY_ITEM_NAME := NULL;
P_HEADER_REC.ORGANIZATION_CODE :=NULL;
P_HEADER_REC.Eng_Routing_Flag := NULL; -- 2 for manufacturing 1- for Engineering
P_HEADER_REC.transaction_type := NULL;

END IF;

     FND_FILE.PUT_LINE (FND_FILE.LOG, ' j:' ||j);
      FND_FILE.PUT_LINE (FND_FILE.LOG, ' P_HEADER_REC.Eng_Routing_Flag:' ||P_HEADER_REC.Eng_Routing_Flag);
           FND_FILE.PUT_LINE (FND_FILE.LOG, 'C_REC3.OPERATION_SEQUENCE_NUMBER:' ||C_REC3.OPERATION_SEQUENCE_NUMBER);
     FND_FILE.PUT_LINE (FND_FILE.LOG, 'C_REC3.RESOURCE_SEQUENCE_NUMBER:' ||C_REC3.RESOURCE_SEQUENCE_NUMBER);
     FND_FILE.PUT_LINE (FND_FILE.LOG, 'C_REC3.RESOURCE_CODE:' ||C_REC3.RESOURCE_CODE);
     FND_FILE.PUT_LINE (FND_FILE.LOG, ' C_REC3.BASIS_TYPE:' || C_REC3.BASIS_TYPE);
     FND_FILE.PUT_LINE (FND_FILE.LOG, 'C_REC3.USAGE_RATE_OR_AMOUNT:' ||C_REC3.USAGE_RATE_OR_AMOUNT);
     FND_FILE.PUT_LINE (FND_FILE.LOG, 'C_REC3.USAGE_RATE_OR_AMOUNT_INVERSE:' ||C_REC3.USAGE_RATE_OR_AMOUNT_INVERSE);

P_RESOURCE_REC (j) .ASSEMBLY_ITEM_NAME := C_REC3.ASSEMBLY_ITEM_NAME;
P_RESOURCE_REC (j) .ORGANIZATION_CODE := C_REC3.ORGANIZATION_CODE;
P_RESOURCE_REC (j) .OPERATION_SEQUENCE_NUMBER := C_REC3.OPERATION_SEQUENCE_NUMBER;
P_RESOURCE_REC (j) .RESOURCE_SEQUENCE_NUMBER := C_REC3.RESOURCE_SEQUENCE_NUMBER;
P_RESOURCE_REC (j) .RESOURCE_CODE := C_REC3.RESOURCE_CODE;
           FND_FILE.PUT_LINE (FND_FILE.LOG, 'P_RESOURCE_REC (j) .OPERATION_SEQUENCE_NUMBER:' ||P_RESOURCE_REC (j) .OPERATION_SEQUENCE_NUMBER);
          FND_FILE.PUT_LINE (FND_FILE.LOG, 'P_RESOURCE_REC (j) .RESOURCE_SEQUENCE_NUMBER:' ||P_RESOURCE_REC (j) .RESOURCE_SEQUENCE_NUMBER);
if  (C_REC3.BASIS_TYPE ='Lot' or C_REC3.BASIS_TYPE ='LOT') then
P_RESOURCE_REC (j) .BASIS_TYPE := 2;
else
P_RESOURCE_REC (j) .BASIS_TYPE := 1;
end if;
FND_FILE.PUT_LINE (FND_FILE.LOG, 'P_RESOURCE_REC (j) .BASIS_TYPE :' ||P_RESOURCE_REC (j) .BASIS_TYPE );
P_RESOURCE_REC (j) .USAGE_RATE_OR_AMOUNT := C_REC3.USAGE_RATE_OR_AMOUNT;
P_RESOURCE_REC (j) .USAGE_RATE_OR_AMOUNT_INVERSE := C_REC3.USAGE_RATE_OR_AMOUNT_INVERSE;
P_RESOURCE_REC (j) .Operation_Type  := 1;
--P_RESOURCE_REC (j) .Op_Start_Effective_Date := SYSDATE;
--P_RESOURCE_REC (j) .Transaction_Type: = 'CREATE';
      /*IF C_REC3.TRANSACTION_TYPE LIKE '%CREATE%'
      THEN
         P_RESOURCE_REC (j) .Transaction_Type := 'CREATE'; -- l_rec.TRANSACTION_TYPE;--'CREATE';
         FND_FILE.PUT_LINE (            FND_FILE.LOG, 'sss1.1- ' ||P_RESOURCE_REC (j) .Transaction_Type);
      ELSE
         P_RESOURCE_REC (j) .Transaction_Type := 'UPDATE';
         FND_FILE.PUT_LINE (FND_FILE.LOG, 'sss1.2- ' || P_RESOURCE_REC (j) .Transaction_Type );
      END IF;*/

    IF C_REC2.TRANSACTION_TYPE LIKE '%CREATE%' THEN
        P_RESOURCE_REC (j) .Op_Start_Effective_Date := SYSDATE;
        IF P_OPERATION_REC (i) .STANDARD_OPERATION_CODE is not null and P_OPERATION_REC (i) .DEPARTMENT_CODE is null then
        --P_OPERATION_REC (i).transaction_type := 'UPDATE';
        P_RESOURCE_REC (j) .Transaction_Type := 'UPDATE';
        /*IF j <= 1 then
        P_OPERATION_REC (i).transaction_type := 'CREATE';
        P_RESOURCE_REC (j) .Transaction_Type := 'UPDATE';
        else
        P_OPERATION_REC (i).transaction_type := 'UPDATE';
        P_RESOURCE_REC (j) .Transaction_Type := 'UPDATE';
        end if;*/
        ELSIF P_OPERATION_REC (i) .STANDARD_OPERATION_CODE is null and P_OPERATION_REC (i) .DEPARTMENT_CODE is not null then
        --P_OPERATION_REC (i).transaction_type := 'UPDATE';
        P_RESOURCE_REC (j) .Transaction_Type := 'CREATE';
        /*IF j <= 1 then
        P_OPERATION_REC (i).transaction_type := 'CREATE';
        P_RESOURCE_REC (j) .Transaction_Type := 'CREATE';
        else
        P_OPERATION_REC (i).transaction_type := 'UPDATE';
        P_RESOURCE_REC (j) .Transaction_Type := 'CREATE';
        end if;*/
        end if;
    ELSE
        BEGIN
            SELECT   bos.EFFECTIVITY_DATE
            INTO P_RESOURCE_REC (j) .Op_Start_Effective_Date
              FROM   BOM_OPERATIONAL_ROUTINGS_V bor,
                 mtl_system_items_b msi,
                 BOM_OPERATION_SEQUENCES_V bos,
                 BOM_OPERATION_RESOURCES_V borv,
                 org_organization_definitions ood
             WHERE       msi.inventory_item_id = bor.ASSEMBLY_ITEM_ID
                 AND msi.ORGANIZATION_ID = bor.ORGANIZATION_ID
                 AND msi.ORGANIZATION_ID = ood.ORGANIZATION_ID
                 AND bor.ORGANIZATION_ID = ood.ORGANIZATION_ID
                 AND bor.ROUTING_SEQUENCE_ID = bos.ROUTING_SEQUENCE_ID
                 AND bos.operation_sequence_id = borv.operation_sequence_id
                 AND ood.ORGANIZATION_CODE = C_REC3.ORGANIZATION_CODE
                 and msi.segment1 = C_REC3.ASSEMBLY_ITEM_NAME
                 and bos.OPERATION_SEQ_NUM =C_REC2.OPERATION_SEQUENCE_NUMBER
                 and borv.RESOURCE_SEQ_NUM =C_REC3.RESOURCE_SEQUENCE_NUMBER;

         EXCEPTION WHEN OTHERS THEN
            P_RESOURCE_REC (j) .Op_Start_Effective_Date := SYSDATE;
        END;
        --P_RESOURCE_REC (j) .Transaction_Type := 'CREATE';
        P_RESOURCE_REC (j) .Transaction_Type := 'UPDATE';
    END IF;

 --FND_FILE.PUT_LINE (FND_FILE.LOG, 'sss1.1- ' || P_OPERATION_REC (i).transaction_type );
 FND_FILE.PUT_LINE (FND_FILE.LOG, 'sss1.2- ' ||P_RESOURCE_REC (j) .Transaction_Type );

P_RESOURCE_REC (j) .Schedule_Flag := 1;
P_RESOURCE_REC (J) .ASSIGNED_UNITS := C_REC3.ASSIGNED_UNITS;




/*x_rtg_header_rec := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
x_rtg_revision_tbl.delete;
x_operation_tbl.delete;
x_op_resource_tbl.delete;
x_sub_resource_tbl.delete;
x_op_network_tbl.delete;*/

BEGIN
APPS.BOM_RTG_PUB.PROCESS_RTG (p_init_msg_list => FALSE,
p_rtg_header_rec => P_HEADER_REC,
--p_operation_tbl => P_OPERATION_REC,
p_op_resource_tbl => P_RESOURCE_REC,
x_rtg_header_rec => x_rtg_header_rec,
x_rtg_revision_tbl => x_rtg_revision_tbl,
x_operation_tbl => x_operation_tbl,
x_op_resource_tbl => x_op_resource_tbl,
x_sub_resource_tbl => x_sub_resource_tbl,
x_op_network_tbl => x_op_network_tbl,
x_return_status => P_RETURN_STATUS,
X_MSG_COUNT => P_MSG_COUNT);

exception when others then
FND_FILE.PUT_LINE (FND_FILE.LOG, 'RESOURCEexception' ||sqlerrm);
FND_FILE.PUT_LINE (FND_FILE.LOG, 'RESOURCEP_RETURN_STATUSEXP' ||P_RETURN_STATUS);
FND_FILE.PUT_LINE (FND_FILE.LOG, 'RESOURCEP_MSG_COUNTEXP' ||P_MSG_COUNT);
FND_FILE.PUT_LINE (FND_FILE.LOG, 'RESOURCEl_ret_textEXP' ||l_ret_text);
end;


IF P_RETURN_STATUS <> 'S' THEN
error_handler.Get_Message_List (p_message_list);
l_ret_text := null;

FOR i IN 1 .. p_message_list.COUNT LOOP
IF i = 1 THEN
--if l_ret_text is null then
FND_FILE.PUT_LINE (FND_FILE.LOG, 'p_Message_List (i) .message_text,' ||p_Message_List (i) .message_text);
l_ret_text := substr(p_Message_List (i) .message_text,1,250);

ELSE
FND_FILE.PUT_LINE (FND_FILE.LOG, 'p_Message_List (i) .message_text else,' ||p_Message_List (i) .message_text);
l_ret_text := l_ret_text || '~'|| substr(p_Message_List (i) .message_text,1,250);
END IF;
END LOOP;

ROLLBACK;

--FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
--P_HEADER_REC.ASSEMBLY_ITEM_NAME ||
--' ERROR!!!!!!!' || Chr (10) || 'Error:' || p_Message_List (i) .message_type || '-' || p_Message_List (i) || .message_name
--l_ret_text);

--DBMS_OUTPUT.PUT_LINE (P_HEADER_REC.ASSEMBLY_ITEM_NAME ||' ERROR!!!!!!!' || Chr (10) || 'Error:' || p_Message_List (i) .message_type || '-' || p_Message_List (i) || .message_name
--l_ret_text);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'RESOURCE_ERROR!!!!' ||c_rec.assembly_item_name||'-'|| c_rec.organization_code||'-'||l_ret_text);


UPDATE APPS.GEPS_MAINTENANCE_ROUTES xxinn
SET XXINN.ERROR_MESSAGE = l_ret_text,
PROCESS_FLAG ='E'
WHERE XXINN.ASSEMBLY_ITEM_NAME = c_rec.assembly_item_name
AND XXINN.ORGANIZATION_CODE = c_rec.organization_code
AND xxinn.operation_sequence_number = C_REC2.OPERATION_SEQUENCE_NUMBER
and xxinn.RESOURCE_SEQUENCE_NUMBER = C_rec3.RESOURCE_SEQUENCE_NUMBER;
commit;
ELSE

--FND_FILE.PUT_LINE (FND_FILE.OUTPUT,P_HEADER_REC.ASSEMBLY_ITEM_NAME ||'The process is successful ..');
--DBMS_OUTPUT.PUT_LINE (P_HEADER_REC.ASSEMBLY_ITEM_NAME ||'The process is successful ..');
FND_FILE.PUT_LINE (FND_FILE.LOG, 'RESOURCE_The process is successful ..' ||c_rec.assembly_item_name||'-'|| c_rec.organization_code);

UPDATE APPS.GEPS_MAINTENANCE_ROUTES xxinn
SET --XXINN.ERROR_MESSAGE = null
PROCESS_FLAG ='S'
WHERE XXINN.ASSEMBLY_ITEM_NAME = c_rec.assembly_item_name
AND XXINN.ORGANIZATION_CODE = c_rec.organization_code
AND xxinn.operation_sequence_number = C_REC2.OPERATION_SEQUENCE_NUMBER
and xxinn.RESOURCE_SEQUENCE_NUMBER = C_rec3.RESOURCE_SEQUENCE_NUMBER;
commit;
END IF;

END LOOP; --C1_RES
FND_FILE.PUT_LINE (FND_FILE.LOG, 'C1_RES END LOOP' );
END LOOP; -- C1_OPR
FND_FILE.PUT_LINE (FND_FILE.LOG, 'C1_OPR END LOOP' );
END LOOP; --C1_ENG
FND_FILE.PUT_LINE (FND_FILE.LOG, 'C1_ENG END LOOP' );
EXCEPTION WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, 'Main_Eexception' ||sqlerrm);

END GEPS_MAINTENANCE_ROUTES_PRC;
/