[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;
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)
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
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
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(
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'
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
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
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
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
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'
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'
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'
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
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
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 --
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;
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,
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
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
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,
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
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
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: --
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
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
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