SELECT -- WC.CARRIER_ID,
HP.PARTY_NAME NAME,
WC.FREIGHT_CODE SHORT_NAME,
HP.STATUS ACTIVE_FLAG,
WC.SCAC_CODE,
WC.CURRENCY_CODE DEFAULT_CURRENCY,
WC.GENERIC_FLAG,
FLV.MEANING SERVICE_LEVEL,
WCS.MODE_OF_TRANSPORT,
FL.MEANING SHIP_METHOD_MEANING,
WCS.SL_TIME_UOM,
WCS.MIN_SL_TIME,
WCS.MAX_SL_TIME,
WCS.ENABLED_FLAG,
WCS.WEB_ENABLED,
WC.ATTRIBUTE1||WC.ATTRIBUTE2||WC.ATTRIBUTE3||WC.ATTRIBUTE4||WC.ATTRIBUTE5||WC.ATTRIBUTE6||WC.ATTRIBUTE7||WC.ATTRIBUTE8||WC.ATTRIBUTE9||WC.ATTRIBUTE10||WC.ATTRIBUTE11||WC.ATTRIBUTE12||WC.ATTRIBUTE13||WC.ATTRIBUTE14||WC.ATTRIBUTE15 DFF,
WCSM.SITE_NUMBER,
WCSM.LOCATION ADDRESS,
WCSM.ACTIVE SITE_ACTIVE_FLAG,
WCC.PERSON_LAST_NAME,
WCC.PERSON_FIRST_NAME,
WCC.PERSON_PRE_NAME_ADJUNCT TITLE,
WCC.ACTIVE CONTACT_NAME_ACTIVE_FLAG,
WCCI.COUNTRY_CODE,
WCCI.AREA_CODE,
WCCI.PHONE_NUMBER,
WCCI.EXTENSION,
WCCI.CONTACT_TYPE,
WCCI.PRIMARY PRIMARY_FLAG,
WCCI.ACTIVE CONTACT_TELE_ACTIVE_FLAG,
WCCV.CARRIER_CLASS_CATEGORY CATEGORY,
WCCV.ACTIVE CATEGORY_ENABLED_FLAG,
WC.MANIFESTING_ENABLED_FLAG,
WC.WEIGHT_UOM WEIGHT,
WC.TIME_UOM TIME,
WC.DIMENSION_UOM DIMENSION,
WC.VOLUME_UOM VOLUME,
WC.DISTANCE_UOM DISTANCE,
PV.VENDOR_NAME SUPPLIER,
PVS.VENDOR_SITE_CODE DEFAULT_SUPPLIER_SITE,
WC.FREIGHT_BILL_AUTO_APPROVAL AUTO_APPROVE_BILLS_FLAG,
WC.FREIGHT_AUDIT_LINE_LEVEL AUDIT_BILL_LINES_FLAG,
WC.ORIGIN_DSTN_SURCHARGE_LEVEL SURCHARGE_LEVEL,
WC.CM_FIRST_LOAD_DISCOUNT FIRST_LOAD_DISCOUNT_FLAG,
WC.DISTANCE_CALCULATION_METHOD DISTANCE_CALCULATION,
WC.CM_FREE_DH_MILEAGE FREE_DEADHEAD_DISTANCE,
WC.MAX_OUT_OF_ROUTE "MAX_OUT_OF_ROUTE%",
WC.MIN_CM_TIME MIN_TIME,
WC.UNIT_RATE_BASIS,
WC.MIN_CM_DISTANCE MIN_DISTANCE,
WC.CM_RATE_VARIANT RATE_VARIANT,
WC.ALLOW_INTERSPERSE_LOAD ALLOW_INTERSPERSAL_LOAD_FLAG,
WC.ALLOW_CONTINUOUS_MOVE ALLOW_CONTINUOUS_MOVE_FLAG,
WC.MAX_NUM_STOPS_PERMITTED MAX_NUMBER_OF_STOPS,
WC.MAX_CM_TIME MAX_TIME,
WC.MAX_TOTAL_DISTANCE,
WC.MAX_CM_DISTANCE MAX_DISTANCE,
WC.MAX_TOTAL_TIME,
WC.MAX_CM_DH_DISTANCE MAX_DEADHEAD_DISTANCE,
WC.MAX_CM_DH_TIME MAX_DEADHEAD_TIME,
WC.MIN_LAYOVER_TIME,
WC.MAX_TOTAL_DISTANCE_IN_24HR MAX_DISTANCE_PER_DAY,
WC.MAX_LAYOVER_TIME,
WC.MAX_DRIVING_TIME_IN_24HR MAX_DRIVING_TIME_PER_DAY,
WC.MAX_DUTY_TIME_IN_24HR MAX_ON_DUTY_TIME_PER_DAY,
WC.MIN_SIZE_LENGTH,
WC.MAX_SIZE_LENGTH,
WC.MIN_SIZE_HEIGHT,
WC.MAX_SIZE_HEIGHT,
WC.MIN_SIZE_WIDTH,
WC.MAX_SIZE_WIDTH
FROM HZ_PARTIES HP,
WSH_CARRIERS WC,
WSH_CARRIER_SERVICES WCS,
FND_LOOKUP_VALUES_VL FL,
FND_LOOKUP_VALUES FLV,
WSH_CARRIER_SITES_MAIN_V WCSM,
WSH_CARRIER_CONTACTS_V WCC,
HZ_PARTY_RELATIONSHIPS HPR,
WSH_CARRIER_CONTACT_INFO_V WCCI,
WSH_CARRIER_CLASSIFICATIONS_V WCCV,
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVS
WHERE WC.CARRIER_ID = HP.PARTY_ID
AND WC.CARRIER_ID = WCS.CARRIER_ID
AND FL.LOOKUP_TYPE = 'SHIP_METHOD'
AND FL.LOOKUP_CODE = WCS.SHIP_METHOD_CODE
AND FL.VIEW_APPLICATION_ID = 3
AND FLV.LOOKUP_TYPE = 'WSH_SERVICE_LEVELS'
AND WCS.SERVICE_LEVEL = FLV.LOOKUP_CODE
AND WC.CARRIER_ID = WCSM.CARRIER_ID(+)
AND WC.CARRIER_ID = WCC.CARRIER_ID(+)
AND WC.CARRIER_ID = HPR.OBJECT_ID(+)
AND HPR.PARTY_ID = WCCI.RELATION_PARTY_ID(+)
AND WC.CARRIER_ID = WCCV.PARTY_ID(+)
AND WC.SUPPLIER_ID = PV.VENDOR_ID(+)
AND WC.SUPPLIER_SITE_ID = PVS.VENDOR_SITE_ID(+)
--AND HP.PARTY_NAME = 'PO- MERCER'
Saturday, 22 November 2014
link between MTL_SYSTEM_ITEMS_B and MTL_ITEM_CATEGORIES_V
select msi.ORGANIZATION_ID,
msi.segment1,
msi.DESCRIPTION,
mst.LONG_DESCRIPTION,
msi.PRIMARY_UOM_CODE,
msi.ITEM_TYPE,
msi.INVENTORY_ITEM_STATUS_CODE,
miv.CATEGORY_CONCAT_SEGS HS_CATEGORY_CODE
from mtl_system_items_b msi ,
MTL_SYSTEM_ITEMS_TL mst,
MTL_ITEM_CATEGORIES_V miv
where msi.inventory_item_id = mst.inventory_item_id
and msi.organization_id = mst.organization_id
and msi.inventory_item_id = miv.inventory_item_id
and msi.organization_id = miv.organization_id
and miv.CATEGORY_SET_NAME ='ENVS HS Code'
and msi.segment1 ='08180813' and msi.organization_id =104
msi.segment1,
msi.DESCRIPTION,
mst.LONG_DESCRIPTION,
msi.PRIMARY_UOM_CODE,
msi.ITEM_TYPE,
msi.INVENTORY_ITEM_STATUS_CODE,
miv.CATEGORY_CONCAT_SEGS HS_CATEGORY_CODE
from mtl_system_items_b msi ,
MTL_SYSTEM_ITEMS_TL mst,
MTL_ITEM_CATEGORIES_V miv
where msi.inventory_item_id = mst.inventory_item_id
and msi.organization_id = mst.organization_id
and msi.inventory_item_id = miv.inventory_item_id
and msi.organization_id = miv.organization_id
and miv.CATEGORY_SET_NAME ='ENVS HS Code'
and msi.segment1 ='08180813' and msi.organization_id =104
Tuesday, 11 November 2014
API for Load the Price List (qp_price_list_pub.process_price_list)
SET SERVEROUTPUT ON;
DECLARE
v_return_status VARCHAR2(1) := NULL;
v_msg_count NUMBER := 0;
v_msg_data VARCHAR2 (2000);
v_price_list_rec qp_price_list_pub.price_list_rec_type;
v_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
v_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
v_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
v_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
v_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
v_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
v_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
ppr_price_list_rec qp_price_list_pub.price_list_rec_type;
ppr_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
ppr_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
ppr_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
ppr_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
ppr_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
ppr_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
ppr_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
BEGIN
v_price_list_rec.list_header_id := 353500;
v_price_list_rec.list_type_code := 'PRL';
v_price_list_rec.operation := qp_globals.g_opr_update;
v_price_list_line_tbl(1).list_header_id := 353500;
v_price_list_line_tbl(1).list_line_id := fnd_api.g_miss_num;
v_price_list_line_tbl(1).list_line_type_code:= 'PLL';
v_price_list_line_tbl(1).operation := qp_globals.g_opr_create;
v_price_list_line_tbl(1).operand := 51.6;
v_price_list_line_tbl(1).arithmetic_operator:= 'UNIT_PRICE';
v_price_list_line_tbl(1).start_date_active := '01-NOV-2014';
v_price_list_line_tbl(1).end_date_active := '31-OCT-2015';
v_price_list_line_tbl(1).PRODUCT_PRECEDENCE :=220;
v_price_list_line_tbl(1).organization_id := NULL;
v_pricing_attr_tbl(1).pricing_attribute_id := fnd_api.g_miss_num;
v_pricing_attr_tbl(1).list_line_id := fnd_api.g_miss_num;
v_pricing_attr_tbl(1).product_attribute_context := 'ITEM';
v_pricing_attr_tbl(1).product_attribute := 'PRICING_ATTRIBUTE1';
v_pricing_attr_tbl(1).product_attr_value := '1027096';
v_pricing_attr_tbl(1).product_uom_code := 'EA';
v_pricing_attr_tbl(1).excluder_flag := 'N';
v_pricing_attr_tbl(1).attribute_grouping_no := 1;
v_pricing_attr_tbl(1).price_list_line_index := 1;
v_pricing_attr_tbl(1).operation := qp_globals.g_opr_create;
dbms_output.put_line('Calling API to Enter Item Into Price List');
qp_price_list_pub.process_price_list
(
p_api_version_number => 1
,p_init_msg_list => fnd_api.g_true
,p_return_values => fnd_api.g_false
,p_commit => fnd_api.g_false
,x_return_status => v_return_status
,x_msg_count => v_msg_count
,x_msg_data => v_msg_data
,p_price_list_rec => v_price_list_rec
,p_price_list_line_tbl=> v_price_list_line_tbl
,p_pricing_attr_tbl => v_pricing_attr_tbl
,x_price_list_rec => ppr_price_list_rec
,x_price_list_val_rec => ppr_price_list_val_rec
,x_price_list_line_tbl=> ppr_price_list_line_tbl
,x_qualifiers_tbl => ppr_qualifiers_tbl
,x_qualifiers_val_tbl => ppr_qualifiers_val_tbl
,x_pricing_attr_tbl => ppr_pricing_attr_tbl
,x_pricing_attr_val_tbl => ppr_pricing_attr_val_tbl
,x_price_list_line_val_tbl => ppr_price_list_line_val_tbl
);
IF v_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.put_line ('The Item loading into the price list is Sucessfull');
ELSE
DBMS_OUTPUT.put_line ('The Item loading into the price list Failed');
ROLLBACK;
FOR i IN 1 .. v_msg_count
LOOP
v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
dbms_output.put_line( i|| ') '|| v_msg_data);
END LOOP;
END IF;
END;
/
DECLARE
v_return_status VARCHAR2(1) := NULL;
v_msg_count NUMBER := 0;
v_msg_data VARCHAR2 (2000);
v_price_list_rec qp_price_list_pub.price_list_rec_type;
v_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
v_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
v_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
v_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
v_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
v_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
v_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
ppr_price_list_rec qp_price_list_pub.price_list_rec_type;
ppr_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
ppr_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
ppr_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
ppr_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
ppr_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
ppr_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
ppr_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
BEGIN
v_price_list_rec.list_header_id := 353500;
v_price_list_rec.list_type_code := 'PRL';
v_price_list_rec.operation := qp_globals.g_opr_update;
v_price_list_line_tbl(1).list_header_id := 353500;
v_price_list_line_tbl(1).list_line_id := fnd_api.g_miss_num;
v_price_list_line_tbl(1).list_line_type_code:= 'PLL';
v_price_list_line_tbl(1).operation := qp_globals.g_opr_create;
v_price_list_line_tbl(1).operand := 51.6;
v_price_list_line_tbl(1).arithmetic_operator:= 'UNIT_PRICE';
v_price_list_line_tbl(1).start_date_active := '01-NOV-2014';
v_price_list_line_tbl(1).end_date_active := '31-OCT-2015';
v_price_list_line_tbl(1).PRODUCT_PRECEDENCE :=220;
v_price_list_line_tbl(1).organization_id := NULL;
v_pricing_attr_tbl(1).pricing_attribute_id := fnd_api.g_miss_num;
v_pricing_attr_tbl(1).list_line_id := fnd_api.g_miss_num;
v_pricing_attr_tbl(1).product_attribute_context := 'ITEM';
v_pricing_attr_tbl(1).product_attribute := 'PRICING_ATTRIBUTE1';
v_pricing_attr_tbl(1).product_attr_value := '1027096';
v_pricing_attr_tbl(1).product_uom_code := 'EA';
v_pricing_attr_tbl(1).excluder_flag := 'N';
v_pricing_attr_tbl(1).attribute_grouping_no := 1;
v_pricing_attr_tbl(1).price_list_line_index := 1;
v_pricing_attr_tbl(1).operation := qp_globals.g_opr_create;
dbms_output.put_line('Calling API to Enter Item Into Price List');
qp_price_list_pub.process_price_list
(
p_api_version_number => 1
,p_init_msg_list => fnd_api.g_true
,p_return_values => fnd_api.g_false
,p_commit => fnd_api.g_false
,x_return_status => v_return_status
,x_msg_count => v_msg_count
,x_msg_data => v_msg_data
,p_price_list_rec => v_price_list_rec
,p_price_list_line_tbl=> v_price_list_line_tbl
,p_pricing_attr_tbl => v_pricing_attr_tbl
,x_price_list_rec => ppr_price_list_rec
,x_price_list_val_rec => ppr_price_list_val_rec
,x_price_list_line_tbl=> ppr_price_list_line_tbl
,x_qualifiers_tbl => ppr_qualifiers_tbl
,x_qualifiers_val_tbl => ppr_qualifiers_val_tbl
,x_pricing_attr_tbl => ppr_pricing_attr_tbl
,x_pricing_attr_val_tbl => ppr_pricing_attr_val_tbl
,x_price_list_line_val_tbl => ppr_price_list_line_val_tbl
);
IF v_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.put_line ('The Item loading into the price list is Sucessfull');
ELSE
DBMS_OUTPUT.put_line ('The Item loading into the price list Failed');
ROLLBACK;
FOR i IN 1 .. v_msg_count
LOOP
v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
dbms_output.put_line( i|| ') '|| v_msg_data);
END LOOP;
END IF;
END;
/
Thursday, 30 October 2014
API for Creating BOM and BOM Inventory Components
create or replace PROCEDURE GEPS_BOM_INV_COMP_PRG
(
errbuf OUT VARCHAR2
,retcode OUT NUMBER
)
as
l_bom_header_rec Bom_Bo_Pub.Bom_Head_Rec_Type := Bom_Bo_Pub.G_MISS_BOM_HEADER_REC;
l_bom_revision_tbl Bom_Bo_Pub.Bom_Revision_Tbl_Type := Bom_Bo_Pub.G_MISS_BOM_REVISION_TBL;
l_bom_component_tbl Bom_Bo_Pub.Bom_Comps_Tbl_Type := Bom_Bo_Pub.G_MISS_BOM_COMPONENT_TBL;
l_bom_ref_designator_tbl Bom_Bo_Pub.Bom_Ref_Designator_Tbl_type := Bom_Bo_Pub.G_MISS_BOM_REF_DESIGNATOR_TBL;
l_bom_sub_component_tbl Bom_Bo_Pub.Bom_Sub_Component_Tbl_Type := Bom_Bo_Pub.G_MISS_BOM_SUB_COMPONENT_TBL;
l_error_message_list Error_handler.error_tbl_type;
l_x_bom_header_rec Bom_Bo_Pub.bom_Head_Rec_Type;
l_x_bom_revision_tbl Bom_Bo_Pub.Bom_Revision_Tbl_Type;
l_x_bom_component_tbl Bom_Bo_pub.Bom_Comps_Tbl_Type;
l_x_bom_ref_designator_tbl Bom_Bo_Pub.Bom_Ref_Designator_Tbl_Type;
l_x_bom_sub_component_tbl Bom_Bo_Pub.Bom_Sub_Component_Tbl_Type;
l_x_return_status VARCHAR2(2000);
l_x_msg_count NUMBER;
l_cnt NUMBER;
l_supply_type mfg_lookups.lookup_code%type;
l_hdritem_cnt number;
l_err_message varchar2(2000);
l_err_message_type varchar2(2000);
cursor c_component is
select *
from GEPS_BOM_INV_COMPONENTS_STG
where PROCESS_FLAG ='P';
Begin
FND_FILE.PUT_LINE(FND_FILE.LOG,' Starting of GEPS_BOM_INV_COMP_PRG Procedure');
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);
for l_rec in c_component loop
FND_FILE.PUT_LINE(FND_FILE.LOG,' loop start');
l_cnt :=1;
begin
select count(*)
into l_hdritem_cnt
from
mtl_system_items_b msi,
org_organization_definitions ood,
bom_bill_of_materials bom
where bom.assembly_item_id = msi.inventory_item_id
and msi.organization_id = ood.organization_id
and bom.organization_id = ood.organization_id
and ood.organization_code = l_rec.ORGANIZATION_CODE
and msi.segment1 =l_rec.ITEM;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'l_hdritem_cnt:' || l_hdritem_cnt);
exception when others then
l_hdritem_cnt :=0;
end;
FND_FILE.PUT_LINE(FND_FILE.LOG,'l_hdritem_cnt:' || l_hdritem_cnt);
if l_hdritem_cnt < 1 then
FND_FILE.PUT_LINE(FND_FILE.LOG,'inside l_hdritem_cnt IF' );
l_bom_header_rec.Assembly_item_name := l_rec.ITEM;--'03401100';
l_bom_header_rec.Organization_code := l_rec.ORGANIZATION_CODE;--'E01';
l_bom_header_rec.Assembly_type := 1;
l_bom_header_rec.Transaction_Type := 'CREATE';--l_rec.TRANSACTION_TYPE;
l_Bom_Header_Rec.Return_Status := NULL;
else
FND_FILE.PUT_LINE(FND_FILE.LOG,'inside l_hdritem_cnt ELSIF' );
l_bom_header_rec.Assembly_item_name := null;--'03401100';
l_bom_header_rec.Organization_code := null;--'E01';
l_bom_header_rec.Assembly_type := null;
l_bom_header_rec.Transaction_Type := null;--l_rec.TRANSACTION_TYPE;
l_Bom_Header_Rec.Return_Status := NULL;
end if;
begin
select lookup_code
into l_supply_type
from mfg_lookups ml
where ml.lookup_type = 'WIP_SUPPLY'
and ml.meaning =l_rec.SUPPLY_TYPE
and ml.ENABLED_FLAG ='Y'
and nvl(ml.END_DATE_ACTIVE,sysdate) >=sysdate;
exception when others then
l_supply_type := 1;
end;
FND_FILE.PUT_LINE(FND_FILE.LOG,'l_supply_type:' ||l_supply_type );
--dbms_output.put_line(' Assembly_Item_name ' || j.SEGMENT1);
--dbms_output.put_line(' Component_Item_Name' || j.COMPONENT);
l_bom_component_tbl(l_cnt) := bom_bo_pub.g_miss_bom_component_rec;
if l_rec.TRANSACTION_TYPE like '%CREATE%' then
l_bom_component_tbl(l_cnt).transaction_type := 'CREATE';-- l_rec.TRANSACTION_TYPE;--'CREATE';
else
l_bom_component_tbl(l_cnt).transaction_type := 'UPDATE';
end if;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'sss1' );
l_bom_component_tbl(l_cnt).Organization_CODE := l_rec.ORGANIZATION_CODE;--'E01';--j.ORGANIZATION_CODE;
l_bom_component_tbl(l_cnt).Assembly_Item_name := l_rec.ITEM; --'SLIT_QWOV_OC';--j.SEGMENT1;
l_bom_component_tbl(l_cnt).Item_Sequence_Number := l_rec.ITEM_SEQ;--'790';--j.ITEM_NUM;--(i*10);
l_bom_component_tbl(l_cnt).operation_Sequence_Number := l_rec.OPERATION_SEQ;--'1';--j.OPERATION_SEQ_NUM;
l_bom_component_tbl(l_cnt).Component_Item_Name := l_rec.COMPONENT;--'01000015';--j.COMPONENT;
l_bom_component_tbl(l_cnt).Quantity_Per_Assembly := l_rec.COMPONENT_QUANTITY;--2;--v_QtyPerAssy;
--l_bom_component_tbl(l_cnt).Assembly_Item_id := '169531';
--FND_FILE.PUT_LINE(FND_FILE.LOG,'sss2' );
l_bom_component_tbl(l_cnt).Start_effective_date := nvl(to_date(l_rec.COMPONENT_FROM_DATE, 'dd-mm-rrrr hh24:mi:ss'),sysdate);--to_date(l_rec.COMPONENT_FROM_DATE, 'dd-mm-rrrr hh24:mi:ss');--to_date('04-DEC-2008 20:54:49', 'dd-MON-yyyy hh24:mi:ss');--j.EFFECTIVITY_DATE;----sysdate;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'sss2.1' );
l_bom_component_tbl(l_cnt).projected_yield := l_rec.COMPONENT_YIELD_FACTOR;--0.9620;
--l_bom_component_tbl(l_cnt).ENFORCE_INT_REQUIREMENTS := decode(l_rec.ENFORCE_INT_REQUIREMENTS,'None',0,'Up',1,'Down',2,0);--'1';
--l_bom_component_tbl(l_cnt).Include_In_Cost_Rollup := decode(l_rec.INCLUDE_IN_COST_ROLLUP,'Y',1,2);--2;
l_bom_component_tbl(l_cnt).Wip_Supply_Type := l_supply_type;--1;
l_bom_component_tbl(l_cnt).Supply_Subinventory := l_rec.SUPPLY_SUBINVENTORY;
l_bom_component_tbl(l_cnt).Location_Name := l_rec.LOCATOR;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'sss3' );
--l_bom_component_tbl(l_cnt).check_atp := decode(l_rec.CHECK_ATP,'Y',1,2);
--l_bom_component_tbl(l_cnt).Optional := decode(l_rec.OPTIONAL,'Y',1,2);
--l_bom_component_tbl(l_cnt).Mutually_Exclusive := decode(l_rec.MUTUALLY_EXCLUSIVE_OPTIONS,'Y',1,2);
l_bom_component_tbl(l_cnt).Minimum_Allowed_Quantity := l_rec.LOW_QUANTITY;
l_bom_component_tbl(l_cnt).Maximum_Allowed_Quantity := l_rec.HIGH_QUANTITY;
--l_bom_component_tbl(l_cnt).So_Basis := decode(l_rec.SO_BASIS,'Y',1,2);
--l_bom_component_tbl(l_cnt).Include_On_Ship_Docs := decode(l_rec.INCLUDE_ON_SHIP_DOCS,'Y',1,2);
--l_bom_component_tbl(l_cnt).Required_To_Ship := decode(l_rec.REQUIRED_TO_SHIP,'Y',1,2);
--l_bom_component_tbl(l_cnt).Required_For_Revenue := decode(l_rec.REQUIRED_FOR_REVENUE,'Y',1,2);
l_bom_component_tbl(l_cnt).Comments := l_rec.COMPONENT_REMARKS;
l_bom_component_tbl(l_cnt).return_status := NULL;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'sss4' );
--l_bom_component_tbl(l_cnt).Component_Item_id := '23612';
/*l_bom_component_tbl(l_cnt).Alternate_bom_code := NULL;
l_bom_component_tbl(l_cnt).projected_yield := NULL;
l_bom_component_tbl(l_cnt).planning_percent := NULL;
l_bom_component_tbl(l_cnt).quantity_related := NULL;
l_bom_component_tbl(l_cnt).check_atp := NULL;
l_bom_component_tbl(l_cnt).Include_In_Cost_Rollup := NULL;*/
/*l_bom_component_tbl(l_cnt).So_Basis := NULL;
l_bom_component_tbl(l_cnt).Optional := NULL;
l_bom_component_tbl(l_cnt).Mutually_Exclusive := NULL;
l_bom_component_tbl(l_cnt).Shipping_Allowed := NULL;
l_bom_component_tbl(l_cnt).Required_To_Ship := NULL;
l_bom_component_tbl(l_cnt).Required_For_Revenue := NULL;
l_bom_component_tbl(l_cnt).Include_On_Ship_Docs := NULL;
l_bom_component_tbl(l_cnt).Supply_Subinventory := NULL;
l_bom_component_tbl(l_cnt).Location_Name := NULL;
l_bom_component_tbl(l_cnt).Minimum_Allowed_Quantity := NULL;
l_bom_component_tbl(l_cnt).Maximum_Allowed_Quantity := NULL;
l_bom_component_tbl(l_cnt).Comments := NULL;
l_bom_component_tbl(l_cnt).from_end_item_unit_number := NULL;
l_bom_component_tbl(l_cnt).to_end_item_unit_number := NULL;*/
--l_bom_component_tbl(l_cnt).Transaction_Type := 'CREATE';
--l_cnt := l_cnt +1;
--end loop;
--dbms_output.put_line(' start5');
Error_Handler.Initialize;
-- Call the Public API
-- The public API is the user's interface to the import program. The user must call it
-- programatically, while sending in one business object at a time. The public API returns
-- the processed business object, the business object status, and a count of all
-- associated error and warning messages.
--dbms_output.put_line(' start6 ');
FND_FILE.PUT_LINE(FND_FILE.LOG,'before bom_bo_pub.Process_Bom' );
bom_bo_pub.Process_Bom
( p_bo_identifier => 'BOM'
, p_api_version_number => 1.0 -- This parameter is required. It is used by the
-- API to compare the version number of incoming
-- calls to its current version number.
, p_init_msg_list => TRUE -- This parameter is set to TRUE, allows callers to
-- to request that the API do the initialization
-- of message list on their behalf.
, p_bom_header_rec => l_bom_header_rec -- This is a set of data structures that represent
-- the incoming business objects. This is a record
-- that holds the Bill of Materials header for the
-- BOM
, p_bom_revision_tbl => l_bom_revision_tbl -- All the p*_tbl parameters are data structure
-- that represent incoming business objects They
-- are PL/SQL tables of records that hold records
-- for each of the other entities.
, p_bom_component_tbl => l_bom_component_tbl
, p_bom_ref_designator_tbl => l_bom_ref_designator_tbl
, p_bom_sub_component_tbl => l_bom_sub_component_tbl
, x_bom_header_rec => l_x_bom_header_rec -- All the x*_tbl parameters are data structure
-- that represent outgoing business objects They
-- are PL/SQL tables of records that hold records
-- for each of the other entities except now they
-- have all the changes that the import program
-- made to it through all the steps.
, x_bom_revision_tbl => l_x_bom_revision_tbl
, x_bom_component_tbl => l_x_bom_component_tbl
, x_bom_ref_designator_tbl => l_x_bom_ref_designator_tbl
, x_bom_sub_component_tbl => l_x_bom_sub_component_tbl
, x_return_status => l_x_return_status -- This is a flag that indicates the state of the
-- whole business object after the import.
-- 'S' - Success
-- 'E' - Error
-- 'F' - Fatal Error
-- 'U' - Unexpected Error
, x_msg_count => l_x_msg_count -- This holds the number of messages in the API
-- message stack after the import.
, p_debug => 'N'
, p_output_dir => ''
, p_debug_filename => ''
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'after bom_bo_pub.Process_Bom' );
FND_FILE.PUT_LINE(FND_FILE.LOG,'Return Status for '||l_rec.COMPONENT||' = '||l_x_return_status);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Message Count for '||l_rec.COMPONENT||' = '||l_x_msg_count);
/**** Error messages ****/
Error_Handler.Get_message_list(l_error_message_list);
if l_x_return_status <> 'S'
then
-- Error Processing
l_err_message := null;
l_err_message_type := null;
for k in 1..l_x_msg_count loop
if l_err_message is null then
l_err_message := SUBSTR(l_error_message_list(k).message_text,1,250);
else
l_err_message := l_err_message ||'~'||SUBSTR(l_error_message_list(k).message_text,1,250);
end if;
if l_err_message_type is null then
l_err_message_type := l_error_message_list(k).message_type;
else
l_err_message_type := l_err_message_type ||'~'|| l_error_message_list(k).message_type;
end if;
FND_FILE.PUT_LINE(FND_FILE.LOG,' MESSAGE TEXT '||SUBSTR(l_error_message_list(k).message_text,1,250));
FND_FILE.PUT_LINE(FND_FILE.LOG,' MESSAGE TYPE '||l_error_message_list(k).message_type);
end loop;
update GEPS_BOM_INV_COMPONENTS_STG
set PROCESS_FLAG ='E',
ERROR_MESSAGE =l_err_message,
ERROR_MESSAGE_TYPE = l_err_message_type
where COMPONENT = l_rec.COMPONENT
and ITEM = l_rec.ITEM
and ITEM_SEQ =l_rec.ITEM_SEQ
and OPERATION_SEQ =l_rec.OPERATION_SEQ;
commit;
else
update GEPS_BOM_INV_COMPONENTS_STG
set PROCESS_FLAG ='S'
where COMPONENT = l_rec.COMPONENT
and ITEM = l_rec.ITEM
and ITEM_SEQ =l_rec.ITEM_SEQ
and OPERATION_SEQ =l_rec.OPERATION_SEQ;
commit;
end if;
--i :=i+1;
--end if;
end loop;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,' OTHER exception ');
end GEPS_BOM_INV_COMP_PRG;
/
(
errbuf OUT VARCHAR2
,retcode OUT NUMBER
)
as
l_bom_header_rec Bom_Bo_Pub.Bom_Head_Rec_Type := Bom_Bo_Pub.G_MISS_BOM_HEADER_REC;
l_bom_revision_tbl Bom_Bo_Pub.Bom_Revision_Tbl_Type := Bom_Bo_Pub.G_MISS_BOM_REVISION_TBL;
l_bom_component_tbl Bom_Bo_Pub.Bom_Comps_Tbl_Type := Bom_Bo_Pub.G_MISS_BOM_COMPONENT_TBL;
l_bom_ref_designator_tbl Bom_Bo_Pub.Bom_Ref_Designator_Tbl_type := Bom_Bo_Pub.G_MISS_BOM_REF_DESIGNATOR_TBL;
l_bom_sub_component_tbl Bom_Bo_Pub.Bom_Sub_Component_Tbl_Type := Bom_Bo_Pub.G_MISS_BOM_SUB_COMPONENT_TBL;
l_error_message_list Error_handler.error_tbl_type;
l_x_bom_header_rec Bom_Bo_Pub.bom_Head_Rec_Type;
l_x_bom_revision_tbl Bom_Bo_Pub.Bom_Revision_Tbl_Type;
l_x_bom_component_tbl Bom_Bo_pub.Bom_Comps_Tbl_Type;
l_x_bom_ref_designator_tbl Bom_Bo_Pub.Bom_Ref_Designator_Tbl_Type;
l_x_bom_sub_component_tbl Bom_Bo_Pub.Bom_Sub_Component_Tbl_Type;
l_x_return_status VARCHAR2(2000);
l_x_msg_count NUMBER;
l_cnt NUMBER;
l_supply_type mfg_lookups.lookup_code%type;
l_hdritem_cnt number;
l_err_message varchar2(2000);
l_err_message_type varchar2(2000);
cursor c_component is
select *
from GEPS_BOM_INV_COMPONENTS_STG
where PROCESS_FLAG ='P';
Begin
FND_FILE.PUT_LINE(FND_FILE.LOG,' Starting of GEPS_BOM_INV_COMP_PRG Procedure');
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);
for l_rec in c_component loop
FND_FILE.PUT_LINE(FND_FILE.LOG,' loop start');
l_cnt :=1;
begin
select count(*)
into l_hdritem_cnt
from
mtl_system_items_b msi,
org_organization_definitions ood,
bom_bill_of_materials bom
where bom.assembly_item_id = msi.inventory_item_id
and msi.organization_id = ood.organization_id
and bom.organization_id = ood.organization_id
and ood.organization_code = l_rec.ORGANIZATION_CODE
and msi.segment1 =l_rec.ITEM;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'l_hdritem_cnt:' || l_hdritem_cnt);
exception when others then
l_hdritem_cnt :=0;
end;
FND_FILE.PUT_LINE(FND_FILE.LOG,'l_hdritem_cnt:' || l_hdritem_cnt);
if l_hdritem_cnt < 1 then
FND_FILE.PUT_LINE(FND_FILE.LOG,'inside l_hdritem_cnt IF' );
l_bom_header_rec.Assembly_item_name := l_rec.ITEM;--'03401100';
l_bom_header_rec.Organization_code := l_rec.ORGANIZATION_CODE;--'E01';
l_bom_header_rec.Assembly_type := 1;
l_bom_header_rec.Transaction_Type := 'CREATE';--l_rec.TRANSACTION_TYPE;
l_Bom_Header_Rec.Return_Status := NULL;
else
FND_FILE.PUT_LINE(FND_FILE.LOG,'inside l_hdritem_cnt ELSIF' );
l_bom_header_rec.Assembly_item_name := null;--'03401100';
l_bom_header_rec.Organization_code := null;--'E01';
l_bom_header_rec.Assembly_type := null;
l_bom_header_rec.Transaction_Type := null;--l_rec.TRANSACTION_TYPE;
l_Bom_Header_Rec.Return_Status := NULL;
end if;
begin
select lookup_code
into l_supply_type
from mfg_lookups ml
where ml.lookup_type = 'WIP_SUPPLY'
and ml.meaning =l_rec.SUPPLY_TYPE
and ml.ENABLED_FLAG ='Y'
and nvl(ml.END_DATE_ACTIVE,sysdate) >=sysdate;
exception when others then
l_supply_type := 1;
end;
FND_FILE.PUT_LINE(FND_FILE.LOG,'l_supply_type:' ||l_supply_type );
--dbms_output.put_line(' Assembly_Item_name ' || j.SEGMENT1);
--dbms_output.put_line(' Component_Item_Name' || j.COMPONENT);
l_bom_component_tbl(l_cnt) := bom_bo_pub.g_miss_bom_component_rec;
if l_rec.TRANSACTION_TYPE like '%CREATE%' then
l_bom_component_tbl(l_cnt).transaction_type := 'CREATE';-- l_rec.TRANSACTION_TYPE;--'CREATE';
else
l_bom_component_tbl(l_cnt).transaction_type := 'UPDATE';
end if;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'sss1' );
l_bom_component_tbl(l_cnt).Organization_CODE := l_rec.ORGANIZATION_CODE;--'E01';--j.ORGANIZATION_CODE;
l_bom_component_tbl(l_cnt).Assembly_Item_name := l_rec.ITEM; --'SLIT_QWOV_OC';--j.SEGMENT1;
l_bom_component_tbl(l_cnt).Item_Sequence_Number := l_rec.ITEM_SEQ;--'790';--j.ITEM_NUM;--(i*10);
l_bom_component_tbl(l_cnt).operation_Sequence_Number := l_rec.OPERATION_SEQ;--'1';--j.OPERATION_SEQ_NUM;
l_bom_component_tbl(l_cnt).Component_Item_Name := l_rec.COMPONENT;--'01000015';--j.COMPONENT;
l_bom_component_tbl(l_cnt).Quantity_Per_Assembly := l_rec.COMPONENT_QUANTITY;--2;--v_QtyPerAssy;
--l_bom_component_tbl(l_cnt).Assembly_Item_id := '169531';
--FND_FILE.PUT_LINE(FND_FILE.LOG,'sss2' );
l_bom_component_tbl(l_cnt).Start_effective_date := nvl(to_date(l_rec.COMPONENT_FROM_DATE, 'dd-mm-rrrr hh24:mi:ss'),sysdate);--to_date(l_rec.COMPONENT_FROM_DATE, 'dd-mm-rrrr hh24:mi:ss');--to_date('04-DEC-2008 20:54:49', 'dd-MON-yyyy hh24:mi:ss');--j.EFFECTIVITY_DATE;----sysdate;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'sss2.1' );
l_bom_component_tbl(l_cnt).projected_yield := l_rec.COMPONENT_YIELD_FACTOR;--0.9620;
--l_bom_component_tbl(l_cnt).ENFORCE_INT_REQUIREMENTS := decode(l_rec.ENFORCE_INT_REQUIREMENTS,'None',0,'Up',1,'Down',2,0);--'1';
--l_bom_component_tbl(l_cnt).Include_In_Cost_Rollup := decode(l_rec.INCLUDE_IN_COST_ROLLUP,'Y',1,2);--2;
l_bom_component_tbl(l_cnt).Wip_Supply_Type := l_supply_type;--1;
l_bom_component_tbl(l_cnt).Supply_Subinventory := l_rec.SUPPLY_SUBINVENTORY;
l_bom_component_tbl(l_cnt).Location_Name := l_rec.LOCATOR;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'sss3' );
--l_bom_component_tbl(l_cnt).check_atp := decode(l_rec.CHECK_ATP,'Y',1,2);
--l_bom_component_tbl(l_cnt).Optional := decode(l_rec.OPTIONAL,'Y',1,2);
--l_bom_component_tbl(l_cnt).Mutually_Exclusive := decode(l_rec.MUTUALLY_EXCLUSIVE_OPTIONS,'Y',1,2);
l_bom_component_tbl(l_cnt).Minimum_Allowed_Quantity := l_rec.LOW_QUANTITY;
l_bom_component_tbl(l_cnt).Maximum_Allowed_Quantity := l_rec.HIGH_QUANTITY;
--l_bom_component_tbl(l_cnt).So_Basis := decode(l_rec.SO_BASIS,'Y',1,2);
--l_bom_component_tbl(l_cnt).Include_On_Ship_Docs := decode(l_rec.INCLUDE_ON_SHIP_DOCS,'Y',1,2);
--l_bom_component_tbl(l_cnt).Required_To_Ship := decode(l_rec.REQUIRED_TO_SHIP,'Y',1,2);
--l_bom_component_tbl(l_cnt).Required_For_Revenue := decode(l_rec.REQUIRED_FOR_REVENUE,'Y',1,2);
l_bom_component_tbl(l_cnt).Comments := l_rec.COMPONENT_REMARKS;
l_bom_component_tbl(l_cnt).return_status := NULL;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'sss4' );
--l_bom_component_tbl(l_cnt).Component_Item_id := '23612';
/*l_bom_component_tbl(l_cnt).Alternate_bom_code := NULL;
l_bom_component_tbl(l_cnt).projected_yield := NULL;
l_bom_component_tbl(l_cnt).planning_percent := NULL;
l_bom_component_tbl(l_cnt).quantity_related := NULL;
l_bom_component_tbl(l_cnt).check_atp := NULL;
l_bom_component_tbl(l_cnt).Include_In_Cost_Rollup := NULL;*/
/*l_bom_component_tbl(l_cnt).So_Basis := NULL;
l_bom_component_tbl(l_cnt).Optional := NULL;
l_bom_component_tbl(l_cnt).Mutually_Exclusive := NULL;
l_bom_component_tbl(l_cnt).Shipping_Allowed := NULL;
l_bom_component_tbl(l_cnt).Required_To_Ship := NULL;
l_bom_component_tbl(l_cnt).Required_For_Revenue := NULL;
l_bom_component_tbl(l_cnt).Include_On_Ship_Docs := NULL;
l_bom_component_tbl(l_cnt).Supply_Subinventory := NULL;
l_bom_component_tbl(l_cnt).Location_Name := NULL;
l_bom_component_tbl(l_cnt).Minimum_Allowed_Quantity := NULL;
l_bom_component_tbl(l_cnt).Maximum_Allowed_Quantity := NULL;
l_bom_component_tbl(l_cnt).Comments := NULL;
l_bom_component_tbl(l_cnt).from_end_item_unit_number := NULL;
l_bom_component_tbl(l_cnt).to_end_item_unit_number := NULL;*/
--l_bom_component_tbl(l_cnt).Transaction_Type := 'CREATE';
--l_cnt := l_cnt +1;
--end loop;
--dbms_output.put_line(' start5');
Error_Handler.Initialize;
-- Call the Public API
-- The public API is the user's interface to the import program. The user must call it
-- programatically, while sending in one business object at a time. The public API returns
-- the processed business object, the business object status, and a count of all
-- associated error and warning messages.
--dbms_output.put_line(' start6 ');
FND_FILE.PUT_LINE(FND_FILE.LOG,'before bom_bo_pub.Process_Bom' );
bom_bo_pub.Process_Bom
( p_bo_identifier => 'BOM'
, p_api_version_number => 1.0 -- This parameter is required. It is used by the
-- API to compare the version number of incoming
-- calls to its current version number.
, p_init_msg_list => TRUE -- This parameter is set to TRUE, allows callers to
-- to request that the API do the initialization
-- of message list on their behalf.
, p_bom_header_rec => l_bom_header_rec -- This is a set of data structures that represent
-- the incoming business objects. This is a record
-- that holds the Bill of Materials header for the
-- BOM
, p_bom_revision_tbl => l_bom_revision_tbl -- All the p*_tbl parameters are data structure
-- that represent incoming business objects They
-- are PL/SQL tables of records that hold records
-- for each of the other entities.
, p_bom_component_tbl => l_bom_component_tbl
, p_bom_ref_designator_tbl => l_bom_ref_designator_tbl
, p_bom_sub_component_tbl => l_bom_sub_component_tbl
, x_bom_header_rec => l_x_bom_header_rec -- All the x*_tbl parameters are data structure
-- that represent outgoing business objects They
-- are PL/SQL tables of records that hold records
-- for each of the other entities except now they
-- have all the changes that the import program
-- made to it through all the steps.
, x_bom_revision_tbl => l_x_bom_revision_tbl
, x_bom_component_tbl => l_x_bom_component_tbl
, x_bom_ref_designator_tbl => l_x_bom_ref_designator_tbl
, x_bom_sub_component_tbl => l_x_bom_sub_component_tbl
, x_return_status => l_x_return_status -- This is a flag that indicates the state of the
-- whole business object after the import.
-- 'S' - Success
-- 'E' - Error
-- 'F' - Fatal Error
-- 'U' - Unexpected Error
, x_msg_count => l_x_msg_count -- This holds the number of messages in the API
-- message stack after the import.
, p_debug => 'N'
, p_output_dir => ''
, p_debug_filename => ''
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'after bom_bo_pub.Process_Bom' );
FND_FILE.PUT_LINE(FND_FILE.LOG,'Return Status for '||l_rec.COMPONENT||' = '||l_x_return_status);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Message Count for '||l_rec.COMPONENT||' = '||l_x_msg_count);
/**** Error messages ****/
Error_Handler.Get_message_list(l_error_message_list);
if l_x_return_status <> 'S'
then
-- Error Processing
l_err_message := null;
l_err_message_type := null;
for k in 1..l_x_msg_count loop
if l_err_message is null then
l_err_message := SUBSTR(l_error_message_list(k).message_text,1,250);
else
l_err_message := l_err_message ||'~'||SUBSTR(l_error_message_list(k).message_text,1,250);
end if;
if l_err_message_type is null then
l_err_message_type := l_error_message_list(k).message_type;
else
l_err_message_type := l_err_message_type ||'~'|| l_error_message_list(k).message_type;
end if;
FND_FILE.PUT_LINE(FND_FILE.LOG,' MESSAGE TEXT '||SUBSTR(l_error_message_list(k).message_text,1,250));
FND_FILE.PUT_LINE(FND_FILE.LOG,' MESSAGE TYPE '||l_error_message_list(k).message_type);
end loop;
update GEPS_BOM_INV_COMPONENTS_STG
set PROCESS_FLAG ='E',
ERROR_MESSAGE =l_err_message,
ERROR_MESSAGE_TYPE = l_err_message_type
where COMPONENT = l_rec.COMPONENT
and ITEM = l_rec.ITEM
and ITEM_SEQ =l_rec.ITEM_SEQ
and OPERATION_SEQ =l_rec.OPERATION_SEQ;
commit;
else
update GEPS_BOM_INV_COMPONENTS_STG
set PROCESS_FLAG ='S'
where COMPONENT = l_rec.COMPONENT
and ITEM = l_rec.ITEM
and ITEM_SEQ =l_rec.ITEM_SEQ
and OPERATION_SEQ =l_rec.OPERATION_SEQ;
commit;
end if;
--i :=i+1;
--end if;
end loop;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,' OTHER exception ');
end GEPS_BOM_INV_COMP_PRG;
/
API for updating bom_inventory_components
Declare
--script for sweep the supply tupe from bulk to Assembly Pull
l_bom_header_rec Bom_Bo_Pub.Bom_Head_Rec_Type := Bom_Bo_Pub.G_MISS_BOM_HEADER_REC;
l_bom_revision_tbl Bom_Bo_Pub.Bom_Revision_Tbl_Type := Bom_Bo_Pub.G_MISS_BOM_REVISION_TBL;
l_bom_component_tbl Bom_Bo_Pub.Bom_Comps_Tbl_Type := Bom_Bo_Pub.G_MISS_BOM_COMPONENT_TBL;
l_bom_ref_designator_tbl Bom_Bo_Pub.Bom_Ref_Designator_Tbl_type := Bom_Bo_Pub.G_MISS_BOM_REF_DESIGNATOR_TBL;
l_bom_sub_component_tbl Bom_Bo_Pub.Bom_Sub_Component_Tbl_Type := Bom_Bo_Pub.G_MISS_BOM_SUB_COMPONENT_TBL;
l_error_message_list Error_handler.error_tbl_type;
l_x_bom_header_rec Bom_Bo_Pub.bom_Head_Rec_Type;
l_x_bom_revision_tbl Bom_Bo_Pub.Bom_Revision_Tbl_Type;
l_x_bom_component_tbl Bom_Bo_pub.Bom_Comps_Tbl_Type;
l_x_bom_ref_designator_tbl Bom_Bo_Pub.Bom_Ref_Designator_Tbl_Type;
l_x_bom_sub_component_tbl Bom_Bo_Pub.Bom_Sub_Component_Tbl_Type;
l_x_return_status VARCHAR2(2000);
l_x_msg_count NUMBER;
i NUMBER;
cursor c1 is
select ood.organization_code,
ood.organization_id,
--bom.assembly_item_id,
msi.segment1,
msi.description ,
bic.item_num,
bic.operation_seq_num,
bic.component_item_id,
msi1.segment1 COMPONENT,
ml.meaning,
bic.wip_supply_type,
bic.effectivity_date,
bic.disable_date
from
mtl_system_items_b msi,
org_organization_definitions ood,
bom_bill_of_materials bom,
bom_inventory_components bic,
mtl_system_items_b msi1,
mfg_lookups ml
where bom.assembly_item_id = msi.inventory_item_id
and bom.bill_sequence_id = bic.bill_sequence_id
and msi.organization_id = ood.organization_id
and bom.organization_id = ood.organization_id
and msi1.inventory_item_status_code ='Active'
and bic.component_item_id = msi1.inventory_item_id
and msi1.organization_id = ood.organization_id
and ml.lookup_code(+) = bic.wip_supply_type
and ml.lookup_type(+) = 'WIP_SUPPLY'
and ood.organization_id = 143
and msi.segment1 in ('02995502')
and msi1.segment1 in ('0RM00538');
Begin
FND_GLOBAL.apps_initialize (1001255, 50326, 700, 0);
for j in c1 loop
i :=1;
dbms_output.put_line(' Assembly_Item_name ' || j.SEGMENT1);
dbms_output.put_line(' Component_Item_Name' || j.COMPONENT);
l_bom_component_tbl(i) := bom_bo_pub.g_miss_bom_component_rec;
l_bom_component_tbl(i).transaction_type := 'UPDATE';
l_bom_component_tbl(i).Organization_CODE := j.ORGANIZATION_CODE;
l_bom_component_tbl(i).Assembly_Item_name := j.SEGMENT1;
--l_bom_component_tbl(i).Assembly_Item_id := '169531';
l_bom_component_tbl(i).Start_effective_date := j.EFFECTIVITY_DATE;--to_date('04-DEC-2008 20:54:49', 'dd-MON-yyyy hh24:mi:ss');--sysdate;
l_bom_component_tbl(i).Component_Item_Name := j.COMPONENT;
--l_bom_component_tbl(i).Component_Item_id := '23612';
/*l_bom_component_tbl(i).Alternate_bom_code := NULL;
l_bom_component_tbl(i).projected_yield := NULL;
l_bom_component_tbl(i).planning_percent := NULL;
l_bom_component_tbl(i).quantity_related := NULL;
l_bom_component_tbl(i).check_atp := NULL;
l_bom_component_tbl(i).Include_In_Cost_Rollup := NULL;*/
l_bom_component_tbl(i).Wip_Supply_Type := 2;
/*l_bom_component_tbl(i).So_Basis := NULL;
l_bom_component_tbl(i).Optional := NULL;
l_bom_component_tbl(i).Mutually_Exclusive := NULL;
l_bom_component_tbl(i).Shipping_Allowed := NULL;
l_bom_component_tbl(i).Required_To_Ship := NULL;
l_bom_component_tbl(i).Required_For_Revenue := NULL;
l_bom_component_tbl(i).Include_On_Ship_Docs := NULL;
l_bom_component_tbl(i).Supply_Subinventory := NULL;
l_bom_component_tbl(i).Location_Name := NULL;
l_bom_component_tbl(i).Minimum_Allowed_Quantity := NULL;
l_bom_component_tbl(i).Maximum_Allowed_Quantity := NULL;
l_bom_component_tbl(i).Comments := NULL;
l_bom_component_tbl(i).from_end_item_unit_number := NULL;
l_bom_component_tbl(i).to_end_item_unit_number := NULL;*/
l_bom_component_tbl(i).Item_Sequence_Number := j.ITEM_NUM;--(i*10);
l_bom_component_tbl(i).operation_Sequence_Number := j.OPERATION_SEQ_NUM;
--l_bom_component_tbl(i).Transaction_Type := 'CREATE';
--l_bom_component_tbl(i).Quantity_Per_Assembly := 1;--v_QtyPerAssy;
l_bom_component_tbl(i).return_status := NULL;
--end loop;
--dbms_output.put_line(' start5');
Error_Handler.Initialize;
-- Call the Public API
-- The public API is the user's interface to the import program. The user must call it
-- programatically, while sending in one business object at a time. The public API returns
-- the processed business object, the business object status, and a count of all
-- associated error and warning messages.
--dbms_output.put_line(' start6 ');
bom_bo_pub.Process_Bom
( p_bo_identifier => 'BOM'
, p_api_version_number => 1.0 -- This parameter is required. It is used by the
-- API to compare the version number of incoming
-- calls to its current version number.
, p_init_msg_list => TRUE -- This parameter is set to TRUE, allows callers to
-- to request that the API do the initialization
-- of message list on their behalf.
, p_bom_header_rec => l_bom_header_rec -- This is a set of data structures that represent
-- the incoming business objects. This is a record
-- that holds the Bill of Materials header for the
-- BOM
, p_bom_revision_tbl => l_bom_revision_tbl -- All the p*_tbl parameters are data structure
-- that represent incoming business objects They
-- are PL/SQL tables of records that hold records
-- for each of the other entities.
, p_bom_component_tbl => l_bom_component_tbl
, p_bom_ref_designator_tbl => l_bom_ref_designator_tbl
, p_bom_sub_component_tbl => l_bom_sub_component_tbl
, x_bom_header_rec => l_x_bom_header_rec -- All the x*_tbl parameters are data structure
-- that represent outgoing business objects They
-- are PL/SQL tables of records that hold records
-- for each of the other entities except now they
-- have all the changes that the import program
-- made to it through all the steps.
, x_bom_revision_tbl => l_x_bom_revision_tbl
, x_bom_component_tbl => l_x_bom_component_tbl
, x_bom_ref_designator_tbl => l_x_bom_ref_designator_tbl
, x_bom_sub_component_tbl => l_x_bom_sub_component_tbl
, x_return_status => l_x_return_status -- This is a flag that indicates the state of the
-- whole business object after the import.
-- 'S' - Success
-- 'E' - Error
-- 'F' - Fatal Error
-- 'U' - Unexpected Error
, x_msg_count => l_x_msg_count -- This holds the number of messages in the API
-- message stack after the import.
, p_debug => 'N'
, p_output_dir => ''
, p_debug_filename => ''
);
--dbms_output.put_line(' start7 ');
dbms_output.put_line('Return Status = '||l_x_return_status);
dbms_output.put_line('Message Count = '||l_x_msg_count);
/**** Error messages ****/
Error_Handler.Get_message_list(l_error_message_list);
if l_x_return_status <> 'S'
then
-- Error Processing
for k in 1..l_x_msg_count loop
dbms_output.put_line(TO_CHAR(k)||' MESSAGE TEXT '||SUBSTR(l_error_message_list(k).message_text,1,250));
dbms_output.put_line(TO_CHAR(k)||' MESSAGE TYPE '||l_error_message_list(k).message_type);
end loop;
-- The business object APIs do not issue commits or rollbacks. It is the responsibility of
-- the calling code to issue them. This ensures that parts of the transactions are not left
-- in the database. If an error occurs, the whole transaction is rolled back.
rollback;
else
commit;
end if;
--i :=i+1;
--end if;
end loop;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(' OTHER exception ');
end;
/
--script for sweep the supply tupe from bulk to Assembly Pull
l_bom_header_rec Bom_Bo_Pub.Bom_Head_Rec_Type := Bom_Bo_Pub.G_MISS_BOM_HEADER_REC;
l_bom_revision_tbl Bom_Bo_Pub.Bom_Revision_Tbl_Type := Bom_Bo_Pub.G_MISS_BOM_REVISION_TBL;
l_bom_component_tbl Bom_Bo_Pub.Bom_Comps_Tbl_Type := Bom_Bo_Pub.G_MISS_BOM_COMPONENT_TBL;
l_bom_ref_designator_tbl Bom_Bo_Pub.Bom_Ref_Designator_Tbl_type := Bom_Bo_Pub.G_MISS_BOM_REF_DESIGNATOR_TBL;
l_bom_sub_component_tbl Bom_Bo_Pub.Bom_Sub_Component_Tbl_Type := Bom_Bo_Pub.G_MISS_BOM_SUB_COMPONENT_TBL;
l_error_message_list Error_handler.error_tbl_type;
l_x_bom_header_rec Bom_Bo_Pub.bom_Head_Rec_Type;
l_x_bom_revision_tbl Bom_Bo_Pub.Bom_Revision_Tbl_Type;
l_x_bom_component_tbl Bom_Bo_pub.Bom_Comps_Tbl_Type;
l_x_bom_ref_designator_tbl Bom_Bo_Pub.Bom_Ref_Designator_Tbl_Type;
l_x_bom_sub_component_tbl Bom_Bo_Pub.Bom_Sub_Component_Tbl_Type;
l_x_return_status VARCHAR2(2000);
l_x_msg_count NUMBER;
i NUMBER;
cursor c1 is
select ood.organization_code,
ood.organization_id,
--bom.assembly_item_id,
msi.segment1,
msi.description ,
bic.item_num,
bic.operation_seq_num,
bic.component_item_id,
msi1.segment1 COMPONENT,
ml.meaning,
bic.wip_supply_type,
bic.effectivity_date,
bic.disable_date
from
mtl_system_items_b msi,
org_organization_definitions ood,
bom_bill_of_materials bom,
bom_inventory_components bic,
mtl_system_items_b msi1,
mfg_lookups ml
where bom.assembly_item_id = msi.inventory_item_id
and bom.bill_sequence_id = bic.bill_sequence_id
and msi.organization_id = ood.organization_id
and bom.organization_id = ood.organization_id
and msi1.inventory_item_status_code ='Active'
and bic.component_item_id = msi1.inventory_item_id
and msi1.organization_id = ood.organization_id
and ml.lookup_code(+) = bic.wip_supply_type
and ml.lookup_type(+) = 'WIP_SUPPLY'
and ood.organization_id = 143
and msi.segment1 in ('02995502')
and msi1.segment1 in ('0RM00538');
Begin
FND_GLOBAL.apps_initialize (1001255, 50326, 700, 0);
for j in c1 loop
i :=1;
dbms_output.put_line(' Assembly_Item_name ' || j.SEGMENT1);
dbms_output.put_line(' Component_Item_Name' || j.COMPONENT);
l_bom_component_tbl(i) := bom_bo_pub.g_miss_bom_component_rec;
l_bom_component_tbl(i).transaction_type := 'UPDATE';
l_bom_component_tbl(i).Organization_CODE := j.ORGANIZATION_CODE;
l_bom_component_tbl(i).Assembly_Item_name := j.SEGMENT1;
--l_bom_component_tbl(i).Assembly_Item_id := '169531';
l_bom_component_tbl(i).Start_effective_date := j.EFFECTIVITY_DATE;--to_date('04-DEC-2008 20:54:49', 'dd-MON-yyyy hh24:mi:ss');--sysdate;
l_bom_component_tbl(i).Component_Item_Name := j.COMPONENT;
--l_bom_component_tbl(i).Component_Item_id := '23612';
/*l_bom_component_tbl(i).Alternate_bom_code := NULL;
l_bom_component_tbl(i).projected_yield := NULL;
l_bom_component_tbl(i).planning_percent := NULL;
l_bom_component_tbl(i).quantity_related := NULL;
l_bom_component_tbl(i).check_atp := NULL;
l_bom_component_tbl(i).Include_In_Cost_Rollup := NULL;*/
l_bom_component_tbl(i).Wip_Supply_Type := 2;
/*l_bom_component_tbl(i).So_Basis := NULL;
l_bom_component_tbl(i).Optional := NULL;
l_bom_component_tbl(i).Mutually_Exclusive := NULL;
l_bom_component_tbl(i).Shipping_Allowed := NULL;
l_bom_component_tbl(i).Required_To_Ship := NULL;
l_bom_component_tbl(i).Required_For_Revenue := NULL;
l_bom_component_tbl(i).Include_On_Ship_Docs := NULL;
l_bom_component_tbl(i).Supply_Subinventory := NULL;
l_bom_component_tbl(i).Location_Name := NULL;
l_bom_component_tbl(i).Minimum_Allowed_Quantity := NULL;
l_bom_component_tbl(i).Maximum_Allowed_Quantity := NULL;
l_bom_component_tbl(i).Comments := NULL;
l_bom_component_tbl(i).from_end_item_unit_number := NULL;
l_bom_component_tbl(i).to_end_item_unit_number := NULL;*/
l_bom_component_tbl(i).Item_Sequence_Number := j.ITEM_NUM;--(i*10);
l_bom_component_tbl(i).operation_Sequence_Number := j.OPERATION_SEQ_NUM;
--l_bom_component_tbl(i).Transaction_Type := 'CREATE';
--l_bom_component_tbl(i).Quantity_Per_Assembly := 1;--v_QtyPerAssy;
l_bom_component_tbl(i).return_status := NULL;
--end loop;
--dbms_output.put_line(' start5');
Error_Handler.Initialize;
-- Call the Public API
-- The public API is the user's interface to the import program. The user must call it
-- programatically, while sending in one business object at a time. The public API returns
-- the processed business object, the business object status, and a count of all
-- associated error and warning messages.
--dbms_output.put_line(' start6 ');
bom_bo_pub.Process_Bom
( p_bo_identifier => 'BOM'
, p_api_version_number => 1.0 -- This parameter is required. It is used by the
-- API to compare the version number of incoming
-- calls to its current version number.
, p_init_msg_list => TRUE -- This parameter is set to TRUE, allows callers to
-- to request that the API do the initialization
-- of message list on their behalf.
, p_bom_header_rec => l_bom_header_rec -- This is a set of data structures that represent
-- the incoming business objects. This is a record
-- that holds the Bill of Materials header for the
-- BOM
, p_bom_revision_tbl => l_bom_revision_tbl -- All the p*_tbl parameters are data structure
-- that represent incoming business objects They
-- are PL/SQL tables of records that hold records
-- for each of the other entities.
, p_bom_component_tbl => l_bom_component_tbl
, p_bom_ref_designator_tbl => l_bom_ref_designator_tbl
, p_bom_sub_component_tbl => l_bom_sub_component_tbl
, x_bom_header_rec => l_x_bom_header_rec -- All the x*_tbl parameters are data structure
-- that represent outgoing business objects They
-- are PL/SQL tables of records that hold records
-- for each of the other entities except now they
-- have all the changes that the import program
-- made to it through all the steps.
, x_bom_revision_tbl => l_x_bom_revision_tbl
, x_bom_component_tbl => l_x_bom_component_tbl
, x_bom_ref_designator_tbl => l_x_bom_ref_designator_tbl
, x_bom_sub_component_tbl => l_x_bom_sub_component_tbl
, x_return_status => l_x_return_status -- This is a flag that indicates the state of the
-- whole business object after the import.
-- 'S' - Success
-- 'E' - Error
-- 'F' - Fatal Error
-- 'U' - Unexpected Error
, x_msg_count => l_x_msg_count -- This holds the number of messages in the API
-- message stack after the import.
, p_debug => 'N'
, p_output_dir => ''
, p_debug_filename => ''
);
--dbms_output.put_line(' start7 ');
dbms_output.put_line('Return Status = '||l_x_return_status);
dbms_output.put_line('Message Count = '||l_x_msg_count);
/**** Error messages ****/
Error_Handler.Get_message_list(l_error_message_list);
if l_x_return_status <> 'S'
then
-- Error Processing
for k in 1..l_x_msg_count loop
dbms_output.put_line(TO_CHAR(k)||' MESSAGE TEXT '||SUBSTR(l_error_message_list(k).message_text,1,250));
dbms_output.put_line(TO_CHAR(k)||' MESSAGE TYPE '||l_error_message_list(k).message_type);
end loop;
-- The business object APIs do not issue commits or rollbacks. It is the responsibility of
-- the calling code to issue them. This ensures that parts of the transactions are not left
-- in the database. If an error occurs, the whole transaction is rolled back.
rollback;
else
commit;
end if;
--i :=i+1;
--end if;
end loop;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(' OTHER exception ');
end;
/
link between bom_bill_of_materials and bom_inventory_components
select distinct ood.organization_code,
--ood.organization_id,
--bom.assembly_item_id,
msi.segment1,
msi.description ,
bic.item_num,
bic.operation_seq_num,
--bic.component_item_id,
msi1.segment1 COMPONENT,
ml.meaning,
--bic.wip_supply_type,
bic.effectivity_date,
bic.disable_date
--bic.*
from
mtl_system_items_b msi,
org_organization_definitions ood,
bom_bill_of_materials bom,
bom_inventory_components bic,
mtl_system_items_b msi1,
mfg_lookups ml
where bom.assembly_item_id = msi.inventory_item_id
and bom.bill_sequence_id = bic.bill_sequence_id
and msi.organization_id = ood.organization_id
and bom.organization_id = ood.organization_id
and msi1.inventory_item_status_code ='Active'
and bic.component_item_id = msi1.inventory_item_id
and msi1.organization_id = ood.organization_id
and ml.lookup_code(+) = bic.wip_supply_type
and bic.wip_supply_type =4
--and nvl(bic.disable_date,sysdate) >= sysdate
and ml.lookup_type(+) = 'WIP_SUPPLY'
and ood.organization_id = 143
--and msi.segment1 in ('08140070')
and msi1.segment1 in ('01000013');
--ood.organization_id,
--bom.assembly_item_id,
msi.segment1,
msi.description ,
bic.item_num,
bic.operation_seq_num,
--bic.component_item_id,
msi1.segment1 COMPONENT,
ml.meaning,
--bic.wip_supply_type,
bic.effectivity_date,
bic.disable_date
--bic.*
from
mtl_system_items_b msi,
org_organization_definitions ood,
bom_bill_of_materials bom,
bom_inventory_components bic,
mtl_system_items_b msi1,
mfg_lookups ml
where bom.assembly_item_id = msi.inventory_item_id
and bom.bill_sequence_id = bic.bill_sequence_id
and msi.organization_id = ood.organization_id
and bom.organization_id = ood.organization_id
and msi1.inventory_item_status_code ='Active'
and bic.component_item_id = msi1.inventory_item_id
and msi1.organization_id = ood.organization_id
and ml.lookup_code(+) = bic.wip_supply_type
and bic.wip_supply_type =4
--and nvl(bic.disable_date,sysdate) >= sysdate
and ml.lookup_type(+) = 'WIP_SUPPLY'
and ood.organization_id = 143
--and msi.segment1 in ('08140070')
and msi1.segment1 in ('01000013');
Query to findout list of components for manufactured Bill of material
select distinct ood.organization_code,
--ood.organization_id,
--bom.assembly_item_id,
msi.segment1,
msi.description ,
bic.item_num,
bic.operation_seq_num,
--bic.component_item_id,
msi1.segment1 COMPONENT,
ml.meaning,
--bic.wip_supply_type,
bic.effectivity_date,
bic.disable_date
--bic.*
from
mtl_system_items_b msi,
org_organization_definitions ood,
bom_bill_of_materials bom,
bom_inventory_components bic,
mtl_system_items_b msi1,
mfg_lookups ml
where bom.assembly_item_id = msi.inventory_item_id
and bom.bill_sequence_id = bic.bill_sequence_id
and msi.organization_id = ood.organization_id
and bom.organization_id = ood.organization_id
and msi1.inventory_item_status_code ='Active'
and bic.component_item_id = msi1.inventory_item_id
and msi1.organization_id = ood.organization_id
and ml.lookup_code(+) = bic.wip_supply_type
and bic.wip_supply_type =4
--and nvl(bic.disable_date,sysdate) >= sysdate
and ml.lookup_type(+) = 'WIP_SUPPLY'
and ood.organization_id = 143
and msi.segment1 in ('08140070');
--and msi1.segment1 in ('01000013');
--ood.organization_id,
--bom.assembly_item_id,
msi.segment1,
msi.description ,
bic.item_num,
bic.operation_seq_num,
--bic.component_item_id,
msi1.segment1 COMPONENT,
ml.meaning,
--bic.wip_supply_type,
bic.effectivity_date,
bic.disable_date
--bic.*
from
mtl_system_items_b msi,
org_organization_definitions ood,
bom_bill_of_materials bom,
bom_inventory_components bic,
mtl_system_items_b msi1,
mfg_lookups ml
where bom.assembly_item_id = msi.inventory_item_id
and bom.bill_sequence_id = bic.bill_sequence_id
and msi.organization_id = ood.organization_id
and bom.organization_id = ood.organization_id
and msi1.inventory_item_status_code ='Active'
and bic.component_item_id = msi1.inventory_item_id
and msi1.organization_id = ood.organization_id
and ml.lookup_code(+) = bic.wip_supply_type
and bic.wip_supply_type =4
--and nvl(bic.disable_date,sysdate) >= sysdate
and ml.lookup_type(+) = 'WIP_SUPPLY'
and ood.organization_id = 143
and msi.segment1 in ('08140070');
--and msi1.segment1 in ('01000013');
Subscribe to:
Posts (Atom)