[Home] [Help]
1: package body CTO_UTILITY_PK as
2: /* $Header: CTOUTILB.pls 120.22.12020000.3 2013/03/29 10:57:53 abhissri ship $*/
3:
4: /*----------------------------------------------------------------------------+
5: | Copyright (c) 1993 Oracle Corporation Belmont, California, USA
35: | changes made to derive perform_match value
36: | through BOM:MATCH_CONFIG profile.
37: | Modified on 15-JUN-2001 by Renga Kannan
38: | Moved the get_model_sourcing_org code from
39: | CTO_ATP_INTERFACE_PK to CTO_UTILITY_PK
40: | This decision is taken by CTO team on
41: | 06/15/2001 to avoid the dependency
42: | with CTOATPIB.pls for this procedure
43: | This procedure is used in change order
176: |
177: |
178: +-----------------------------------------------------------------------------*/
179:
180: G_PKG_NAME CONSTANT VARCHAR2(30) := 'CTO_UTILITY_PK';
181: --Bugfix 9148706: Indexing by LONG
182: --TYPE TAB_BCOL is TABLE of bom_cto_order_lines%rowtype index by binary_integer ;
183: TYPE TAB_BCOL is TABLE of bom_cto_order_lines%rowtype index by LONG;
184: gMrpAssignmentSet number ;
589:
590:
591: lStmtNumber := 28 ;
592:
593: if( lProgramId = CTO_UTILITY_PK.PC_BOM_PROGRAM_ID ) then
594:
595: v_source_type_code := 'INTERNAL' ;
596:
597: IF PG_DEBUG <> 0 THEN
1392:
1393: --- Added by Renga Kannan storing the po validation org in pkg variable for further use
1394: --- Added on 08/24/01
1395:
1396: CTO_UTILITY_PK.PO_VALIDATION_ORG := lPoValidationOrg;
1397:
1398: -- End of addition
1399:
1400: IF PG_DEBUG <> 0 THEN
3853: -- an unreserve activity. With this fix, CTO will check wrt reservation qty and decide whether
3854: -- to allow unreservation or not.
3855: --
3856:
3857: l_source_document_type_id := CTO_UTILITY_PK.get_source_document_id ( pLineId => p_order_line_id );
3858:
3859:
3860: --
3861: -- Get the total shipped qty for this line.
4588:
4589: BEGIN
4590:
4591: IF PG_DEBUG <> 0 THEN
4592: oe_debug_pub.add('chk_all_rsv_details: ' || 'Entered CTO_UTILITY_PK.chk_all_rsv_details',1);
4593: END IF;
4594: x_return_status := FND_API.G_RET_STS_SUCCESS;
4595:
4596:
4911: bic.component_item_id,
4912: --concat(concat(bet.component_sequence_id,'-'),bic.component_sequence_id),
4913: decode(bic.plan_level,null,(bet.plan_level+1),(bic.plan_level+bet.plan_level)),
4914: bic.component_quantity,
4915: CTO_UTILITY_PK.Concat_Values(bet.component_code,bic.bill_sequence_id),
4916: bic.item_num,
4917: xGrpId
4918: from
4919: bom_inventory_components bic,
5361:
5362:
5363: --call query sourcing org
5364: lStmtNum:=40;
5365: CTO_UTILITY_PK.query_sourcing_org(
5366: p_inventory_item_id => P_config_item_id,
5367: p_organization_id => P_org_id,
5368: p_sourcing_rule_exists => l_sourcing_rule_exists,
5369: p_sourcing_org => x_sourcing_org,
5543:
5544: lstmtnum := 10 ;
5545:
5546:
5547: CTO_UTILITY_PK.Populate_Bcol(
5548: p_bcol_line_id => p_ato_line_id,
5549: x_return_status => x_return_status ,
5550: x_msg_count => x_msg_count,
5551: x_msg_data => x_msg_data,
5592: exception
5593: when others then
5594: IF PG_DEBUG <> 0 THEN
5595:
5596: oe_debug_pub.add('CTO_UTILITY_PK.split_line: ' || 'Others Exception in stmt num : '
5597: || to_char(lStmtNum), 1);
5598: oe_debug_pub.add('CTO_UTILITY_PK.split_line: ' || 'errormsg='||sqlerrm, 1);
5599:
5600: END IF;
5594: IF PG_DEBUG <> 0 THEN
5595:
5596: oe_debug_pub.add('CTO_UTILITY_PK.split_line: ' || 'Others Exception in stmt num : '
5597: || to_char(lStmtNum), 1);
5598: oe_debug_pub.add('CTO_UTILITY_PK.split_line: ' || 'errormsg='||sqlerrm, 1);
5599:
5600: END IF;
5601: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5602:
5612: )
5613: is
5614: begin
5615:
5616: oe_debug_pub.add('CTO_UTILITY_PK.adjust_bcol_for_split: entered ' || p_ato_line_id , 1);
5617:
5618: update bom_cto_order_lines bcol
5619: set ordered_quantity = ( select ordered_quantity
5620: from oe_order_lines_all
5621: where ato_line_id = bcol.ato_line_id
5622: and line_id = bcol.line_id )
5623: where ato_line_id = p_ato_line_id ;
5624:
5625: oe_debug_pub.add('CTO_UTILITY_PK.adjust_bcol_for_split: upd cnt ' || SQL%ROWCOUNT , 1);
5626:
5627: end adjust_bcol_for_split ;
5628:
5629:
5635: )
5636: is
5637: begin
5638:
5639: oe_debug_pub.add('CTO_UTILITY_PK.adjust_bcol_for_warehouse: entered ' || p_ato_line_id , 1);
5640:
5641: update bom_cto_order_lines bcol
5642: set ship_from_org_id = ( select ship_from_org_id
5643: from oe_order_lines_all
5644: where ato_line_id = bcol.ato_line_id
5645: and line_id = bcol.line_id )
5646: where ato_line_id = p_ato_line_id ;
5647:
5648: oe_debug_pub.add('CTO_UTILITY_PK.adjust_bcol_for_warehouse: upd cnt ' || SQL%ROWCOUNT , 1);
5649:
5650:
5651:
5652: end adjust_bcol_for_warehouse;
6205:
6206:
6207: begin
6208:
6209: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: ******** BEGIN AUTONOMOUS TRANSACTION **********' , 1 );
6210:
6211:
6212:
6213: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: msg count ' || P_MSG_COUNT , 1 );
6209: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: ******** BEGIN AUTONOMOUS TRANSACTION **********' , 1 );
6210:
6211:
6212:
6213: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: msg count ' || P_MSG_COUNT , 1 );
6214:
6215:
6216: if ( nvl( p_msg_count , 0 ) = 0 ) then
6217: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: nothing to notify' , 1 );
6213: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: msg count ' || P_MSG_COUNT , 1 );
6214:
6215:
6216: if ( nvl( p_msg_count , 0 ) = 0 ) then
6217: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: nothing to notify' , 1 );
6218: return ;
6219: end if;
6220:
6221:
6226: l_msg_data := substr(fnd_msg_pub.get(
6227: p_msg_index => l_index,
6228: p_encoded => FND_API.G_FALSE),1,2000);
6229:
6230: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_error: '||substr(l_msg_data,1,250));
6231:
6232: -- Fixed bug 5639511
6233: -- Added Substr fucntion to avoid buffer overflow error
6234:
6239: if( length (v_error_message ) >= 2000 ) then
6240:
6241: v_error_message := substr(v_error_message, 1 , 2000) ;
6242:
6243: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_error: truncated v_error_message to 4000 chars ' );
6244: exit; -- Added exit statement for bug fix 5639511
6245: end if;
6246:
6247:
6247:
6248: END LOOP;
6249:
6250:
6251: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: error_message : ' || v_error_message );
6252:
6253: g_t_expected_error_info(1).NOTIFY_USER := P_NOTIFY_USER ;
6254: g_t_expected_error_info(1).SALES_ORDER_NUM := P_SALES_ORDER_NUM ;
6255: g_t_expected_error_info(1).TOP_MODEL_NAME := p_top_model_name ;
6292: oe_debug_pub.add( 'NOTIFY_USER: ' || g_t_expected_error_info(1).NOTIFY_USER, 1) ;
6293: oe_debug_pub.add( 'REQUEST_ID: ' || g_t_expected_error_info(1).REQUEST_ID , 1 ) ;
6294:
6295:
6296: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: done expected errors ' , 1 );
6297:
6298:
6299:
6300:
6297:
6298:
6299:
6300:
6301: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: going to call send notification ' , 1 );
6302:
6303: send_notification (
6304: P_PROCESS => g_t_expected_error_info(1).process
6305: ,P_LINE_ID => g_t_expected_error_info(1).line_id
6319:
6320: commit ; /* COMMIT FOR AUTONOMOUS TRANSACTION */
6321:
6322:
6323: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: done send notification ' , 1 );
6324:
6325: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: ******** END AUTONOMOUS TRANSACTION FOR NOTIFY_EXPECTED_ERRORS **********' , 1 );
6326: end notify_expected_errors ;
6327:
6321:
6322:
6323: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: done send notification ' , 1 );
6324:
6325: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: ******** END AUTONOMOUS TRANSACTION FOR NOTIFY_EXPECTED_ERRORS **********' , 1 );
6326: end notify_expected_errors ;
6327:
6328:
6329: PROCEDURE APPLY_CREATE_CONFIG_HOLD( p_line_id in number
6432:
6433:
6434: oe_debug_pub.add( 'DROPPED ITEM TABLE ' || ' going to send notification ' , 1 ) ;
6435:
6436: CTO_UTILITY_PK.send_notification (
6437: P_PROCESS => CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).process
6438: ,P_LINE_ID => CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).line_id
6439: ,P_SALES_ORDER_NUM => CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).sales_order_num
6440: ,P_ERROR_MESSAGE => CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).error_message
6581:
6582:
6583:
6584:
6585: CTO_UTILITY_PK.notify_expected_errors ( P_PROCESS => p_process
6586: ,P_LINE_ID => p_line_id
6587: ,P_SALES_ORDER_NUM => p_sales_order_num
6588: ,P_TOP_MODEL_NAME => p_top_model_name
6589: ,P_TOP_MODEL_LINE_NUM => p_top_model_line_num
6649: For config_items_rec in config_items_cur
6650: Loop
6651:
6652: l_attach_text := '';
6653: CTO_UTILITY_PK.generate_bom_attach_text
6654: (p_line_id => config_items_rec.line_id
6655: ,x_text => l_attach_text
6656: ,x_Return_Status => x_Return_Status);
6657:
6663: -- they are already existing for this item. It they are existing
6664: -- We should not create it once again.
6665:
6666: IF PG_DEBUG <> 0 THEN
6667: oe_debug_pub.add('Cto_Utility_pk: ' || 'Creating attachment for line id = '
6668: ||to_char(config_items_rec.line_id),1);
6669: oe_debug_pub.add('Cto_Utility_pk: '|| 'Config item id = '||to_char(config_items_rec.config_item_id),1);
6670: oe_debug_pub.add('Cto_Utility_pk: '|| 'Po Validation Org = '||to_char(config_orgs_rec.po_valid_org),1);
6671: END IF;
6665:
6666: IF PG_DEBUG <> 0 THEN
6667: oe_debug_pub.add('Cto_Utility_pk: ' || 'Creating attachment for line id = '
6668: ||to_char(config_items_rec.line_id),1);
6669: oe_debug_pub.add('Cto_Utility_pk: '|| 'Config item id = '||to_char(config_items_rec.config_item_id),1);
6670: oe_debug_pub.add('Cto_Utility_pk: '|| 'Po Validation Org = '||to_char(config_orgs_rec.po_valid_org),1);
6671: END IF;
6672:
6673: BEGIN
6666: IF PG_DEBUG <> 0 THEN
6667: oe_debug_pub.add('Cto_Utility_pk: ' || 'Creating attachment for line id = '
6668: ||to_char(config_items_rec.line_id),1);
6669: oe_debug_pub.add('Cto_Utility_pk: '|| 'Config item id = '||to_char(config_items_rec.config_item_id),1);
6670: oe_debug_pub.add('Cto_Utility_pk: '|| 'Po Validation Org = '||to_char(config_orgs_rec.po_valid_org),1);
6671: END IF;
6672:
6673: BEGIN
6674: SELECT document_id
6679: AND entity_name = 'MTL_SYSTEM_ITEMS'
6680: AND Pk3_value = 'CTO:BOM:ATTACHMENT';
6681:
6682: IF PG_DEBUG <> 0 THEN
6683: oe_debug_pub.add('Cto_Utility_pk: '
6684: || 'Bom attachment already exists for this item.. document_id ='||to_char(l_document_id),1);
6685: END IF;
6686: EXCEPTION
6687: WHEN NO_DATA_FOUND THEN
6685: END IF;
6686: EXCEPTION
6687: WHEN NO_DATA_FOUND THEN
6688: IF PG_DEBUG <> 0 THEN
6689: oe_debug_pub.add('Cto_Utility_pk: '
6690: || 'There is no bom document attached to this item.. We need to attach the document',1);
6691: END IF;
6692:
6693: IF l_attach_text is not null THEN
6691: END IF;
6692:
6693: IF l_attach_text is not null THEN
6694:
6695: CTO_UTILITY_PK.create_attachment
6696: (p_item_id => config_items_rec.config_item_id,
6697: p_org_id => config_orgs_rec.po_valid_org,
6698: p_text => l_attach_text,
6699: p_desc => 'Bill Of Material Details',
6700: p_doc_type => 'CTO:BOM:ATTACHMENT',
6701: x_Return_Status => x_Return_Status);
6702:
6703: IF PG_DEBUG <> 0 THEN
6704: oe_debug_pub.add('Cto_Utility_pk: ' || 'Return status from create_attachment after bom attachment='
6705: ||x_Return_Status,1);
6706: END IF;
6707:
6708: END IF;
7181: ******************************************************************************************/
7182:
7183: PROCEDURE Get_Resv_Qty
7184: ( p_order_line_id NUMBER,
7185: x_rsv_rec OUT NOCOPY CTO_UTILITY_PK.resv_tbl_rec_type,
7186: x_primary_uom_code OUT NOCOPY VARCHAR2,
7187: x_sum_rsv_qty OUT NOCOPY NUMBER,
7188: x_return_status OUT NOCOPY VARCHAR2,
7189: x_msg_count OUT NOCOPY NUMBER,
7299: oe_debug_pub.add('get_resv_qty: l_item_id::' || l_item_id, 1);
7300: oe_debug_pub.add('get_resv_qty: l_routing_type::' || l_routing_type, 1);
7301: END IF;
7302:
7303: l_source_document_type_id := CTO_UTILITY_PK.get_source_document_id ( pLineId => p_order_line_id );
7304:
7305: lStmtNum := 10;
7306: FOR cur_var in c_resv
7307: LOOP
7341: --conversions before assigning it to x_rsv_rec(l_index).primary_reservation_quantity.
7342:
7343: --Level1 conversion: Primary UOM to Order UOM using lot level conversion.
7344: lStmtNum := 14;
7345: l_conv_qty_lot_level := CTO_UTILITY_PK.convert_uom
7346: (from_uom => x_primary_uom_code,
7347: to_uom => l_order_uom_code,
7348: quantity => cur_var.primary_reservation_quantity,
7349: item_id => l_item_id,
7357: END IF;
7358:
7359: --Level2 conversion: Order UOM to Primary UOM using item level conversion.
7360: lStmtNum := 16;
7361: l_conv_qty_item_level := CTO_UTILITY_PK.convert_uom
7362: (from_uom => l_order_uom_code,
7363: to_uom => x_primary_uom_code,
7364: quantity => l_conv_qty_lot_level,
7365: item_id => l_item_id,
7454: oe_debug_pub.add('get_resv_qty:'||'adding flow to x_rsv_rec', 5);
7455: END IF;
7456:
7457: --add this to record structure
7458: l_index := CTO_UTILITY_PK.g_source_type_flow;
7459:
7460: --flow qty is not reserved thru inv so cannot consider as reservation_qty
7461: --Hence assigimng the qty to record structure
7462: --Supply source type id is hard coded to in CTOWFAPS as constant
7469: --OPM and Ireq kkonada , get interface data
7470: --added join with oel as part of code review
7471:
7472: lStmtNum := 30;
7473: Select sum(CTO_UTILITY_PK.convert_uom(po.uom_code,x_primary_uom_code,nvl(po.quantity,0),po.item_id)),
7474: sum(nvl(po.secondary_quantity,0))
7475: into l_ext_req_qty,
7476: l_ext_req_secondary_qty
7477: from po_requisitions_interface_all po,
7482: and po.source_type_code = 'VENDOR'
7483: and po.process_flag is null;
7484:
7485: lStmtNum := 35;
7486: Select sum(CTO_UTILITY_PK.convert_uom(po.uom_code,x_primary_uom_code,nvl(po.quantity,0),po.item_id)),
7487: sum(nvl(po.secondary_quantity,0))
7488: into l_int_req_qty,
7489: l_int_req_secondary_qty --changed as part of opm code review
7490: from po_requisitions_interface_all po,
7498:
7499: --add external and internal req intf data to record struc
7500: If l_ext_req_qty is not null then
7501: lStmtNum := 40;
7502: l_index := CTO_UTILITY_PK.g_source_type_ext_req_if;
7503: x_rsv_rec(l_index).primary_reservation_quantity := l_ext_req_qty;
7504: x_rsv_rec(l_index).secondary_reservation_quantity := l_ext_req_secondary_qty;
7505: x_rsv_rec(l_index).supply_source_type_id := l_index;
7506: end if;
7506: end if;
7507:
7508: If l_int_req_qty is not null then
7509: lStmtNum := 45;
7510: l_index := CTO_UTILITY_PK.g_source_type_int_req_if;
7511: x_rsv_rec(l_index).primary_reservation_quantity := l_int_req_qty;
7512: x_rsv_rec(l_index).secondary_reservation_quantity := l_int_req_secondary_qty;
7513: x_rsv_rec(l_index).supply_source_type_id := l_index;
7514: end if;
7560: , p_msg_data => x_msg_data
7561: );
7562: END Get_Resv_Qty;
7563:
7564: END CTO_UTILITY_PK;