[Home] [Help]
396: i binary_integer;
397: lconfig_id oe_order_lines.configuration_id%type;
398: lorder_no oe_order_headers_all.order_number%type;
399: lheader_id oe_order_headers_all.header_id%type;
400: lplanner_code fnd_user.user_name%type; -- Modified by Renga on 11/24/04
401: --for bug 4026568
402: lOrg_id mtl_system_items.organization_id%type;
403: lStmtNumber number;
404: lResv_exists boolean;
1336: lcust_line_no varchar2(50);
1337: lnotify_person varchar2(200);
1338: i binary_integer;
1339: linv_item_id oe_order_lines_all.inventory_item_id%type;
1340: lplanner_code fnd_user.user_name%type; -- Modified by Renga on
1341: --11/24/04 for bug 4026568
1342: lship_org_id oe_order_lines_all.ship_from_org_id%type;
1343: ldummy varchar2(1);
1344: lstmt number;
1394: CURSOR get_buyer_user_name(inv_item_id NUMBER,ship_org_id NUMBER) IS
1395: SELECT FU.user_name
1396: FROM MTL_SYSTEM_ITEMS MTI,
1397: PO_BUYERS_ALL_V PBAV,
1398: FND_USER FU
1399: WHERE MTI.inventory_item_id = inv_item_id
1400: AND MTI.organization_id = ship_org_id
1401: AND MTI.buyer_id = PBAV.employee_id
1402: AND PBAV.employee_id = FU.employee_id(+) --outer join b'cos employee need not be an fnd user.
1398: FND_USER FU
1399: WHERE MTI.inventory_item_id = inv_item_id
1400: AND MTI.organization_id = ship_org_id
1401: AND MTI.buyer_id = PBAV.employee_id
1402: AND PBAV.employee_id = FU.employee_id(+) --outer join b'cos employee need not be an fnd user.
1403: ORDER BY FU.user_name asc;
1404:
1405: -- bug 7447357.pdube Tue Oct 21 04:31:34 PDT 2008
1406: -- Introdcued this to get a unique user name
1407: CURSOR get_planner_user_name(inv_item_id NUMBER,ship_org_id NUMBER) IS
1408: SELECT u.user_name
1409: FROM mtl_system_items_vl item,
1410: mtl_planners p,
1411: fnd_user u
1412: WHERE item.inventory_item_id = inv_item_id
1413: and item.organization_id = ship_org_id
1414: and p.organization_id = item.organization_id
1415: and p.planner_code = item.planner_code
1423: select u.user_name
1424: from mtl_reservations mr,
1425: po_headers_all poh,
1426: oe_order_lines_all oel,
1427: fnd_user u
1428: where oel.line_id = config_line_id and
1429: mr.demand_source_type_id in (8,2) and
1430: mr.demand_source_line_id = oel.line_id and
1431: mr.supply_source_type_id = 1 and
1441: from mtl_reservations mr,
1442: po_requisition_headers_all porh,
1443: po_requisition_lines_all porl,
1444: oe_order_lines_all oel,
1445: fnd_user u
1446: where oel.line_id = config_line_id and
1447: mr.demand_source_type_id in (8,2) and
1448: mr.demand_source_line_id = oel.line_id and
1449: mr.supply_source_type_id = 17 and
1585: /*SELECT u.user_name
1586: INTO lplanner_code
1587: FROM MTL_SYSTEM_ITEMS A,
1588: PO_BUYERS_ALL_V B,
1589: FND_USER U
1590: WHERE a.inventory_item_id = linv_item_id
1591: AND a.organization_id = lship_org_id
1592: AND a.buyer_id = b.employee_id
1593: AND b.employee_id = u.employee_id(+); --outer join b'cos employee need not be an fnd user.*/
1589: FND_USER U
1590: WHERE a.inventory_item_id = linv_item_id
1591: AND a.organization_id = lship_org_id
1592: AND a.buyer_id = b.employee_id
1593: AND b.employee_id = u.employee_id(+); --outer join b'cos employee need not be an fnd user.*/
1594:
1595: -- bug 7447357.ntungare
1596: -- Get the Config Item line id
1597: OPEN get_config_line_id(l_header_id);
1647: /*SELECT u.user_name
1648: INTO lplanner_code
1649: FROM mtl_system_items_vl item
1650: ,mtl_planners p
1651: ,fnd_user u
1652: WHERE item.inventory_item_id = linv_item_id
1653: and item.organization_id = lship_org_id
1654: and p.organization_id = item.organization_id
1655: and p.planner_code = item.planner_code
1652: WHERE item.inventory_item_id = linv_item_id
1653: and item.organization_id = lship_org_id
1654: and p.organization_id = item.organization_id
1655: and p.planner_code = item.planner_code
1656: and p.employee_id = u.employee_id(+); --outer join b'cos employee need not be an fnd user.*/
1657: OPEN get_planner_user_name(linv_item_id,lship_org_id);
1658: FETCH get_planner_user_name into lplanner_code;
1659: CLOSE get_planner_user_name;
1660: