[Home] [Help]
1182:
1183: l_return_status varchar2(1);
1184:
1185: l_conterms_exist_flag VARCHAR2(1); --
1186: l_po_header_id PO_HEADERS_ALL.po_header_id%TYPE; --
1188: -- BELOW CODE FOR CLM PROJECT
1189: l_functional_area_code VARCHAR2(500);
1190: l_document_type_code VARCHAR2(500);
1435: WHERE draft_id = p_draft_id;
1436: ELSE
1437: SELECT clm_contract_officer
1438: INTO l_contracting_officer
1439: FROM po_headers_all
1440: WHERE po_header_id = l_document_id;
1441: END IF; -- end if l_draft_type
1442:
1443: -- No need to call validate_co_warrant in PAR.
1489: -- CLM Controls Project changes
1490: -- Added Org_id in the query
1491: SELECT 'PURCHASING', PH.TYPE_LOOKUP_CODE, PH.STYLE_ID, nvl(PDSH.CLM_FLAG, 'N'), PH.ORG_ID
1492: INTO l_functional_area_code, l_document_type_code, l_document_style_id, l_is_clm_doc, l_org_id
1493: FROM po_headers_all PH, po_doc_style_headers PDSH
1494: WHERE PH.PO_HEADER_ID = l_document_id
1495: AND PH.style_id = PDSH.style_id;
1496:
1497: PO_DEBUG.debug_stmt(l_log_head || l_api_name, l_progress, 'Fetched template details');
1511: -- bug 10389450 start
1512: begin
1513: select 'Y'
1514: into l_is_ord_off_idv
1515: from po_headers_all
1516: where po_header_id = l_document_id and clm_award_type in ('BPA_CALL', 'DELIVERY_ORD');
1517: exception
1518: when no_data_found then
1519: null;
1608: p_sequence +1,
1609: FND_MESSAGE.GET_STRING('PO', 'PO_PRINT_FORM_FORMAT_INVALID'),
1610: 'PO_PRINT_FORM_FORMAT_INVALID',
1611: 'E'
1612: FROM po_headers_all phmv
1613: WHERE phmv.po_header_id = l_document_id
1614: AND NOT EXISTS (
1615: SELECT 1
1616: FROM po_print_form_formats ppff
1814:
1815: SELECT PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(p_template_id => pha.uda_template_id, pk1_value => pha.po_header_id,
1816: pk2_value => -1, p_attr_grp_int_name => 'AWD_CTGR', p_attr_int_name => 'NO_COMP_RSN')
1817: INTO l_no_comp_reason
1818: FROM po_headers_all pha
1819: WHERE pha.po_header_id = l_document_id;
1820:
1821: IF (l_no_comp_reason IS NOT NULL
1822: AND l_no_comp_reason <> 'NONE') THEN
4071: l_dist_qty_tbl NumTab; --
4072: l_dist_amt_tbl NumTab; --
4073:
4074: -- bug 6530879 Releases < Var addition START>
4075: l_agreement_id PO_HEADERS_ALL.po_header_id%TYPE;
4076: l_vendor_id PO_HEADERS_ALL.vendor_id%TYPE;
4077: l_vendor_site_id PO_HEADERS_ALL.vendor_site_id%TYPE;
4078: l_vendor_contact_id PO_HEADERS_ALL.vendor_contact_id%TYPE;
4079: -- bug 6530879 Releases < Var addition END>
4072: l_dist_amt_tbl NumTab; --
4073:
4074: -- bug 6530879 Releases < Var addition START>
4075: l_agreement_id PO_HEADERS_ALL.po_header_id%TYPE;
4076: l_vendor_id PO_HEADERS_ALL.vendor_id%TYPE;
4077: l_vendor_site_id PO_HEADERS_ALL.vendor_site_id%TYPE;
4078: l_vendor_contact_id PO_HEADERS_ALL.vendor_contact_id%TYPE;
4079: -- bug 6530879 Releases < Var addition END>
4080:
4073:
4074: -- bug 6530879 Releases < Var addition START>
4075: l_agreement_id PO_HEADERS_ALL.po_header_id%TYPE;
4076: l_vendor_id PO_HEADERS_ALL.vendor_id%TYPE;
4077: l_vendor_site_id PO_HEADERS_ALL.vendor_site_id%TYPE;
4078: l_vendor_contact_id PO_HEADERS_ALL.vendor_contact_id%TYPE;
4079: -- bug 6530879 Releases < Var addition END>
4080:
4081: --
4074: -- bug 6530879 Releases < Var addition START>
4075: l_agreement_id PO_HEADERS_ALL.po_header_id%TYPE;
4076: l_vendor_id PO_HEADERS_ALL.vendor_id%TYPE;
4077: l_vendor_site_id PO_HEADERS_ALL.vendor_site_id%TYPE;
4078: l_vendor_contact_id PO_HEADERS_ALL.vendor_contact_id%TYPE;
4079: -- bug 6530879 Releases < Var addition END>
4080:
4081: --
4082: l_return_status VARCHAR2(1);
5588: HZ_PARTY_SITES HPS ,
5589: HZ_ORG_CONTACTS HOC ,
5590: HZ_PARTIES HP2 ,
5591: AP_SUPPLIERS APS,
5592: PO_HEADERS_ALL POH
5593: WHERE PVC.PER_PARTY_ID = HP.PARTY_ID
5594: AND PVC.REL_PARTY_ID = HP2.PARTY_ID
5595: AND PVC.ORG_CONTACT_ID = HOC.ORG_CONTACT_ID(+)
5596: AND PVC.RELATIONSHIP_ID = HPR.RELATIONSHIP_ID
8266:
8267: -- CLM Aprvl
8268: Begin
8269: SELECT 'Y' into l_clm_document
8270: FROM po_headers_all h,
8271: po_doc_style_headers s
8272: WHERE h.po_header_id = p_document_id
8273: AND h.style_id = s.style_id
8274: AND Nvl(s.clm_flag,'N') = 'Y';
8694: p_sequence + ROWNUM,
8695: substr(g_linemsg||g_delim||POL.line_num||g_delim||l_textline,1,240),
8696: 'PO_SUB_REF_UNAPPROVED_CONTRACT'
8697: FROM PO_LINES_GT POL,
8698: PO_HEADERS_ALL POC --
8700: AND POL.contract_id = POC.po_header_id --
8701: AND POC.type_lookup_code = 'CONTRACT'
8702: /* R12 GCPA
8755: substr(g_linemsg||g_delim||POL.line_num||g_delim||l_textline,1,240),
8756: 'PO_SUB_LINE_CONTRACT_MISMATCH',
8757: Decode(is_approved_clm_document(p_document_id), 'Y', 'W', NULL)
8758: FROM PO_LINES_GT POL,
8759: PO_HEADERS_ALL POC, --
8761: WHERE POH.po_header_id = p_document_id
8762: AND POL.po_header_id = POH.po_header_id
8763: AND POL.contract_id = POC.po_header_id --
9153: l_textline,1,240),
9154: 'PO_SUB_LINE_CONTRACT_HOLD'
9155: FROM po_lines_gt POL,
9156: po_headers_gt POH,
9157: po_headers_all POC
9158: WHERE POH.po_header_id = p_document_id
9159: AND NVL(POH.authorization_status, 'INCOMPLETE') = 'INCOMPLETE'
9160: AND POL.po_header_id = POH.po_header_id
9161: AND POC.po_header_id = POL.contract_id
9209: l_textline,1,240),
9210: 'PO_SUB_LINE_CONTRACT_EXP'
9211: FROM po_lines_gt POL,
9212: po_headers_gt POH,
9213: po_headers_all POC
9214: WHERE POH.po_header_id = p_document_id
9215: AND NVL(POH.authorization_status, 'INCOMPLETE') = 'INCOMPLETE'
9216: AND POL.po_header_id = POH.po_header_id
9217: AND POC.po_header_id = POL.contract_id
10440: l_textline po_online_report_text.text_line%TYPE := NULL;
10441: l_api_name CONSTANT varchar2(40) := 'CHECK_PLANNED_PO_BLANKET_PA';
10442: l_progress VARCHAR2(3);
10443: /* bug 14196636 */
10444: l_currency_code PO_HEADERS_ALL.CURRENCY_CODE%TYPE;
10445: l_precision NUMBER := null;
10446: l_ext_precision NUMBER := null;
10447: l_min_acct_unit NUMBER := null;
10448: /*end bug 14196636 */
11242: -- Bug 2818810. Added extra join to alias POHA to return 'Y' only if at least
11243: -- one line references a GA.
11244: CURSOR std_ga_ref_cursor(p_document_id NUMBER) IS
11245: SELECT 'Y'
11246: FROM PO_HEADERS_GT POH, PO_LINES_GT POL, PO_HEADERS_ALL POHA
11247: WHERE POH.po_header_id = p_document_id
11248: AND POH.po_header_id = POL.po_header_id
11249: AND POL.from_header_id = POHA.po_header_id
11250: AND POHA.type_lookup_code = 'BLANKET'
11253: --
11254: CURSOR std_gc_ref_cursor (p_doc_id NUMBER) IS
11255: SELECT 'Y'
11256: FROM po_lines_gt POL,
11257: po_headers_all POHA
11258: WHERE POL.po_header_id = p_doc_id
11259: AND POL.contract_id = POHA.po_header_id
11260: AND POHA.global_agreement_flag = 'Y';
11261: --
11779: p_sequence + ROWNUM,
11780: substr(g_linemsg||g_delim||POL.line_num||g_delim
11781: ||l_textline,1,240),
11782: 'PO_SUB_STD_GA_DISABLED'
11783: FROM PO_HEADERS_GT POH1, PO_LINES_GT POL, PO_HEADERS_ALL POH
11784: WHERE POH1.po_header_id = p_document_id
11785: AND POL.po_header_id = POH1.po_header_id
11786: AND POL.from_header_id = POH.po_header_id --JOIN
11787: AND POH.type_lookup_code = 'BLANKET'
11847: p_sequence + ROWNUM,
11848: substr(g_linemsg||g_delim||POL.line_num||g_delim
11849: ||l_textline,1,240),
11850: 'PO_SUB_STD_GA_APPROVED'
11851: FROM PO_HEADERS_GT POH1, PO_LINES_GT POL, PO_HEADERS_ALL POH2
11852: WHERE POH1.po_header_id = p_document_id
11853: AND POL.po_header_id = POH1.po_header_id --JOIN
11854: AND POH2.po_header_id = POL.from_header_id --JOIN
11855: AND POH2.type_lookup_code = 'BLANKET'
11904: p_sequence + ROWNUM,
11905: substr(g_linemsg||g_delim||POL.line_num||g_delim
11906: ||l_textline,1,240),
11907: 'PO_SUB_STD_GA_ON_HOLD'
11908: FROM PO_HEADERS_GT POH1, PO_LINES_GT POL, PO_HEADERS_ALL POH2
11909: WHERE POH1.po_header_id = p_document_id
11910: AND POL.po_header_id = POH1.po_header_id --JOIN
11911: AND POH2.po_header_id = POL.from_header_id --JOIN
11912: AND POH2.type_lookup_code = 'BLANKET'
11968: substr(g_linemsg||g_delim||POL.line_num||g_delim
11969: ||l_textline,1,240),
11970: 'PO_SUB_STD_GA_VENDOR_MISMATCH',
11971: Decode(is_approved_clm_document(p_document_id), 'Y', 'W', NULL)
11972: FROM PO_LINES_GT POL, PO_HEADERS_GT POH1, PO_HEADERS_ALL POH2
11973: WHERE POL.po_header_id = p_document_id
11974: AND POL.po_header_id = POH1.po_header_id --JOIN
11975: AND POL.from_header_id = POH2.po_header_id --JOIN
11976: AND POH2.type_lookup_code = 'BLANKET'
12026: substr(g_linemsg||g_delim||POL.line_num||g_delim
12027: ||l_textline,1,240),
12028: 'PO_SUB_STD_GA_VDR_SITE_MISMT',
12029: Decode(is_approved_clm_document(p_document_id), 'Y', Decode(POH1.vendor_id, POH2.vendor_id, NULL, 'W'), NULL)
12030: FROM PO_LINES_GT POL, PO_HEADERS_GT POH1, PO_HEADERS_ALL POH2
12031: WHERE POL.po_header_id = p_document_id
12032: AND POL.po_header_id = POH1.po_header_id --JOIN
12033: AND POL.from_header_id = POH2.po_header_id --JOIN
12034: AND POH2.type_lookup_code = 'BLANKET'
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
12157: p_sequence + ROWNUM,
12158: substr(g_linemsg||g_delim||POL.line_num||g_delim||
12159: g_shipmsg||g_delim||PLL.shipment_num||g_delim||l_textline,1,240),
12160: 'PO_SUB_STD_BEFORE_GA_DATE'
12161: FROM PO_LINES_GT POL, PO_HEADERS_GT POH, PO_HEADERS_ALL POH2, PO_LINE_LOCATIONS_GT PLL
12162: WHERE POL.po_header_id = p_document_id
12163: AND POL.po_header_id = POH.po_header_id
12164: AND PLL.po_line_id = POL.po_line_id --JOIN
12165: AND POL.from_header_id = POH2.po_header_id --JOIN
12211: p_sequence + ROWNUM,
12212: substr(g_linemsg||g_delim||POL.line_num||g_delim
12213: ||l_textline,1,240),
12214: 'PO_SUB_STD_GA_CURR_MISMATCH'
12215: FROM PO_LINES_GT POL, PO_HEADERS_GT POH1, PO_HEADERS_ALL POH2
12216: WHERE POL.po_header_id = p_document_id
12217: AND POL.po_header_id = POH1.po_header_id --JOIN
12218: AND POL.from_header_id = POH2.po_header_id --JOIN
12219: AND POH2.type_lookup_code = 'BLANKET'
12282: )
12283: , 0 )
12284: , POH1.amount_limit
12285: BULK COLLECT INTO l_curr_doc_line_num,l_po_amount,l_amount_limit
12286: FROM PO_LINES_GT POL, PO_HEADERS_ALL POH1, PO_LINE_LOCATIONS_GT PLL1
12287: WHERE PLL1.from_header_id = POL.from_header_id
12288: AND POL.po_header_id = p_document_id
12289: AND POL.from_header_id = POH1.po_header_id
12290: AND POH1.type_lookup_code = 'BLANKET'
12316: * PLL2.price_override )
12317: )
12318: , 0 )
12319: BULK COLLECT INTO l_prev_doc_line_num, l_prev_rel_amount
12320: FROM PO_LINE_LOCATIONS_ALL PLL2, PO_HEADERS_ALL POH1,
12321: PO_HEADERS_ALL POH2, PO_LINES_GT POL
12322: WHERE POL.po_header_id = p_document_id
12323: AND POL.from_header_id = POH1.po_header_id --JOIN
12324: AND POH1.type_lookup_code = 'BLANKET'
12317: )
12318: , 0 )
12319: BULK COLLECT INTO l_prev_doc_line_num, l_prev_rel_amount
12320: FROM PO_LINE_LOCATIONS_ALL PLL2, PO_HEADERS_ALL POH1,
12321: PO_HEADERS_ALL POH2, PO_LINES_GT POL
12322: WHERE POL.po_header_id = p_document_id
12323: AND POL.from_header_id = POH1.po_header_id --JOIN
12324: AND POH1.type_lookup_code = 'BLANKET'
12325: AND POH1.global_agreement_flag = 'Y'
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
12705: 'PA_SUB_STD_GC_NOT_EN_PUR'
12706: FROM
12707: po_headers_gt POH,
12708: po_lines_gt POL,
12709: po_headers_all POHA
12710: WHERE
12711: POH.po_header_id = p_document_id
12712: AND POL.po_header_id = POH.po_header_id
12713: AND POL.contract_id = POHA.po_header_id
12781: Decode(is_approved_clm_document(p_document_id), 'Y', Decode(POH.vendor_id, POHA.vendor_id, NULL, 'W'), NULL)
12782: FROM
12783: po_headers_gt POH,
12784: po_lines_gt POL,
12785: po_headers_all POHA
12786: WHERE
12787: POH.po_header_id = p_document_id
12788: AND POL.po_header_id = POH.po_header_id
12789: AND POL.contract_id = POHA.po_header_id
12817: -- Removed Check 3 as different currencies are being allowed if OU is same.
12818: -- Renumbered Check 4 as Check 3.
12819: -- In the new Check 3, Removed the IF condition as currency mismatch is allowed.
12820: -- Also multiplied amount values by rate for handling currency mismatch.
12821: -- Added a join with po_headers_all POHA1 for getting rate.
12822:
12823: -- Check 3: Amount Released should not exceed the amount limit on the GC
12824:
12825: -- bug3251561
12858: l_current_different,
12859: l_current_rate,
12860: l_contract_rate
12861: FROM po_lines_gt POL, -- target_line
12862: po_headers_all POHA, -- global contract
12863: po_lines_gt POL1, -- all lines in current doc
12864: -- having the same contract ref
12865: po_headers_all POHA1 -- document
12866: WHERE
12861: FROM po_lines_gt POL, -- target_line
12862: po_headers_all POHA, -- global contract
12863: po_lines_gt POL1, -- all lines in current doc
12864: -- having the same contract ref
12865: po_headers_all POHA1 -- document
12866: WHERE
12867: POL.po_header_id = p_document_id
12868: AND POL.po_header_id = POHA1.po_header_id
12869: AND POL.contract_id = POHA.po_header_id
12912: l_prev_rel_amount,
12913: l_prev_different,
12914: l_prev_rate
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
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
12924: POL.po_header_id = p_document_id
12925: AND POL.contract_id = POHA.po_header_id
13055: x_sequence + ROWNUM,
13056: substr(g_linemsg||g_delim||POL.line_num||g_delim
13057: ||l_textline,1,240),
13058: 'PO_SUB_STD_AFTER_GA_DATE'
13059: FROM PO_LINES_GT POL, PO_HEADERS_GT POH, PO_HEADERS_ALL POH2, PO_LINE_LOCATIONS_GT PLL
13060: WHERE POL.po_header_id = p_document_id
13061: AND POL.po_header_id = POH.po_header_id
13062: AND PLL.po_line_id = POL.po_line_id
13063: AND POL.contract_id = POH2.po_header_id
16270: FROM DUAL
16271: WHERE (p_release_id(i) IS NOT NULL
16272: --Case 1: No Release is specified, PO Header has to be specified
16273: -- Through any of HeaderId, DocNum-and-SubType, or VendorOrderNum
16274: OR EXISTS (select null from po_headers_all h
16275: WHERE h.po_header_id = l_header_id(i)
16276: AND (h.authorization_status is NULL
16277: OR h.authorization_status NOT IN ('PRE-APPROVED', 'IN PROCESS'))
16278: AND (h.cancel_flag is null or h.cancel_flag <> 'Y')
16497: WHERE (p_release_id(i) IS NOT NULL
16498: --Case 1: No Release is specified, PO Header has to be specified
16499: -- Through any of HeaderId, DocNum-and-SubType, or VendorOrderNum
16500: -- Bug 3407980: Modified closed_code condition so that it only discards 'FINALLY CLOSED'
16501: OR EXISTS (select null from po_headers_all h
16502: WHERE h.po_header_id = l_header_id(i)
16503: AND (h.authorization_status is NULL
16504: OR h.authorization_status NOT IN ('APPROVED')
16505: /* <
16675: WHERE (p_release_id(i) IS NOT NULL
16676: --Case 1: No Release is specified, PO Header has to be specified
16677: -- Through any of HeaderId, DocNum-and-SubType, or VendorOrderNum
16678: -- Bug 3407980: Modified closed_code condition so that it only discards 'FINALLY CLOSED'
16679: OR EXISTS (select null from po_headers_all h
16680: WHERE h.po_header_id = l_header_id(i)
16681: AND (h.closed_code is NULL or h.closed_code <> 'FINALLY CLOSED')
16682: AND (h.frozen_flag is NULL or h.frozen_flag <> 'Y')
16683: AND (h.user_hold_flag is NULL or h.user_hold_flag <> 'Y')))
16812:
16813: -- For all the entities with null p_release_id(i), get Header status fields into
16814: -- global temprary table while storing sequence into po_headers_gt.PO_HEADER_ID column
16815: l_progress := '030';
16816: -- bug 4931241 modified the sql query to avoid FTS on PO_HEADERS_ALL
16817: -- Removed the where clause for document num and vendor_order_num
16818: -- They do not uniquely identify the document. Added validation in group package
16819: FORALL i IN 1..p_count
16820: INSERT
16829: SEGMENT1, SUMMARY_FLAG, ENABLED_FLAG,
16830: NVL(authorization_status, 'INCOMPLETE'), nvl(approved_flag, 'N'),
16831: nvl(closed_code, 'OPEN'), NVL(cancel_flag, 'N'),
16832: NVL(frozen_flag, 'N'), NVL(user_hold_flag, 'N'), l_sequence(i)
16833: FROM po_headers_all h
16834: WHERE p_release_id(i) is null
16835: AND h.po_header_id = p_header_id(i)
16836: ;
16837:
18700: -- set x_header_id(i) as -999, indicating that po_header_Id
18701: -- cannot be derived by the IN paramters
18702: SELECT NVL(MIN(po_header_id), '-999')
18703: INTO x_header_id(i)
18704: FROM po_headers_all
18705: WHERE vendor_order_num = p_vendor_order_num(i)
18706: AND po_header_id = NVL(x_header_id(i), po_header_id);
18707:
18708: IF g_debug_stmt THEN
18810: -- SQL Why: need to check vendor site registration status below
18811: BEGIN
18812: SELECT vendor_id, vendor_site_id
18813: INTO l_vendor_id, l_vendor_site_id
18814: FROM po_headers_all
18815: WHERE po_header_id = p_document_id;
18816: EXCEPTION
18817: WHEN OTHERS THEN
18818: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
20882:
20883: -- Get the currency code and precision
20884: SELECT poh.currency_code
20885: INTO l_currency_code
20886: FROM po_headers_all poh
20887: WHERE poh.po_header_id = p_document_id;
20888:
20889: PO_CORE_S2.get_currency_info(
20890: x_currency_code => l_currency_code
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;
21781: EXCEPTION
21908: IF (p_draft_id IS NULL or p_draft_id = -1) THEN
21909:
21910: SELECT PHA.VENDOR_SITE_ID
21911: INTO l_vendor_site_id
21912: FROM PO_HEADERS_ALL PHA
21913: WHERE PHA.PO_HEADER_ID = p_header_id;
21914:
21915: ELSE
21916: SELECT PHA.VENDOR_SITE_ID
22020: msg_type VARCHAR2(50) := NULL;
22021: l_isCompletedCount NUMBER := 0;
22022: l_warrant_enabled_flag VARCHAR2(1) := 'N';
22023: l_valid_co VARCHAR2(1) := 'N';
22024: l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
22025: l_warrant_found boolean := true;
22026: l_warrant_amount NUMBER;
22027: l_doc_amount NUMBER;
22028: l_warrant_currency VARCHAR2(15);
22029: l_doc_currency VARCHAR2(15);
22030: l_ko_sign_required varchar2(1);
22031: l_currency_code VARCHAR2(15);
22032: l_rate_type VARCHAR2(25);
22033: l_rate PO_HEADERS_ALL.rate%TYPE;
22034: l_rate_date DATE;
22035: l_mau NUMBER;
22036: l_curr_status VARCHAR2(1);
22037: l_org_id NUMBER; -- CLM Controls Project changes
22190:
22191: SELECT 'Y'
22192: INTO l_warrant_enabled_flag
22193: FROM po_doc_style_headers pdsh,
22194: po_headers_all pha
22195: WHERE pha.po_header_id = p_header_id
22196: AND pha.style_id = pdsh.style_id
22197: AND pdsh.warrant_type is not NULL;
22198: EXCEPTION
22215: FROM po_warrant_lines pwl,
22216: po_user_warrants puw,
22217: po_doc_style_headers pdsh,
22218: po_agents pa,
22219: po_headers_all phm
22220: WHERE phm.po_header_id = p_header_id
22221: AND phm.style_id = pdsh.style_id
22222: AND p_contracting_officer = pa.agent_id
22223: AND pa.warrant_id = puw.warrant_id
22243: FROM po_warrant_lines pwl,
22244: po_user_warrants puw,
22245: po_doc_style_headers pdsh,
22246: po_agents pa,
22247: po_headers_all phm
22248: WHERE phm.po_header_id = p_header_id
22249: AND phm.style_id = pdsh.style_id
22250: AND p_contracting_officer = pa.agent_id
22251: AND pa.warrant_id = puw.warrant_id
22304: IF p_draft_id is NULL or p_draft_id = -1 THEN
22305: -- Finding the document amount, agreed amount in the case of PA
22306: SELECT nvl(BLANKET_TOTAL_AMOUNT, 0)
22307: INTO l_doc_amount
22308: FROM po_headers_all phm
22309: WHERE phm.po_header_id = p_header_id;
22310: ELSE
22311: -- Finding the mod amount, agreed amount in the case of PA
22312: SELECT nvl(BLANKET_TOTAL_AMOUNT, 0)
23053: -- check if doc is CLM Modification or CLM Reapproval Base document
23054: SELECT 'Y'
23055: INTO l_is_approved_clm_document
23056: FROM po_headers_gt POH1,
23057: po_headers_all POH,
23058: po_doc_style_headers PDSH
23059: WHERE POH1.po_header_id = p_document_id
23060: AND POH1.po_header_id = POH.po_header_id
23061: AND POH.style_id = PDSH.style_id
24864: SUBSTR ( l_textline ||g_delim||plc.displayed_field||'.',1,240),
24865: 'PO_PAY_INSTR_CHANGED_TO_MANUAL',
24866: 'W'
24867: FROM po_headers_gt POH,
24868: po_headers_all POH1,
24869: po_lookup_codes plc
24870: WHERE POH.po_header_id = p_document_id
24871: AND POH1.po_header_id = POH.po_header_id
24872: AND NVL(POH.clm_payment_instr_code, 'null') <> NVL(POH1.clm_payment_instr_code, 'null')
26572: ELSE
26573: Nvl(pod.changed_amt,0)
26574: END) changed_amt -- Bug 16713804
26575: INTO l_okc_doc_type,l_changed_amt
26576: FROM po_headers_draft_all phda, po_headers_all phd,
26577: po_drafts pod
26578: WHERE phda.po_header_id = p_document_id
26579: AND phd.po_header_id = phda.po_header_id
26580: AND phda.draft_id = p_draft_id