[Home] [Help]
1: package body CTO_UTILITY_PK as
2: /* $Header: CTOUTILB.pls 120.15.12010000.4 2008/08/14 12:33:57 ntungare 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: TYPE TAB_BCOL is TABLE of bom_cto_order_lines%rowtype index by binary_integer ;
182: gMrpAssignmentSet number ;
183:
184:
587:
588:
589: lStmtNumber := 28 ;
590:
591: if( lProgramId = CTO_UTILITY_PK.PC_BOM_PROGRAM_ID ) then
592:
593: v_source_type_code := 'INTERNAL' ;
594:
595: IF PG_DEBUG <> 0 THEN
1390:
1391: --- Added by Renga Kannan storing the po validation org in pkg variable for further use
1392: --- Added on 08/24/01
1393:
1394: CTO_UTILITY_PK.PO_VALIDATION_ORG := lPoValidationOrg;
1395:
1396: -- End of addition
1397:
1398: IF PG_DEBUG <> 0 THEN
3831: -- an unreserve activity. With this fix, CTO will check wrt reservation qty and decide whether
3832: -- to allow unreservation or not.
3833: --
3834:
3835: l_source_document_type_id := CTO_UTILITY_PK.get_source_document_id ( pLineId => p_order_line_id );
3836:
3837:
3838: --
3839: -- Get the total shipped qty for this line.
4564:
4565: BEGIN
4566:
4567: IF PG_DEBUG <> 0 THEN
4568: oe_debug_pub.add('chk_all_rsv_details: ' || 'Entered CTO_UTILITY_PK.chk_all_rsv_details',1);
4569: END IF;
4570: x_return_status := FND_API.G_RET_STS_SUCCESS;
4571:
4572:
4887: bic.component_item_id,
4888: --concat(concat(bet.component_sequence_id,'-'),bic.component_sequence_id),
4889: decode(bic.plan_level,null,(bet.plan_level+1),(bic.plan_level+bet.plan_level)),
4890: bic.component_quantity,
4891: CTO_UTILITY_PK.Concat_Values(bet.component_code,bic.bill_sequence_id),
4892: bic.item_num,
4893: xGrpId
4894: from
4895: bom_inventory_components bic,
5337:
5338:
5339: --call query sourcing org
5340: lStmtNum:=40;
5341: CTO_UTILITY_PK.query_sourcing_org(
5342: p_inventory_item_id => P_config_item_id,
5343: p_organization_id => P_org_id,
5344: p_sourcing_rule_exists => l_sourcing_rule_exists,
5345: p_sourcing_org => x_sourcing_org,
5519:
5520: lstmtnum := 10 ;
5521:
5522:
5523: CTO_UTILITY_PK.Populate_Bcol(
5524: p_bcol_line_id => p_ato_line_id,
5525: x_return_status => x_return_status ,
5526: x_msg_count => x_msg_count,
5527: x_msg_data => x_msg_data,
5568: exception
5569: when others then
5570: IF PG_DEBUG <> 0 THEN
5571:
5572: oe_debug_pub.add('CTO_UTILITY_PK.split_line: ' || 'Others Exception in stmt num : '
5573: || to_char(lStmtNum), 1);
5574: oe_debug_pub.add('CTO_UTILITY_PK.split_line: ' || 'errormsg='||sqlerrm, 1);
5575:
5576: END IF;
5570: IF PG_DEBUG <> 0 THEN
5571:
5572: oe_debug_pub.add('CTO_UTILITY_PK.split_line: ' || 'Others Exception in stmt num : '
5573: || to_char(lStmtNum), 1);
5574: oe_debug_pub.add('CTO_UTILITY_PK.split_line: ' || 'errormsg='||sqlerrm, 1);
5575:
5576: END IF;
5577: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5578:
5588: )
5589: is
5590: begin
5591:
5592: oe_debug_pub.add('CTO_UTILITY_PK.adjust_bcol_for_split: entered ' || p_ato_line_id , 1);
5593:
5594: update bom_cto_order_lines bcol
5595: set ordered_quantity = ( select ordered_quantity
5596: from oe_order_lines_all
5597: where ato_line_id = bcol.ato_line_id
5598: and line_id = bcol.line_id )
5599: where ato_line_id = p_ato_line_id ;
5600:
5601: oe_debug_pub.add('CTO_UTILITY_PK.adjust_bcol_for_split: upd cnt ' || SQL%ROWCOUNT , 1);
5602:
5603: end adjust_bcol_for_split ;
5604:
5605:
5611: )
5612: is
5613: begin
5614:
5615: oe_debug_pub.add('CTO_UTILITY_PK.adjust_bcol_for_warehouse: entered ' || p_ato_line_id , 1);
5616:
5617: update bom_cto_order_lines bcol
5618: set ship_from_org_id = ( select ship_from_org_id
5619: from oe_order_lines_all
5620: where ato_line_id = bcol.ato_line_id
5621: and line_id = bcol.line_id )
5622: where ato_line_id = p_ato_line_id ;
5623:
5624: oe_debug_pub.add('CTO_UTILITY_PK.adjust_bcol_for_warehouse: upd cnt ' || SQL%ROWCOUNT , 1);
5625:
5626:
5627:
5628: end adjust_bcol_for_warehouse;
6181:
6182:
6183: begin
6184:
6185: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: ******** BEGIN AUTONOMOUS TRANSACTION **********' , 1 );
6186:
6187:
6188:
6189: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: msg count ' || P_MSG_COUNT , 1 );
6185: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: ******** BEGIN AUTONOMOUS TRANSACTION **********' , 1 );
6186:
6187:
6188:
6189: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: msg count ' || P_MSG_COUNT , 1 );
6190:
6191:
6192: if ( nvl( p_msg_count , 0 ) = 0 ) then
6193: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: nothing to notify' , 1 );
6189: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: msg count ' || P_MSG_COUNT , 1 );
6190:
6191:
6192: if ( nvl( p_msg_count , 0 ) = 0 ) then
6193: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: nothing to notify' , 1 );
6194: return ;
6195: end if;
6196:
6197:
6202: l_msg_data := substr(fnd_msg_pub.get(
6203: p_msg_index => l_index,
6204: p_encoded => FND_API.G_FALSE),1,2000);
6205:
6206: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_error: '||substr(l_msg_data,1,250));
6207:
6208: -- Fixed bug 5639511
6209: -- Added Substr fucntion to avoid buffer overflow error
6210:
6215: if( length (v_error_message ) >= 2000 ) then
6216:
6217: v_error_message := substr(v_error_message, 1 , 2000) ;
6218:
6219: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_error: truncated v_error_message to 4000 chars ' );
6220: exit; -- Added exit statement for bug fix 5639511
6221: end if;
6222:
6223:
6223:
6224: END LOOP;
6225:
6226:
6227: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: error_message : ' || v_error_message );
6228:
6229: g_t_expected_error_info(1).NOTIFY_USER := P_NOTIFY_USER ;
6230: g_t_expected_error_info(1).SALES_ORDER_NUM := P_SALES_ORDER_NUM ;
6231: g_t_expected_error_info(1).TOP_MODEL_NAME := p_top_model_name ;
6268: oe_debug_pub.add( 'NOTIFY_USER: ' || g_t_expected_error_info(1).NOTIFY_USER, 1) ;
6269: oe_debug_pub.add( 'REQUEST_ID: ' || g_t_expected_error_info(1).REQUEST_ID , 1 ) ;
6270:
6271:
6272: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: done expected errors ' , 1 );
6273:
6274:
6275:
6276:
6273:
6274:
6275:
6276:
6277: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: going to call send notification ' , 1 );
6278:
6279: send_notification (
6280: P_PROCESS => g_t_expected_error_info(1).process
6281: ,P_LINE_ID => g_t_expected_error_info(1).line_id
6295:
6296: commit ; /* COMMIT FOR AUTONOMOUS TRANSACTION */
6297:
6298:
6299: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: done send notification ' , 1 );
6300:
6301: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: ******** END AUTONOMOUS TRANSACTION FOR NOTIFY_EXPECTED_ERRORS **********' , 1 );
6302: end notify_expected_errors ;
6303:
6297:
6298:
6299: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: done send notification ' , 1 );
6300:
6301: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: ******** END AUTONOMOUS TRANSACTION FOR NOTIFY_EXPECTED_ERRORS **********' , 1 );
6302: end notify_expected_errors ;
6303:
6304:
6305: PROCEDURE APPLY_CREATE_CONFIG_HOLD( p_line_id in number
6408:
6409:
6410: oe_debug_pub.add( 'DROPPED ITEM TABLE ' || ' going to send notification ' , 1 ) ;
6411:
6412: CTO_UTILITY_PK.send_notification (
6413: P_PROCESS => CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).process
6414: ,P_LINE_ID => CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).line_id
6415: ,P_SALES_ORDER_NUM => CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).sales_order_num
6416: ,P_ERROR_MESSAGE => CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).error_message
6557:
6558:
6559:
6560:
6561: CTO_UTILITY_PK.notify_expected_errors ( P_PROCESS => p_process
6562: ,P_LINE_ID => p_line_id
6563: ,P_SALES_ORDER_NUM => p_sales_order_num
6564: ,P_TOP_MODEL_NAME => p_top_model_name
6565: ,P_TOP_MODEL_LINE_NUM => p_top_model_line_num
6625: For config_items_rec in config_items_cur
6626: Loop
6627:
6628: l_attach_text := '';
6629: CTO_UTILITY_PK.generate_bom_attach_text
6630: (p_line_id => config_items_rec.line_id
6631: ,x_text => l_attach_text
6632: ,x_Return_Status => x_Return_Status);
6633:
6639: -- they are already existing for this item. It they are existing
6640: -- We should not create it once again.
6641:
6642: IF PG_DEBUG <> 0 THEN
6643: oe_debug_pub.add('Cto_Utility_pk: ' || 'Creating attachment for line id = '
6644: ||to_char(config_items_rec.line_id),1);
6645: oe_debug_pub.add('Cto_Utility_pk: '|| 'Config item id = '||to_char(config_items_rec.config_item_id),1);
6646: oe_debug_pub.add('Cto_Utility_pk: '|| 'Po Validation Org = '||to_char(config_orgs_rec.po_valid_org),1);
6647: END IF;
6641:
6642: IF PG_DEBUG <> 0 THEN
6643: oe_debug_pub.add('Cto_Utility_pk: ' || 'Creating attachment for line id = '
6644: ||to_char(config_items_rec.line_id),1);
6645: oe_debug_pub.add('Cto_Utility_pk: '|| 'Config item id = '||to_char(config_items_rec.config_item_id),1);
6646: oe_debug_pub.add('Cto_Utility_pk: '|| 'Po Validation Org = '||to_char(config_orgs_rec.po_valid_org),1);
6647: END IF;
6648:
6649: BEGIN
6642: IF PG_DEBUG <> 0 THEN
6643: oe_debug_pub.add('Cto_Utility_pk: ' || 'Creating attachment for line id = '
6644: ||to_char(config_items_rec.line_id),1);
6645: oe_debug_pub.add('Cto_Utility_pk: '|| 'Config item id = '||to_char(config_items_rec.config_item_id),1);
6646: oe_debug_pub.add('Cto_Utility_pk: '|| 'Po Validation Org = '||to_char(config_orgs_rec.po_valid_org),1);
6647: END IF;
6648:
6649: BEGIN
6650: SELECT document_id
6655: AND entity_name = 'MTL_SYSTEM_ITEMS'
6656: AND Pk3_value = 'CTO:BOM:ATTACHMENT';
6657:
6658: IF PG_DEBUG <> 0 THEN
6659: oe_debug_pub.add('Cto_Utility_pk: '
6660: || 'Bom attachment already exists for this item.. document_id ='||to_char(l_document_id),1);
6661: END IF;
6662: EXCEPTION
6663: WHEN NO_DATA_FOUND THEN
6661: END IF;
6662: EXCEPTION
6663: WHEN NO_DATA_FOUND THEN
6664: IF PG_DEBUG <> 0 THEN
6665: oe_debug_pub.add('Cto_Utility_pk: '
6666: || 'There is no bom document attached to this item.. We need to attach the document',1);
6667: END IF;
6668:
6669: IF l_attach_text is not null THEN
6667: END IF;
6668:
6669: IF l_attach_text is not null THEN
6670:
6671: CTO_UTILITY_PK.create_attachment
6672: (p_item_id => config_items_rec.config_item_id,
6673: p_org_id => config_orgs_rec.po_valid_org,
6674: p_text => l_attach_text,
6675: p_desc => 'Bill Of Material Details',
6676: p_doc_type => 'CTO:BOM:ATTACHMENT',
6677: x_Return_Status => x_Return_Status);
6678:
6679: IF PG_DEBUG <> 0 THEN
6680: oe_debug_pub.add('Cto_Utility_pk: ' || 'Return status from create_attachment after bom attachment='
6681: ||x_Return_Status,1);
6682: END IF;
6683:
6684: END IF;
7157: ******************************************************************************************/
7158:
7159: PROCEDURE Get_Resv_Qty
7160: ( p_order_line_id NUMBER,
7161: x_rsv_rec OUT NOCOPY CTO_UTILITY_PK.resv_tbl_rec_type,
7162: x_primary_uom_code OUT NOCOPY VARCHAR2,
7163: x_sum_rsv_qty OUT NOCOPY NUMBER,
7164: x_return_status OUT NOCOPY VARCHAR2,
7165: x_msg_count OUT NOCOPY NUMBER,
7235: l_message := 'Before cursor c_resv';
7236: cto_wip_workflow_api_pk.cto_debug('get_resv_qty:', l_message);
7237: END IF;
7238:
7239: l_source_document_type_id := CTO_UTILITY_PK.get_source_document_id ( pLineId => p_order_line_id );
7240:
7241: lStmtNum := 10;
7242: FOR cur_var in c_resv
7243: LOOP
7338: oe_debug_pub.add('get_resv_qty:'||'adding flow to x_rsv_rec', 5);
7339: END IF;
7340:
7341: --add this to record structure
7342: l_index := CTO_UTILITY_PK.g_source_type_flow;
7343:
7344: --flow qty is not reserved thru inv so cannot consider as reservation_qty
7345: --Hence assigimng the qty to record structure
7346: --Supply source type id is hard coded to in CTOWFAPS as constant
7351:
7352: --OPM and Ireq kkonada , get interface data
7353: --added join with oel as part of code review
7354:
7355: Select sum(CTO_UTILITY_PK.convert_uom(po.uom_code,x_primary_uom_code,nvl(po.quantity,0),po.item_id)),
7356: sum(nvl(po.secondary_quantity,0))
7357: into l_ext_req_qty,
7358: l_ext_req_secondary_qty
7359: from po_requisitions_interface_all po,
7363: and po.item_id = oel.inventory_item_id
7364: and po.source_type_code = 'VENDOR'
7365: and po.process_flag is null;
7366:
7367: Select sum(CTO_UTILITY_PK.convert_uom(po.uom_code,x_primary_uom_code,nvl(po.quantity,0),po.item_id)),
7368: sum(nvl(po.secondary_quantity,0))
7369: into l_int_req_qty,
7370: l_int_req_secondary_qty --changed as part of opm code review
7371: from po_requisitions_interface_all po,
7378:
7379:
7380: --add external and internal req intf data to record struc
7381: If l_ext_req_qty is not null then
7382: l_index := CTO_UTILITY_PK.g_source_type_ext_req_if;
7383: x_rsv_rec(l_index).primary_reservation_quantity := l_ext_req_qty;
7384: x_rsv_rec(l_index).secondary_reservation_quantity := l_ext_req_secondary_qty;
7385: x_rsv_rec(l_index).supply_source_type_id := l_index;
7386: end if;
7385: x_rsv_rec(l_index).supply_source_type_id := l_index;
7386: end if;
7387:
7388: If l_int_req_qty is not null then
7389: l_index := CTO_UTILITY_PK.g_source_type_int_req_if;
7390: x_rsv_rec(l_index).primary_reservation_quantity := l_int_req_qty;
7391: x_rsv_rec(l_index).secondary_reservation_quantity := l_int_req_secondary_qty;
7392: x_rsv_rec(l_index).supply_source_type_id := l_index;
7393: end if;
7438: , p_msg_data => x_msg_data
7439: );
7440: END Get_Resv_Qty;
7441:
7442: END CTO_UTILITY_PK;