[Home] [Help]
72: | Fixed the following issues in LBM and effecitivity
73: | part of code
74: |
75: | 1.) LBM code does not handle null value for basis type
76: | Added nvl clause for all insert stmt from bom_inventory_components
77: | to bom_inventory_components_interface
78: |
79: | 2.) for overlapping effectivity dates with components having
80: | having different basis type the message is not raised
73: | part of code
74: |
75: | 1.) LBM code does not handle null value for basis type
76: | Added nvl clause for all insert stmt from bom_inventory_components
77: | to bom_inventory_components_interface
78: |
79: | 2.) for overlapping effectivity dates with components having
80: | having different basis type the message is not raised
81: | properly. fixd that code
85: |
86: || Modified by Renga Kannan on 09/07/2005
87: | Bug Fix 4595162
88: | Modified the code that populates basis type to
89: | bom_inventory_components table. As per bom team
90: | basis_type should have null for 'ITEM' and 2 for 'LOT'
91: |
92: | Modified by Renga Kannan on 09/26/2006
93: | Bug Fix for 4628806
1072: -- bugfix 3985173
1073: -- new cursor for component sequence
1074: cursor club_comp_seq ( xComponentItemId number, xOperation_seq_num number ) is
1075: select bic.component_sequence_id comp_seq_id
1076: from bom_inventory_components bic,
1077: bom_bill_of_materials bom
1078: where bom.assembly_item_id = pConfigId
1079: and bom.organization_id = pOrgId
1080: and bic.bill_sequence_id = bom.bill_sequence_id
1136: WriteToLog('Entering Update_Bom_Rtg_Loop', 2);
1137:
1138: xBillId := 0;
1139: lStmtNum := 10;
1140: select bom_inventory_components_s.nextval
1141: into lConfigBillId
1142: from dual;
1143:
1144: WriteToLog('Creating Bill:: ' || lConfigBillId, 2);
1340: NULL, -- low_quantity */
1341: NULL, -- high_quantity */
1342: NULL, -- acd_type */
1343: NULL, --old_component_sequence_id */
1344: bom_inventory_components_s.nextval, -- component sequence id */
1345: lConfigBillId, -- bill sequence id */
1346: NULL, -- request_id */
1347: NULL, -- program_application_id */
1348: NULL, -- program_id */
1361: bcol3.inventory_item_id /* Bug Fix: 4147224 */
1362: , nvl(ic1.basis_type,1), /* LBM project */
1363: cto_msutil_pub.bom_batch_id
1364: from
1365: bom_inventory_components ic1,
1366: bom_cto_order_lines_upg bcol1, -- Option
1367: bom_cto_order_lines_upg bcol2, -- Parent-Model
1368: bom_cto_order_lines_upg bcol3, -- Parent-component
1369: mtl_system_items msi_child , /* 02-14-2005 Sushant */ -- begin bugfix 1653881
1375: and alternate_bom_designator is null
1376: and assembly_item_id =(
1377: select distinct assembly_item_id
1378: from bom_bill_of_materials bbm1,
1379: bom_inventory_components bic1
1380: where bbm1.common_bill_sequence_id = bic1.bill_sequence_id
1381: and component_sequence_id = bcol1.component_sequence_id
1382: and bbm1.assembly_item_id = bcol3.inventory_item_id ))
1383: and ic1.component_item_id = bcol1.inventory_item_id
1586: NULL, -- low_quantity
1587: NULL, -- high_quantity
1588: NULL, -- acd_type
1589: NULL, -- old_component_sequence_id
1590: bom_inventory_components_s.nextval, -- component sequence id
1591: lConfigBillId, -- bill sequence id
1592: NULL, -- request_id
1593: NULL, -- program_application_id
1594: NULL, -- program_id
1609: bom_cto_order_lines_upg bcol2, -- Model
1610: mtl_system_items si1,
1611: mtl_system_items si2,
1612: bom_bill_of_materials b,
1613: bom_inventory_components ic1
1614: where si1.organization_id = pOrgId
1615: and bcol1.inventory_item_id = si1.inventory_item_id
1616: and si1.bom_item_type in (1,2) -- model, option class
1617: and si2.inventory_item_id = bcol2.inventory_item_id
2116: raise fnd_api.g_exc_error;
2117: END ; /* check for dropped components */
2118:
2119: /*---------------------------------------------------------------+
2120: Third : Get the base model row into BOM_INVENTORY_COMPONENTS
2121: +----------------------------------------------------------------*/
2122:
2123: lStmtNum := 60;
2124: insert into BOM_INVENTORY_COMPS_INTERFACE
2233: NULL, -- low_quantity
2234: NULL, -- high_quantity
2235: NULL, -- acd_type
2236: NULL, -- old_component_sequence_id
2237: bom_inventory_components_s.nextval, -- component sequence id
2238: lConfigBillId, -- bill sequence id
2239: NULL, -- request_id
2240: NULL, -- program_application_id
2241: NULL, -- program_id
2495: from
2496: bom_cto_order_lines_upg bcol1, -- components
2497: bom_cto_order_lines_upg bcol2, -- parent models or option classes
2498: mtl_system_items msi,
2499: bom_inventory_components ic1,
2500: bom_bill_of_materials b1,
2501: bom_operational_routings or1,
2502: bom_operation_sequences os1
2503: where bcol1.parent_ato_line_id = pLineId /*AP*/
2516: and alternate_bom_designator is null
2517: and assembly_item_id =(
2518: select distinct assembly_item_id
2519: from bom_bill_of_materials bbm1,
2520: bom_inventory_components bic1
2521: where bbm1.common_bill_sequence_id = bic1.bill_sequence_id
2522: and component_sequence_id = bcol1.component_sequence_id
2523: and bbm1.assembly_item_id = bcol2.inventory_item_id ))
2524: and ic1.component_item_id = bcol1.inventory_item_id
2560: from
2561: bom_operation_sequences os1,
2562: bom_operational_routings or1,
2563: mtl_system_items si2,
2564: bom_inventory_components ic1,
2565: bom_bill_of_materials b1,
2566: mtl_system_items si1
2567: where si1.organization_id = pOrgId
2568: and si1.inventory_item_id = pModelId
2915: os1.routing_sequence_id
2916: from
2917: bom_cto_order_lines_upg bcol1, /* components */
2918: bom_cto_order_lines_upg bcol2, /* parents model */
2919: bom_inventory_components ic1,
2920: bom_bill_of_materials b1,
2921: bom_operational_routings or1,
2922: bom_operation_sequences os1
2923: where bcol1.parent_ato_line_id = pLineId
2934: and alternate_bom_designator is null
2935: and assembly_item_id =(
2936: select distinct assembly_item_id
2937: from bom_bill_of_materials bbm1,
2938: bom_inventory_components bic1
2939: where bbm1.common_bill_sequence_id = bic1.bill_sequence_id
2940: and component_sequence_id = bcol1.component_sequence_id
2941: and bbm1.assembly_item_id = bcol2.inventory_item_id ))
2942: and ic1.component_item_id = bcol1.inventory_item_id
2977: from
2978: bom_operation_sequences os1,
2979: bom_operational_routings or1,
2980: mtl_system_items si2,
2981: bom_inventory_components ic1,
2982: bom_bill_of_materials b1,
2983: mtl_system_items si1,
2984: bom_cto_order_lines_upg bcol /* Model or option class */
2985: where bcol.parent_ato_line_id = pLineId
4066: low_quantity,
4067: high_quantity,
4068: acd_type,
4069: old_component_sequence_id,
4070: bom_inventory_components_s.nextval,
4071: request_id,
4072: program_application_id,
4073: program_id,
4074: program_update_date,
4336:
4337: WriteToLog('Inserted rows into bom_bill_of_materials::'||sql%rowcount, 2 );
4338:
4339: /*-----------------------------------------------+
4340: Load Bom_inventory_components
4341: +----------------------------------------------*/
4342: WriteToLog('Before second insert into bom_inventory_components. ', 3);
4343: lStmtNum := 310;
4344: xTableName := 'BOM_INVENTORY_COMPONENTS';
4338:
4339: /*-----------------------------------------------+
4340: Load Bom_inventory_components
4341: +----------------------------------------------*/
4342: WriteToLog('Before second insert into bom_inventory_components. ', 3);
4343: lStmtNum := 310;
4344: xTableName := 'BOM_INVENTORY_COMPONENTS';
4345: insert into BOM_INVENTORY_COMPONENTS
4346: (
4340: Load Bom_inventory_components
4341: +----------------------------------------------*/
4342: WriteToLog('Before second insert into bom_inventory_components. ', 3);
4343: lStmtNum := 310;
4344: xTableName := 'BOM_INVENTORY_COMPONENTS';
4345: insert into BOM_INVENTORY_COMPONENTS
4346: (
4347: operation_seq_num,
4348: component_item_id,
4341: +----------------------------------------------*/
4342: WriteToLog('Before second insert into bom_inventory_components. ', 3);
4343: lStmtNum := 310;
4344: xTableName := 'BOM_INVENTORY_COMPONENTS';
4345: insert into BOM_INVENTORY_COMPONENTS
4346: (
4347: operation_seq_num,
4348: component_item_id,
4349: last_update_date,
4507: /*-----------------------------------------------+
4508: Populate Substitutes for Mandatory components
4509: +----------------------------------------------*/
4510: IF PG_DEBUG <> 0 THEN
4511: WriteToLog('create_bom_data_ml: ' || 'Before second insert into bom_inventory_components. ', 2);
4512: END IF;
4513: lStmtNum := 315;
4514: xTableName := 'BOM_SUBSTITUTE_COMPONENTS';
4515:
4593: ,creation_date
4594: ,created_by
4595: ,last_update_login
4596: */
4597: from bom_inventory_comps_interface b , bom_inventory_components bic, bom_substitute_components s
4598: where b.bill_sequence_id = lConfigBillId
4599: and ABS(b.model_comp_seq_id) = bic.component_sequence_id
4600: and bic.optional = 2 /* only mandatory components */
4601: and bic.component_sequence_id = s.component_sequence_id ;
4695: r.ATTRIBUTE13,
4696: r.ATTRIBUTE14,
4697: r.ATTRIBUTE15
4698: from
4699: bom_inventory_components ic,
4700: bom_reference_designators r,
4701: bom_bill_of_materials b
4702: where b.assembly_item_id = pConfigId
4703: and b.organization_id = pOrgId
4736: -- Determine the component_sequence_id into which this item has been clubbed
4737: select
4738: bic.component_sequence_id into club_component_sequence_id
4739: from
4740: bom_inventory_components bic,
4741: bom_bill_of_materials bom
4742: where bom.assembly_item_id = pConfigId
4743: and bom.organization_id = pOrgId
4744: and bic.bill_sequence_id = bom.bill_sequence_id
4958: ( select /*+ ORDERED */
4959: NVL(max(v.element_value),i.element_value)
4960: from
4961: bom_bill_of_materials bi,
4962: bom_inventory_components bc1,
4963: bom_inventory_components bc2,
4964: bom_dependent_desc_elements be,
4965: mtl_descr_element_values v
4966: where bi.assembly_item_id = pConfigId
4959: NVL(max(v.element_value),i.element_value)
4960: from
4961: bom_bill_of_materials bi,
4962: bom_inventory_components bc1,
4963: bom_inventory_components bc2,
4964: bom_dependent_desc_elements be,
4965: mtl_descr_element_values v
4966: where bi.assembly_item_id = pConfigId
4967: and bi.organization_id = pOrgId
4986: ( select /*+ ORDERED */
4987: NVL(max(v.element_value),i.element_value)
4988: from
4989: bom_bill_of_materials bi,
4990: bom_inventory_components bc1,
4991: bom_inventory_components bc2,
4992: bom_dependent_desc_elements be,
4993: mtl_descr_element_values v
4994: where bi.assembly_item_id = pConfigId
4987: NVL(max(v.element_value),i.element_value)
4988: from
4989: bom_bill_of_materials bi,
4990: bom_inventory_components bc1,
4991: bom_inventory_components bc2,
4992: bom_dependent_desc_elements be,
4993: mtl_descr_element_values v
4994: where bi.assembly_item_id = pConfigId
4995: and bi.organization_id = pOrgId
5656: FUNCTION inherit_op_seq_ml(
5657: pLineId in oe_order_lines.line_id%TYPE := NULL,
5658: pOrgId in oe_order_lines.ship_from_org_id%TYPE := NULL,
5659: pModelId in bom_bill_of_materials.assembly_item_id%TYPE := NULL ,
5660: pConfigBillId in bom_inventory_components.bill_sequence_id%TYPE := NULL,
5661: xErrorMessage out NOCOPY VARCHAR2,
5662: xMessageName out NOCOPY VARCHAR2)
5663: RETURN INTEGER IS
5664:
5663: RETURN INTEGER IS
5664:
5665: CURSOR c_incl_items_all_level ( xOrgId mtl_system_items.organization_id%TYPE,
5666: xLineId bom_cto_order_lines_upg.line_id%TYPE,
5667: xConfigBillId bom_inventory_components.bill_sequence_id%TYPE ,
5668: xSchShpdt date,
5669: xEstReldt date ) IS
5670: select bbm.organization_id,
5671: nvl(bic.operation_seq_num,1) operation_seq_num , -- 2433862
5715: bom_cto_order_lines_upg bcol2, -- MODEL
5716: mtl_system_items si1,
5717: mtl_system_items si2,
5718: bom_bill_of_materials bbm,
5719: bom_inventory_components bic, -- Components
5720: bom_inventory_components bic1, -- Parent
5721: bom_explosion_temp bet
5722: where bcol1.parent_ato_line_id = xLineId
5723: and bcol1.component_code = bet.component_code
5716: mtl_system_items si1,
5717: mtl_system_items si2,
5718: bom_bill_of_materials bbm,
5719: bom_inventory_components bic, -- Components
5720: bom_inventory_components bic1, -- Parent
5721: bom_explosion_temp bet
5722: where bcol1.parent_ato_line_id = xLineId
5723: and bcol1.component_code = bet.component_code
5724: and si1.organization_id = xOrgId
5748: and bic.implementation_date is not null
5749: and NVL(bic.disable_date,NVL(xEstReldt, SYSDATE)+1) > NVL(xEstReldt,SYSDATE)
5750: and NVL(bic.disable_date,SYSDATE) >= SYSDATE;
5751:
5752: CURSOR c_model_oc_oi_rows(xConfigBillId bom_inventory_components.bill_sequence_id%TYPE) IS
5753: SELECT /*+ INDEX( BOM_EXPLOSION_TEMP BOM_EXPLOSION_TEMP_N11) */
5754: nvl(operation_seq_num,1) operation_seq_num, -- 2433862
5755: component_code,
5756: rowid
5899: 2,
5900: ic1.required_for_revenue,
5901: ic1.include_on_ship_docs,
5902: ic1.include_on_bill_docs,
5903: bom_inventory_components_s.nextval,
5904: pConfigBillId,
5905: ic1.wip_supply_type,
5906: 2,
5907: decode(bcol1.config_item_id, NULL, (-1)*ic1.component_sequence_id, ic1.component_sequence_id),
5918: sysdate ),
5919: nvl(ic1.disable_date,g_futuredate),
5920: bcol3.inventory_item_id , /* Bug Fix: 4147224 */
5921: nvl(ic1.basis_type,1)
5922: from bom_inventory_components ic1,
5923: bom_cto_order_lines_upg bcol1,
5924: bom_cto_order_lines_upg bcol2,
5925: bom_cto_order_lines_upg bcol3,
5926: mtl_system_items msi_child,
5932: and alternate_bom_designator is null
5933: and assembly_item_id =(
5934: select distinct assembly_item_id
5935: from bom_bill_of_materials bbm1,
5936: bom_inventory_components bic1
5937: where bbm1.common_bill_sequence_id = bic1.bill_sequence_id
5938: and component_sequence_id = bcol1.component_sequence_id
5939: and bbm1.assembly_item_id = bcol3.inventory_item_id ))
5940: and ic1.component_item_id = bcol1.inventory_item_id
6103: 2, -- required_to_ship = NO
6104: bic.required_for_revenue,
6105: bic.include_on_ship_docs,
6106: bic.include_on_bill_docs,
6107: bom_inventory_components_s.nextval, -- component sequence id
6108: pConfigBillId, -- bill sequence id
6109: bic.wip_supply_type,
6110: 2, -- pick_components = NO
6111: (-1)*bic.component_sequence_id, -- model comp seq for later use
6121: nvl(bic.disable_date,g_futuredate) -- 3222932
6122: , nvl(bic.basis_type,1) /* LBM project */
6123: from bom_cto_order_lines_upg bcol,
6124: bom_bill_of_materials bbm,
6125: bom_inventory_components bic
6126: where bcol.line_id = pLineId
6127: and bcol.ordered_quantity <> 0
6128: -- bugfix 2389283 and instr(bcol.component_code,'-',1,1) = 0 /* To identify Top Model */
6129: and bcol.inventory_item_id = pModelId
6287: 2, -- required_to_ship = NO
6288: r2.required_for_revenue,
6289: r2.include_on_ship_docs,
6290: r2.include_on_bill_docs,
6291: bom_inventory_components_s.nextval, -- component sequence id
6292: pConfigBillId, -- bill sequence id
6293: r2.wip_supply_type,
6294: 2, -- pick_components = NO
6295: (-1)*r2.component_sequence_id, -- model comp seq for later use