[Home] [Help]
2: /* $Header: porelgeb.pls 120.30.12020000.4 2013/04/05 08:34:17 jemishra ship $ */
3:
4: --
5: g_dest_type_code_SHOP_FLOOR CONSTANT
6: PO_DISTRIBUTIONS_ALL.destination_type_code%TYPE
7: := 'SHOP FLOOR'
8: ;
9:
10: -- Bug 2701147 START
364: plla.lcm_flag = NULL
365: WHERE plla.po_release_id = x_po_release_id
366: AND plla.lcm_flag = 'Y';
367:
368: UPDATE po_distributions_all pda
369: SET pda.lcm_flag = NULL
370: WHERE pda.line_location_id = x_line_location_id
371: AND pda.lcm_flag = 'Y';
372:
621: plla.lcm_flag = NULL
622: WHERE plla.po_release_id = x_po_release_id
623: AND plla.lcm_flag = 'Y';
624:
625: UPDATE po_distributions_all pda
626: SET pda.lcm_flag = NULL
627: WHERE pda.line_location_id = x_line_location_id
628: AND pda.lcm_flag = 'Y';
629:
1411: );
1412: END IF;
1413: -- Bug 3570793 END
1414:
1415: INSERT INTO PO_DISTRIBUTIONS
1416: (PO_DISTRIBUTION_ID,
1417: LAST_UPDATE_DATE,
1418: LAST_UPDATED_BY,
1419: PO_HEADER_ID,
1473: --added distribution_type for encumb. rewrite
1474: distribution_type,
1475: Org_Id --
1476: )
1477: SELECT PO_DISTRIBUTIONS_S.NEXTVAL,
1478: sysdate,
1479: req_line.last_updated_by, -- :cpo_last_updated_by
1480: req_line.blanket_po_header_id, --:po_header_id
1481: sysdate,
1593: SELECT SUM(POD.QUANTITY_ORDERED)
1594: INTO x_dist_quantity
1595: FROM PO_LINE_LOCATIONS POLL,
1596: PO_REQUISITION_LINES PORL,
1597: PO_DISTRIBUTIONS POD
1598: WHERE POLL.LINE_LOCATION_ID = PORL.LINE_LOCATION_ID
1599: AND PORL.REQUISITION_LINE_ID
1600: = req_line.requisition_line_id
1601: AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID;
1602:
1603: x_qty_difference := x_shipment_quantity-x_dist_quantity;
1604:
1605: IF (x_qty_difference <> 0) THEN
1606: UPDATE PO_DISTRIBUTIONS POD
1607: SET POD.QUANTITY_ORDERED
1608: = POD.QUANTITY_ORDERED + x_qty_difference
1609: WHERE POD.PO_DISTRIBUTION_ID =
1610: (SELECT POD2.PO_DISTRIBUTION_ID
1607: SET POD.QUANTITY_ORDERED
1608: = POD.QUANTITY_ORDERED + x_qty_difference
1609: WHERE POD.PO_DISTRIBUTION_ID =
1610: (SELECT POD2.PO_DISTRIBUTION_ID
1611: FROM PO_DISTRIBUTIONS POD2,
1612: PO_LINE_LOCATIONS POLL,
1613: PO_REQUISITION_LINES PORL
1614: WHERE POD2.LINE_LOCATION_ID
1615: = POLL.LINE_LOCATION_ID
1980: pod.deliver_to_location_id,
1981: pod.destination_organization_id,
1982: pod.destination_subinventory,
1983: 'Y'
1984: from po_distributions pod,
1985: po_line_locations poll,
1986: po_lines pol
1987: where poll.line_location_id = x_line_location_id
1988: and nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
2414: IS
2415: BEGIN
2416: SELECT prh.segment1, prl.line_num
2417: INTO x_req_num, x_req_line_num
2418: FROM po_distributions pod,
2419: po_req_distributions prd,
2420: po_requisition_lines prl,
2421: po_requisition_headers prh
2422: WHERE pod.po_distribution_id = p_po_distribution_id
2444: l_po_distribution_id NUMBER;
2445: BEGIN
2446: SELECT min(pod.po_distribution_id)
2447: INTO l_po_distribution_id
2448: FROM po_distributions pod
2449: WHERE pod.line_location_id = p_line_location_id;
2450:
2451: get_req_info_from_po_dist (l_po_distribution_id,
2452: x_req_num, x_req_line_num);
2526: nvl( POLL.line_location_id,0) line_loc_id
2527: FROM PO_LINE_LOCATIONS POLL,
2528: PO_LINE_TYPES PLT,
2529: PO_LINES POL,
2530: PO_DISTRIBUTIONS POD
2531: WHERE POLL.po_line_id = POL.po_line_id
2532: AND POLL.line_location_id = POD.line_location_id
2533: AND POLL.po_release_id = p_document_id
2534: AND POL.line_type_id = PLT.line_type_id
2561: min(POLL.shipment_num)
2562: FROM PO_REQUISITION_LINES PRL,
2563: PO_LINE_LOCATIONS POLL,
2564: PO_LINES POL,
2565: PO_DISTRIBUTIONS PD,
2566: PO_REQ_DISTRIBUTIONS PRD
2567: WHERE POLL.line_location_id = p_line_location_id
2568: AND POLL.po_line_id = POL.po_line_id
2569: AND PRL.unit_price >= 0
3020: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_PDOI_INVALID_GL_ENC_PER');
3021: SELECT substr(l_textline,1,240),
3022: POD.po_distribution_id
3023: BULK COLLECT INTO l_error_messages, l_dist_id
3024: FROM PO_DISTRIBUTIONS POD, PO_LINE_LOCATIONS PLL, PO_LINES POL
3025: WHERE POD.line_location_id = PLL.line_location_id
3026: AND PLL.po_release_id = x_po_release_id
3027: AND POL.po_line_id = PLL.po_line_id
3028: AND nvl(POD.encumbered_flag,'N') = 'N'
3342: POD.distribution_num||g_delim||l_textline, 1,240)
3343: BULK COLLECT INTO l_error_messages
3344: FROM PO_RELEASES_GT POR,
3345: PO_LINE_LOCATIONS_GT PLL,
3346: PO_DISTRIBUTIONS_GT POD
3347: WHERE POR.po_release_id = POD.po_release_id
3348: AND POD.line_location_id = PLL.line_location_id
3349: AND POR.po_release_id = x_po_release_id
3350: AND Nvl(POD.LCM_FLAG,'N') = 'Y'
3488: --Name: create_award_distribution
3489: --Pre-reqs:
3490: -- None
3491: --Modifies:
3492: -- PO_DISTRIBUTIONS
3493: -- GMS_AWARD_DISTRIBUTIONS
3494: --Locks:
3495: -- None
3496: --Function:
3520:
3521: l_progress := '001';
3522:
3523: --SQL WHAT: Select the columns that Grants needs from the
3524: -- po_distributions table for this release where
3525: -- award_id is referenced.
3526: --SQL WHY : Need to call GMS API to update award distribution
3527: -- lines table.
3528:
3539: l_gms_po_interface_obj.project_id,
3540: l_gms_po_interface_obj.task_id,
3541: l_gms_po_interface_obj.award_set_id_in,
3542: l_gms_po_interface_obj.award_set_id_out
3543: FROM PO_DISTRIBUTIONS
3544: WHERE line_location_id = x_line_location_id AND
3545: award_id IS NOT NULL;
3546:
3547: IF SQL%NOTFOUND THEN
3565: END IF;
3566:
3567: l_progress := '003';
3568:
3569: --Update po_distributions table with the new award_id's
3570:
3571: FORALL i IN 1..l_gms_po_interface_obj.distribution_id.COUNT
3572: UPDATE po_distributions
3573: SET award_id = l_gms_po_interface_obj.award_set_id_out(i)
3568:
3569: --Update po_distributions table with the new award_id's
3570:
3571: FORALL i IN 1..l_gms_po_interface_obj.distribution_id.COUNT
3572: UPDATE po_distributions
3573: SET award_id = l_gms_po_interface_obj.award_set_id_out(i)
3574: WHERE po_distribution_id = l_gms_po_interface_obj.distribution_id(i);
3575:
3576: EXCEPTION