Home » SQL & PL/SQL » SQL & PL/SQL » Program never get completed (11i)
| Program never get completed [message #629657] |
Sat, 13 December 2014 23:50  |
 |
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
|
Member |
|
|
There is a query in package which is executing in loop and it never comes out of loop and that's reason program never get completed.But i can't remove that condition.It simply check the existence.The main problematic query is:
BEGIN
v_num_no_count := 0;
/* Count to check if there exists any record which needs to be exploded*/
SELECT COUNT (1)
INTO v_num_no_count
FROM xxpo_bom_comps_reg
WHERE sequence_id = v_num_sequence_id AND flag IN
('N', 'EXP');
fnd_file.put_line (fnd_file.LOG,
';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;'
);
fnd_file.put_line (fnd_file.LOG,
'v_num_no_count :=' || v_num_no_count
);
fnd_file.put_line (fnd_file.LOG,
';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;'
);
EXCEPTION
WHEN OTHERS
THEN
v_num_no_count := 0;
END;
EXIT WHEN v_num_no_count = 0;
Package body
CREATE OR REPLACE PACKAGE BODY APPS.XXPO_BOM_COMPS_PKG_REG
AS
-- **************************************************************************************
PROCEDURE explode_bom (
out_errbuf OUT VARCHAR2,
out_retcode OUT NUMBER,
i_num_request_id IN NUMBER,
i_num_organization_id IN NUMBER,
i_num_item_id IN NUMBER,
i_num_line_number IN NUMBER,
i_num_sequence_id IN NUMBER
)
IS
v_chr_flag VARCHAR2 (10);
v_chr_errbuff VARCHAR2 (60);
v_num_retcode NUMBER;
v_num_no_count NUMBER := 0;
v_num_ite_count NUMBER := 0;
v_chr_exp_flag VARCHAR2 (10);
v_num_count_bom NUMBER := 0;
v_num_count_fir_lvl NUMBER := 0;
v_num_count_bom_first NUMBER := 0;
v_chr_embase VARCHAR2 (40);
v_num_sequence_id NUMBER := 0;
v_num_sequence_id_model NUMBER := 0;
v_num_sequence_id_bef_exp NUMBER := 0;
/* Cursor to fetch the items which got inserted in to table for the first time */
CURSOR cur_update_flag_1 (c_num_sequence_id NUMBER)
IS
SELECT ROWID rowid_num, xbcr.*
FROM xxpo_bom_comps_reg xbcr
WHERE xbcr.flag = 'N'
AND xbcr.request_id = i_num_request_id
AND xbcr.sequence_id = c_num_sequence_id
ORDER BY xbcr.component_item_id, xbcr.sno;
/* Cursor to fetch the items which needs to be exploded */
CURSOR cur_assembly_items (c_num_sequence_id NUMBER)
IS
SELECT ROWID rowid_num, xbcr.*
FROM xxpo_bom_comps_reg xbcr
WHERE xbcr.flag = 'EXP'
AND xbcr.request_id = i_num_request_id
AND xbcr.sequence_id = c_num_sequence_id
ORDER BY xbcr.component_item_id, xbcr.sno;
/* Cursor to check if BOM exists for the item to be exploded */
CURSOR cur_item_type_seg (i_num_assembly_item_id NUMBER, i_org_id NUMBER)
IS
SELECT msi1.inventory_item_id assembly_item_id,
msi1.segment1 assembly, msi1.item_type asmbly_user_item_type,
msi1.bom_item_type asmbly_bom_item_type,
msi2.inventory_item_id component_item_id,
msi2.segment1 component, msi2.item_type comp_user_item_type,
msi2.bom_item_type comp_bom_item_type,
bic.component_quantity quantity, 'N' flag,
i_num_organization_id organization_id, 'MRP' mrp_plan
FROM bom_bill_of_materials bom,
mtl_system_items_b msi1,
bom_inventory_components bic,
mtl_system_items_b msi2
WHERE bom.organization_id = msi1.organization_id
AND msi1.inventory_item_id = bom.assembly_item_id
AND bic.bill_sequence_id = bom.common_bill_sequence_id
AND msi2.inventory_item_id = bic.component_item_id
AND msi2.organization_id = msi1.organization_id
AND bom.assembly_item_id = i_num_assembly_item_id
AND bic.implementation_date IS NOT NULL
-- AND bic.disable_date IS NULL HPOV 379694
AND bom.alternate_bom_designator IS NULL
AND bom.organization_id = i_org_id;
BEGIN
-- Getting the Components for the assembly
BEGIN
IF i_num_sequence_id = 0
THEN
v_num_sequence_id := 1;
ELSE
v_num_sequence_id := i_num_sequence_id;
END IF;
/* Calling the Insert procedure to insert the items into the table */
xxpo_bom_comps_insert_reg (v_chr_errbuff,
v_num_retcode,
1,
i_num_organization_id,
i_num_item_id,
i_num_line_number, --
i_num_request_id,
v_num_sequence_id
);
/* Updating the custom table with the EM_BASe fetched form the function*/
UPDATE xxpo_bom_comps_reg xbcr
SET em_base =
xxpo_embase_fun_reg (xbcr.component_item_id,
xbcr.organization_id,
xbcr.comp_user_item_type
)
WHERE xbcr.comp_user_item_type = 'EMM';
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Insert Statement-' || SQLERRM
);
END;
COMMIT;
BEGIN
v_num_ite_count := 0;
LOOP -- infinite loop
-- INITIAL RECORD SET USING LOOP TO SET Y AND N
FOR cur_update_flag_rec IN
cur_update_flag_1 (v_num_sequence_id) -- to fetch all Ns
LOOP
IF cur_update_flag_rec.sno = 1
THEN
IF (cur_update_flag_rec.comp_user_item_type IN
('EMM', 'SA', 'CONFIGURED_ITEM')
)
THEN
v_chr_flag := 'EXP';
ELSIF cur_update_flag_rec.comp_user_item_type IN
('P', 'EMR_PURCH')
THEN
/* Query to check if the BOM exists for the parts with item type Purchased Item*/
BEGIN
SELECT COUNT (1)
INTO v_num_count_bom_first
FROM bom_bill_of_materials a,
bom_inventory_components b
WHERE a.assembly_item_id =
cur_update_flag_rec.component_item_id
AND a.organization_id = i_num_organization_id
AND b.bill_sequence_id = a.common_bill_sequence_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'error while checking bom exists for the component'
);
END;
IF v_num_count_bom_first > 0
THEN
v_chr_flag := 'EXP';
ELSE
v_chr_flag := 'PRINT';
END IF;
ELSE
v_chr_flag := 'Y';
END IF;
ELSE
-- check on BOM of the Item (form 2nd level onwards)
BEGIN
SELECT COUNT (1)
INTO v_num_count_bom
FROM bom_bill_of_materials a,
bom_inventory_components b
WHERE a.assembly_item_id =
cur_update_flag_rec.component_item_id
AND a.organization_id = i_num_organization_id
AND b.bill_sequence_id = a.common_bill_sequence_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'error while checking bom exists for the component'
);
END;
IF v_num_count_bom > 0
THEN
v_chr_flag := 'EXP';
ELSE
v_chr_flag := 'PRINT';
END IF;
--commentd by REGS dated 11/10/2012
-- Logic to update the duplicate records with flag = 'Y'
IF v_chr_flag = 'PRINT'
THEN
fnd_file.put_line (fnd_file.LOG,
'IF FLAG PRINT ' || V_CHR_FLAG
);
-- Query to check if any duplicate records are getting inserted
BEGIN
SELECT COUNT (1)
INTO v_num_count_fir_lvl
FROM xxpo_bom_comps_reg
WHERE assembly_item_id =
cur_update_flag_rec.assembly_item_id
AND component_item_id =
cur_update_flag_rec.component_item_id
AND top_assembly_item_id =
cur_update_flag_rec.top_assembly_item_id
AND line_number = cur_update_flag_rec.line_number ----
AND flag = 'PRINT';
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
-- If the record already exists with flag = 'PRINT' update new record with flag = 'Y'
IF v_num_count_fir_lvl > 0
THEN
v_chr_flag := 'Y';
END IF;
END IF;
END IF;
BEGIN
/* Updating the custom table with the flag*/
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_flag,
line_number = i_num_line_number
WHERE ROWID = cur_update_flag_rec.rowid_num;
fnd_file.put_line
(fnd_file.LOG,
'Exception updating successful records-'
|| i_num_line_number || ',' || v_chr_flag || ',' || cur_update_flag_rec.rowid_num
);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Exception updating successful records-'
|| SQLERRM
);
END;
END LOOP; --cur_update_flag_rec IN cur_update_flag_1
FOR rec_assembly_items IN cur_assembly_items (v_num_sequence_id)
-- fetch all remaing EXP records
LOOP
v_chr_exp_flag := 'EXP_NOT';
v_chr_embase := rec_assembly_items.em_base;
IF rec_assembly_items.comp_user_item_type = 'CONFIGURED_ITEM'
THEN
v_num_sequence_id_model := v_num_sequence_id + 1;
v_chr_flag := 'Y';
fnd_file.put_line (fnd_file.LOG,
'IF FLAG ITEM TYPE ' || rec_assembly_items.comp_user_item_type);
BEGIN
/* Updating the custom table with the flag*/
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_flag,
line_number = i_num_line_number
WHERE ROWID = rec_assembly_items.rowid_num;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Exception updating successful records-'
|| SQLERRM
);
END;
v_num_sequence_id_bef_exp := v_num_sequence_id;
/* Calling the procedure to explode the configured item if exists any*/
explode_bom (v_chr_errbuff,
v_num_retcode,
i_num_request_id,
i_num_organization_id,
rec_assembly_items.component_item_id,
i_num_line_number,
v_num_sequence_id_model
);
COMMIT;
v_num_sequence_id := v_num_sequence_id_bef_exp;
ELSE
FOR rec_item_type_seg IN
cur_item_type_seg (rec_assembly_items.component_item_id,
rec_assembly_items.organization_id
)
LOOP
BEGIN
INSERT INTO xxpo_bom_comps_reg
(sno, assembly_item_id,
assembly,
asmbly_user_item_type,
asmbly_bom_item_type,
component_item_id,
component,
comp_user_item_type,
comp_bom_item_type,
quantity, flag,
line_number,
top_assembly,
top_assembly_item_id,
top_asmbly_user_item_type,
top_asmbly_bom_item_type,
organization_id,
mrp_plan,
created_by, creation_date,
last_updated_by, last_updated_date,
last_updated_login, request_id,
program_application_id,
program_id,
program_update_date, em_base,
sequence_id
)
VALUES (2, rec_item_type_seg.assembly_item_id,
rec_item_type_seg.assembly,
rec_item_type_seg.asmbly_user_item_type,
rec_item_type_seg.asmbly_bom_item_type,
rec_item_type_seg.component_item_id,
rec_item_type_seg.component,
rec_item_type_seg.comp_user_item_type,
rec_item_type_seg.comp_bom_item_type,
rec_item_type_seg.quantity, 'N',
i_num_line_number,
rec_assembly_items.top_assembly,
rec_assembly_items.top_assembly_item_id,
rec_assembly_items.top_asmbly_user_item_type,
rec_assembly_items.top_asmbly_bom_item_type,
rec_assembly_items.organization_id,
rec_assembly_items.mrp_plan,
fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.login_id, i_num_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
TRUNC (SYSDATE), v_chr_embase,
v_num_sequence_id
);
v_chr_exp_flag := 'EXP_DONE';
fnd_file.put_line (fnd_file.LOG,
'IF' || v_chr_exp_flag);
fnd_file.put_line (fnd_file.LOG,
'AFTER INSERT' || rec_item_type_seg.assembly_item_id);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'---ERROR 1---' || SQLERRM
);
END;
END LOOP;
END IF;
BEGIN
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_exp_flag,
line_number = i_num_line_number
WHERE ROWID = rec_assembly_items.rowid_num;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'ERROR WHILE UPDATING FLAG '
|| SQLERRM
);
END;
END LOOP; --rec_assembly_items IN cur_assembly_items
COMMIT;
BEGIN
v_num_no_count := 0;
/* Count to check if there exists any record which needs to be exploded*/
SELECT COUNT (1)
INTO v_num_no_count
FROM xxpo_bom_comps_reg
WHERE sequence_id = v_num_sequence_id AND flag IN
('N', 'EXP');
fnd_file.put_line (fnd_file.LOG,
';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;'
);
fnd_file.put_line (fnd_file.LOG,
'v_num_no_count :=' || v_num_no_count
);
fnd_file.put_line (fnd_file.LOG,
';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;'
);
EXCEPTION
WHEN OTHERS
THEN
v_num_no_count := 0;
END;
EXIT WHEN v_num_no_count = 0;
END LOOP;
-- Infinite loop which exist when all the records in
--custom table are 'Y'
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Main Package-' || SQLERRM
);
END;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Main Package-' || SQLERRM
);
END explode_bom;
PROCEDURE explode_bom_model (
out_errbuf OUT VARCHAR2,
out_retcode OUT NUMBER,
i_num_request_id IN NUMBER,
i_num_organization_id IN NUMBER,
i_num_item_id IN NUMBER,
i_num_line_number IN NUMBER,
i_num_sequence_id IN NUMBER
)
IS
v_chr_flag VARCHAR2 (10);
v_chr_errbuff VARCHAR2 (60);
v_num_retcode NUMBER;
v_num_no_count NUMBER := 0;
v_num_ite_count NUMBER := 0;
v_chr_exp_flag VARCHAR2 (10);
v_num_count_fir_lvl NUMBER := 0;
v_num_count_bom_first NUMBER := 0;
v_chr_embase VARCHAR2 (40);
v_num_sequence_id NUMBER := 0;
v_num_sequence_id_model NUMBER := 0;
v_num_sequence_id_bef_exp NUMBER := 0;
/* Cursor to fetch the items which got inserted in to table for the first time */
CURSOR cur_update_flag_1 (c_num_sequence_id NUMBER)
IS
SELECT ROWID rowid_num, xbcr.*
FROM xxpo_bom_comps_reg xbcr
WHERE flag = 'N'
AND request_id = i_num_request_id
AND sequence_id = c_num_sequence_id
ORDER BY component_item_id, sno;
/* Cursor to fetch the items which needs to be exploded */
CURSOR cur_assembly_items (c_num_sequence_id NUMBER)
IS
SELECT ROWID rowid_num, xbcr.*
FROM xxpo_bom_comps_reg xbcr
WHERE flag = 'EXP'
AND request_id = i_num_request_id
AND sequence_id = c_num_sequence_id
ORDER BY component_item_id, sno;
/* Cursor to check if BOM exists for the item to be exploded */
CURSOR cur_item_type_seg (i_num_assembly_item_id NUMBER, i_org_id NUMBER)
IS
SELECT msi1.inventory_item_id assembly_item_id,
msi1.segment1 assembly, msi1.item_type asmbly_user_item_type,
msi1.bom_item_type asmbly_bom_item_type,
msi2.inventory_item_id component_item_id,
msi2.segment1 component, msi2.item_type comp_user_item_type,
msi2.bom_item_type comp_bom_item_type,
bic.component_quantity quantity, 'N' flag,
i_num_organization_id organization_id, 'MRP' mrp_plan
FROM bom_bill_of_materials bom,
mtl_system_items_b msi1,
bom_inventory_components bic,
mtl_system_items_b msi2
WHERE bom.organization_id = msi1.organization_id
AND msi1.inventory_item_id = bom.assembly_item_id
AND bic.bill_sequence_id = bom.common_bill_sequence_id
AND msi2.inventory_item_id = bic.component_item_id
AND msi2.organization_id = msi1.organization_id
AND bom.assembly_item_id = i_num_assembly_item_id
AND bic.implementation_date IS NOT NULL
-- AND bic.disable_date IS NULL HPOV 379694
AND bom.alternate_bom_designator IS NULL
AND bom.organization_id = i_org_id;
BEGIN
-- Getting the Components for the assembly
BEGIN
IF i_num_sequence_id = 0
THEN
v_num_sequence_id := 1;
ELSE
v_num_sequence_id := i_num_sequence_id;
END IF;
/* Calling the Insert procedure to insert the items into the table */
xxpo_bom_comps_insert_reg (v_chr_errbuff,
v_num_retcode,
1,
i_num_organization_id,
i_num_item_id,
i_num_line_number, --
i_num_request_id,
v_num_sequence_id
);
/* Updating the Custom table EM_BASE column for all the EM Bases*/
UPDATE xxpo_bom_comps_reg xbcr
SET em_base = xbcr.component
WHERE xbcr.comp_user_item_type = 'EMB'
AND request_id = i_num_request_id
AND sequence_id = v_num_sequence_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Insert Statement-' || SQLERRM
);
END;
COMMIT;
BEGIN
v_num_ite_count := 0;
LOOP -- infinite loop
-- INITIAL RECORD SET USING LOOP TO SET Y AND N
FOR cur_update_flag_rec IN
cur_update_flag_1 (v_num_sequence_id) -- to fetch all Ns
LOOP
/* Cursor to check if BOM exists for the item to be exploded */
BEGIN
SELECT COUNT (1)
INTO v_num_count_bom_first
FROM bom_bill_of_materials a, bom_inventory_components b
WHERE a.assembly_item_id =
cur_update_flag_rec.component_item_id
AND a.organization_id = i_num_organization_id
AND b.bill_sequence_id = a.common_bill_sequence_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'error while checking bom exists for the component'
);
END;
IF v_num_count_bom_first > 0
THEN
v_chr_flag := 'EXP';
ELSE
v_chr_flag := 'PRINT';
END IF;
-- Logic to update the duplicate records with flag = 'Y'
IF v_chr_flag = 'PRINT'
THEN
BEGIN
SELECT COUNT (1)
INTO v_num_count_fir_lvl
FROM xxpo_bom_comps_reg
WHERE assembly_item_id =
cur_update_flag_rec.assembly_item_id
AND component_item_id =
cur_update_flag_rec.component_item_id
AND top_assembly_item_id =
cur_update_flag_rec.top_assembly_item_id
AND line_number = cur_update_flag_rec.line_number -- ADDED AS PER SR#1051394 BY XXXX ON 02-NOV-2012
AND flag = 'PRINT';
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
-- If the record already exists with flag = 'PRINT' update new record with flag = 'Y'
IF v_num_count_fir_lvl > 0
THEN
v_chr_flag := 'Y';
END IF;
END IF;
BEGIN
/* Updating the custom table with appropriate print flag*/
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_flag,
line_number = i_num_line_number
WHERE ROWID = cur_update_flag_rec.rowid_num;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Exception updating successful records-'
|| SQLERRM
);
END;
END LOOP; --cur_update_flag_rec IN cur_update_flag_1
FOR rec_assembly_items IN cur_assembly_items (v_num_sequence_id)
-- fetch all remaing EXP records
LOOP
v_chr_exp_flag := 'EXP_NOT';
v_chr_embase := rec_assembly_items.em_base;
IF rec_assembly_items.comp_user_item_type = 'ATO'
THEN
v_num_sequence_id_model := v_num_sequence_id + 1;
v_chr_flag := 'Y';
BEGIN
/* Updating the custom table with appropriate print flag*/
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_flag,
line_number = i_num_line_number
WHERE ROWID = rec_assembly_items.rowid_num;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Exception updating successful records-'
|| SQLERRM
);
END;
v_num_sequence_id_bef_exp := v_num_sequence_id;
/* Calling the procedure to explode the model item if exists any*/
explode_bom_model (v_chr_errbuff,
v_num_retcode,
i_num_request_id,
i_num_organization_id,
rec_assembly_items.component_item_id,
i_num_line_number,
v_num_sequence_id_model
);
COMMIT;
v_num_sequence_id := v_num_sequence_id_bef_exp;
ELSE
FOR rec_item_type_seg IN
cur_item_type_seg (rec_assembly_items.component_item_id,
rec_assembly_items.organization_id
)
LOOP
BEGIN
INSERT INTO xxpo_bom_comps_reg
(sno, assembly_item_id,
assembly,
asmbly_user_item_type,
asmbly_bom_item_type,
component_item_id,
component,
comp_user_item_type,
comp_bom_item_type,
quantity, flag,
line_number,
top_assembly,
top_assembly_item_id,
top_asmbly_user_item_type,
top_asmbly_bom_item_type,
organization_id,
mrp_plan,
created_by, creation_date,
last_updated_by, last_updated_date,
last_updated_login, request_id,
program_application_id,
program_id,
program_update_date, em_base,
sequence_id
)
VALUES (2, rec_item_type_seg.assembly_item_id,
rec_item_type_seg.assembly,
rec_item_type_seg.asmbly_user_item_type,
rec_item_type_seg.asmbly_bom_item_type,
rec_item_type_seg.component_item_id,
rec_item_type_seg.component,
rec_item_type_seg.comp_user_item_type,
rec_item_type_seg.comp_bom_item_type,
rec_item_type_seg.quantity, 'N',
i_num_line_number,
rec_assembly_items.top_assembly,
rec_assembly_items.top_assembly_item_id,
rec_assembly_items.top_asmbly_user_item_type,
rec_assembly_items.top_asmbly_bom_item_type,
rec_assembly_items.organization_id,
rec_assembly_items.mrp_plan,
fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.login_id, i_num_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
TRUNC (SYSDATE), v_chr_embase,
v_num_sequence_id
);
v_chr_exp_flag := 'EXP_DONE';
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'---ERROR 1---' || SQLERRM
);
END;
END LOOP;
END IF;
fnd_file.put_line (fnd_file.LOG,'LOG10:' ||i_num_line_number || ' '|| v_chr_exp_flag);
--rec_item_type_seg IN cur_item_type_seg( rec_assembly_items.assembly_item_id,
BEGIN
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_exp_flag,
line_number = i_num_line_number
WHERE ROWID = rec_assembly_items.rowid_num;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'ERROR WHILE UPDATING FLAG '
|| SQLERRM
);
END;
END LOOP; --rec_assembly_items IN cur_assembly_items
COMMIT;
BEGIN
v_num_no_count := 0;
/* Count to check if there exists any record which needs to be exploded*/
SELECT COUNT (1)
INTO v_num_no_count
FROM xxpo_bom_comps_reg
WHERE sequence_id = v_num_sequence_id AND flag IN
('N', 'EXP');
fnd_file.put_line (fnd_file.LOG,
';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;'
);
fnd_file.put_line (fnd_file.LOG,
'v_num_no_count :=' || v_num_no_count
);
fnd_file.put_line (fnd_file.LOG,
';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;'
);
EXCEPTION
WHEN OTHERS
THEN
v_num_no_count := 0;
END;
EXIT WHEN v_num_no_count = 0;
END LOOP;
-- Infinite loop which exist when all the records in
--custom table are 'Y'
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Main Package-' || SQLERRM
);
END;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Main Package-' || SQLERRM
);
END explode_bom_model;
PROCEDURE xxpo_bom_comps_insert_reg (
out_errbuf OUT VARCHAR2,
out_retcode OUT NUMBER,
i_num_seq IN NUMBER,
i_num_organization_id IN NUMBER,
i_num_item_id IN NUMBER,
i_num_line_number IN NUMBER, -- ADDED AS PER SR#1051394 BY XXXX ON 02-NOV-2012
i_num_request_id IN NUMBER,
i_num_sequence_id IN NUMBER
)
IS
-- ADDED AS PER SR#1051394 BY XXXX ON 02-NOV-2012
v_num_count NUMBER :=0;
BEGIN
SELECT COUNT(1)
INTO v_num_count
FROM bom_bill_of_materials
WHERE assembly_item_id = i_num_item_id
AND organization_id IN (select organization_id from mtl_parameters
where organization_code in ('FRX','MCK','MPW'));
IF v_num_count = 0
THEN
BEGIN
INSERT INTO xxpo_bom_comps_reg
(sno, LINE_NUMBER,assembly_item_id, assembly, asmbly_user_item_type,
asmbly_bom_item_type, component_item_id, component,
comp_user_item_type, comp_bom_item_type,flag,
top_assembly, top_assembly_item_id,
top_asmbly_user_item_type, top_asmbly_bom_item_type,
organization_id, mrp_plan, created_by, creation_date,
last_updated_by, last_updated_date, last_updated_login,
request_id, program_application_id, program_id,
program_update_date, sequence_id)
SELECT i_num_seq,i_num_LINE_NUMBER ,msi1.inventory_item_id, msi1.segment1,
msi1.item_type, msi1.bom_item_type, msi1.inventory_item_id,
msi1.segment1, msi1.item_type, msi1.bom_item_type,
'N', msi1.segment1,msi1.inventory_item_id, msi1.item_type, msi1.bom_item_type,
i_num_organization_id, 'MRP', fnd_global.user_id,
TRUNC (SYSDATE), fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.login_id, i_num_request_id,
fnd_global.prog_appl_id, fnd_global.conc_program_id,
TRUNC (SYSDATE), i_num_sequence_id
FROM mtl_system_items_b msi1
WHERE organization_id = i_num_organization_id
AND inventory_item_id = i_num_item_id;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error in inserting the records in xxpo_bom_comps_reg' || SQLERRM
);
END;
ELSE
-- ADDED AS PER SR#1051394 BY XXXX ON 02-NOV-2012 TILL HERE
BEGIN
INSERT INTO xxpo_bom_comps_reg
(sno, LINE_NUMBER,assembly_item_id, assembly, asmbly_user_item_type,
asmbly_bom_item_type, component_item_id, component,
comp_user_item_type, comp_bom_item_type, quantity, flag,
top_assembly, top_assembly_item_id,
top_asmbly_user_item_type, top_asmbly_bom_item_type,
organization_id, mrp_plan, created_by, creation_date,
last_updated_by, last_updated_date, last_updated_login,
request_id, program_application_id, program_id,
program_update_date, sequence_id)
SELECT i_num_seq,i_num_LINE_NUMBER ,msi1.inventory_item_id, msi1.segment1,
msi1.item_type, msi1.bom_item_type, msi2.inventory_item_id,
msi2.segment1, msi2.item_type, msi2.bom_item_type,
bic.component_quantity, 'N', msi1.segment1,
msi1.inventory_item_id, msi1.item_type, msi1.bom_item_type,
i_num_organization_id, 'MRP', fnd_global.user_id,
TRUNC (SYSDATE), fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.login_id, i_num_request_id,
fnd_global.prog_appl_id, fnd_global.conc_program_id,
TRUNC (SYSDATE), i_num_sequence_id
FROM bom_bill_of_materials bom,
mtl_system_items_b msi1,
bom_inventory_components bic,
mtl_system_items_b msi2
WHERE bom.organization_id = msi1.organization_id
AND msi1.inventory_item_id = bom.assembly_item_id
AND bic.bill_sequence_id = bom.common_bill_sequence_id
AND msi2.inventory_item_id = bic.component_item_id
AND msi2.organization_id = msi1.organization_id
AND bic.implementation_date IS NOT NULL
-- AND bic.disable_date IS NULL HPOV 379694
AND bom.alternate_bom_designator IS NULL
-- ADDED AS PER SR#1051394 BY XXXX ON 02-NOV-2012
AND bom.organization_id in (select organization_id from mtl_parameters
where organization_code ='FRX' OR organization_code ='MCK'
OR organization_code = 'MPW' ) --i_num_organization_id
AND bom.assembly_item_id = i_num_item_id
group by i_num_seq,i_num_LINE_NUMBER ,msi1.inventory_item_id, msi1.segment1,
msi1.item_type, msi1.bom_item_type, msi2.inventory_item_id,
msi2.segment1, msi2.item_type, msi2.bom_item_type,
bic.component_quantity, 'N', msi1.segment1,
msi1.inventory_item_id, msi1.item_type, msi1.bom_item_type,
i_num_organization_id, 'MRP', fnd_global.user_id,
TRUNC (SYSDATE), fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.login_id, i_num_request_id,
fnd_global.prog_appl_id, fnd_global.conc_program_id,
TRUNC (SYSDATE), i_num_sequence_id;
-- ADDED AS PER SR#1051394 BY XXXX ON 02-NOV-2012 TILL HERE
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error in inserting the records in xxpo_bom_comps_reg:' || SQLERRM
);
END;
END IF;
END xxpo_bom_comps_insert_reg;
FUNCTION xxpo_embase_fun_reg (
i_num_comp_item_id IN NUMBER,
i_num_organization_id IN NUMBER,
i_chr_item_type IN VARCHAR2
)
RETURN VARCHAR2
IS
v_num_assembly_item_id1 NUMBER;
v_num_comp_item_id_cur NUMBER;
v_num_assembly_item_id2 NUMBER := NULL;
v_chr_item_type1 VARCHAR2 (100);
v_num_excep_flag NUMBER := 0;
v_chr_assembly_item VARCHAR2 (40);
v_num_comp_sequence_id_cur NUMBER := 0;
BEGIN
v_num_comp_item_id_cur := i_num_comp_item_id;
v_num_excep_flag := 1;
LOOP
IF i_chr_item_type NOT IN ('CONFIGURED_ITEM', 'ATO')
THEN
BEGIN
SELECT assembly_item_id
INTO v_num_assembly_item_id1
FROM bom_bill_of_materials bom
WHERE EXISTS (
SELECT 1
FROM bom_inventory_components bic
WHERE bic.parent_bill_seq_id =
bom.common_bill_sequence_id
AND bic.component_item_id = v_num_comp_item_id_cur)
AND bom.organization_id = i_num_organization_id;
v_num_excep_flag := 0;
EXCEPTION
WHEN OTHERS
THEN
v_num_excep_flag := 1;
DBMS_OUTPUT.put_line
( 'FAILED IN CALC OF ASSEMBLY ITEM ID COMPONENT ITEM '
|| v_num_comp_item_id_cur
|| 'COMPONENT_SEQUENCE_ID'
|| v_num_comp_sequence_id_cur
);
END;
IF v_num_excep_flag = 0
THEN
BEGIN
SELECT item_type, segment1
INTO v_chr_item_type1, v_chr_assembly_item
FROM mtl_system_items_b
WHERE inventory_item_id = v_num_assembly_item_id1
AND organization_id = i_num_organization_id;
EXCEPTION
WHEN OTHERS
THEN
v_num_excep_flag := 1;
DBMS_OUTPUT.put_line ( 'FAILED IN CALC OF ITEM type'
|| v_num_assembly_item_id1
|| 'organization_ID'
|| i_num_organization_id
);
END;
END IF;
END IF;
IF v_chr_item_type1 = 'EMB' AND v_num_excep_flag = 0
THEN
v_num_assembly_item_id2 := v_num_assembly_item_id1;
v_num_comp_item_id_cur := v_num_assembly_item_id1;
EXIT;
ELSIF ( v_chr_item_type1 = 'CONFIGURED_ITEM'
OR v_chr_item_type1 = 'ATO'
)
AND v_num_excep_flag = 0
THEN
v_num_assembly_item_id2 := NULL;
v_chr_assembly_item := NULL;
EXIT;
ELSE
IF v_num_excep_flag = 0
THEN
v_num_comp_item_id_cur := v_num_assembly_item_id1;
ELSE
v_num_assembly_item_id2 := NULL;
v_chr_assembly_item := NULL;
EXIT;
END IF;
END IF;
END LOOP;
RETURN (v_chr_assembly_item);
END;
PROCEDURE vendor_name_prc (
i_num_organization_id IN NUMBER,
i_num_component_item_id IN NUMBER,
o_chr_vendor_name OUT VARCHAR2
)
IS
v_num_assgn_set_id NUMBER;
v_num_sourcing_id NUMBER;
v_num_organization_id NUMBER;
v_num_source_type NUMBER;
v_chr_sourcing_name VARCHAR2 (50);
v_num_receipt_id NUMBER;
v_chr_vendor_name VARCHAR2 (240);
v_num_org_id NUMBER;
v_num_component_item_id NUMBER;
v_chr_out_name VARCHAR2 (240);
v_chr_assign_set_name VARCHAR2 (1000)
:= fnd_profile.VALUE ('XXPO_ASS_SET_FOR_CON_REG');
v_num_vendor_id NUMBER;
BEGIN
v_num_component_item_id := i_num_component_item_id;
/* To fetch the assignment set id */
BEGIN
SELECT assignment_set_id
INTO v_num_assgn_set_id
FROM mrp_assignment_sets
WHERE assignment_set_name = v_chr_assign_set_name;
EXCEPTION
WHEN OTHERS
THEN
v_num_assgn_set_id := NULL;
END;
IF v_num_assgn_set_id IS NOT NULL
THEN
BEGIN
SELECT sourcing_rule_name
INTO v_chr_sourcing_name
FROM mrp_sr_assignments_v
WHERE assignment_set_id = v_num_assgn_set_id
AND organization_id = i_num_organization_id
AND inventory_item_id = i_num_component_item_id;
EXCEPTION
WHEN OTHERS
THEN
v_num_sourcing_id := NULL;
END;
END IF;
IF v_chr_sourcing_name IS NOT NULL
THEN
BEGIN
SELECT sourcing_rule_id
INTO v_num_sourcing_id
FROM mrp_sourcing_rules
WHERE sourcing_rule_name = v_chr_sourcing_name
AND organization_id = i_num_organization_id;
EXCEPTION
WHEN OTHERS
THEN
v_num_sourcing_id := NULL;
END;
END IF;
IF v_num_sourcing_id IS NOT NULL
THEN
BEGIN
SELECT sr_receipt_id
INTO v_num_receipt_id
FROM mrp_sr_receipt_org
WHERE sourcing_rule_id = v_num_sourcing_id;
EXCEPTION
WHEN OTHERS
THEN
v_num_receipt_id := NULL;
END;
END IF;
IF v_num_receipt_id IS NOT NULL
THEN
BEGIN
SELECT source_type, vendor_id, source_organization_id
INTO v_num_source_type, v_num_vendor_id, v_num_org_id
FROM mrp_sr_source_org
WHERE sr_receipt_id = v_num_receipt_id;
EXCEPTION
WHEN OTHERS
THEN
v_num_vendor_id := NULL;
END;
IF v_num_vendor_id IS NOT NULL
THEN
BEGIN
SELECT vendor_name
INTO v_chr_vendor_name
FROM po_vendors
WHERE vendor_id = v_num_vendor_id;
EXCEPTION
WHEN OTHERS
THEN
v_chr_vendor_name := NULL;
END;
END IF;
IF v_num_source_type <> 3
THEN
vendor_name_prc (v_num_org_id,
v_num_component_item_id,
v_chr_out_name
);
ELSE
o_chr_vendor_name := v_chr_vendor_name;
END IF;
END IF;
END vendor_name_prc;
END XXPO_BOM_COMPS_PKG_REG;
/
[EDITED by LF: applied [spoiler] tags]
[Updated on: Sun, 14 December 2014 04:50] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Program never get completed [message #629663 is a reply to message #629660] |
Sun, 14 December 2014 01:32  |
|
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
|
Member |
|
|
CREATE TABLE XXPO_BOM_COMPS_REG
(
SNO NUMBER,
ASSEMBLY_ITEM_ID NUMBER,
ASSEMBLY VARCHAR2(240 BYTE),
ASMBLY_USER_ITEM_TYPE VARCHAR2(60 BYTE),
ASMBLY_BOM_ITEM_TYPE VARCHAR2(60 BYTE),
COMPONENT_ITEM_ID NUMBER,
COMPONENT VARCHAR2(60 BYTE),
COMP_USER_ITEM_TYPE VARCHAR2(60 BYTE),
COMP_BOM_ITEM_TYPE VARCHAR2(60 BYTE),
QUANTITY NUMBER,
FLAG VARCHAR2(10 BYTE),
LINE_NUMBER NUMBER,
TOP_ASSEMBLY_ITEM_ID NUMBER,
TOP_ASSEMBLY VARCHAR2(240 BYTE),
TOP_ASMBLY_USER_ITEM_TYPE VARCHAR2(60 BYTE),
TOP_ASMBLY_BOM_ITEM_TYPE VARCHAR2(60 BYTE),
LEVEL_ID NUMBER,
ORGANIZATION_ID NUMBER,
MRP_PLAN VARCHAR2(60 BYTE),
CREATED_BY NUMBER,
CREATION_DATE DATE,
LAST_UPDATED_BY NUMBER,
LAST_UPDATED_DATE DATE,
LAST_UPDATED_LOGIN NUMBER,
REQUEST_ID NUMBER,
PROGRAM_APPLICATION_ID NUMBER,
PROGRAM_ID NUMBER,
PROGRAM_UPDATE_DATE DATE,
ATTRIBUTE1 VARCHAR2(150 BYTE),
ATTRIBUTE2 VARCHAR2(150 BYTE),
ATTRIBUTE3 VARCHAR2(150 BYTE),
ATTRIBUTE4 VARCHAR2(150 BYTE),
ATTRIBUTE5 VARCHAR2(150 BYTE),
ATTRIBUTE6 VARCHAR2(150 BYTE),
ATTRIBUTE7 VARCHAR2(150 BYTE),
ATTRIBUTE8 VARCHAR2(150 BYTE),
ATTRIBUTE9 VARCHAR2(150 BYTE),
ATTRIBUTE10 VARCHAR2(150 BYTE),
ATTRIBUTE11 VARCHAR2(150 BYTE),
ATTRIBUTE12 VARCHAR2(150 BYTE),
ATTRIBUTE13 VARCHAR2(150 BYTE),
ATTRIBUTE14 VARCHAR2(150 BYTE),
ATTRIBUTE15 VARCHAR2(150 BYTE),
EM_BASE VARCHAR2(40 BYTE),
SEQUENCE_ID NUMBER
)
[EDITED by LF: fixed [code] tags]
[Updated on: Sun, 14 December 2014 04:51] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Program never get completed [message #629689 is a reply to message #629683] |
Mon, 15 December 2014 03:07 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You've got an infinite loop - you know this.
We can't tell why you've got an infinite loop since you haven't supplied the complete code, the snippet posted doesn't even contain the loop statement.
We know nothing about your system, or what this particular process is supposed to do, or even what you are looping over.
So how do you expect us to help?
|
|
|
|
| Re: Program never get completed [message #629813 is a reply to message #629689] |
Tue, 16 December 2014 04:35 |
|
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
|
Member |
|
|
CREATE OR REPLACE PACKAGE BODY APPS.XXPO_BOM_COMPS_PKG_REG
AS
PROCEDURE explode_bom (
out_errbuf OUT VARCHAR2,
out_retcode OUT NUMBER,
i_num_request_id IN NUMBER,
i_num_organization_id IN NUMBER,
i_num_item_id IN NUMBER,
i_num_line_number IN NUMBER,
i_num_sequence_id IN NUMBER
)
IS
v_chr_flag VARCHAR2 (10);
v_chr_errbuff VARCHAR2 (60);
v_num_retcode NUMBER;
v_num_no_count NUMBER := 0;
v_num_ite_count NUMBER := 0;
v_chr_exp_flag VARCHAR2 (10);
v_num_count_bom NUMBER := 0;
v_num_count_fir_lvl NUMBER := 0;
v_num_count_bom_first NUMBER := 0;
v_chr_embase VARCHAR2 (40);
v_num_sequence_id NUMBER := 0;
v_num_sequence_id_model NUMBER := 0;
v_num_sequence_id_bef_exp NUMBER := 0;
/* Cursor to fetch the items which got inserted in to table for the first time */
CURSOR cur_update_flag_1 (c_num_sequence_id NUMBER)
IS
SELECT ROWID rowid_num, xbcr.*
FROM xxpo_bom_comps_reg xbcr
WHERE xbcr.flag = 'N'
AND xbcr.request_id = i_num_request_id
AND xbcr.sequence_id = c_num_sequence_id
ORDER BY xbcr.component_item_id, xbcr.sno;
/* Cursor to fetch the items which needs to be exploded */
CURSOR cur_assembly_items (c_num_sequence_id NUMBER)
IS
SELECT ROWID rowid_num, xbcr.*
FROM xxpo_bom_comps_reg xbcr
WHERE xbcr.flag = 'EXP'
AND xbcr.request_id = i_num_request_id
AND xbcr.sequence_id = c_num_sequence_id
ORDER BY xbcr.component_item_id, xbcr.sno;
/* Cursor to check if BOM exists for the item to be exploded */
CURSOR cur_item_type_seg (i_num_assembly_item_id NUMBER, i_org_id NUMBER)
IS
SELECT msi1.inventory_item_id assembly_item_id,
msi1.segment1 assembly, msi1.item_type asmbly_user_item_type,
msi1.bom_item_type asmbly_bom_item_type,
msi2.inventory_item_id component_item_id,
msi2.segment1 component, msi2.item_type comp_user_item_type,
msi2.bom_item_type comp_bom_item_type,
bic.component_quantity quantity, 'N' flag,
i_num_organization_id organization_id, 'MRP' mrp_plan
FROM bom_bill_of_materials bom,
mtl_system_items_b msi1,
bom_inventory_components bic,
mtl_system_items_b msi2
WHERE bom.organization_id = msi1.organization_id
AND msi1.inventory_item_id = bom.assembly_item_id
AND bic.bill_sequence_id = bom.common_bill_sequence_id
AND msi2.inventory_item_id = bic.component_item_id
AND msi2.organization_id = msi1.organization_id
AND bom.assembly_item_id = i_num_assembly_item_id
AND bic.implementation_date IS NOT NULL
-- AND bic.disable_date IS NULL HPOV 379694
AND bom.alternate_bom_designator IS NULL
AND bom.organization_id = i_org_id;
BEGIN
-- Getting the Components for the assembly
BEGIN
IF i_num_sequence_id = 0
THEN
v_num_sequence_id := 1;
ELSE
v_num_sequence_id := i_num_sequence_id;
END IF;
/* Calling the Insert procedure to insert the items into the table */
xxpo_bom_comps_insert_reg (v_chr_errbuff,
v_num_retcode,
1,
i_num_organization_id,
i_num_item_id,
i_num_line_number, -- ADDED AS PER SR#1051394 BY XXXX ON 02-NOV-2012
i_num_request_id,
v_num_sequence_id
);
/* Updating the custom table with the EM_BASe fetched form the function*/
UPDATE xxpo_bom_comps_reg xbcr
SET em_base =
xxpo_embase_fun_reg (xbcr.component_item_id,
xbcr.organization_id,
xbcr.comp_user_item_type
)
WHERE xbcr.comp_user_item_type = 'EMM';
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Insert Statement-' || SQLERRM
);
END;
COMMIT;
BEGIN
v_num_ite_count := 0;
LOOP -- infinite loop
-- INITIAL RECORD SET USING LOOP TO SET Y AND N
FOR cur_update_flag_rec IN
cur_update_flag_1 (v_num_sequence_id) -- to fetch all Ns
LOOP
IF cur_update_flag_rec.sno = 1
THEN
IF (cur_update_flag_rec.comp_user_item_type IN
('EMM', 'SA', 'CONFIGURED_ITEM')
)
THEN
v_chr_flag := 'EXP';
ELSIF cur_update_flag_rec.comp_user_item_type IN
('P', 'EMR_PURCH')
THEN
/* Query to check if the BOM exists for the parts with item type Purchased Item*/
BEGIN
SELECT COUNT (1)
INTO v_num_count_bom_first
FROM bom_bill_of_materials a,
bom_inventory_components b
WHERE a.assembly_item_id =
cur_update_flag_rec.component_item_id
AND a.organization_id = i_num_organization_id
AND b.bill_sequence_id = a.common_bill_sequence_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'error while checking bom exists for the component'
);
END;
IF v_num_count_bom_first > 0
THEN
v_chr_flag := 'EXP';
ELSE
v_chr_flag := 'PRINT';
END IF;
ELSE
v_chr_flag := 'Y';
END IF;
ELSE
-- check on BOM of the Item (form 2nd level onwards)
BEGIN
SELECT COUNT (1)
INTO v_num_count_bom
FROM bom_bill_of_materials a,
bom_inventory_components b
WHERE a.assembly_item_id =
cur_update_flag_rec.component_item_id
AND a.organization_id = i_num_organization_id
AND b.bill_sequence_id = a.common_bill_sequence_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'error while checking bom exists for the component'
);
END;
IF v_num_count_bom > 0
THEN
v_chr_flag := 'EXP';
ELSE
v_chr_flag := 'PRINT';
END IF;
--commentd by REGS dated 11/10/2012
-- Logic to update the duplicate records with flag = 'Y'
IF v_chr_flag = 'PRINT'
THEN
fnd_file.put_line (fnd_file.LOG,
'IF FLAG PRINT ' || V_CHR_FLAG
);
-- Query to check if any duplicate records are getting inserted
BEGIN
SELECT COUNT (1)
INTO v_num_count_fir_lvl
FROM xxpo_bom_comps_reg
WHERE assembly_item_id =
cur_update_flag_rec.assembly_item_id
AND component_item_id =
cur_update_flag_rec.component_item_id
AND top_assembly_item_id =
cur_update_flag_rec.top_assembly_item_id
AND line_number = cur_update_flag_rec.line_number ---- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012
AND flag = 'PRINT';
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
-- If the record already exists with flag = 'PRINT' update new record with flag = 'Y'
IF v_num_count_fir_lvl > 0
THEN
v_chr_flag := 'Y';
END IF;
END IF;
END IF;
BEGIN
/* Updating the custom table with the flag*/
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_flag,
line_number = i_num_line_number
WHERE ROWID = cur_update_flag_rec.rowid_num;
fnd_file.put_line
(fnd_file.LOG,
'Exception updating successful records-'
|| i_num_line_number || ',' || v_chr_flag || ',' || cur_update_flag_rec.rowid_num
);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Exception updating successful records-'
|| SQLERRM
);
END;
END LOOP; --cur_update_flag_rec IN cur_update_flag_1
FOR rec_assembly_items IN cur_assembly_items (v_num_sequence_id)
-- fetch all remaing EXP records
LOOP
v_chr_exp_flag := 'EXP_NOT';
v_chr_embase := rec_assembly_items.em_base;
IF rec_assembly_items.comp_user_item_type = 'CONFIGURED_ITEM'
THEN
v_num_sequence_id_model := v_num_sequence_id + 1;
v_chr_flag := 'Y';
fnd_file.put_line (fnd_file.LOG,
'IF FLAG ITEM TYPE ' || rec_assembly_items.comp_user_item_type);
BEGIN
/* Updating the custom table with the flag*/
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_flag,
line_number = i_num_line_number
WHERE ROWID = rec_assembly_items.rowid_num;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Exception updating successful records-'
|| SQLERRM
);
END;
v_num_sequence_id_bef_exp := v_num_sequence_id;
/* Calling the procedure to explode the configured item if exists any*/
explode_bom (v_chr_errbuff,
v_num_retcode,
i_num_request_id,
i_num_organization_id,
rec_assembly_items.component_item_id,
i_num_line_number,
v_num_sequence_id_model
);
COMMIT;
v_num_sequence_id := v_num_sequence_id_bef_exp;
ELSE
FOR rec_item_type_seg IN
cur_item_type_seg (rec_assembly_items.component_item_id,
rec_assembly_items.organization_id
)
LOOP
BEGIN
INSERT INTO xxpo_bom_comps_reg
(sno, assembly_item_id,
assembly,
asmbly_user_item_type,
asmbly_bom_item_type,
component_item_id,
component,
comp_user_item_type,
comp_bom_item_type,
quantity, flag,
line_number,
top_assembly,
top_assembly_item_id,
top_asmbly_user_item_type,
top_asmbly_bom_item_type,
organization_id,
mrp_plan,
created_by, creation_date,
last_updated_by, last_updated_date,
last_updated_login, request_id,
program_application_id,
program_id,
program_update_date, em_base,
sequence_id
)
VALUES (2, rec_item_type_seg.assembly_item_id,
rec_item_type_seg.assembly,
rec_item_type_seg.asmbly_user_item_type,
rec_item_type_seg.asmbly_bom_item_type,
rec_item_type_seg.component_item_id,
rec_item_type_seg.component,
rec_item_type_seg.comp_user_item_type,
rec_item_type_seg.comp_bom_item_type,
rec_item_type_seg.quantity, 'N',
i_num_line_number,
rec_assembly_items.top_assembly,
rec_assembly_items.top_assembly_item_id,
rec_assembly_items.top_asmbly_user_item_type,
rec_assembly_items.top_asmbly_bom_item_type,
rec_assembly_items.organization_id,
rec_assembly_items.mrp_plan,
fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.login_id, i_num_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
TRUNC (SYSDATE), v_chr_embase,
v_num_sequence_id
);
v_chr_exp_flag := 'EXP_DONE';
fnd_file.put_line (fnd_file.LOG,
'IF' || v_chr_exp_flag);
fnd_file.put_line (fnd_file.LOG,
'AFTER INSERT' || rec_item_type_seg.assembly_item_id);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'---ERROR 1---' || SQLERRM
);
END;
END LOOP;
END IF;
BEGIN
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_exp_flag,
line_number = i_num_line_number
WHERE ROWID = rec_assembly_items.rowid_num;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'ERROR WHILE UPDATING FLAG '
|| SQLERRM
);
END;
END LOOP; --rec_assembly_items IN cur_assembly_items
COMMIT;
BEGIN
v_num_no_count := 0;
/* Count to check if there exists any record which needs to be exploded*/
SELECT COUNT (1)
INTO v_num_no_count
FROM xxpo_bom_comps_reg
WHERE sequence_id = v_num_sequence_id AND flag IN
('N', 'EXP');
fnd_file.put_line (fnd_file.LOG,
';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;'
);
fnd_file.put_line (fnd_file.LOG,
'v_num_no_count :=' || v_num_no_count
);
fnd_file.put_line (fnd_file.LOG,
';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;'
);
EXCEPTION
WHEN OTHERS
THEN
v_num_no_count := 0;
END;
EXIT WHEN v_num_no_count = 0;
END LOOP;
-- Infinite loop which exist when all the records in
--custom table are 'Y'
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Main Package-' || SQLERRM
);
END;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Main Package-' || SQLERRM
);
END explode_bom;
PROCEDURE explode_bom_model (
out_errbuf OUT VARCHAR2,
out_retcode OUT NUMBER,
i_num_request_id IN NUMBER,
i_num_organization_id IN NUMBER,
i_num_item_id IN NUMBER,
i_num_line_number IN NUMBER,
i_num_sequence_id IN NUMBER
)
IS
v_chr_flag VARCHAR2 (10);
v_chr_errbuff VARCHAR2 (60);
v_num_retcode NUMBER;
v_num_no_count NUMBER := 0;
v_num_ite_count NUMBER := 0;
v_chr_exp_flag VARCHAR2 (10);
v_num_count_fir_lvl NUMBER := 0;
v_num_count_bom_first NUMBER := 0;
v_chr_embase VARCHAR2 (40);
v_num_sequence_id NUMBER := 0;
v_num_sequence_id_model NUMBER := 0;
v_num_sequence_id_bef_exp NUMBER := 0;
/* Cursor to fetch the items which got inserted in to table for the first time */
CURSOR cur_update_flag_1 (c_num_sequence_id NUMBER)
IS
SELECT ROWID rowid_num, xbcr.*
FROM xxpo_bom_comps_reg xbcr
WHERE flag = 'N'
AND request_id = i_num_request_id
AND sequence_id = c_num_sequence_id
ORDER BY component_item_id, sno;
/* Cursor to fetch the items which needs to be exploded */
CURSOR cur_assembly_items (c_num_sequence_id NUMBER)
IS
SELECT ROWID rowid_num, xbcr.*
FROM xxpo_bom_comps_reg xbcr
WHERE flag = 'EXP'
AND request_id = i_num_request_id
AND sequence_id = c_num_sequence_id
ORDER BY component_item_id, sno;
/* Cursor to check if BOM exists for the item to be exploded */
CURSOR cur_item_type_seg (i_num_assembly_item_id NUMBER, i_org_id NUMBER)
IS
SELECT msi1.inventory_item_id assembly_item_id,
msi1.segment1 assembly, msi1.item_type asmbly_user_item_type,
msi1.bom_item_type asmbly_bom_item_type,
msi2.inventory_item_id component_item_id,
msi2.segment1 component, msi2.item_type comp_user_item_type,
msi2.bom_item_type comp_bom_item_type,
bic.component_quantity quantity, 'N' flag,
i_num_organization_id organization_id, 'MRP' mrp_plan
FROM bom_bill_of_materials bom,
mtl_system_items_b msi1,
bom_inventory_components bic,
mtl_system_items_b msi2
WHERE bom.organization_id = msi1.organization_id
AND msi1.inventory_item_id = bom.assembly_item_id
AND bic.bill_sequence_id = bom.common_bill_sequence_id
AND msi2.inventory_item_id = bic.component_item_id
AND msi2.organization_id = msi1.organization_id
AND bom.assembly_item_id = i_num_assembly_item_id
AND bic.implementation_date IS NOT NULL
-- AND bic.disable_date IS NULL HPOV 379694
AND bom.alternate_bom_designator IS NULL
AND bom.organization_id = i_org_id;
BEGIN
-- Getting the Components for the assembly
BEGIN
IF i_num_sequence_id = 0
THEN
v_num_sequence_id := 1;
ELSE
v_num_sequence_id := i_num_sequence_id;
END IF;
/* Calling the Insert procedure to insert the items into the table */
xxpo_bom_comps_insert_reg (v_chr_errbuff,
v_num_retcode,
1,
i_num_organization_id,
i_num_item_id,
i_num_line_number, -- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012
i_num_request_id,
v_num_sequence_id
);
/* Updating the Custom table EM_BASE column for all the EM Bases*/
UPDATE xxpo_bom_comps_reg xbcr
SET em_base = xbcr.component
WHERE xbcr.comp_user_item_type = 'EMB'
AND request_id = i_num_request_id
AND sequence_id = v_num_sequence_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Insert Statement-' || SQLERRM
);
END;
COMMIT;
BEGIN
v_num_ite_count := 0;
LOOP -- infinite loop
-- INITIAL RECORD SET USING LOOP TO SET Y AND N
FOR cur_update_flag_rec IN
cur_update_flag_1 (v_num_sequence_id) -- to fetch all Ns
LOOP
/* Cursor to check if BOM exists for the item to be exploded */
BEGIN
SELECT COUNT (1)
INTO v_num_count_bom_first
FROM bom_bill_of_materials a, bom_inventory_components b
WHERE a.assembly_item_id =
cur_update_flag_rec.component_item_id
AND a.organization_id = i_num_organization_id
AND b.bill_sequence_id = a.common_bill_sequence_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'error while checking bom exists for the component'
);
END;
IF v_num_count_bom_first > 0
THEN
v_chr_flag := 'EXP';
ELSE
v_chr_flag := 'PRINT';
END IF;
-- Logic to update the duplicate records with flag = 'Y'
IF v_chr_flag = 'PRINT'
THEN
BEGIN
SELECT COUNT (1)
INTO v_num_count_fir_lvl
FROM xxpo_bom_comps_reg
WHERE assembly_item_id =
cur_update_flag_rec.assembly_item_id
AND component_item_id =
cur_update_flag_rec.component_item_id
AND top_assembly_item_id =
cur_update_flag_rec.top_assembly_item_id
AND line_number = cur_update_flag_rec.line_number -- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012
AND flag = 'PRINT';
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
-- If the record already exists with flag = 'PRINT' update new record with flag = 'Y'
IF v_num_count_fir_lvl > 0
THEN
v_chr_flag := 'Y';
END IF;
END IF;
BEGIN
/* Updating the custom table with appropriate print flag*/
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_flag,
line_number = i_num_line_number
WHERE ROWID = cur_update_flag_rec.rowid_num;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Exception updating successful records-'
|| SQLERRM
);
END;
END LOOP; --cur_update_flag_rec IN cur_update_flag_1
FOR rec_assembly_items IN cur_assembly_items (v_num_sequence_id)
-- fetch all remaing EXP records
LOOP
v_chr_exp_flag := 'EXP_NOT';
v_chr_embase := rec_assembly_items.em_base;
IF rec_assembly_items.comp_user_item_type = 'ATO'
THEN
v_num_sequence_id_model := v_num_sequence_id + 1;
v_chr_flag := 'Y';
BEGIN
/* Updating the custom table with appropriate print flag*/
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_flag,
line_number = i_num_line_number
WHERE ROWID = rec_assembly_items.rowid_num;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Exception updating successful records-'
|| SQLERRM
);
END;
v_num_sequence_id_bef_exp := v_num_sequence_id;
/* Calling the procedure to explode the model item if exists any*/
explode_bom_model (v_chr_errbuff,
v_num_retcode,
i_num_request_id,
i_num_organization_id,
rec_assembly_items.component_item_id,
i_num_line_number,
v_num_sequence_id_model
);
COMMIT;
v_num_sequence_id := v_num_sequence_id_bef_exp;
ELSE
FOR rec_item_type_seg IN
cur_item_type_seg (rec_assembly_items.component_item_id,
rec_assembly_items.organization_id
)
LOOP
BEGIN
INSERT INTO xxpo_bom_comps_reg
(sno, assembly_item_id,
assembly,
asmbly_user_item_type,
asmbly_bom_item_type,
component_item_id,
component,
comp_user_item_type,
comp_bom_item_type,
quantity, flag,
line_number,
top_assembly,
top_assembly_item_id,
top_asmbly_user_item_type,
top_asmbly_bom_item_type,
organization_id,
mrp_plan,
created_by, creation_date,
last_updated_by, last_updated_date,
last_updated_login, request_id,
program_application_id,
program_id,
program_update_date, em_base,
sequence_id
)
VALUES (2, rec_item_type_seg.assembly_item_id,
rec_item_type_seg.assembly,
rec_item_type_seg.asmbly_user_item_type,
rec_item_type_seg.asmbly_bom_item_type,
rec_item_type_seg.component_item_id,
rec_item_type_seg.component,
rec_item_type_seg.comp_user_item_type,
rec_item_type_seg.comp_bom_item_type,
rec_item_type_seg.quantity, 'N',
i_num_line_number,
rec_assembly_items.top_assembly,
rec_assembly_items.top_assembly_item_id,
rec_assembly_items.top_asmbly_user_item_type,
rec_assembly_items.top_asmbly_bom_item_type,
rec_assembly_items.organization_id,
rec_assembly_items.mrp_plan,
fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.login_id, i_num_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
TRUNC (SYSDATE), v_chr_embase,
v_num_sequence_id
);
v_chr_exp_flag := 'EXP_DONE';
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'---ERROR 1---' || SQLERRM
);
END;
END LOOP;
END IF;
fnd_file.put_line (fnd_file.LOG,'LOG10:' ||i_num_line_number || ' '|| v_chr_exp_flag);
--rec_item_type_seg IN cur_item_type_seg( rec_assembly_items.assembly_item_id,
BEGIN
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_exp_flag,
line_number = i_num_line_number
WHERE ROWID = rec_assembly_items.rowid_num;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'ERROR WHILE UPDATING FLAG '
|| SQLERRM
);
END;
END LOOP; --rec_assembly_items IN cur_assembly_items
COMMIT;
BEGIN
v_num_no_count := 0;
/* Count to check if there exists any record which needs to be exploded*/
SELECT COUNT (1)
INTO v_num_no_count
FROM xxpo_bom_comps_reg
WHERE sequence_id = v_num_sequence_id AND flag IN
('N', 'EXP');
fnd_file.put_line (fnd_file.LOG,
';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;'
);
fnd_file.put_line (fnd_file.LOG,
'v_num_no_count :=' || v_num_no_count
);
fnd_file.put_line (fnd_file.LOG,
';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;'
);
EXCEPTION
WHEN OTHERS
THEN
v_num_no_count := 0;
END;
EXIT WHEN v_num_no_count = 0;
END LOOP;
-- Infinite loop which exist when all the records in
--custom table are 'Y'
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Main Package-' || SQLERRM
);
END;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Main Package-' || SQLERRM
);
END explode_bom_model;
PROCEDURE xxpo_bom_comps_insert_reg (
out_errbuf OUT VARCHAR2,
out_retcode OUT NUMBER,
i_num_seq IN NUMBER,
i_num_organization_id IN NUMBER,
i_num_item_id IN NUMBER,
i_num_line_number IN NUMBER, -- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012
i_num_request_id IN NUMBER,
i_num_sequence_id IN NUMBER
)
IS
-- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012
v_num_count NUMBER :=0;
BEGIN
SELECT COUNT(1)
INTO v_num_count
FROM bom_bill_of_materials
WHERE assembly_item_id = i_num_item_id
AND organization_id IN (select organization_id from mtl_parameters
where organization_code in ('FRX','MCK','MPW'));
IF v_num_count = 0
THEN
BEGIN
INSERT INTO xxpo_bom_comps_reg
(sno, LINE_NUMBER,assembly_item_id, assembly, asmbly_user_item_type,
asmbly_bom_item_type, component_item_id, component,
comp_user_item_type, comp_bom_item_type,flag,
top_assembly, top_assembly_item_id,
top_asmbly_user_item_type, top_asmbly_bom_item_type,
organization_id, mrp_plan, created_by, creation_date,
last_updated_by, last_updated_date, last_updated_login,
request_id, program_application_id, program_id,
program_update_date, sequence_id)
SELECT i_num_seq,i_num_LINE_NUMBER ,msi1.inventory_item_id, msi1.segment1,
msi1.item_type, msi1.bom_item_type, msi1.inventory_item_id,
msi1.segment1, msi1.item_type, msi1.bom_item_type,
'N', msi1.segment1,msi1.inventory_item_id, msi1.item_type, msi1.bom_item_type,
i_num_organization_id, 'MRP', fnd_global.user_id,
TRUNC (SYSDATE), fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.login_id, i_num_request_id,
fnd_global.prog_appl_id, fnd_global.conc_program_id,
TRUNC (SYSDATE), i_num_sequence_id
FROM mtl_system_items_b msi1
WHERE organization_id = i_num_organization_id
AND inventory_item_id = i_num_item_id;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error in inserting the records in xxpo_bom_comps_reg' || SQLERRM
);
END;
ELSE
-- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012 TILL HERE
BEGIN
INSERT INTO xxpo_bom_comps_reg
(sno, LINE_NUMBER,assembly_item_id, assembly, asmbly_user_item_type,
asmbly_bom_item_type, component_item_id, component,
comp_user_item_type, comp_bom_item_type, quantity, flag,
top_assembly, top_assembly_item_id,
top_asmbly_user_item_type, top_asmbly_bom_item_type,
organization_id, mrp_plan, created_by, creation_date,
last_updated_by, last_updated_date, last_updated_login,
request_id, program_application_id, program_id,
program_update_date, sequence_id)
SELECT i_num_seq,i_num_LINE_NUMBER ,msi1.inventory_item_id, msi1.segment1,
msi1.item_type, msi1.bom_item_type, msi2.inventory_item_id,
msi2.segment1, msi2.item_type, msi2.bom_item_type,
bic.component_quantity, 'N', msi1.segment1,
msi1.inventory_item_id, msi1.item_type, msi1.bom_item_type,
i_num_organization_id, 'MRP', fnd_global.user_id,
TRUNC (SYSDATE), fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.login_id, i_num_request_id,
fnd_global.prog_appl_id, fnd_global.conc_program_id,
TRUNC (SYSDATE), i_num_sequence_id
FROM bom_bill_of_materials bom,
mtl_system_items_b msi1,
bom_inventory_components bic,
mtl_system_items_b msi2
WHERE bom.organization_id = msi1.organization_id
AND msi1.inventory_item_id = bom.assembly_item_id
AND bic.bill_sequence_id = bom.common_bill_sequence_id
AND msi2.inventory_item_id = bic.component_item_id
AND msi2.organization_id = msi1.organization_id
AND bic.implementation_date IS NOT NULL
-- AND bic.disable_date IS NULL HPOV 379694
AND bom.alternate_bom_designator IS NULL
-- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012
AND bom.organization_id in (select organization_id from mtl_parameters
where organization_code ='FRX' OR organization_code ='MCK'
OR organization_code = 'MPW' ) --i_num_organization_id
AND bom.assembly_item_id = i_num_item_id
group by i_num_seq,i_num_LINE_NUMBER ,msi1.inventory_item_id, msi1.segment1,
msi1.item_type, msi1.bom_item_type, msi2.inventory_item_id,
msi2.segment1, msi2.item_type, msi2.bom_item_type,
bic.component_quantity, 'N', msi1.segment1,
msi1.inventory_item_id, msi1.item_type, msi1.bom_item_type,
i_num_organization_id, 'MRP', fnd_global.user_id,
TRUNC (SYSDATE), fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.login_id, i_num_request_id,
fnd_global.prog_appl_id, fnd_global.conc_program_id,
TRUNC (SYSDATE), i_num_sequence_id;
-- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012 TILL HERE
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error in inserting the records in xxpo_bom_comps_reg:' || SQLERRM
);
END;
END IF;
END xxpo_bom_comps_insert_reg;
FUNCTION xxpo_embase_fun_reg (
i_num_comp_item_id IN NUMBER,
i_num_organization_id IN NUMBER,
i_chr_item_type IN VARCHAR2
)
RETURN VARCHAR2
IS
v_num_assembly_item_id1 NUMBER;
v_num_comp_item_id_cur NUMBER;
v_num_assembly_item_id2 NUMBER := NULL;
v_chr_item_type1 VARCHAR2 (100);
v_num_excep_flag NUMBER := 0;
v_chr_assembly_item VARCHAR2 (40);
v_num_comp_sequence_id_cur NUMBER := 0;
BEGIN
v_num_comp_item_id_cur := i_num_comp_item_id;
v_num_excep_flag := 1;
LOOP
IF i_chr_item_type NOT IN ('CONFIGURED_ITEM', 'ATO')
THEN
BEGIN
SELECT assembly_item_id
INTO v_num_assembly_item_id1
FROM bom_bill_of_materials bom
WHERE EXISTS (
SELECT 1
FROM bom_inventory_components bic
WHERE bic.parent_bill_seq_id =
bom.common_bill_sequence_id
AND bic.component_item_id = v_num_comp_item_id_cur)
AND bom.organization_id = i_num_organization_id;
v_num_excep_flag := 0;
EXCEPTION
WHEN OTHERS
THEN
v_num_excep_flag := 1;
DBMS_OUTPUT.put_line
( 'FAILED IN CALC OF ASSEMBLY ITEM ID COMPONENT ITEM '
|| v_num_comp_item_id_cur
|| 'COMPONENT_SEQUENCE_ID'
|| v_num_comp_sequence_id_cur
);
END;
IF v_num_excep_flag = 0
THEN
BEGIN
SELECT item_type, segment1
INTO v_chr_item_type1, v_chr_assembly_item
FROM mtl_system_items_b
WHERE inventory_item_id = v_num_assembly_item_id1
AND organization_id = i_num_organization_id;
EXCEPTION
WHEN OTHERS
THEN
v_num_excep_flag := 1;
DBMS_OUTPUT.put_line ( 'FAILED IN CALC OF ITEM type'
|| v_num_assembly_item_id1
|| 'organization_ID'
|| i_num_organization_id
);
END;
END IF;
END IF;
IF v_chr_item_type1 = 'EMB' AND v_num_excep_flag = 0
THEN
v_num_assembly_item_id2 := v_num_assembly_item_id1;
v_num_comp_item_id_cur := v_num_assembly_item_id1;
EXIT;
ELSIF ( v_chr_item_type1 = 'CONFIGURED_ITEM'
OR v_chr_item_type1 = 'ATO'
)
AND v_num_excep_flag = 0
THEN
v_num_assembly_item_id2 := NULL;
v_chr_assembly_item := NULL;
EXIT;
ELSE
IF v_num_excep_flag = 0
THEN
v_num_comp_item_id_cur := v_num_assembly_item_id1;
ELSE
v_num_assembly_item_id2 := NULL;
v_chr_assembly_item := NULL;
EXIT;
END IF;
END IF;
END LOOP;
RETURN (v_chr_assembly_item);
END;
PROCEDURE vendor_name_prc (
i_num_organization_id IN NUMBER,
i_num_component_item_id IN NUMBER,
o_chr_vendor_name OUT VARCHAR2
)
IS
v_num_assgn_set_id NUMBER;
v_num_sourcing_id NUMBER;
v_num_organization_id NUMBER;
v_num_source_type NUMBER;
v_chr_sourcing_name VARCHAR2 (50);
v_num_receipt_id NUMBER;
v_chr_vendor_name VARCHAR2 (240);
v_num_org_id NUMBER;
v_num_component_item_id NUMBER;
v_chr_out_name VARCHAR2 (240);
v_chr_assign_set_name VARCHAR2 (1000)
:= fnd_profile.VALUE ('XXPO_ASS_SET_FOR_CON_REG');
v_num_vendor_id NUMBER;
BEGIN
v_num_component_item_id := i_num_component_item_id;
/* To fetch the assignment set id */
BEGIN
SELECT assignment_set_id
INTO v_num_assgn_set_id
FROM mrp_assignment_sets
WHERE assignment_set_name = v_chr_assign_set_name;
EXCEPTION
WHEN OTHERS
THEN
v_num_assgn_set_id := NULL;
END;
IF v_num_assgn_set_id IS NOT NULL
THEN
BEGIN
SELECT sourcing_rule_name
INTO v_chr_sourcing_name
FROM mrp_sr_assignments_v
WHERE assignment_set_id = v_num_assgn_set_id
AND organization_id = i_num_organization_id
AND inventory_item_id = i_num_component_item_id;
EXCEPTION
WHEN OTHERS
THEN
v_num_sourcing_id := NULL;
END;
END IF;
IF v_chr_sourcing_name IS NOT NULL
THEN
BEGIN
SELECT sourcing_rule_id
INTO v_num_sourcing_id
FROM mrp_sourcing_rules
WHERE sourcing_rule_name = v_chr_sourcing_name
AND organization_id = i_num_organization_id;
EXCEPTION
WHEN OTHERS
THEN
v_num_sourcing_id := NULL;
END;
END IF;
IF v_num_sourcing_id IS NOT NULL
THEN
BEGIN
SELECT sr_receipt_id
INTO v_num_receipt_id
FROM mrp_sr_receipt_org
WHERE sourcing_rule_id = v_num_sourcing_id;
EXCEPTION
WHEN OTHERS
THEN
v_num_receipt_id := NULL;
END;
END IF;
IF v_num_receipt_id IS NOT NULL
THEN
BEGIN
SELECT source_type, vendor_id, source_organization_id
INTO v_num_source_type, v_num_vendor_id, v_num_org_id
FROM mrp_sr_source_org
WHERE sr_receipt_id = v_num_receipt_id;
EXCEPTION
WHEN OTHERS
THEN
v_num_vendor_id := NULL;
END;
IF v_num_vendor_id IS NOT NULL
THEN
BEGIN
SELECT vendor_name
INTO v_chr_vendor_name
FROM po_vendors
WHERE vendor_id = v_num_vendor_id;
EXCEPTION
WHEN OTHERS
THEN
v_chr_vendor_name := NULL;
END;
END IF;
IF v_num_source_type <> 3
THEN
vendor_name_prc (v_num_org_id,
v_num_component_item_id,
v_chr_out_name
);
ELSE
o_chr_vendor_name := v_chr_vendor_name;
END IF;
END IF;
END vendor_name_prc;
END XXPO_BOM_COMPS_PKG_REG;
/
|
|
|
|
|
|
| Re: Program never get completed [message #629817 is a reply to message #629814] |
Tue, 16 December 2014 04:46 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
So we've gone from far too little code to far too much.
Remember, we've got no idea what your system does, so a huge mass of code isn't going to mean much.
However, I assume the problem is that v_num_no_count is never 0. What you do about that I have no idea, since I have no idea what the code is supposed to do.
|
|
|
|
Goto Forum:
Current Time: Tue Apr 23 01:26:29 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
