[Home] [Help]
1685: 'PO_PRICING_MISMATCH',
1686: 'E',
1687: pol.line_num
1688: FROM po_lines_gt pol,
1689: po_lines_all_ext_b ple,
1690: po_uda_ag_template_usages ptu
1691: WHERE ple.po_line_id = pol.po_line_id
1692: AND ple.draft_id = p_draft_id
1693: AND ple.attr_group_id = ptu.attribute_group_id
1739: FROM po_lines_gt pol
1740: WHERE pol.order_type_lookup_code <> 'QUANTITY'
1741: AND NOT EXISTS ( Select 1
1742: FROM po_uda_ag_template_usages ptu,
1743: po_lines_all_ext_b ple
1744: WHERE ptu.attribute_category = 'PRICING'
1745: AND ptu.attribute2 = pol.contract_type
1746: AND ptu.attribute1 = pol.clm_idc_type
1747: AND ptu.template_id = ple.uda_template_id
4933: l_shipment_num,
4934: l_dist_num,
4935: l_quantity1,
4936: l_quantity2
4937: FROM PO_LINES_ALL POL,PO_RELEASES_GT POR,PO_LINE_LOCATIONS_GT PLL
4938: WHERE PLL.po_release_id = POR.po_release_id
4939: AND PLL.po_release_id = p_document_id
4940: AND POL.po_line_id = PLL.po_line_id
4941: AND POL.min_release_amount is not null
5963: TYPE CurrencyTab IS TABLE of FND_CURRENCIES.CURRENCY_CODE%TYPE;
5964: l_is_same_foreign_currency VARCHAR2(1);
5965: l_req_line_curr_unit_price NumTab;
5966: l_req_line_curr_code CurrencyTab;
5967: l_po_line_type PO_LINES_ALL.ORDER_TYPE_LOOKUP_CODE%TYPE;
5968: -- End of Bug# 13857241
5969: /*
5970: ** Setup the PO select cursor
5971: ** Select shipment price and convert it to base currency.
12093: p_sequence + ROWNUM,
12094: substr(g_linemsg||g_delim||POL.line_num||g_delim
12095: ||l_textline,1,240),
12096: 'PO_SUB_STD_AFTER_GA_DATE'
12097: FROM PO_LINES_GT POL, PO_HEADERS_GT POH, PO_HEADERS_ALL POH2, PO_LINES_ALL POL2, PO_LINE_LOCATIONS_GT PLL
12098: WHERE POL.po_header_id = p_document_id
12099: AND POL.po_header_id = POH.po_header_id -- JOIN
12100: AND PLL.po_line_id = POL.po_line_id --JOIN, Bug #5415428 - Get the Need by date
12101: AND POL.from_header_id = POH2.po_header_id --JOIN
12462: decode ( POL1.order_type_lookup_code --
12463: , 'FIXED PRICE' , 'PO_SUB_PO_LINE_GT_GA_AMT_TOL'
12464: , 'PO_SUB_PO_LINE_GT_GA_PRICE_TOL'
12465: )
12466: FROM PO_LINES_GT POL1, PO_LINES_ALL POL2, PO_HEADERS_ALL POH
12467: WHERE POL1.po_header_id = p_document_id
12468: AND POH.po_header_id = POL1.from_header_id --JOIN
12469: AND POH.type_lookup_code = 'BLANKET'
12470: AND POH.global_agreement_flag = 'Y'
12534: p_sequence + ROWNUM,
12535: substr(g_linemsg||g_delim||POL1.line_num||g_delim
12536: ||l_textline,1,240),
12537: 'PO_SUB_STD_GA_LINE_LESS_MINREL' -- <2710030>
12538: FROM PO_LINES_GT POL1, PO_LINES_ALL POL2, PO_HEADERS_ALL POH
12539: WHERE POL1.po_header_id = p_document_id
12540: AND POL1.from_header_id = POL2.po_header_id --JOIN
12541: AND POL1.from_line_id = POL2.po_line_id --JOIN
12542: AND POL1.from_header_id = POH.po_header_id --JOIN
12829: -- total amount released for that contract on this PO
12830: -- SQL Why: Need to calculate the total amount released for the
12831: -- contract and we are calculating current PO and other
12832: -- POs separately because current PO lines (stored in
12833: -- po_lines_gt) may not go to po_lines_all eventually
12834: --
12835: --instead of summing up the Line Loc values. The results should be
12836: --equivalent for SPOs.
12837:
12915: FROM po_lines_gt POL, -- target line
12916: po_headers_all POHA, -- global contract
12917: --bug 9242146, force tables
12918: --to be joined in the order specified.
12919: po_lines_all POL1, -- all lines from other doc with
12920: -- the same GC ref
12921: po_headers_all POH1 -- headers of lines in POL1
12922:
12923: WHERE
14119: -- Note: the value basis decode assumes Milestone Pay Items are Amount
14120: -- Milestones, since payment type is not changeable on Qty-based lines
14121: --
14122: FROM po_line_locations poll
14123: , po_lines_all pol --
14125: p_requested_changes.shipment_changes.parent_line_location_id(i)
14126: AND p_requested_changes.shipment_changes.po_line_location_id(i) IS NULL
14127: AND poll.po_line_id = pol.po_line_id; --
14995: FROM po_lines_merge_v pol
14996: Where pol.po_line_id = l_line_id_tbl(i)
14997: and pol.draft_id = p_draft_id
14998: /*-- CLM Aprvl
14999: FROM po_lines_all pol
15000: WHERE pol.po_line_id = l_line_id_tbl(i)
15001: -- CLM Aprvl*/
15002: --
15003: AND (l_draft_type <> 'PAR'
16288: AND (h.closed_code is NULL or h.closed_code NOT IN ('FINALLY CLOSED'))
16289: AND (h.frozen_flag is NULL or h.frozen_flag <> 'Y')))
16290: AND (p_line_id(i) IS NULL
16291: --Case 3: Optionally, Line is specified
16292: OR EXISTS (SELECT null from po_lines_all l
16293: WHERE l.po_line_id = p_line_id(i)
16294: AND (l.cancel_flag is null or l.cancel_flag <> 'Y')
16295: AND (l.closed_code is NULL or l.closed_code NOT IN ('FINALLY CLOSED'))))
16296: AND (p_line_location_id(i) IS NULL
16522: AND (h.hold_flag is NULL or h.hold_flag <> 'Y')))
16523: AND (p_line_id(i) IS NULL
16524: --Case 3: Optionally, Line is specified
16525: -- Bug 3407980: Modified closed_code condition so that it only discards 'FINALLY CLOSED'
16526: OR EXISTS (SELECT null from po_lines_all l
16527: WHERE l.po_line_id = p_line_id(i)
16528: AND (l.closed_code is NULL or l.closed_code <> 'FINALLY CLOSED')))
16529: AND (p_line_location_id(i) IS NULL
16530: --Case 4: Optionally, Line Location is specified
16691: AND (h.hold_flag is NULL or h.hold_flag <> 'Y')))
16692: AND (p_line_id(i) IS NULL
16693: --Case 3: Optionally, Line is specified
16694: -- Bug 3407980: Modified closed_code condition so that it only discards 'FINALLY CLOSED'
16695: OR EXISTS (SELECT null from po_lines_all l
16696: WHERE l.po_line_id = p_line_id(i)
16697: AND (l.closed_code is NULL or l.closed_code <> 'FINALLY CLOSED')))
16698: AND (p_line_location_id(i) IS NULL
16699: --Case 4: Optionally, Line Location is specified
16862: UPDATE po_headers_gt gt
16863: SET (closed_code, cancel_flag, user_hold_flag)
16864: =
16865: (SELECT nvl(closed_code, 'OPEN'), NVL(cancel_flag, 'N'), NVL(user_hold_flag, 'N')
16866: FROM po_lines_all s
16867: WHERE s.po_line_id = p_line_id(i))
16868: WHERE p_line_id(i) is not null and gt.po_header_id = l_sequence(i)
16869: ;
16870:
17256: , 'PO_SUB_SHIP_BILL_GT_ORD_QTY'
17257: )
17258: FROM
17259: PO_LINE_LOCATIONS_GT POLL
17260: , PO_LINES_ALL POL -- For releases, PO_LINES_GT table isn't populated.
17261: WHERE POLL.po_line_id = POL.po_line_id
17262: AND nvl(POLL.cancel_flag, 'N') = 'N'
17263: AND nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
17264: AND ( --
17344: )
17345: FROM
17346: PO_DISTRIBUTIONS_GT POD
17347: , PO_LINE_LOCATIONS_GT POLL
17348: , PO_LINES_ALL POL -- For releases, PO_LINES_GT table isn't populated.
17349: WHERE POD.line_location_id = POLL.line_location_id
17350: AND POL.po_line_id = POLL.po_line_id
17351: AND nvl(POLL.cancel_flag, 'N') = 'N'
17352: AND nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
17431: )
17432: FROM
17433: PO_DISTRIBUTIONS_GT POD
17434: , PO_LINE_LOCATIONS_GT POLL
17435: , PO_LINES_ALL POL -- For releases, PO_LINES_GT table isn't populated.
17436: WHERE POD.line_location_id = POLL.line_location_id
17437: AND POL.po_line_id = POLL.po_line_id
17438: AND nvl(POLL.cancel_flag,'N') = 'N'
17439: AND nvl(POLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
18415: , l_distribution_num_tbl
18416: FROM
18417: PO_DISTRIBUTIONS_GT POD
18418: , PO_LINE_LOCATIONS_GT POLL
18419: , PO_LINES_ALL POL -- For Releases, PO_LINES_GT doesn't get populated.
18420: , PO_HEADERS_GT POH
18421: WHERE POLL.line_location_id(+) = POD.line_location_id --JOIN
18422: AND POL.po_line_id(+) = POD.po_line_id --JOIN
18423: -- PA distributions don't have associated lines or shipments
20462: FROM po_lines_gt pol
20463: WHERE EXISTS
20464: (
20465: SELECT 'Uncancelled Std PO lines referencing this GA line exist'
20466: FROM po_lines_all pol2
20467: WHERE pol2.from_line_id = pol.po_line_id
20468: AND NVL(pol2.cancel_flag, 'N') = 'N'
20469: AND NVL(pol2.closed_code, 'OPEN') <> 'FINALLY CLOSED'
20470: );
20558: FROM po_headers_gt poh
20559: WHERE EXISTS
20560: (
20561: SELECT 'Open Std PO lines referencing this contract exist'
20562: FROM po_lines_all pol
20563: WHERE pol.contract_id = poh.po_header_id
20564: AND NVL(pol.cancel_flag, 'N') = 'N'
20565: AND NVL(pol.closed_code, 'OPEN') <> 'FINALLY CLOSED'
20566: );
21772: l_consigned_flag,
21773: l_outsourced_assembly,
21774: l_line_location_id
21775: FROM po_line_locations_all poll,
21776: po_lines_all pol,
21777: po_headers_all poh
21778: WHERE poh.po_header_id = pol.po_header_id
21779: AND pol.po_line_id = poll.po_line_id
21780: AND poll.line_location_id = p_line_location_id;
22955: 'PO_LINE_SHIP_UOM_MISMATCH'
22956:
22957: FROM
22958: PO_LINE_LOCATIONS_ALL PLL,
22959: PO_LINES_ALL POL
22960: WHERE POL.PO_LINE_ID=PLL.PO_LINE_ID
22961: AND NVL(PLL.unit_meas_lookup_code,-1) <> NVL(POL.unit_meas_lookup_code,-1)
22962: AND PLL.PO_RELEASE_ID = P_DOCUMENT_ID;
22963:
22993: ),
22994: 'PO_LINE_SHIP_UOM_MISMATCH'
22995: FROM
22996: PO_LINE_LOCATIONS_ALL PLL,
22997: PO_LINES_ALL POL
22998: WHERE POL.PO_LINE_ID=PLL.PO_LINE_ID
22999: AND NVL(PLL.unit_meas_lookup_code,-1)<>NVL(POL.unit_meas_lookup_code,-1)
23000: -- bug 12597958 : bypassing check for amount based lines.
23001: AND (PLL.VALUE_BASIS not in ('FIXED PRICE','AMOUNT') -- Bug 12332819 # Do not consider Advance and Fixed Priced Shipments
24909: l_textline ||g_delim||plc.displayed_field||'.',1,240),
24910: 'PO_PAY_INSTR_CHANGED_TO_MANUAL',
24911: 'W'
24912: FROM po_lines_gt POL,
24913: po_lines_all POL1,
24914: po_lookup_codes plc
24915: WHERE POL.po_header_id = p_document_id
24916: AND POL1.po_header_id = POL.po_header_id
24917: AND POL1.po_line_id = POL.po_line_id