[Home] [Help]
397: SELECT revision_num
398: ,start_date
399: ,end_date
400: , nvl(draft_id, -1) -- Mod Project
401: FROM po_headers_gt
402: WHERE po_header_id = p_document_id;
403:
404: -- contracts dependency
405: SUBTYPE qa_result_tbl_type IS OKC_TERMS_QA_GRP.qa_result_tbl_type;
409: SUBTYPE Event_tbl_type IS OKC_TERMS_QA_GRP.busdocdates_tbl_type;
410: l_event_tbl Event_tbl_type;
411:
412:
413: l_revision_num po_headers.revision_num%TYPE;
414: l_authorization_status po_headers.authorization_status%TYPE;
415: l_approved_flag po_headers.approved_flag%TYPE;
416: l_po_start_date po_headers.start_date%TYPE;
417: l_po_end_date po_headers.end_date%TYPE;
410: l_event_tbl Event_tbl_type;
411:
412:
413: l_revision_num po_headers.revision_num%TYPE;
414: l_authorization_status po_headers.authorization_status%TYPE;
415: l_approved_flag po_headers.approved_flag%TYPE;
416: l_po_start_date po_headers.start_date%TYPE;
417: l_po_end_date po_headers.end_date%TYPE;
418:
411:
412:
413: l_revision_num po_headers.revision_num%TYPE;
414: l_authorization_status po_headers.authorization_status%TYPE;
415: l_approved_flag po_headers.approved_flag%TYPE;
416: l_po_start_date po_headers.start_date%TYPE;
417: l_po_end_date po_headers.end_date%TYPE;
418:
419: l_draft_id NUMBER; -- Mod Project
412:
413: l_revision_num po_headers.revision_num%TYPE;
414: l_authorization_status po_headers.authorization_status%TYPE;
415: l_approved_flag po_headers.approved_flag%TYPE;
416: l_po_start_date po_headers.start_date%TYPE;
417: l_po_end_date po_headers.end_date%TYPE;
418:
419: l_draft_id NUMBER; -- Mod Project
420: l_document_id NUMBER; --Mod Project
413: l_revision_num po_headers.revision_num%TYPE;
414: l_authorization_status po_headers.authorization_status%TYPE;
415: l_approved_flag po_headers.approved_flag%TYPE;
416: l_po_start_date po_headers.start_date%TYPE;
417: l_po_end_date po_headers.end_date%TYPE;
418:
419: l_draft_id NUMBER; -- Mod Project
420: l_document_id NUMBER; --Mod Project
421: l_api_name CONSTANT VARCHAR2(30) := 'Check Terms';
565:
566: l_doc_type VARCHAR2(30);
567: l_textline po_online_report_text.text_line%TYPE := NULL;
568:
569: l_revision_num po_headers.revision_num%TYPE;
570: l_authorization_status po_headers.authorization_status%TYPE;
571: l_approved_flag po_headers.approved_flag%TYPE;
572: l_po_start_date po_headers.start_date%TYPE;
573: l_po_end_date po_headers.end_date%TYPE;
566: l_doc_type VARCHAR2(30);
567: l_textline po_online_report_text.text_line%TYPE := NULL;
568:
569: l_revision_num po_headers.revision_num%TYPE;
570: l_authorization_status po_headers.authorization_status%TYPE;
571: l_approved_flag po_headers.approved_flag%TYPE;
572: l_po_start_date po_headers.start_date%TYPE;
573: l_po_end_date po_headers.end_date%TYPE;
574:
567: l_textline po_online_report_text.text_line%TYPE := NULL;
568:
569: l_revision_num po_headers.revision_num%TYPE;
570: l_authorization_status po_headers.authorization_status%TYPE;
571: l_approved_flag po_headers.approved_flag%TYPE;
572: l_po_start_date po_headers.start_date%TYPE;
573: l_po_end_date po_headers.end_date%TYPE;
574:
575:
568:
569: l_revision_num po_headers.revision_num%TYPE;
570: l_authorization_status po_headers.authorization_status%TYPE;
571: l_approved_flag po_headers.approved_flag%TYPE;
572: l_po_start_date po_headers.start_date%TYPE;
573: l_po_end_date po_headers.end_date%TYPE;
574:
575:
576: CURSOR c_po_status (p_document_id NUMBER) IS
569: l_revision_num po_headers.revision_num%TYPE;
570: l_authorization_status po_headers.authorization_status%TYPE;
571: l_approved_flag po_headers.approved_flag%TYPE;
572: l_po_start_date po_headers.start_date%TYPE;
573: l_po_end_date po_headers.end_date%TYPE;
574:
575:
576: CURSOR c_po_status (p_document_id NUMBER) IS
577: SELECT revision_num
577: SELECT revision_num
578: ,start_date
579: ,end_date
580: , nvl(draft_id, -1) -- Mod Project
581: FROM po_headers_gt
582: WHERE po_header_id = p_document_id;
583:
584: CURSOR c_vendor_check_params IS
585: SELECT ENABLE_EPLS , Trunc(EPLS_TIMEFRAME)
767:
768: l_doc_type VARCHAR2(30);
769: l_textline po_online_report_text.text_line%TYPE := NULL;
770:
771: l_revision_num po_headers.revision_num%TYPE;
772: l_authorization_status po_headers.authorization_status%TYPE;
773: l_approved_flag po_headers.approved_flag%TYPE;
774: l_po_start_date po_headers.start_date%TYPE;
775: l_po_end_date po_headers.end_date%TYPE;
768: l_doc_type VARCHAR2(30);
769: l_textline po_online_report_text.text_line%TYPE := NULL;
770:
771: l_revision_num po_headers.revision_num%TYPE;
772: l_authorization_status po_headers.authorization_status%TYPE;
773: l_approved_flag po_headers.approved_flag%TYPE;
774: l_po_start_date po_headers.start_date%TYPE;
775: l_po_end_date po_headers.end_date%TYPE;
776:
769: l_textline po_online_report_text.text_line%TYPE := NULL;
770:
771: l_revision_num po_headers.revision_num%TYPE;
772: l_authorization_status po_headers.authorization_status%TYPE;
773: l_approved_flag po_headers.approved_flag%TYPE;
774: l_po_start_date po_headers.start_date%TYPE;
775: l_po_end_date po_headers.end_date%TYPE;
776:
777:
770:
771: l_revision_num po_headers.revision_num%TYPE;
772: l_authorization_status po_headers.authorization_status%TYPE;
773: l_approved_flag po_headers.approved_flag%TYPE;
774: l_po_start_date po_headers.start_date%TYPE;
775: l_po_end_date po_headers.end_date%TYPE;
776:
777:
778: CURSOR c_po_status (p_document_id NUMBER) IS
771: l_revision_num po_headers.revision_num%TYPE;
772: l_authorization_status po_headers.authorization_status%TYPE;
773: l_approved_flag po_headers.approved_flag%TYPE;
774: l_po_start_date po_headers.start_date%TYPE;
775: l_po_end_date po_headers.end_date%TYPE;
776:
777:
778: CURSOR c_po_status (p_document_id NUMBER) IS
779: SELECT revision_num
779: SELECT revision_num
780: ,start_date
781: ,end_date
782: , nvl(draft_id, -1) -- Mod Project
783: FROM po_headers_gt
784: WHERE po_header_id = p_document_id;
785:
786:
787: CURSOR c_wdol_params IS
923:
924: l_doc_type VARCHAR2(30);
925: l_textline po_online_report_text.text_line%TYPE := NULL;
926:
927: l_revision_num po_headers.revision_num%TYPE;
928: l_authorization_status po_headers.authorization_status%TYPE;
929: l_approved_flag po_headers.approved_flag%TYPE;
930: l_po_start_date po_headers.start_date%TYPE;
931: l_po_end_date po_headers.end_date%TYPE;
924: l_doc_type VARCHAR2(30);
925: l_textline po_online_report_text.text_line%TYPE := NULL;
926:
927: l_revision_num po_headers.revision_num%TYPE;
928: l_authorization_status po_headers.authorization_status%TYPE;
929: l_approved_flag po_headers.approved_flag%TYPE;
930: l_po_start_date po_headers.start_date%TYPE;
931: l_po_end_date po_headers.end_date%TYPE;
932:
925: l_textline po_online_report_text.text_line%TYPE := NULL;
926:
927: l_revision_num po_headers.revision_num%TYPE;
928: l_authorization_status po_headers.authorization_status%TYPE;
929: l_approved_flag po_headers.approved_flag%TYPE;
930: l_po_start_date po_headers.start_date%TYPE;
931: l_po_end_date po_headers.end_date%TYPE;
932:
933:
926:
927: l_revision_num po_headers.revision_num%TYPE;
928: l_authorization_status po_headers.authorization_status%TYPE;
929: l_approved_flag po_headers.approved_flag%TYPE;
930: l_po_start_date po_headers.start_date%TYPE;
931: l_po_end_date po_headers.end_date%TYPE;
932:
933:
934: CURSOR c_po_status (p_document_id NUMBER) IS
927: l_revision_num po_headers.revision_num%TYPE;
928: l_authorization_status po_headers.authorization_status%TYPE;
929: l_approved_flag po_headers.approved_flag%TYPE;
930: l_po_start_date po_headers.start_date%TYPE;
931: l_po_end_date po_headers.end_date%TYPE;
932:
933:
934: CURSOR c_po_status (p_document_id NUMBER) IS
935: SELECT revision_num
935: SELECT revision_num
936: ,start_date
937: ,end_date
938: , nvl(draft_id, -1) -- Mod Project
939: FROM po_headers_gt
940: WHERE po_header_id = p_document_id;
941:
942: CURSOR c_eda_params IS
943: SELECT ENABLE_EDA
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
2894: -- SQL why : need to check before calling contracts qa
2895: -- SQL join: po_header_id
2896: SELECT conterms_exist_flag
2897: INTO l_conterms_exist_flag
2898: FROM po_headers_gt
2899: WHERE po_header_id = l_document_id;
2900:
2901: IF (NVL(l_conterms_exist_flag, 'N')='Y') THEN
2902: check_terms(
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);
4134: 0,0,0,
4135: p_sequence + ROWNUM,
4136: substr(l_textline,1,240),
4137: 'PO_SUB_REL_PA_APPROVED'
4138: FROM PO_RELEASES_GT POR,PO_HEADERS_GT POH
4139: WHERE POR.po_header_id = POH.po_header_id
4140: AND POR.po_release_id = p_document_id
4141: AND NVL(POH.approved_flag, 'N') <> 'Y'
4142: AND NVL(POH.user_hold_flag, 'N') <> 'Y'; --< Bug 3422733 >
4193: 0,
4194: p_sequence + ROWNUM,
4195: substr(l_textline,1,240),
4196: 'PO_SUB_REL_PA_EXPIRED' -- bug3199869
4197: FROM PO_RELEASES_GT POR,PO_HEADERS_GT POH
4198: WHERE POR.po_header_id = POH.po_header_id
4199: AND POR.po_release_id = p_document_id
4200: AND POR.approved_date IS NULL -- bug3199869
4201: AND TRUNC(SYSDATE) > -- bug3199869
4241: 0,
4242: p_sequence + ROWNUM,
4243: substr(l_textline,1,240),
4244: 'PO_SUB_REL_PA_ON_HOLD'
4245: FROM PO_RELEASES_GT POR,PO_HEADERS_GT POH
4246: WHERE POR.po_header_id = POH.po_header_id
4247: AND POR.po_release_id = p_document_id
4248: AND nvl(POH.user_hold_flag, 'N') = 'Y';
4249:
4287: 0,
4288: p_sequence + ROWNUM,
4289: substr(l_textline,1,240),
4290: 'PO_SUB_REL_VENDOR_ON_HOLD'
4291: FROM PO_RELEASES_GT POR,PO_HEADERS_GT POH,PO_VENDORS POV,
4292: PO_SYSTEM_PARAMETERS PSP
4293: WHERE POR.po_header_id = POH.po_header_id
4294: AND POV.vendor_id = POH.vendor_id
4295: AND POR.po_release_id = p_document_id
4695: ||l_textline,1,240),
4696: 'PO_SUB_REL_RATE_NULL'
4697: FROM PO_DISTRIBUTIONS_GT POD, --
4698: PO_LINE_LOCATIONS_GT POLL,PO_LINES POL,
4699: PO_RELEASES_GT POR,PO_HEADERS_GT POH,GL_SETS_OF_BOOKS SOB,
4700: FINANCIALS_SYSTEM_PARAMETERS FSP
4701: WHERE POLL.po_release_id = POR.po_release_id
4702: AND POD.line_location_id = POLL.line_location_id
4703: AND POLL.po_line_id = POL.po_line_id
4750: 0,
4751: p_sequence + ROWNUM,
4752: substr(l_textline,1,240),
4753: 'PO_SUB_REL_AMT_LESS_MINREL_AMT'
4754: FROM PO_HEADERS_GT POH,PO_RELEASES_GT POR
4755: WHERE POR.po_release_id = p_document_id
4756: AND POH.po_header_id = POR.po_header_id
4757: AND POH.min_release_amount IS NOT NULL
4758: AND POH.min_release_amount > --
4807: , ( ( PLL1.quantity
4808: - nvl(PLL1.quantity_cancelled,0) )
4809: * PLL1.price_override ) ) )
4810: INTO l_this_rel_amount
4811: FROM PO_LINE_LOCATIONS_GT PLL1, PO_RELEASES_GT POR1, PO_HEADERS_GT POH
4812: WHERE POR1.po_release_id = p_document_id
4813: AND POR1.po_header_id = POH.po_header_id -- Bug 7188760
4814: AND POH.amount_limit is Not Null -- Bug 7188760
4815: AND PLL1.po_release_id = POR1.po_release_id;
4832: - nvl(PLL2.quantity_cancelled,0) )
4833: * PLL2.price_override ) ) )
4834: , 0 )
4835: INTO l_previous_rel_amount
4836: FROM PO_LINE_LOCATIONS PLL2, PO_RELEASES POR2, PO_HEADERS_GT POH
4837: WHERE PLL2.po_release_id = POR2.po_release_id
4838: AND POR2.po_header_id = POH.po_header_id
4839: AND nvl(POR2.approved_flag, 'N') = 'Y'
4840: AND POH.amount_limit is Not Null -- Bug 7188760
4853: - nvl(PLL2.quantity_cancelled,0) )
4854: * PLL2.price_override ) ) )
4855: , 0 )
4856: INTO l_previous_rel_archive_amount
4857: FROM po_line_locations_archive_all PLL2, po_releases_all POR2, PO_HEADERS_GT POH
4858: WHERE PLL2.po_release_id = POR2.po_release_id
4859: AND POR2.po_header_id = POH.po_header_id
4860: AND nvl(POR2.approved_flag, 'N') IN ('R','F')
4861: and NVL(PLL2.LATEST_EXTERNAL_FLAG,'N') = 'Y'
4893: 0,
4894: p_sequence + ROWNUM,
4895: substr(l_textline,1,240),
4896: 'PO_SUB_REL_AMT_GRT_LIMIT_AMT'
4897: FROM PO_HEADERS_GT POH,PO_RELEASES_GT POR
4898: WHERE POR.po_release_id = p_document_id
4899: AND POH.po_header_id = POR.po_header_id
4900:
4901: AND POH.amount_limit is not null
5142: ||l_textline,1,240),
5143: --
5144: 'PO_SUB_ITEM_NOT_APPROVED_REL'
5145: FROM MTL_SYSTEM_ITEMS MSI, PO_LINE_LOCATIONS_GT PLL,
5146: PO_RELEASES_GT POR,PO_LINES POL, PO_HEADERS_GT POH,
5147: FINANCIALS_SYSTEM_PARAMETERS FSP
5148: WHERE POR.po_release_id = p_document_id
5149: AND POR.po_header_id = POH.po_header_id
5150: AND POR.po_header_id = POL.po_header_id
5244: ||l_textline,1,240),
5245: --
5246: 'PO_SUB_ITEM_ASL_DEBARRED_REL'
5247: FROM PO_LINE_LOCATIONS_GT PLL,
5248: PO_RELEASES_GT POR,PO_LINES POL, PO_HEADERS_GT POH,
5249: FINANCIALS_SYSTEM_PARAMETERS FSP
5250: WHERE POR.po_release_id = p_document_id
5251: AND POR.po_header_id = POH.po_header_id
5252: AND POR.po_header_id = POL.po_header_id
5447: ---------------------------------------------------------------------------------
5448: -- bug 6530879 Releases
5449: select poh.po_header_id,poh.vendor_id, poh.vendor_site_id, poh.vendor_contact_id
5450: into l_agreement_id, l_vendor_id,l_vendor_site_id,l_vendor_contact_id
5451: from po_headers_gt poh, po_releases_gt por
5452: where poh.po_header_id = por.po_header_id
5453: and por.po_release_id = p_document_id;
5454: -- bug 6530879
5455: -- Check 20: Vendor should be valid when approving the document.
5489: substr(l_textline,1,240),
5490: 'PO_PDOI_INVALID_VENDOR'
5491: FROM dual
5492: where not exists (select 'Y'
5493: from PO_HEADERS_GT POH, po_vendors pov
5494: WHERE POH.po_header_id = l_agreement_id
5495: AND pov.vendor_id = poh.vendor_id
5496: AND pov.enabled_flag = 'Y'
5497: AND SYSDATE BETWEEN nvl(pov.start_date_active, SYSDATE-1)
5534: substr(l_textline,1,240),
5535: 'PO_PDOI_INVALID_VENDOR_SITE'
5536: FROM dual
5537: where not exists (select 'Y'
5538: from PO_HEADERS_GT POH, po_vendor_sites povs
5539: WHERE POH.po_header_id = l_agreement_id
5540: AND povs.vendor_site_id = poh.vendor_site_id
5541: AND nvl(povs.rfq_only_site_flag,'N') <> 'Y'
5542: AND povs.purchasing_site_flag = 'Y'
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
6328: BEGIN
6329: l_progress := '006';
6330: SELECT POH.currency_code, nvl(trunc(POH.rate_date), trunc(POLL.creation_date))
6331: INTO l_po_curr, l_rate_date
6332: FROM PO_HEADERS POH, PO_LINE_LOCATIONS_GT POLL
6333: WHERE POLL.line_location_id = l_line_location_id(shipment_line)
6334: AND POLL.po_header_id = POH.po_header_id;
6335: EXCEPTION
6336: WHEN OTHERS THEN
7142: l_textline po_online_report_text.text_line%TYPE := NULL;
7143: l_api_name CONSTANT varchar2(40) := 'CHECK_PO_PA_HEADER';
7144: l_progress VARCHAR2(3);
7145:
7146: l_vendor_id po_headers.vendor_id%TYPE;
7147: l_vendor_site_id po_headers.vendor_site_id%TYPE;
7148: l_vendor_contact_id po_headers.vendor_contact_id%TYPE; /*bug 6530879*/
7149: l_ship_to_location_id po_headers.ship_to_location_id%TYPE;
7150: l_bill_to_location_id po_headers.bill_to_location_id%TYPE;
7143: l_api_name CONSTANT varchar2(40) := 'CHECK_PO_PA_HEADER';
7144: l_progress VARCHAR2(3);
7145:
7146: l_vendor_id po_headers.vendor_id%TYPE;
7147: l_vendor_site_id po_headers.vendor_site_id%TYPE;
7148: l_vendor_contact_id po_headers.vendor_contact_id%TYPE; /*bug 6530879*/
7149: l_ship_to_location_id po_headers.ship_to_location_id%TYPE;
7150: l_bill_to_location_id po_headers.bill_to_location_id%TYPE;
7151: l_currency_code po_headers.currency_code%TYPE;
7144: l_progress VARCHAR2(3);
7145:
7146: l_vendor_id po_headers.vendor_id%TYPE;
7147: l_vendor_site_id po_headers.vendor_site_id%TYPE;
7148: l_vendor_contact_id po_headers.vendor_contact_id%TYPE; /*bug 6530879*/
7149: l_ship_to_location_id po_headers.ship_to_location_id%TYPE;
7150: l_bill_to_location_id po_headers.bill_to_location_id%TYPE;
7151: l_currency_code po_headers.currency_code%TYPE;
7152: l_sob_currency_code po_headers.currency_code%TYPE;
7145:
7146: l_vendor_id po_headers.vendor_id%TYPE;
7147: l_vendor_site_id po_headers.vendor_site_id%TYPE;
7148: l_vendor_contact_id po_headers.vendor_contact_id%TYPE; /*bug 6530879*/
7149: l_ship_to_location_id po_headers.ship_to_location_id%TYPE;
7150: l_bill_to_location_id po_headers.bill_to_location_id%TYPE;
7151: l_currency_code po_headers.currency_code%TYPE;
7152: l_sob_currency_code po_headers.currency_code%TYPE;
7153: l_rate_type po_headers.rate_type%TYPE;
7146: l_vendor_id po_headers.vendor_id%TYPE;
7147: l_vendor_site_id po_headers.vendor_site_id%TYPE;
7148: l_vendor_contact_id po_headers.vendor_contact_id%TYPE; /*bug 6530879*/
7149: l_ship_to_location_id po_headers.ship_to_location_id%TYPE;
7150: l_bill_to_location_id po_headers.bill_to_location_id%TYPE;
7151: l_currency_code po_headers.currency_code%TYPE;
7152: l_sob_currency_code po_headers.currency_code%TYPE;
7153: l_rate_type po_headers.rate_type%TYPE;
7154: l_rate po_headers.rate%TYPE;
7147: l_vendor_site_id po_headers.vendor_site_id%TYPE;
7148: l_vendor_contact_id po_headers.vendor_contact_id%TYPE; /*bug 6530879*/
7149: l_ship_to_location_id po_headers.ship_to_location_id%TYPE;
7150: l_bill_to_location_id po_headers.bill_to_location_id%TYPE;
7151: l_currency_code po_headers.currency_code%TYPE;
7152: l_sob_currency_code po_headers.currency_code%TYPE;
7153: l_rate_type po_headers.rate_type%TYPE;
7154: l_rate po_headers.rate%TYPE;
7155: l_rate_date po_headers.rate_date%TYPE;
7148: l_vendor_contact_id po_headers.vendor_contact_id%TYPE; /*bug 6530879*/
7149: l_ship_to_location_id po_headers.ship_to_location_id%TYPE;
7150: l_bill_to_location_id po_headers.bill_to_location_id%TYPE;
7151: l_currency_code po_headers.currency_code%TYPE;
7152: l_sob_currency_code po_headers.currency_code%TYPE;
7153: l_rate_type po_headers.rate_type%TYPE;
7154: l_rate po_headers.rate%TYPE;
7155: l_rate_date po_headers.rate_date%TYPE;
7156: l_acceptance_required po_headers.acceptance_required_flag%TYPE;
7149: l_ship_to_location_id po_headers.ship_to_location_id%TYPE;
7150: l_bill_to_location_id po_headers.bill_to_location_id%TYPE;
7151: l_currency_code po_headers.currency_code%TYPE;
7152: l_sob_currency_code po_headers.currency_code%TYPE;
7153: l_rate_type po_headers.rate_type%TYPE;
7154: l_rate po_headers.rate%TYPE;
7155: l_rate_date po_headers.rate_date%TYPE;
7156: l_acceptance_required po_headers.acceptance_required_flag%TYPE;
7157:
7150: l_bill_to_location_id po_headers.bill_to_location_id%TYPE;
7151: l_currency_code po_headers.currency_code%TYPE;
7152: l_sob_currency_code po_headers.currency_code%TYPE;
7153: l_rate_type po_headers.rate_type%TYPE;
7154: l_rate po_headers.rate%TYPE;
7155: l_rate_date po_headers.rate_date%TYPE;
7156: l_acceptance_required po_headers.acceptance_required_flag%TYPE;
7157:
7158: --bug#3987438
7151: l_currency_code po_headers.currency_code%TYPE;
7152: l_sob_currency_code po_headers.currency_code%TYPE;
7153: l_rate_type po_headers.rate_type%TYPE;
7154: l_rate po_headers.rate%TYPE;
7155: l_rate_date po_headers.rate_date%TYPE;
7156: l_acceptance_required po_headers.acceptance_required_flag%TYPE;
7157:
7158: --bug#3987438
7159: --Adding a new variable that would hold the name of the invalid
7152: l_sob_currency_code po_headers.currency_code%TYPE;
7153: l_rate_type po_headers.rate_type%TYPE;
7154: l_rate po_headers.rate%TYPE;
7155: l_rate_date po_headers.rate_date%TYPE;
7156: l_acceptance_required po_headers.acceptance_required_flag%TYPE;
7157:
7158: --bug#3987438
7159: --Adding a new variable that would hold the name of the invalid
7160: --ship-to or bill-to location
7206: 0,
7207: p_sequence + ROWNUM,
7208: substr(l_textline,1,240),
7209: 'PO_SUB_VENDOR_ON_HOLD'
7210: FROM PO_HEADERS_GT POH, PO_VENDORS POV, PO_SYSTEM_PARAMETERS PSP
7211: WHERE POV.vendor_id = POH.vendor_id
7212: AND POH.po_header_id = p_document_id
7213: AND nvl(PSP.ENFORCE_VENDOR_HOLD_FLAG,'N') = 'Y'
7214: AND nvl(POV.hold_flag,'N') = 'Y';
7240: l_rate_type,
7241: l_rate,
7242: l_rate_date,
7243: l_acceptance_required
7244: FROM PO_HEADERS_GT POH,
7245: GL_SETS_OF_BOOKS SOB,
7246: FINANCIALS_SYSTEM_PARAMETERS FSP
7247: WHERE POH.po_header_id = p_document_id
7248: AND SOB.set_of_books_id = FSP.set_of_books_id;
7730: 0,
7731: p_sequence + ROWNUM,
7732: substr(l_textline,1,240),
7733: 'PO_ON_HOLD_CANNOT_APPROVE'
7734: FROM PO_HEADERS_GT POH
7735: WHERE POH.po_header_id = p_document_id
7736: AND nvl(POH.USER_HOLD_FLAG,'N') = 'Y';
7737:
7738: --Increment the p_sequence with number of errors reported in last query
7784: substr(l_textline,1,240),
7785: 'PO_PDOI_INVALID_VENDOR'
7786: FROM dual
7787: where not exists (select 'Y'
7788: from PO_HEADERS_GT POH, po_vendors pov
7789: WHERE POH.po_header_id = p_document_id
7790: AND pov.vendor_id = poh.vendor_id
7791: AND pov.enabled_flag = 'Y'
7792: AND SYSDATE BETWEEN nvl(pov.start_date_active, SYSDATE-1)
7828: substr(l_textline,1,240),
7829: 'PO_PDOI_INVALID_VENDOR_SITE'
7830: FROM dual
7831: where not exists (select 'Y'
7832: from PO_HEADERS_GT POH, po_vendor_sites povs
7833: WHERE POH.po_header_id = p_document_id
7834: AND povs.vendor_site_id = poh.vendor_site_id
7835: AND nvl(povs.rfq_only_site_flag,'N') <> 'Y'
7836: AND povs.purchasing_site_flag = 'Y'
7881: HZ_PARTY_SITES HPS ,
7882: HZ_ORG_CONTACTS HOC ,
7883: HZ_PARTIES HP2 ,
7884: AP_SUPPLIERS APS,
7885: po_headers_gt POH
7886: WHERE PVC.PER_PARTY_ID = HP.PARTY_ID
7887: AND PVC.REL_PARTY_ID = HP2.PARTY_ID
7888: AND PVC.ORG_CONTACT_ID = HOC.ORG_CONTACT_ID(+)
7889: AND PVC.RELATIONSHIP_ID = HPR.RELATIONSHIP_ID
7972: 'PO_CONTACT_WITHOUT_VALID_USER'
7973: FROM dual
7974: WHERE NOT EXISTS (SELECT 'Y'
7975: FROM po_supplier_contacts_val_v pscv,
7976: po_headers_gt poh,
7977: fnd_user fu
7978: WHERE pscv.vendor_contact_id = poh.vendor_contact_id
7979: AND pscv.vendor_site_id = poh.vendor_site_id -- bug 9865162, included the check for vendor_site_id
7980: AND poh.po_header_id = p_document_id
8017: 0,
8018: p_sequence + ROWNUM,
8019: substr(l_textline,1,240),
8020: 'PO_SUB_AWARD_TYPE_NULL'
8021: FROM PO_HEADERS_GT POH
8022: WHERE POH.po_header_id = p_document_id
8023: AND POH.type_lookup_code = 'STANDARD'
8024: AND POH.clm_award_type IS NULL
8025: AND nvl(l_clm_po,'N') = 'Y';
8055: 0,
8056: p_sequence + ROWNUM,
8057: substr(l_textline,1,240),
8058: 'PO_SUB_IDV_WITHOUT_SOURCE'
8059: FROM PO_HEADERS_GT POH
8060: WHERE POH.po_header_id = p_document_id
8061: AND POH.type_lookup_code = 'STANDARD'
8062: AND Nvl(POH.clm_award_type,'N') IN (SELECT lookup_code
8063: FROM PO_LOOKUP_CODES
8096: 0,
8097: p_sequence + ROWNUM,
8098: substr(l_textline,1,240),
8099: 'PO_SUB_AWARD_WITH_SOURCE'
8100: FROM PO_HEADERS_GT POH
8101: WHERE POH.po_header_id = p_document_id
8102: AND POH.type_lookup_code = 'STANDARD'
8103: AND Nvl(POH.clm_award_type,'N') IN (SELECT lookup_code
8104: FROM PO_LOOKUP_CODES
8141: l_textline,
8142: 'PO_BUYER_INACTIVE'
8143: FROM dual
8144: where not exists (select 'inactive buyer'
8145: from PO_HEADERS_GT POH,
8146: PO_BUYERS_V POB --
8147: where poh.agent_id = pob.employee_id);
8148:
8149: --Increment the p_sequence with number of errors reported in last query
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';
8303: 0, 0, 0,
8304: p_sequence + ROWNUM,
8305: substr(l_textline,1,240),
8306: 'PO_SUB_HEADER_NO_LINES'
8307: FROM PO_HEADERS_GT POH
8308: WHERE POH.po_header_id = p_document_id AND
8309: NOT EXISTS (SELECT 'Lines Exist'
8310: FROM PO_LINES_GT POL
8311: WHERE POL.po_header_id = POH.po_header_id
8519: ||g_distmsg||g_delim||POD.distribution_num||g_delim
8520: ||l_textline,1,240),
8521: 'PO_SUB_DIST_RATE_NULL'
8522: FROM PO_DISTRIBUTIONS_GT POD,PO_LINE_LOCATIONS_GT PLL,PO_LINES_GT POL,
8523: PO_HEADERS_GT POH,GL_SETS_OF_BOOKS SOB,FINANCIALS_SYSTEM_PARAMETERS FSP
8524: WHERE POD.po_header_id = POH.po_header_id
8525: AND POD.line_location_id = PLL.line_location_id
8526: AND PLL.po_line_id = POL.po_line_id
8527: AND POH.po_header_id = p_document_id
8578: ||g_distmsg||g_delim||POD.distribution_num||g_delim
8579: ||l_textline,1,240),
8580: 'PO_SUB_DIST_RATE_NOT_NULL'
8581: FROM PO_DISTRIBUTIONS_GT POD,PO_LINE_LOCATIONS_GT PLL,PO_LINES_GT POL,
8582: PO_HEADERS_GT POH,GL_SETS_OF_BOOKS SOB,FINANCIALS_SYSTEM_PARAMETERS FSP
8583: WHERE POD.po_header_id = POH.po_header_id
8584: AND POD.line_location_id = PLL.line_location_id
8585: AND PLL.po_line_id = POL.po_line_id
8586: AND POH.po_header_id = p_document_id
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 --
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, --
8760: PO_HEADERS_GT POH
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 --
8764: AND POC.type_lookup_code = 'CONTRACT'
8961: g_shipmsg||g_delim||PLL.shipment_num||g_delim
8962: ||l_textline,1,240),
8963: 'PO_SUB_ITEM_NOT_APPROVED'
8964: FROM MTL_SYSTEM_ITEMS MSI, PO_LINE_LOCATIONS_GT PLL,
8965: PO_LINES_GT POL, PO_HEADERS_GT POH,
8966: FINANCIALS_SYSTEM_PARAMETERS FSP
8967: WHERE POH.po_header_id = p_document_id
8968: AND POH.po_header_id = POL.po_header_id
8969: AND PLL.po_line_id(+) = POL.po_line_id
9065: ||g_shipmsg||g_delim||PLL.shipment_num||g_delim
9066: ||l_textline,1,240),
9067: 'PO_SUB_ITEM_ASL_DEBARRED'
9068: FROM PO_LINE_LOCATIONS_GT PLL,
9069: PO_LINES_GT POL, PO_HEADERS_GT POH,
9070: FINANCIALS_SYSTEM_PARAMETERS FSP
9071: WHERE POH.po_header_id = p_document_id
9072: AND POH.po_header_id = POL.po_header_id
9073: AND PLL.po_line_id(+) = POL.po_line_id
9152: SUBSTR (g_linemsg || g_delim || POL.line_num ||g_delim ||
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
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
9208: SUBSTR (g_linemsg || g_delim || POL.line_num ||g_delim ||
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
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
9264: SUBSTR (g_linemsg || g_delim || POL.line_num ||g_delim ||
9265: l_textline,1,240),
9266: 'PO_ATO_ITEM_NA'
9267: FROM po_lines_gt POL,
9268: po_headers_gt POH,
9269: financials_system_params_all FSP,
9270: mtl_system_items MSI
9271: WHERE fsp.org_id = poh.org_id
9272: AND POH.po_header_id = p_document_id
9319: 0,
9320: p_sequence + ROWNUM,
9321: substr(g_linemsg||g_delim||pl.line_num||g_delim||l_textline,1,240),
9322: 'PO_ALL_NO_ITEM'
9323: from po_headers_gt ph, po_lines_gt pl,mtl_system_items itm,financials_system_params_all fsp,po_line_types_b plt
9324: where fsp.org_id = ph.org_id
9325: and itm.inventory_item_id = pl.item_id
9326: and pl.item_id is not null
9327: and itm.organization_id = fsp.inventory_organization_id
9368: p_sequence + ROWNUM,
9369: substr(g_linemsg||g_delim||pl.line_num||g_delim||
9370: g_shipmsg||g_delim||pll.shipment_num||g_delim||l_textline,1,240),
9371: 'PO_ALL_NO_ITEM'
9372: from po_headers_gt ph,po_lines_gt pl,po_line_locations_gt pll,mtl_system_items itm,po_line_types_b plt
9373: where itm.inventory_item_id = pl.item_id
9374: and pl.item_id is not null
9375: and itm.organization_id = pll.ship_to_organization_id
9376: and itm.purchasing_enabled_flag = 'N'
9433: SUBSTR (g_linemsg || g_delim || POL.line_num ||g_delim ||
9434: g_shipmsg || g_delim || PLL.shipment_num ||g_delim || l_textline,1,240),
9435: 'PO_PO_PLANNED_ITEM_DATE_REQ'
9436: FROM po_lines_gt POL,
9437: po_headers_gt POH,
9438: po_line_locations_gt PLL,
9439: financials_system_parameters FSP,
9440: mtl_system_items MSI
9441: WHERE POH.po_header_id = p_document_id
9692: 1,240),
9693: 'PO_SUB_VMI_ASL_EXISTS',
9694: 'W'
9695: FROM PO_LINES_GT POL,
9696: PO_HEADERS_GT POH,
9697: PO_LINE_LOCATIONS_GT PLL,
9698: PO_APPROVED_SUPPLIER_LIS_VAL_V PASL,
9699: PO_ASL_ATTRIBUTES PAA,
9700: PO_ASL_STATUS_RULES_V PASR
9967: p_sequence + ROWNUM,
9968: substr(g_linemsg||g_delim||POL.line_num||g_delim||g_shipmsg||g_delim||
9969: PLL.shipment_num||g_delim||l_textline,1,240),
9970: 'PO_SUB_PO_SHIP_INV_MATCH_NE_R'
9971: FROM PO_HEADERS_GT POH,
9972: PO_LINES_GT POL,
9973: PO_LINE_LOCATIONS_GT PLL
9974: WHERE POH.po_header_id = POL.po_header_id
9975: AND POL.po_line_id = PLL.po_line_id
10019: p_sequence + ROWNUM,
10020: substr(g_linemsg||g_delim||POL.line_num||g_delim||g_shipmsg||g_delim||PLL.shipment_num||g_delim
10021: ||g_distmsg||g_delim||POD.distribution_num||g_delim||l_textline,1,240),
10022: 'PO_SUB_PO_DIST_DEST_TYPE_NE_IN'
10023: FROM PO_HEADERS_GT POH,
10024: PO_LINES_GT POL,
10025: PO_LINE_LOCATIONS_GT PLL,
10026: PO_DISTRIBUTIONS_GT POD
10027: WHERE POH.po_header_id = POD.po_header_id
10374: p_sequence + ROWNUM,
10375: substr(g_linemsg || g_delim || POL.line_num || g_delim
10376: || l_textline,1,240),
10377: 'PO_IDV_REF_NOT_EQUAL'
10378: FROM PO_HEADERS_GT POH,
10379: PO_LINES_GT POL
10380: WHERE POH.po_header_id = p_document_id
10381: AND POH.po_header_id = POL.po_header_id
10382: AND POH.type_lookup_code = 'STANDARD'
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 */
10487: 0,
10488: p_sequence + ROWNUM,
10489: substr(l_textline,1,240),
10490: 'PO_SUB_AGREED_GRT_LIMIT'
10491: FROM PO_HEADERS_GT POH
10492: WHERE POH.po_header_id = p_document_id
10493: AND POH.blanket_total_amount is not null
10494: AND POH.amount_limit is not null
10495: AND POH.blanket_total_amount > POH.amount_limit;
10536: 0,
10537: p_sequence + ROWNUM,
10538: substr(l_textline,1,240),
10539: 'PO_SUB_MINREL_GRT_LIMIT'
10540: FROM PO_HEADERS_GT POH
10541: WHERE POH.po_header_id = p_document_id
10542: AND POH.min_release_amount is not null
10543: AND POH.amount_limit is not null
10544: AND POH.min_release_amount > POH.amount_limit;
10559:
10560: /* bug 14196636 - Query to fetch precision for currency */
10561: select poh.currency_code
10562: into l_currency_code
10563: FROM PO_HEADERS_GT POH
10564: WHERE POH.po_header_id = p_document_id;
10565:
10566: fnd_currency.get_info (l_currency_code,
10567: l_precision,
10597: 0,
10598: p_sequence + ROWNUM,
10599: substr(l_textline,1,240),
10600: 'PO_SUB_LIMIT_GRT_REL_AMT'
10601: FROM PO_HEADERS_GT POH
10602: WHERE POH.po_header_id = p_document_id
10603: AND POH.amount_limit is not null
10604: AND ( (NVL(POH.global_agreement_flag, 'N') = 'N'
10605: AND
10806: 0, 0, 0,
10807: p_sequence + ROWNUM,
10808: substr(l_textline,1,240),
10809: 'PO_SUB_HEADER_NO_LINES'
10810: FROM PO_HEADERS_GT POH
10811: WHERE POH.po_header_id = p_document_id AND
10812: NOT EXISTS (SELECT 'Lines Exist'
10813: FROM PO_LINES_GT POL
10814: WHERE POL.po_header_id = POH.po_header_id);
10850: 0, 0, 0,
10851: p_sequence + ROWNUM,
10852: substr(l_textline,1,240),
10853: 'PO_SUB_HEADER_END_DATED'
10854: FROM PO_HEADERS_GT POH
10855: WHERE POH.po_header_id = p_document_id
10856: AND TRUNC(sysdate) > TRUNC(POH.end_date);
10857: -- Increment the p_sequence with number of errors reported in last query
10858: p_sequence := p_sequence + SQL%ROWCOUNT;
10937: 0,
10938: p_sequence + ROWNUM,
10939: substr(g_linemsg||g_delim||pl.line_num||g_delim||l_textline,1,240),
10940: 'PO_ALL_NO_ITEM'
10941: from po_headers_gt ph,po_lines_gt pl,mtl_system_items itm,financials_system_parameters fsp,po_line_types_b plt
10942: where itm.inventory_item_id = pl.item_id
10943: and pl.item_id is not null
10944: and itm.organization_id = fsp.inventory_organization_id
10945: and itm.purchasing_enabled_flag = 'N'
11031: THEN PO_MESSAGE_S.POX_EFFECTIVE_DATES6
11032: WHEN pll.end_date > pl.expiration_date
11033: THEN PO_MESSAGE_S.POX_EFFECTIVE_DATES2
11034: END
11035: FROM po_headers_gt ph, po_lines_gt pl, po_line_locations_gt pll
11036: WHERE ph.po_header_id = p_document_id
11037: AND pl.po_header_id = ph.po_header_id
11038: AND pll.po_line_id = pl.po_line_id
11039: AND pll.shipment_type = 'PRICE BREAK'
11084: 0,
11085: p_sequence + ROWNUM,
11086: substr(g_linemsg||g_delim||pl.line_num||g_delim||l_textline,1,240),
11087: PO_MESSAGE_S.POX_EXPIRATION_DATES
11088: FROM po_headers_gt ph, po_lines_gt pl
11089: WHERE ph.po_header_id = p_document_id
11090: AND pl.po_header_id = ph.po_header_id
11091: AND (pl.expiration_date < ph.start_date
11092: or pl.expiration_date > ph.end_date)
11139: ||g_shipmsg||g_delim||PLL.shipment_num||g_delim
11140: ||l_textline,1,240),
11141: 'PO_SUB_ITEM_ASL_DEBARRED'
11142: FROM PO_LINE_LOCATIONS_GT PLL,
11143: PO_LINES_GT POL, PO_HEADERS_GT POH,
11144: FINANCIALS_SYSTEM_PARAMETERS FSP
11145: WHERE POH.po_header_id = p_document_id
11146: AND POH.po_header_id = POL.po_header_id
11147: AND PLL.po_line_id(+) = POL.po_line_id
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: --
11541: l_consigned_flag,
11542: l_line_num,
11543: l_shipment_num,
11544: l_line_location_id
11545: FROM PO_HEADERS_GT POH,
11546: PO_LINE_LOCATIONS_GT PLL,
11547: PO_LINES_GT POL
11548: WHERE POH.po_header_id = p_document_id AND
11549: POH.po_header_id = POL.po_header_id AND
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
12703: 1,
12704: 240),
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
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
12779: 240),
12780: 'PA_SUB_STD_GC_INVALID_SITE',
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
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
13185: -- bug5153099
13186: -- Removed group by clause in subquery.Also removed the checking for
13187: -- global_agreement_flag and the corresponding OR case logic.
13188:
13189: FROM PO_HEADERS_GT POH
13190: WHERE POH.po_header_id = p_document_id
13191: AND POH.type_lookup_code = 'CONTRACT'
13192: AND POH.amount_limit IS NOT NULL
13193: AND ((POH.amount_limit * NVL(POH.rate, 1)) -- amt limit in fn currency --
13198: , 'FIXED PRICE', POL1.amount
13199: , POL1.quantity * POL1.unit_price)
13200: * NVL(POH1.rate,1)
13201: )
13202: FROM po_headers POH1,
13203: po_lines POL1
13204: WHERE POL1.contract_id = POH.po_header_id
13205: AND POL1.po_header_id = POH1.po_header_id
13206: AND NVL(POL1.cancel_flag, 'N') = 'N'
13457: l_api_name CONSTANT varchar2(40) := 'POPULATE_GLOBAL_TEMP_TABLES';
13458: l_log_head CONSTANT VARCHAR2(100) := g_log_head||l_api_name;
13459: l_progress VARCHAR2(3);
13460:
13461: l_blanket_header_id po_headers.po_header_id%TYPE;
13462: l_return_status VARCHAR2(1);
13463:
13464: l_doc_id NUMBER;
13465:
13480: -- bug3413891
13481: -- Clean up gt tables to make sure that all the records in GT tables are from
13482: -- the same document
13483:
13484: DELETE FROM po_headers_gt;
13485: DELETE FROM po_lines_gt;
13486: DELETE FROM po_line_locations_gt;
13487: DELETE FROM po_distributions_gt;
13488: DELETE FROM po_releases_gt;
13538: END IF;
13539: END IF;
13540:
13541: --populate the global headers table
13542: populate_po_headers_gt(
13543: p_document_id => l_doc_id,
13544: p_draft_id => p_draft_id, -- CLM Aprvl
13545: x_return_status => l_return_status);
13546:
13591:
13592: l_progress := '010';
13593: --populate the global headers table with header of blanket/planned PO
13594: --for which this is a Release
13595: populate_po_headers_gt(
13596: p_document_id => l_blanket_header_id,
13597: p_draft_id => p_draft_id, -- CLM Aprvl
13598: x_return_status => l_return_status);
13599:
14178:
14179: END UPDATE_GLOBAL_TEMP_TABLES;
14180:
14181: /**
14182: * Private Procedure: POPULATE_PO_HEADERS_GT
14183: * Requires:
14184: * IN PARAMETERS:
14185: * p_document_id: Id of submitted document
14186: * p_draft_id: Draft Id of Mod Document -- CLM Aprvl
14184: * IN PARAMETERS:
14185: * p_document_id: Id of submitted document
14186: * p_draft_id: Draft Id of Mod Document -- CLM Aprvl
14187: * Modifies:
14188: * Effects: Populates the global temp tables po_headers_gt
14189: * Returns:
14190: */
14191: PROCEDURE populate_po_headers_gt(p_document_id IN number,
14192: p_draft_id IN NUMBER := -1,
14187: * Modifies:
14188: * Effects: Populates the global temp tables po_headers_gt
14189: * Returns:
14190: */
14191: PROCEDURE populate_po_headers_gt(p_document_id IN number,
14192: p_draft_id IN NUMBER := -1,
14193: x_return_status OUT NOCOPY VARCHAR2)IS
14194:
14195: l_api_name CONSTANT varchar2(40) := 'POPULATE_PO_HEADERS_GT';
14191: PROCEDURE populate_po_headers_gt(p_document_id IN number,
14192: p_draft_id IN NUMBER := -1,
14193: x_return_status OUT NOCOPY VARCHAR2)IS
14194:
14195: l_api_name CONSTANT varchar2(40) := 'POPULATE_PO_HEADERS_GT';
14196: l_progress VARCHAR2(3);
14197:
14198: t_po_header_id NUMBER;
14199: t_segment1 po_headers.segment1%TYPE;
14195: l_api_name CONSTANT varchar2(40) := 'POPULATE_PO_HEADERS_GT';
14196: l_progress VARCHAR2(3);
14197:
14198: t_po_header_id NUMBER;
14199: t_segment1 po_headers.segment1%TYPE;
14200:
14201: BEGIN
14202:
14203: l_progress := '000';
14207: || l_progress,'Populate HEADERS');
14208: END IF;
14209: END IF;
14210:
14211: INSERT INTO po_headers_gt(
14212: PO_HEADER_ID,
14213: AGENT_ID,
14214: TYPE_LOOKUP_CODE,
14215: LAST_UPDATE_DATE,
14536: CLM_PAYMENT_INSTR_CODE, -- Payment Instruction
14537: SUPPLIER_NOTIF_METHOD -- MIPR
14538: --Mod Project
14539: /*-- CLM Aprvl
14540: FROM po_headers
14541: WHERE po_header_id = p_document_id
14542: */-- CLM Aprvl
14543: FROM po_headers_merge_v
14544: WHERE po_header_id = p_document_id
14539: /*-- CLM Aprvl
14540: FROM po_headers
14541: WHERE po_header_id = p_document_id
14542: */-- CLM Aprvl
14543: FROM po_headers_merge_v
14544: WHERE po_header_id = p_document_id
14545: and draft_id = p_draft_id;
14546:
14547: l_progress := '001';
14546:
14547: l_progress := '001';
14548: --SANITY check
14549: SELECT po_header_id, segment1 into t_po_header_id, t_segment1
14550: from po_headers_gt where po_header_id = p_document_id;
14551:
14552: IF g_debug_stmt THEN
14553: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
14554: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
14580: || l_progress || ' SQL CODE is '||sqlcode);
14581: END IF;
14582: END IF;
14583:
14584: END POPULATE_PO_HEADERS_GT;
14585:
14586:
14587: --
14588: -------------------------------------------------------------------------------
15038: * Requires:
15039: * IN PARAMETERS:
15040: * p_document_id: Id of submitted document
15041: * Modifies:
15042: * Effects: Populates the global temp tables po_headers_gt
15043: * Returns:
15044: */
15045: PROCEDURE populate_releases_gt(p_document_id IN NUMBER,
15046: x_return_status OUT NOCOPY VARCHAR2) IS
15265: * Requires:
15266: * IN PARAMETERS:
15267: * p_document_id: Id of submitted document
15268: * Modifies:
15269: * Effects: Populates the global temp tables po_headers_gt
15270: * Returns:
15271: */
15272: PROCEDURE populate_req_headers_gt(p_document_id IN NUMBER,
15273: x_return_status OUT NOCOPY VARCHAR2) IS
15440: * Requires:
15441: * IN PARAMETERS:
15442: * p_document_id: Id of submitted document
15443: * Modifies:
15444: * Effects: Populates the global temp tables po_headers_gt
15445: * Returns:
15446: */
15447: PROCEDURE populate_req_lines_gt(p_document_id IN NUMBER,
15448: x_return_status OUT NOCOPY VARCHAR2) IS
15773: * Requires:
15774: * IN PARAMETERS:
15775: * p_document_id: Id of submitted document
15776: * Modifies:
15777: * Effects: Populates the global temp tables po_headers_gt
15778: * Returns:
15779: */
15780: PROCEDURE populate_req_distributions_gt(
15781: p_document_id IN NUMBER
16097: 'EXISTS(SELECT NULL FROM PO_AGENTS WHERE agent_id= ' ||
16098: p_employee_id || ' AND SYSDATE BETWEEN NVL(start_date_active, ' ||
16099: 'SYSDATE) AND NVL(end_date_active, SYSDATE+1))))' ||')'
16100: ||')';
16101: /*Bug6640107 : Here the document access level is the access level set in po_headers_v and the required access level is p_minimum_access_level.
16102: The following allows us to either view the document or to open and mofify it depending on the document access level*/
16103:
16104: x_where_clause := x_where_clause || 'AND (('''||p_minimum_access_level||''' = ''VIEW_ONLY'' ) OR ( '''|| p_minimum_access_level||''' = ''MODIFY'' AND ' || p_query_table||'.access_level_code IN (''MODIFY'',''FULL'') ) OR ( '''
16105: || p_minimum_access_level||''' = ''FULL'' AND '|| p_query_table||'.access_level_code = ''FULL'' ) OR ( '||p_employee_id ||' = '|| p_owner_id_column ||'))))';
16205: ) IS
16206:
16207: l_api_name CONSTANT VARCHAR(30) := 'CHECK_UPDATABLE';
16208: l_progress VARCHAR2(3) := '000';
16209: l_document_id PO_HEADERS.po_header_id%TYPE;
16210: l_document_type PO_DOCUMENT_TYPES.DOCUMENT_TYPE_CODE%TYPE;
16211:
16212: -- bug3592160 START
16213: l_header_id PO_TBL_NUMBER;
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')
16439: ) IS
16440:
16441: l_api_name CONSTANT VARCHAR(30) := 'check_reservable';
16442: l_progress VARCHAR2(3) := '000';
16443: l_document_id PO_HEADERS.po_header_id%TYPE;
16444: l_document_type PO_DOCUMENT_TYPES.DOCUMENT_TYPE_CODE%TYPE;
16445:
16446: -- bug3592160 START
16447: l_header_id PO_TBL_NUMBER;
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: /* <
16617: ) IS
16618:
16619: l_api_name CONSTANT VARCHAR(30) := 'check_unreservable';
16620: l_progress VARCHAR2(3) := '000';
16621: l_document_id PO_HEADERS.po_header_id%TYPE;
16622: l_document_type PO_DOCUMENT_TYPES.DOCUMENT_TYPE_CODE%TYPE;
16623:
16624: -- bug3592160 START
16625: l_header_id PO_TBL_NUMBER;
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')))
16807: END LOOP;
16808:
16809: l_progress := '020';
16810:
16811: delete from po_headers_gt;
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';
16810:
16811: delete from po_headers_gt;
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
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
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
16821: INTO po_headers_gt
16822: ( AGENT_ID, TYPE_LOOKUP_CODE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
16823: SEGMENT1, SUMMARY_FLAG, ENABLED_FLAG,
16824: authorization_status, approved_flag,
16825: closed_code, cancel_flag,
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:
16835: AND h.po_header_id = p_header_id(i)
16836: ;
16837:
16838: -- For all the entities with non-null p_release_id(i), get Relase status fields into
16839: -- global temprary table while storing sequence into po_headers_gt.PO_HEADER_ID column
16840: l_progress := '035';
16841: FORALL i IN 1..p_count
16842: INSERT
16843: INTO po_headers_gt
16839: -- global temprary table while storing sequence into po_headers_gt.PO_HEADER_ID column
16840: l_progress := '035';
16841: FORALL i IN 1..p_count
16842: INSERT
16843: INTO po_headers_gt
16844: ( AGENT_ID, TYPE_LOOKUP_CODE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
16845: SEGMENT1, SUMMARY_FLAG, ENABLED_FLAG,
16846: authorization_status, approved_flag,
16847: closed_code, cancel_flag,
16858:
16859: --IF line ID present at an index, overwrite the status fields with Line Level status
16860: l_progress := '040';
16861: FORALL i IN 1..p_count
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
16870:
16871: --IF line location present at an index, overwrite status fields with Shipment Level status
16872: l_progress := '050';
16873: FORALL i IN 1..p_count
16874: UPDATE po_headers_gt gt
16875: SET (approved_flag, closed_code, cancel_flag)
16876: =
16877: (SELECT nvl(approved_flag, 'N'), nvl(closed_code, 'OPEN'), NVL(cancel_flag, 'N')
16878: FROM po_line_locations_all s
16889: authorization_status, approved_flag, closed_code, cancel_flag, frozen_flag, user_hold_flag
16890: BULK COLLECT INTO
16891: x_po_status_rec.authorization_status, x_po_status_rec.approval_flag, x_po_status_rec.closed_code,
16892: x_po_status_rec.cancel_flag, x_po_status_rec.frozen_flag, x_po_status_rec.hold_flag
16893: FROM po_headers_gt
16894: ORDER BY PO_HEADER_ID;
16895:
16896: x_return_status := FND_API.G_RET_STS_SUCCESS;
16897:
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
18424: AND POH.po_header_id = POD.po_header_id --JOIN
18678: -- set x_header_id(i) as -999, indicating that po_header_Id
18679: -- cannot be derived by the IN paramters
18680: SELECT NVL(MIN(po_header_id), '-999')
18681: INTO x_header_id(i)
18682: FROM po_headers
18683: WHERE segment1 = p_document_num(i)
18684: AND type_lookup_code = p_type_lookup_code(i)
18685: AND po_header_id = NVL(x_header_id(i), po_header_id);
18686:
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
18787: IS
18788: l_api_name CONSTANT VARCHAR2(30) := 'CHECK_VENDOR_SITE_CCR_REGIS';
18789: l_progress VARCHAR2(3);
18790:
18791: l_vendor_id PO_HEADERS.vendor_id%TYPE;
18792: l_vendor_site_id PO_HEADERS.vendor_site_id%TYPE;
18793: l_valid_registration BOOLEAN := FALSE;
18794:
18795: l_text_line PO_ONLINE_REPORT_TEXT.text_line%TYPE := NULL;
18788: l_api_name CONSTANT VARCHAR2(30) := 'CHECK_VENDOR_SITE_CCR_REGIS';
18789: l_progress VARCHAR2(3);
18790:
18791: l_vendor_id PO_HEADERS.vendor_id%TYPE;
18792: l_vendor_site_id PO_HEADERS.vendor_site_id%TYPE;
18793: l_valid_registration BOOLEAN := FALSE;
18794:
18795: l_text_line PO_ONLINE_REPORT_TEXT.text_line%TYPE := NULL;
18796:
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;
19273: , 0
19274: , p_sequence + ROWNUM
19275: , substr(l_textline, 1, 240)
19276: , 'PO_CAN_POH_WITH_RCV_TRX'
19277: FROM po_headers_gt poh
19278: WHERE EXISTS
19279: (
19280: SELECT 'Eligible shipment'
19281: FROM po_line_locations_gt poll
20554: , 0
20555: , p_sequence + ROWNUM
20556: , substr(l_textline,1,240) --Bug5096900
20557: , 'PO_CAN_CGA_WITH_OPEN_STD_REF'
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
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
21050:
21051: --Getting the draft id to get the draft type from po drafts
21052: SELECT draft_id
21053: INTO l_draft_id
21054: FROM po_headers_gt
21055: WHERE po_header_id = p_document_id;
21056:
21057: --Getting the draft type
21058: --SQL to get l_draft_type is executed only when l_draft_id is not -1
21432: , null -- distribution_num
21433: , p_sequence + ROWNUM
21434: , substr(l_textline, 1, 240)
21435: , l_message_name
21436: FROM po_headers_gt poh
21437: WHERE poh.po_header_id = p_doc_level_id
21438: AND chk_unv_invoices(l_invoice_type, poh.po_header_id, NULL, NULL,NULL, NULL, p_origin_doc_id, l_calling_sequence) = 1;
21439:
21440: --
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
21914:
21915: ELSE
21916: SELECT PHA.VENDOR_SITE_ID
21917: INTO l_vendor_site_id
21918: FROM PO_HEADERS_DRAFT_ALL PHA
21919: WHERE PHA.PO_HEADER_ID = p_header_id AND PHA.draft_id = p_draft_id;
21920:
21921: END IF;
21922:
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
22122: -- CLM Controls Project changes
22123: BEGIN
22124: SELECT ORG_ID
22125: INTO l_org_id
22126: FROM PO_HEADERS_MERGE_V
22127: WHERE PO_HEADER_ID = p_header_id
22128: AND DRAFT_ID = p_draft_id;
22129: EXCEPTION
22130: WHEN NO_DATA_FOUND THEN
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)
22310: ELSE
22311: -- Finding the mod amount, agreed amount in the case of PA
22312: SELECT nvl(BLANKET_TOTAL_AMOUNT, 0)
22313: INTO l_doc_amount
22314: FROM po_headers_draft_all phm
22315: WHERE phm.po_header_id = p_header_id
22316: AND phm.draft_id = p_draft_id;
22317: END IF;
22318: END IF; -- l_type_lookup_code = 'STANDARD' THEN
22659: , p_sequence + ROWNUM
22660: , substr(l_textline,1,240)
22661: , PO_MESSAGE_S.PO_ALL_ENTER_PERCENT
22662: , 'E'
22663: FROM PO_HEADERS_GT POH
22664: WHERE POH.po_header_id = p_header_id
22665: AND (POH.clm_min_guar_award_amt_percent > 100
22666: OR POH.clm_min_guar_award_amt_percent < 0);
22667:
22689: , p_sequence + ROWNUM
22690: , substr(l_textline,1,240)
22691: , 'PO_IDV_MX_CL_AMT_GT_MXPM_CLM'
22692: , 'E'
22693: FROM PO_HEADERS_GT POH
22694: WHERE POH.po_header_id = p_header_id
22695: AND POH.amount_limit < POH.clm_max_order_amount;
22696:
22697: -- Maximum Per Order Amount(Line) <= Maximum Per Order Amount(Header)
22737: WHEN (POH.clm_max_order_amount is null and POL.clm_max_order_amount is NULL)
22738: THEN 'PO_IDV_MXPM_H_GT_MN_AMT_L'
22739: END
22740: , 'E'
22741: FROM PO_HEADERS_GT POH, PO_LINES_GT POL
22742: WHERE POH.po_header_id = p_header_id
22743: AND POH.po_header_id = POL.po_header_id
22744: AND nvl(POH.clm_max_order_amount, POH.amount_limit) < nvl(POL.clm_max_order_amount, POL.clm_min_order_amount);
22745:
23052:
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
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
23489: pd.distribution_num ||
23490: ' of shipment ' ||
23491: pll.shipment_num,
23492: 'PO_CLOSE_WIP_JOB'
23493: FROM po_headers_gt ph,
23494: po_line_locations_gt pll,
23495: po_distributions_gt pd,
23496: po_releases_gt pr
23497: WHERE 1=1
23548: pll.shipment_num ||
23549: ' of line ' ||
23550: pl.line_num,
23551: 'PO_CLOSE_WIP_JOB'
23552: FROM po_headers_gt ph,
23553: po_lines_gt pl,
23554: po_line_locations_gt pll,
23555: po_distributions_gt pd
23556: WHERE 1=1
24575: || l_textline, 1, 240),
24576: 'CLM_NO_DELIVERY_PERIOD',
24577: 'E'
24578: FROM po_lines_gt POL,
24579: po_headers_gt POH,
24580: po_line_locations_gt PLL
24581: WHERE POH.po_header_id = p_document_id
24582: AND POL.po_header_id = POH.po_header_id
24583: AND POL.po_line_id = PLL.po_line_id
24668: -- Check 1 : Payment instruction Sequence is not specfied in case payment isntruction code is
24669: -- KO Specified and FV is not enabled in case of BY Fiscal year and By cancellation date
24670: SELECT org_id
24671: INTO l_org_id
24672: FROM po_headers_gt
24673: WHERE po_header_id = p_document_id;
24674:
24675: IF fv_install.enabled(l_org_id) THEN
24676: fv_enabled_flag :='Y';
24709: g_shipmsg || g_delim || POLL.shipment_num ||g_delim ||
24710: g_distmsg || g_delim || POD.distribution_num ||g_delim || l_textline,1,240),
24711: 'PO_PAY_INSTR_SEQ_NOT_SPECIFIED',
24712: 'E'
24713: FROM po_headers_gt POH,
24714: po_lines_gt POL,
24715: po_line_locations_gt POLL,
24716: po_distributions_gt POD
24717: WHERE POH.po_header_id = p_document_id
24815: SUBSTR (g_linemsg || g_delim || Nvl(POL.line_num_display, POL.line_num) || g_delim || l_textline,1,240),
24816: 'PO_PAYMENT_INSTR_BLANK',
24817: 'W'
24818: FROM po_lines_gt POL,
24819: po_headers_gt POH
24820: WHERE POH.po_header_id = p_document_id
24821: AND POL.po_header_id = POH.po_header_id
24822: AND Nvl(POL.clm_info_flag,'N')= 'N'
24823: AND NVl(POL.cost_constraint,'Priced') NOT IN ('NSP', 'NC')
24863: p_sequence + ROWNUM,
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
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')
25033: 'CLM_POP_BEYOND_12',
25034: 'E'
25035: FROM po_lines_gt pol,
25036: po_line_types plt,
25037: po_headers_gt pha
25038: WHERE pha.po_header_id = p_document_id
25039: AND pol.po_header_id = pha.po_header_id
25040: AND pol.line_type_id = plt. line_type_id
25041: AND Nvl(plt.clm_severable_flag, 'N') = 'Y'
25115: FROM po_distributions_gt pda,
25116: po_line_locations_gt pll,
25117: (SELECT pol.po_line_id,
25118: Nvl(POL.line_num_display,POL.line_num) line_num
25119: FROM po_headers_gt pha,
25120: po_lines_gt pol,
25121: po_line_types plt
25122: WHERE pha.po_header_id = p_document_id
25123: AND pol.po_header_id = pha.po_header_id
25185: 'CLM_POP_DURATION_BEYOND_12',
25186: 'E'
25187: FROM po_lines_gt pol,
25188: po_line_types plt,
25189: po_headers_gt pha,
25190: po_line_locations_gt poll
25191: WHERE pha.po_header_id = p_document_id
25192: AND pol.po_header_id = pha.po_header_id
25193: AND poll.po_line_id = pol.po_line_id
25302: 'PO_UCA_PDD_PAST_DATE',
25303: 'W'
25304: FROM po_line_ucas uc,
25305: po_lines_gt pol,
25306: po_headers_gt poh
25307: WHERE Nvl(pol.clm_undef_flag, 'N') = 'Y'
25308: AND uc.planned_def_date IS NOT NULL
25309: AND uc.planned_def_date < SYSDATE
25310: AND poh.po_header_id = pol.po_header_id
25352: 'PO_UCA_UNDEF_INCOMPLETE',
25353: 'E'
25354: FROM po_line_ucas uc,
25355: po_lines_gt pol,
25356: po_headers_gt poh
25357: WHERE Nvl(pol.clm_undef_flag, 'N') = 'Y'
25358: AND ( NOT ( ( uc.planned_def_date IS NOT NULL
25359: AND uc.undef_amount IS NOT NULL
25360: AND uc.limit_gov_liability_amt IS NOT NULL
25407: 'PO_UCA_DEF_DESC_ERR',
25408: 'E'
25409: FROM po_line_ucas uc,
25410: po_lines_gt pol,
25411: po_headers_gt poh
25412: WHERE Nvl(pol.clm_undef_action_code, 'X') = 'DEF'
25413: AND uc.undef_amount IS NULL
25414: AND uc.not_to_exceed_amount IS NULL
25415: AND uc.limit_gov_liability_amt IS NULL
25485: pol.clm_idc_type) cp_label,
25486: line_num_display,
25487: line_num
25488: FROM po_lines_gt pol,
25489: po_headers_gt ph
25490: WHERE pol.po_header_id = ph.po_header_id
25491: AND ph.po_header_id = p_document_id
25492: AND Nvl(pol.clm_undef_action_code, 'X') = 'DEF')
25493: WHERE change_in_ext_price > max_change;
25526: line_num_display),
25527: 'PO_UCA_LINE_DEF_NO_UCAS',
25528: 'E'
25529: FROM po_lines_gt pol,
25530: po_headers_gt ph
25531: WHERE ph.po_header_id = p_document_id
25532: AND ph.po_header_id = pol.po_header_id
25533: AND Nvl(pol.clm_undef_action_code,'X') = 'DEF'
25534: AND NOT EXISTS (SELECT 1
25640: 'PO_CLM_EXHIBIT_ORPHAN',
25641: 'E'
25642: FROM
25643: po_exhibit_details_merge_v poe,
25644: po_headers_gt poh
25645: WHERE poh.po_header_id = p_document_id
25646: AND poh.po_header_id = poe.po_header_id
25647: AND poh.draft_id = poe.draft_id
25648: AND poe.reference_line_id IS NULL;
25955: SYSDATE,
25956: p_sequence + ROWNUM,
25957: l_textline,
25958: 'PO_MIPR_COMM_METHOD_NONE'
25959: FROM po_headers_gt
25960: WHERE po_header_id = p_document_id
25961: AND Nvl(supplier_notif_method, 'NONE') <> 'NONE';
25962:
25963: p_sequence := p_sequence + SQL%ROWCOUNT;
25984: SYSDATE,
25985: p_sequence + ROWNUM,
25986: l_textline,
25987: 'PO_MIPR_BILATERAL_IND_NONE'
25988: FROM po_headers_gt
25989: WHERE po_header_id = p_document_id
25990: AND Nvl(Acceptance_Required_Flag, 'N') <> 'N';
25991:
25992: p_sequence := p_sequence + SQL%ROWCOUNT;
26320: FROM
26321: PO_DISTRIBUTIONS_GT POD,
26322: PO_LINE_LOCATIONS_GT POLL,
26323: PO_LINES_GT POL,
26324: PO_HEADERS_GT POH
26325:
26326: WHERE
26327: POH.po_header_id = P_DOCUMENT_ID
26328: AND POL.po_header_id = POH.po_header_id
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