[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
1131: -- bugfix 3985173
1132: -- new cursor for component sequence
1133: cursor club_comp_seq ( xComponentItemId number, xOperation_seq_num number ) is
1134: select bic.component_sequence_id comp_seq_id
1135: from bom_inventory_components bic,
1136: bom_bill_of_materials bom
1137: where bom.assembly_item_id = pConfigId
1138: and bom.organization_id = pOrgId
1139: and bic.bill_sequence_id = bom.bill_sequence_id
1195: WriteToLog('Entering Update_Bom_Rtg_Loop', 2);
1196:
1197: xBillId := 0;
1198: lStmtNum := 10;
1199: select bom_inventory_components_s.nextval
1200: into lConfigBillId
1201: from dual;
1202:
1203: WriteToLog('Creating Bill:: ' || lConfigBillId, 2);
1399: NULL, -- low_quantity */
1400: NULL, -- high_quantity */
1401: NULL, -- acd_type */
1402: NULL, --old_component_sequence_id */
1403: bom_inventory_components_s.nextval, -- component sequence id */
1404: lConfigBillId, -- bill sequence id */
1405: NULL, -- request_id */
1406: NULL, -- program_application_id */
1407: NULL, -- program_id */
1420: bcol3.inventory_item_id /* Bug Fix: 4147224 */
1421: , nvl(ic1.basis_type,1), /* LBM project */
1422: cto_msutil_pub.bom_batch_id
1423: from
1424: bom_inventory_components ic1,
1425: bom_cto_order_lines_upg bcol1, -- Option
1426: bom_cto_order_lines_upg bcol2, -- Parent-Model
1427: bom_cto_order_lines_upg bcol3, -- Parent-component
1428: mtl_system_items msi_child , /* 02-14-2005 Sushant */ -- begin bugfix 1653881
1434: and alternate_bom_designator is null
1435: and assembly_item_id =(
1436: select distinct assembly_item_id
1437: from bom_bill_of_materials bbm1,
1438: bom_inventory_components bic1
1439: where bbm1.common_bill_sequence_id = bic1.bill_sequence_id
1440: and component_sequence_id = bcol1.component_sequence_id
1441: and bbm1.assembly_item_id = bcol3.inventory_item_id ))
1442: and ic1.component_item_id = bcol1.inventory_item_id
1645: NULL, -- low_quantity
1646: NULL, -- high_quantity
1647: NULL, -- acd_type
1648: NULL, -- old_component_sequence_id
1649: bom_inventory_components_s.nextval, -- component sequence id
1650: lConfigBillId, -- bill sequence id
1651: NULL, -- request_id
1652: NULL, -- program_application_id
1653: NULL, -- program_id
1668: bom_cto_order_lines_upg bcol2, -- Model
1669: mtl_system_items si1,
1670: mtl_system_items si2,
1671: bom_bill_of_materials b,
1672: bom_inventory_components ic1
1673: where si1.organization_id = pOrgId
1674: and bcol1.inventory_item_id = si1.inventory_item_id
1675: and si1.bom_item_type in (1,2) -- model, option class
1676: and si2.inventory_item_id = bcol2.inventory_item_id
2205: raise fnd_api.g_exc_error;
2206: END ; /* check for dropped components */
2207:
2208: /*---------------------------------------------------------------+
2209: Third : Get the base model row into BOM_INVENTORY_COMPONENTS
2210: +----------------------------------------------------------------*/
2211:
2212: lStmtNum := 60;
2213: insert into BOM_INVENTORY_COMPS_INTERFACE
2322: NULL, -- low_quantity
2323: NULL, -- high_quantity
2324: NULL, -- acd_type
2325: NULL, -- old_component_sequence_id
2326: bom_inventory_components_s.nextval, -- component sequence id
2327: lConfigBillId, -- bill sequence id
2328: NULL, -- request_id
2329: NULL, -- program_application_id
2330: NULL, -- program_id
2584: from
2585: bom_cto_order_lines_upg bcol1, -- components
2586: bom_cto_order_lines_upg bcol2, -- parent models or option classes
2587: mtl_system_items msi,
2588: bom_inventory_components ic1,
2589: bom_bill_of_materials b1,
2590: bom_operational_routings or1,
2591: bom_operation_sequences os1
2592: where bcol1.parent_ato_line_id = pLineId /*AP*/
2605: and alternate_bom_designator is null
2606: and assembly_item_id =(
2607: select distinct assembly_item_id
2608: from bom_bill_of_materials bbm1,
2609: bom_inventory_components bic1
2610: where bbm1.common_bill_sequence_id = bic1.bill_sequence_id
2611: and component_sequence_id = bcol1.component_sequence_id
2612: and bbm1.assembly_item_id = bcol2.inventory_item_id ))
2613: and ic1.component_item_id = bcol1.inventory_item_id
2649: from
2650: bom_operation_sequences os1,
2651: bom_operational_routings or1,
2652: mtl_system_items si2,
2653: bom_inventory_components ic1,
2654: bom_bill_of_materials b1,
2655: mtl_system_items si1
2656: where si1.organization_id = pOrgId
2657: and si1.inventory_item_id = pModelId
3004: os1.routing_sequence_id
3005: from
3006: bom_cto_order_lines_upg bcol1, /* components */
3007: bom_cto_order_lines_upg bcol2, /* parents model */
3008: bom_inventory_components ic1,
3009: bom_bill_of_materials b1,
3010: bom_operational_routings or1,
3011: bom_operation_sequences os1
3012: where bcol1.parent_ato_line_id = pLineId
3023: and alternate_bom_designator is null
3024: and assembly_item_id =(
3025: select distinct assembly_item_id
3026: from bom_bill_of_materials bbm1,
3027: bom_inventory_components bic1
3028: where bbm1.common_bill_sequence_id = bic1.bill_sequence_id
3029: and component_sequence_id = bcol1.component_sequence_id
3030: and bbm1.assembly_item_id = bcol2.inventory_item_id ))
3031: and ic1.component_item_id = bcol1.inventory_item_id
3066: from
3067: bom_operation_sequences os1,
3068: bom_operational_routings or1,
3069: mtl_system_items si2,
3070: bom_inventory_components ic1,
3071: bom_bill_of_materials b1,
3072: mtl_system_items si1,
3073: bom_cto_order_lines_upg bcol /* Model or option class */
3074: where bcol.parent_ato_line_id = pLineId
4233: low_quantity,
4234: high_quantity,
4235: acd_type,
4236: old_component_sequence_id,
4237: bom_inventory_components_s.nextval,
4238: request_id,
4239: program_application_id,
4240: program_id,
4241: program_update_date,
4511:
4512: WriteToLog('Inserted rows into bom_bill_of_materials::'||sql%rowcount, 2 );
4513:
4514: /*-----------------------------------------------+
4515: Load Bom_inventory_components
4516: +----------------------------------------------*/
4517: WriteToLog('Before second insert into bom_inventory_components. ', 3);
4518: lStmtNum := 310;
4519: xTableName := 'BOM_INVENTORY_COMPONENTS';
4513:
4514: /*-----------------------------------------------+
4515: Load Bom_inventory_components
4516: +----------------------------------------------*/
4517: WriteToLog('Before second insert into bom_inventory_components. ', 3);
4518: lStmtNum := 310;
4519: xTableName := 'BOM_INVENTORY_COMPONENTS';
4520: insert into BOM_INVENTORY_COMPONENTS
4521: (
4515: Load Bom_inventory_components
4516: +----------------------------------------------*/
4517: WriteToLog('Before second insert into bom_inventory_components. ', 3);
4518: lStmtNum := 310;
4519: xTableName := 'BOM_INVENTORY_COMPONENTS';
4520: insert into BOM_INVENTORY_COMPONENTS
4521: (
4522: operation_seq_num,
4523: component_item_id,
4516: +----------------------------------------------*/
4517: WriteToLog('Before second insert into bom_inventory_components. ', 3);
4518: lStmtNum := 310;
4519: xTableName := 'BOM_INVENTORY_COMPONENTS';
4520: insert into BOM_INVENTORY_COMPONENTS
4521: (
4522: operation_seq_num,
4523: component_item_id,
4524: last_update_date,
4690: /*-----------------------------------------------+
4691: Populate Substitutes for Mandatory components
4692: +----------------------------------------------*/
4693: IF PG_DEBUG <> 0 THEN
4694: WriteToLog('create_bom_data_ml: ' || 'Before second insert into bom_inventory_components. ', 2);
4695: END IF;
4696: lStmtNum := 315;
4697: xTableName := 'BOM_SUBSTITUTE_COMPONENTS';
4698:
4776: ,creation_date
4777: ,created_by
4778: ,last_update_login
4779: */
4780: from bom_inventory_comps_interface b , bom_inventory_components bic, bom_substitute_components s
4781: where b.bill_sequence_id = lConfigBillId
4782: and ABS(b.model_comp_seq_id) = bic.component_sequence_id
4783: and bic.optional = 2 /* only mandatory components */
4784: and bic.component_sequence_id = s.component_sequence_id ;
4886: r.ATTRIBUTE13,
4887: r.ATTRIBUTE14,
4888: r.ATTRIBUTE15
4889: from
4890: bom_inventory_components ic,
4891: bom_reference_designators r,
4892: bom_bill_of_materials b
4893: where b.assembly_item_id = pConfigId
4894: and b.organization_id = pOrgId
4927: -- Determine the component_sequence_id into which this item has been clubbed
4928: select
4929: bic.component_sequence_id into club_component_sequence_id
4930: from
4931: bom_inventory_components bic,
4932: bom_bill_of_materials bom
4933: where bom.assembly_item_id = pConfigId
4934: and bom.organization_id = pOrgId
4935: and bic.bill_sequence_id = bom.bill_sequence_id
5157: ( select /*+ ORDERED */
5158: NVL(max(v.element_value),i.element_value)
5159: from
5160: bom_bill_of_materials bi,
5161: bom_inventory_components bc1,
5162: bom_inventory_components bc2,
5163: bom_dependent_desc_elements be,
5164: mtl_descr_element_values v
5165: where bi.assembly_item_id = pConfigId
5158: NVL(max(v.element_value),i.element_value)
5159: from
5160: bom_bill_of_materials bi,
5161: bom_inventory_components bc1,
5162: bom_inventory_components bc2,
5163: bom_dependent_desc_elements be,
5164: mtl_descr_element_values v
5165: where bi.assembly_item_id = pConfigId
5166: and bi.organization_id = pOrgId
5185: ( select /*+ ORDERED */
5186: NVL(max(v.element_value),i.element_value)
5187: from
5188: bom_bill_of_materials bi,
5189: bom_inventory_components bc1,
5190: bom_inventory_components bc2,
5191: bom_dependent_desc_elements be,
5192: mtl_descr_element_values v
5193: where bi.assembly_item_id = pConfigId
5186: NVL(max(v.element_value),i.element_value)
5187: from
5188: bom_bill_of_materials bi,
5189: bom_inventory_components bc1,
5190: bom_inventory_components bc2,
5191: bom_dependent_desc_elements be,
5192: mtl_descr_element_values v
5193: where bi.assembly_item_id = pConfigId
5194: and bi.organization_id = pOrgId
5865: FUNCTION inherit_op_seq_ml(
5866: pLineId in oe_order_lines.line_id%TYPE := NULL,
5867: pOrgId in oe_order_lines.ship_from_org_id%TYPE := NULL,
5868: pModelId in bom_bill_of_materials.assembly_item_id%TYPE := NULL ,
5869: pConfigBillId in bom_inventory_components.bill_sequence_id%TYPE := NULL,
5870: xErrorMessage out NOCOPY VARCHAR2,
5871: xMessageName out NOCOPY VARCHAR2)
5872: RETURN INTEGER IS
5873:
5872: RETURN INTEGER IS
5873:
5874: CURSOR c_incl_items_all_level ( xOrgId mtl_system_items.organization_id%TYPE,
5875: xLineId bom_cto_order_lines_upg.line_id%TYPE,
5876: xConfigBillId bom_inventory_components.bill_sequence_id%TYPE ,
5877: xSchShpdt date,
5878: xEstReldt date ) IS
5879: select bbm.organization_id,
5880: nvl(bic.operation_seq_num,1) operation_seq_num , -- 2433862
5924: bom_cto_order_lines_upg bcol2, -- MODEL
5925: mtl_system_items si1,
5926: mtl_system_items si2,
5927: bom_bill_of_materials bbm,
5928: bom_inventory_components bic, -- Components
5929: bom_inventory_components bic1, -- Parent
5930: bom_explosion_temp bet
5931: where bcol1.parent_ato_line_id = xLineId
5932: and bcol1.component_code = bet.component_code
5925: mtl_system_items si1,
5926: mtl_system_items si2,
5927: bom_bill_of_materials bbm,
5928: bom_inventory_components bic, -- Components
5929: bom_inventory_components bic1, -- Parent
5930: bom_explosion_temp bet
5931: where bcol1.parent_ato_line_id = xLineId
5932: and bcol1.component_code = bet.component_code
5933: and si1.organization_id = xOrgId
5957: and bic.implementation_date is not null
5958: and NVL(bic.disable_date,NVL(xEstReldt, SYSDATE)+1) > NVL(xEstReldt,SYSDATE)
5959: and NVL(bic.disable_date,SYSDATE) >= SYSDATE;
5960:
5961: CURSOR c_model_oc_oi_rows(xConfigBillId bom_inventory_components.bill_sequence_id%TYPE) IS
5962: SELECT /*+ INDEX( BOM_EXPLOSION_TEMP BOM_EXPLOSION_TEMP_N11) */
5963: nvl(operation_seq_num,1) operation_seq_num, -- 2433862
5964: component_code,
5965: rowid
6108: 2,
6109: ic1.required_for_revenue,
6110: ic1.include_on_ship_docs,
6111: ic1.include_on_bill_docs,
6112: bom_inventory_components_s.nextval,
6113: pConfigBillId,
6114: ic1.wip_supply_type,
6115: 2,
6116: decode(bcol1.config_item_id, NULL, (-1)*ic1.component_sequence_id, ic1.component_sequence_id),
6127: sysdate ),
6128: nvl(ic1.disable_date,g_futuredate),
6129: bcol3.inventory_item_id , /* Bug Fix: 4147224 */
6130: nvl(ic1.basis_type,1)
6131: from bom_inventory_components ic1,
6132: bom_cto_order_lines_upg bcol1,
6133: bom_cto_order_lines_upg bcol2,
6134: bom_cto_order_lines_upg bcol3,
6135: mtl_system_items msi_child,
6141: and alternate_bom_designator is null
6142: and assembly_item_id =(
6143: select distinct assembly_item_id
6144: from bom_bill_of_materials bbm1,
6145: bom_inventory_components bic1
6146: where bbm1.common_bill_sequence_id = bic1.bill_sequence_id
6147: and component_sequence_id = bcol1.component_sequence_id
6148: and bbm1.assembly_item_id = bcol3.inventory_item_id ))
6149: and ic1.component_item_id = bcol1.inventory_item_id
6312: 2, -- required_to_ship = NO
6313: bic.required_for_revenue,
6314: bic.include_on_ship_docs,
6315: bic.include_on_bill_docs,
6316: bom_inventory_components_s.nextval, -- component sequence id
6317: pConfigBillId, -- bill sequence id
6318: bic.wip_supply_type,
6319: 2, -- pick_components = NO
6320: (-1)*bic.component_sequence_id, -- model comp seq for later use
6330: nvl(bic.disable_date,g_futuredate) -- 3222932
6331: , nvl(bic.basis_type,1) /* LBM project */
6332: from bom_cto_order_lines_upg bcol,
6333: bom_bill_of_materials bbm,
6334: bom_inventory_components bic
6335: where bcol.line_id = pLineId
6336: and bcol.ordered_quantity <> 0
6337: -- bugfix 2389283 and instr(bcol.component_code,'-',1,1) = 0 /* To identify Top Model */
6338: and bcol.inventory_item_id = pModelId
6499: 2, -- required_to_ship = NO
6500: r2.required_for_revenue,
6501: r2.include_on_ship_docs,
6502: r2.include_on_bill_docs,
6503: bom_inventory_components_s.nextval, -- component sequence id
6504: pConfigBillId, -- bill sequence id
6505: r2.wip_supply_type,
6506: 2, -- pick_components = NO
6507: (-1)*r2.component_sequence_id, -- model comp seq for later use