eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PO_DOCUMENT_CHECKS_PVT dependencies on PO_HEADERS_GT

Line 319: FROM po_headers_gt

315: CURSOR c_po_status (p_document_id NUMBER) IS
316: SELECT revision_num
317: ,start_date
318: ,end_date
319: FROM po_headers_gt
320: WHERE po_header_id = p_document_id;
321:
322: -- contracts dependency
323: SUBTYPE qa_result_tbl_type IS OKC_TERMS_QA_GRP.qa_result_tbl_type;

Line 1166: FROM po_headers_gt

1162: -- SQL why : need to check before calling contracts qa
1163: -- SQL join: po_header_id
1164: SELECT conterms_exist_flag
1165: INTO l_conterms_exist_flag
1166: FROM po_headers_gt
1167: WHERE po_header_id = l_document_id;
1168:
1169: IF (NVL(l_conterms_exist_flag, 'N')='Y') THEN
1170: check_terms(

Line 2336: FROM PO_RELEASES_GT POR,PO_HEADERS_GT POH

2332: 0,0,0,
2333: p_sequence + ROWNUM,
2334: substr(l_textline,1,240),
2335: 'PO_SUB_REL_PA_APPROVED'
2336: FROM PO_RELEASES_GT POR,PO_HEADERS_GT POH
2337: WHERE POR.po_header_id = POH.po_header_id
2338: AND POR.po_release_id = p_document_id
2339: AND NVL(POH.approved_flag, 'N') <> 'Y'
2340: AND NVL(POH.user_hold_flag, 'N') <> 'Y'; --< Bug 3422733 >

Line 2389: FROM PO_RELEASES_GT POR,PO_HEADERS_GT POH

2385: 0,
2386: p_sequence + ROWNUM,
2387: substr(l_textline,1,240),
2388: 'PO_SUB_REL_PA_EXPIRED' -- bug3199869
2389: FROM PO_RELEASES_GT POR,PO_HEADERS_GT POH
2390: WHERE POR.po_header_id = POH.po_header_id
2391: AND POR.po_release_id = p_document_id
2392: AND POR.approved_date IS NULL -- bug3199869
2393: AND TRUNC(SYSDATE) > -- bug3199869

Line 2437: FROM PO_RELEASES_GT POR,PO_HEADERS_GT POH

2433: 0,
2434: p_sequence + ROWNUM,
2435: substr(l_textline,1,240),
2436: 'PO_SUB_REL_PA_ON_HOLD'
2437: FROM PO_RELEASES_GT POR,PO_HEADERS_GT POH
2438: WHERE POR.po_header_id = POH.po_header_id
2439: AND POR.po_release_id = p_document_id
2440: AND nvl(POH.user_hold_flag, 'N') = 'Y';
2441:

Line 2483: FROM PO_RELEASES_GT POR,PO_HEADERS_GT POH,PO_VENDORS POV,

2479: 0,
2480: p_sequence + ROWNUM,
2481: substr(l_textline,1,240),
2482: 'PO_SUB_REL_VENDOR_ON_HOLD'
2483: FROM PO_RELEASES_GT POR,PO_HEADERS_GT POH,PO_VENDORS POV,
2484: PO_SYSTEM_PARAMETERS PSP
2485: WHERE POR.po_header_id = POH.po_header_id
2486: AND POV.vendor_id = POH.vendor_id
2487: AND POR.po_release_id = p_document_id

Line 2891: PO_RELEASES_GT POR,PO_HEADERS_GT POH,GL_SETS_OF_BOOKS SOB,

2887: ||l_textline,1,240),
2888: 'PO_SUB_REL_RATE_NULL'
2889: FROM PO_DISTRIBUTIONS_GT POD, --
2890: PO_LINE_LOCATIONS_GT POLL,PO_LINES POL,
2891: PO_RELEASES_GT POR,PO_HEADERS_GT POH,GL_SETS_OF_BOOKS SOB,
2892: FINANCIALS_SYSTEM_PARAMETERS FSP
2893: WHERE POLL.po_release_id = POR.po_release_id
2894: AND POD.line_location_id = POLL.line_location_id
2895: AND POLL.po_line_id = POL.po_line_id

Line 2946: FROM PO_HEADERS_GT POH,PO_RELEASES_GT POR

2942: 0,
2943: p_sequence + ROWNUM,
2944: substr(l_textline,1,240),
2945: 'PO_SUB_REL_AMT_LESS_MINREL_AMT'
2946: FROM PO_HEADERS_GT POH,PO_RELEASES_GT POR
2947: WHERE POR.po_release_id = p_document_id
2948: AND POH.po_header_id = POR.po_header_id
2949: AND POH.min_release_amount IS NOT NULL
2950: AND POH.min_release_amount > --

Line 3003: FROM PO_LINE_LOCATIONS_GT PLL1, PO_RELEASES_GT POR1, PO_HEADERS_GT POH

2999: , ( ( PLL1.quantity
3000: - nvl(PLL1.quantity_cancelled,0) )
3001: * PLL1.price_override ) ) )
3002: INTO l_this_rel_amount
3003: FROM PO_LINE_LOCATIONS_GT PLL1, PO_RELEASES_GT POR1, PO_HEADERS_GT POH
3004: WHERE POR1.po_release_id = p_document_id
3005: AND POR1.po_header_id = POH.po_header_id -- Bug 7188760
3006: AND POH.amount_limit is Not Null -- Bug 7188760
3007: AND PLL1.po_release_id = POR1.po_release_id;

Line 3022: FROM PO_LINE_LOCATIONS PLL2, PO_RELEASES POR2, PO_HEADERS_GT POH

3018: - nvl(PLL2.quantity_cancelled,0) )
3019: * PLL2.price_override ) ) )
3020: , 0 )
3021: INTO l_previous_rel_amount
3022: FROM PO_LINE_LOCATIONS PLL2, PO_RELEASES POR2, PO_HEADERS_GT POH
3023: WHERE PLL2.po_release_id = POR2.po_release_id
3024: AND POR2.po_header_id = POH.po_header_id
3025: AND nvl(POR2.approved_flag, 'N') = 'Y'
3026: AND POH.amount_limit is Not Null -- Bug 7188760

Line 3060: FROM PO_HEADERS_GT POH,PO_RELEASES_GT POR

3056: 0,
3057: p_sequence + ROWNUM,
3058: substr(l_textline,1,240),
3059: 'PO_SUB_REL_AMT_GRT_LIMIT_AMT'
3060: FROM PO_HEADERS_GT POH,PO_RELEASES_GT POR
3061: WHERE POR.po_release_id = p_document_id
3062: AND POH.po_header_id = POR.po_header_id
3063:
3064: AND POH.amount_limit is not null

Line 3309: PO_RELEASES_GT POR,PO_LINES POL, PO_HEADERS_GT POH,

3305: ||l_textline,1,240),
3306: --
3307: 'PO_SUB_ITEM_NOT_APPROVED_REL'
3308: FROM MTL_SYSTEM_ITEMS MSI, PO_LINE_LOCATIONS_GT PLL,
3309: PO_RELEASES_GT POR,PO_LINES POL, PO_HEADERS_GT POH,
3310: FINANCIALS_SYSTEM_PARAMETERS FSP
3311: WHERE POR.po_release_id = p_document_id
3312: AND POR.po_header_id = POH.po_header_id
3313: AND POR.po_header_id = POL.po_header_id

Line 3411: PO_RELEASES_GT POR,PO_LINES POL, PO_HEADERS_GT POH,

3407: ||l_textline,1,240),
3408: --
3409: 'PO_SUB_ITEM_ASL_DEBARRED_REL'
3410: FROM PO_LINE_LOCATIONS_GT PLL,
3411: PO_RELEASES_GT POR,PO_LINES POL, PO_HEADERS_GT POH,
3412: FINANCIALS_SYSTEM_PARAMETERS FSP
3413: WHERE POR.po_release_id = p_document_id
3414: AND POR.po_header_id = POH.po_header_id
3415: AND POR.po_header_id = POL.po_header_id

Line 3614: from po_headers_gt poh, po_releases_gt por

3610: ---------------------------------------------------------------------------------
3611: -- bug 6530879 Releases
3612: select poh.po_header_id,poh.vendor_id, poh.vendor_site_id, poh.vendor_contact_id
3613: into l_agreement_id, l_vendor_id,l_vendor_site_id,l_vendor_contact_id
3614: from po_headers_gt poh, po_releases_gt por
3615: where poh.po_header_id = por.po_header_id
3616: and por.po_release_id = p_document_id;
3617: -- bug 6530879
3618: -- Check 20: Vendor should be valid when approving the document.

Line 3656: from PO_HEADERS_GT POH, po_vendors pov

3652: substr(l_textline,1,240),
3653: 'PO_PDOI_INVALID_VENDOR'
3654: FROM dual
3655: where not exists (select 'Y'
3656: from PO_HEADERS_GT POH, po_vendors pov
3657: WHERE POH.po_header_id = l_agreement_id
3658: AND pov.vendor_id = poh.vendor_id
3659: AND pov.enabled_flag = 'Y'
3660: AND SYSDATE BETWEEN nvl(pov.start_date_active, SYSDATE-1)

Line 3701: from PO_HEADERS_GT POH, po_vendor_sites povs

3697: substr(l_textline,1,240),
3698: 'PO_PDOI_INVALID_VENDOR_SITE'
3699: FROM dual
3700: where not exists (select 'Y'
3701: from PO_HEADERS_GT POH, po_vendor_sites povs
3702: WHERE POH.po_header_id = l_agreement_id
3703: AND povs.vendor_site_id = poh.vendor_site_id
3704: AND nvl(povs.rfq_only_site_flag,'N') <> 'Y'
3705: AND povs.purchasing_site_flag = 'Y'

Line 3745: FROM PO_VENDOR_CONTACTS pvc, po_headers_gt poh

3741: substr(l_textline,1,240),
3742: 'PO_PDOI_INVALID_VDR_CNTCT'
3743: FROM dual
3744: WHERE NOT EXISTS (SELECT 'Y'
3745: FROM PO_VENDOR_CONTACTS pvc, po_headers_gt poh
3746: WHERE POH.po_header_id = l_agreement_id
3747: AND pvc.vendor_contact_id =poh.vendor_contact_id
3748: AND SYSDATE < nvl(pvc.inactive_date, SYSDATE+1));
3749:

Line 4876: FROM PO_HEADERS_GT POH, PO_VENDORS POV, PO_SYSTEM_PARAMETERS PSP

4872: 0,
4873: p_sequence + ROWNUM,
4874: substr(l_textline,1,240),
4875: 'PO_SUB_VENDOR_ON_HOLD'
4876: FROM PO_HEADERS_GT POH, PO_VENDORS POV, PO_SYSTEM_PARAMETERS PSP
4877: WHERE POV.vendor_id = POH.vendor_id
4878: AND POH.po_header_id = p_document_id
4879: AND nvl(PSP.ENFORCE_VENDOR_HOLD_FLAG,'N') = 'Y'
4880: AND nvl(POV.hold_flag,'N') = 'Y';

Line 4908: FROM PO_HEADERS_GT POH,

4904: l_sob_currency_code,
4905: l_rate_type,
4906: l_rate,
4907: l_rate_date
4908: FROM PO_HEADERS_GT POH,
4909: GL_SETS_OF_BOOKS SOB,
4910: FINANCIALS_SYSTEM_PARAMETERS FSP
4911: WHERE POH.po_header_id = p_document_id
4912: AND SOB.set_of_books_id = FSP.set_of_books_id;

Line 5398: FROM PO_HEADERS_GT POH

5394: 0,
5395: p_sequence + ROWNUM,
5396: substr(l_textline,1,240),
5397: 'PO_ON_HOLD_CANNOT_APPROVE'
5398: FROM PO_HEADERS_GT POH
5399: WHERE POH.po_header_id = p_document_id
5400: AND nvl(POH.USER_HOLD_FLAG,'N') = 'Y';
5401:
5402: --Increment the p_sequence with number of errors reported in last query

Line 5452: from PO_HEADERS_GT POH, po_vendors pov

5448: substr(l_textline,1,240),
5449: 'PO_PDOI_INVALID_VENDOR'
5450: FROM dual
5451: where not exists (select 'Y'
5452: from PO_HEADERS_GT POH, po_vendors pov
5453: WHERE POH.po_header_id = p_document_id
5454: AND pov.vendor_id = poh.vendor_id
5455: AND pov.enabled_flag = 'Y'
5456: AND SYSDATE BETWEEN nvl(pov.start_date_active, SYSDATE-1)

Line 5496: from PO_HEADERS_GT POH, po_vendor_sites povs

5492: substr(l_textline,1,240),
5493: 'PO_PDOI_INVALID_VENDOR_SITE'
5494: FROM dual
5495: where not exists (select 'Y'
5496: from PO_HEADERS_GT POH, po_vendor_sites povs
5497: WHERE POH.po_header_id = p_document_id
5498: AND povs.vendor_site_id = poh.vendor_site_id
5499: AND nvl(povs.rfq_only_site_flag,'N') <> 'Y'
5500: AND povs.purchasing_site_flag = 'Y'

Line 5539: FROM PO_VENDOR_CONTACTS pvc, po_headers_gt poh

5535: substr(l_textline,1,240),
5536: 'PO_PDOI_INVALID_VDR_CNTCT'
5537: FROM dual
5538: WHERE NOT EXISTS (SELECT 'Y'
5539: FROM PO_VENDOR_CONTACTS pvc, po_headers_gt poh
5540: WHERE POH.po_header_id = p_document_id
5541: AND pvc.vendor_contact_id =poh.vendor_contact_id
5542: AND SYSDATE < nvl(pvc.inactive_date, SYSDATE+1));
5543:

Line 5679: FROM PO_HEADERS_GT POH

5675: 0, 0, 0,
5676: p_sequence + ROWNUM,
5677: substr(l_textline,1,240),
5678: 'PO_SUB_HEADER_NO_LINES'
5679: FROM PO_HEADERS_GT POH
5680: WHERE POH.po_header_id = p_document_id AND
5681: NOT EXISTS (SELECT 'Lines Exist'
5682: FROM PO_LINES_GT POL
5683: WHERE POL.po_header_id = POH.po_header_id

Line 5879: PO_HEADERS_GT POH,GL_SETS_OF_BOOKS SOB,FINANCIALS_SYSTEM_PARAMETERS FSP

5875: ||g_distmsg||g_delim||POD.distribution_num||g_delim
5876: ||l_textline,1,240),
5877: 'PO_SUB_DIST_RATE_NULL'
5878: FROM PO_DISTRIBUTIONS_GT POD,PO_LINE_LOCATIONS_GT PLL,PO_LINES_GT POL,
5879: PO_HEADERS_GT POH,GL_SETS_OF_BOOKS SOB,FINANCIALS_SYSTEM_PARAMETERS FSP
5880: WHERE POD.po_header_id = POH.po_header_id
5881: AND POD.line_location_id = PLL.line_location_id
5882: AND PLL.po_line_id = POL.po_line_id
5883: AND POH.po_header_id = p_document_id

Line 5938: PO_HEADERS_GT POH,GL_SETS_OF_BOOKS SOB,FINANCIALS_SYSTEM_PARAMETERS FSP

5934: ||g_distmsg||g_delim||POD.distribution_num||g_delim
5935: ||l_textline,1,240),
5936: 'PO_SUB_DIST_RATE_NOT_NULL'
5937: FROM PO_DISTRIBUTIONS_GT POD,PO_LINE_LOCATIONS_GT PLL,PO_LINES_GT POL,
5938: PO_HEADERS_GT POH,GL_SETS_OF_BOOKS SOB,FINANCIALS_SYSTEM_PARAMETERS FSP
5939: WHERE POD.po_header_id = POH.po_header_id
5940: AND POD.line_location_id = PLL.line_location_id
5941: AND PLL.po_line_id = POL.po_line_id
5942: AND POH.po_header_id = p_document_id

Line 6113: PO_HEADERS_GT POH

6109: substr(g_linemsg||g_delim||POL.line_num||g_delim||l_textline,1,240),
6110: 'PO_SUB_LINE_CONTRACT_MISMATCH'
6111: FROM PO_LINES_GT POL,
6112: PO_HEADERS_ALL POC, -- : Use _ALL table
6113: PO_HEADERS_GT POH
6114: WHERE POH.po_header_id = p_document_id
6115: AND POL.po_header_id = POH.po_header_id
6116: AND POL.contract_id = POC.po_header_id --
6117: AND POC.type_lookup_code = 'CONTRACT'

Line 6318: PO_LINES_GT POL, PO_HEADERS_GT POH,

6314: g_shipmsg||g_delim||PLL.shipment_num||g_delim
6315: ||l_textline,1,240),
6316: 'PO_SUB_ITEM_NOT_APPROVED'
6317: FROM MTL_SYSTEM_ITEMS MSI, PO_LINE_LOCATIONS_GT PLL,
6318: PO_LINES_GT POL, PO_HEADERS_GT POH,
6319: FINANCIALS_SYSTEM_PARAMETERS FSP
6320: WHERE POH.po_header_id = p_document_id
6321: AND POH.po_header_id = POL.po_header_id
6322: AND PLL.po_line_id(+) = POL.po_line_id

Line 6415: PO_LINES_GT POL, PO_HEADERS_GT POH,

6411: ||g_shipmsg||g_delim||PLL.shipment_num||g_delim
6412: ||l_textline,1,240),
6413: 'PO_SUB_ITEM_ASL_DEBARRED'
6414: FROM PO_LINE_LOCATIONS_GT PLL,
6415: PO_LINES_GT POL, PO_HEADERS_GT POH,
6416: FINANCIALS_SYSTEM_PARAMETERS FSP
6417: WHERE POH.po_header_id = p_document_id
6418: AND POH.po_header_id = POL.po_header_id
6419: AND PLL.po_line_id(+) = POL.po_line_id

Line 6502: po_headers_gt POH,

6498: SUBSTR (g_linemsg || g_delim || POL.line_num ||g_delim ||
6499: l_textline,1,240),
6500: 'PO_SUB_LINE_CONTRACT_HOLD'
6501: FROM po_lines_gt POL,
6502: po_headers_gt POH,
6503: po_headers_all POC
6504: WHERE POH.po_header_id = p_document_id
6505: AND NVL(POH.authorization_status, 'INCOMPLETE') = 'INCOMPLETE'
6506: AND POL.po_header_id = POH.po_header_id

Line 6558: po_headers_gt POH,

6554: SUBSTR (g_linemsg || g_delim || POL.line_num ||g_delim ||
6555: l_textline,1,240),
6556: 'PO_SUB_LINE_CONTRACT_EXP'
6557: FROM po_lines_gt POL,
6558: po_headers_gt POH,
6559: po_headers_all POC
6560: WHERE POH.po_header_id = p_document_id
6561: AND NVL(POH.authorization_status, 'INCOMPLETE') = 'INCOMPLETE'
6562: AND POL.po_header_id = POH.po_header_id

Line 6614: po_headers_gt POH,

6610: SUBSTR (g_linemsg || g_delim || POL.line_num ||g_delim ||
6611: l_textline,1,240),
6612: 'PO_ATO_ITEM_NA'
6613: FROM po_lines_gt POL,
6614: po_headers_gt POH,
6615: financials_system_parameters FSP,
6616: mtl_system_items MSI
6617: WHERE POH.po_header_id = p_document_id
6618: AND POL.po_header_id = POH.po_header_id

Line 6667: from po_headers_gt ph, po_lines_gt pl,mtl_system_items itm,financials_system_parameters fsp,po_line_types_b plt

6663: 0,
6664: p_sequence + ROWNUM,
6665: substr(g_linemsg||g_delim||pl.line_num||g_delim||l_textline,1,240),
6666: 'PO_ALL_NO_ITEM'
6667: from po_headers_gt ph, po_lines_gt pl,mtl_system_items itm,financials_system_parameters fsp,po_line_types_b plt
6668: where itm.inventory_item_id = pl.item_id
6669: and pl.item_id is not null
6670: and itm.organization_id = fsp.inventory_organization_id
6671: and itm.purchasing_enabled_flag = 'N'

Line 6714: from po_headers_gt ph,po_lines_gt pl,po_line_locations_gt pll,mtl_system_items itm,po_line_types_b plt

6710: p_sequence + ROWNUM,
6711: substr(g_linemsg||g_delim||pl.line_num||g_delim||
6712: g_shipmsg||g_delim||pll.shipment_num||g_delim||l_textline,1,240),
6713: 'PO_ALL_NO_ITEM'
6714: from po_headers_gt ph,po_lines_gt pl,po_line_locations_gt pll,mtl_system_items itm,po_line_types_b plt
6715: where itm.inventory_item_id = pl.item_id
6716: and pl.item_id is not null
6717: and itm.organization_id = pll.ship_to_organization_id
6718: and itm.purchasing_enabled_flag = 'N'

Line 6779: po_headers_gt POH,

6775: SUBSTR (g_linemsg || g_delim || POL.line_num ||g_delim ||
6776: g_shipmsg || g_delim || PLL.shipment_num ||g_delim || l_textline,1,240),
6777: 'PO_PO_PLANNED_ITEM_DATE_REQ'
6778: FROM po_lines_gt POL,
6779: po_headers_gt POH,
6780: po_line_locations_gt PLL,
6781: financials_system_parameters FSP,
6782: mtl_system_items MSI
6783: WHERE POH.po_header_id = p_document_id

Line 7029: PO_HEADERS_GT POH,

7025: 1,240),
7026: 'PO_SUB_VMI_ASL_EXISTS',
7027: 'W'
7028: FROM PO_LINES_GT POL,
7029: PO_HEADERS_GT POH,
7030: PO_LINE_LOCATIONS_GT PLL,
7031: PO_APPROVED_SUPPLIER_LIS_VAL_V PASL,
7032: PO_ASL_ATTRIBUTES PAA,
7033: PO_ASL_STATUS_RULES_V PASR

Line 7251: FROM PO_HEADERS_GT POH,

7247: p_sequence + ROWNUM,
7248: substr(g_linemsg||g_delim||POL.line_num||g_delim||g_shipmsg||g_delim||
7249: PLL.shipment_num||g_delim||l_textline,1,240),
7250: 'PO_SUB_PO_SHIP_INV_MATCH_NE_R'
7251: FROM PO_HEADERS_GT POH,
7252: PO_LINES_GT POL,
7253: PO_LINE_LOCATIONS_GT PLL
7254: WHERE POH.po_header_id = POL.po_header_id
7255: AND POL.po_line_id = PLL.po_line_id

Line 7302: FROM PO_HEADERS_GT POH,

7298: p_sequence + ROWNUM,
7299: substr(g_linemsg||g_delim||POL.line_num||g_delim||g_shipmsg||g_delim||PLL.shipment_num||g_delim
7300: ||g_distmsg||g_delim||POD.distribution_num||g_delim||l_textline,1,240),
7301: 'PO_SUB_PO_DIST_DEST_TYPE_NE_IN'
7302: FROM PO_HEADERS_GT POH,
7303: PO_LINES_GT POL,
7304: PO_LINE_LOCATIONS_GT PLL,
7305: PO_DISTRIBUTIONS_GT POD
7306: WHERE POH.po_header_id = POD.po_header_id

Line 7413: FROM PO_HEADERS_GT POH

7409: 0,
7410: p_sequence + ROWNUM,
7411: substr(l_textline,1,240),
7412: 'PO_SUB_AGREED_GRT_LIMIT'
7413: FROM PO_HEADERS_GT POH
7414: WHERE POH.po_header_id = p_document_id
7415: AND POH.blanket_total_amount is not null
7416: AND POH.amount_limit is not null
7417: AND POH.blanket_total_amount > POH.amount_limit;

Line 7462: FROM PO_HEADERS_GT POH

7458: 0,
7459: p_sequence + ROWNUM,
7460: substr(l_textline,1,240),
7461: 'PO_SUB_MINREL_GRT_LIMIT'
7462: FROM PO_HEADERS_GT POH
7463: WHERE POH.po_header_id = p_document_id
7464: AND POH.min_release_amount is not null
7465: AND POH.amount_limit is not null
7466: AND POH.min_release_amount > POH.amount_limit;

Line 7511: FROM PO_HEADERS_GT POH

7507: 0,
7508: p_sequence + ROWNUM,
7509: substr(l_textline,1,240),
7510: 'PO_SUB_LIMIT_GRT_REL_AMT'
7511: FROM PO_HEADERS_GT POH
7512: WHERE POH.po_header_id = p_document_id
7513: AND POH.amount_limit is not null
7514: AND ( (NVL(POH.global_agreement_flag, 'N') = 'N'
7515: AND

Line 7720: FROM PO_HEADERS_GT POH

7716: 0, 0, 0,
7717: p_sequence + ROWNUM,
7718: substr(l_textline,1,240),
7719: 'PO_SUB_HEADER_NO_LINES'
7720: FROM PO_HEADERS_GT POH
7721: WHERE POH.po_header_id = p_document_id AND
7722: NOT EXISTS (SELECT 'Lines Exist'
7723: FROM PO_LINES_GT POL
7724: WHERE POL.po_header_id = POH.po_header_id);

Line 7811: from po_headers_gt ph,po_lines_gt pl,mtl_system_items itm,financials_system_parameters fsp,po_line_types_b plt

7807: 0,
7808: p_sequence + ROWNUM,
7809: substr(g_linemsg||g_delim||pl.line_num||g_delim||l_textline,1,240),
7810: 'PO_ALL_NO_ITEM'
7811: from po_headers_gt ph,po_lines_gt pl,mtl_system_items itm,financials_system_parameters fsp,po_line_types_b plt
7812: where itm.inventory_item_id = pl.item_id
7813: and pl.item_id is not null
7814: and itm.organization_id = fsp.inventory_organization_id
7815: and itm.purchasing_enabled_flag = 'N'

Line 7905: FROM po_headers_gt ph, po_lines_gt pl, po_line_locations_gt pll

7901: THEN PO_MESSAGE_S.POX_EFFECTIVE_DATES6
7902: WHEN pll.end_date > pl.expiration_date
7903: THEN PO_MESSAGE_S.POX_EFFECTIVE_DATES2
7904: END
7905: FROM po_headers_gt ph, po_lines_gt pl, po_line_locations_gt pll
7906: WHERE ph.po_header_id = p_document_id
7907: AND pl.po_header_id = ph.po_header_id
7908: AND pll.po_line_id = pl.po_line_id
7909: AND pll.shipment_type = 'PRICE BREAK'

Line 7956: FROM po_headers_gt ph, po_lines_gt pl

7952: 0,
7953: p_sequence + ROWNUM,
7954: substr(g_linemsg||g_delim||pl.line_num||g_delim||l_textline,1,240),
7955: PO_MESSAGE_S.POX_EXPIRATION_DATES
7956: FROM po_headers_gt ph, po_lines_gt pl
7957: WHERE ph.po_header_id = p_document_id
7958: AND pl.po_header_id = ph.po_header_id
7959: AND (pl.expiration_date < ph.start_date
7960: or pl.expiration_date > ph.end_date);

Line 8030: FROM PO_HEADERS_GT POH, PO_LINES_GT POL, PO_HEADERS_ALL POHA

8026: -- Bug 2818810. Added extra join to alias POHA to return 'Y' only if at least
8027: -- one line references a GA.
8028: CURSOR std_ga_ref_cursor(p_document_id NUMBER) IS
8029: SELECT 'Y'
8030: FROM PO_HEADERS_GT POH, PO_LINES_GT POL, PO_HEADERS_ALL POHA
8031: WHERE POH.po_header_id = p_document_id
8032: AND POH.po_header_id = POL.po_header_id
8033: AND POL.from_header_id = POHA.po_header_id
8034: AND POHA.type_lookup_code = 'BLANKET'

Line 8329: FROM PO_HEADERS_GT POH,

8325: l_consigned_flag,
8326: l_line_num,
8327: l_shipment_num,
8328: l_line_location_id
8329: FROM PO_HEADERS_GT POH,
8330: PO_LINE_LOCATIONS_GT PLL,
8331: PO_LINES_GT POL
8332: WHERE POH.po_header_id = p_document_id AND
8333: POH.po_header_id = POL.po_header_id AND

Line 8567: FROM PO_HEADERS_GT POH1, PO_LINES_GT POL, PO_HEADERS_ALL POH

8563: p_sequence + ROWNUM,
8564: substr(g_linemsg||g_delim||POL.line_num||g_delim
8565: ||l_textline,1,240),
8566: 'PO_SUB_STD_GA_DISABLED'
8567: FROM PO_HEADERS_GT POH1, PO_LINES_GT POL, PO_HEADERS_ALL POH
8568: WHERE POH1.po_header_id = p_document_id
8569: AND POL.po_header_id = POH1.po_header_id
8570: AND POL.from_header_id = POH.po_header_id --JOIN
8571: AND POH.type_lookup_code = 'BLANKET'

Line 8635: FROM PO_HEADERS_GT POH1, PO_LINES_GT POL, PO_HEADERS_ALL POH2

8631: p_sequence + ROWNUM,
8632: substr(g_linemsg||g_delim||POL.line_num||g_delim
8633: ||l_textline,1,240),
8634: 'PO_SUB_STD_GA_APPROVED'
8635: FROM PO_HEADERS_GT POH1, PO_LINES_GT POL, PO_HEADERS_ALL POH2
8636: WHERE POH1.po_header_id = p_document_id
8637: AND POL.po_header_id = POH1.po_header_id --JOIN
8638: AND POH2.po_header_id = POL.from_header_id --JOIN
8639: AND POH2.type_lookup_code = 'BLANKET'

Line 8692: FROM PO_HEADERS_GT POH1, PO_LINES_GT POL, PO_HEADERS_ALL POH2

8688: p_sequence + ROWNUM,
8689: substr(g_linemsg||g_delim||POL.line_num||g_delim
8690: ||l_textline,1,240),
8691: 'PO_SUB_STD_GA_ON_HOLD'
8692: FROM PO_HEADERS_GT POH1, PO_LINES_GT POL, PO_HEADERS_ALL POH2
8693: WHERE POH1.po_header_id = p_document_id
8694: AND POL.po_header_id = POH1.po_header_id --JOIN
8695: AND POH2.po_header_id = POL.from_header_id --JOIN
8696: AND POH2.type_lookup_code = 'BLANKET'

Line 8752: FROM PO_LINES_GT POL, PO_HEADERS_GT POH1, PO_HEADERS_ALL POH2

8748: p_sequence + ROWNUM,
8749: substr(g_linemsg||g_delim||POL.line_num||g_delim
8750: ||l_textline,1,240),
8751: 'PO_SUB_STD_GA_VENDOR_MISMATCH'
8752: FROM PO_LINES_GT POL, PO_HEADERS_GT POH1, PO_HEADERS_ALL POH2
8753: WHERE POL.po_header_id = p_document_id
8754: AND POL.po_header_id = POH1.po_header_id --JOIN
8755: AND POL.from_header_id = POH2.po_header_id --JOIN
8756: AND POH2.type_lookup_code = 'BLANKET'

Line 8806: FROM PO_LINES_GT POL, PO_HEADERS_GT POH1, PO_HEADERS_ALL POH2

8802: p_sequence + ROWNUM,
8803: substr(g_linemsg||g_delim||POL.line_num||g_delim
8804: ||l_textline,1,240),
8805: 'PO_SUB_STD_GA_VDR_SITE_MISMT'
8806: FROM PO_LINES_GT POL, PO_HEADERS_GT POH1, PO_HEADERS_ALL POH2
8807: WHERE POL.po_header_id = p_document_id
8808: AND POL.po_header_id = POH1.po_header_id --JOIN
8809: AND POL.from_header_id = POH2.po_header_id --JOIN
8810: AND POH2.type_lookup_code = 'BLANKET'

Line 8871: FROM PO_LINES_GT POL, PO_HEADERS_GT POH, PO_HEADERS_ALL POH2, PO_LINES_ALL POL2, PO_LINE_LOCATIONS_GT PLL

8867: p_sequence + ROWNUM,
8868: substr(g_linemsg||g_delim||POL.line_num||g_delim
8869: ||l_textline,1,240),
8870: 'PO_SUB_STD_AFTER_GA_DATE'
8871: FROM PO_LINES_GT POL, PO_HEADERS_GT POH, PO_HEADERS_ALL POH2, PO_LINES_ALL POL2, PO_LINE_LOCATIONS_GT PLL
8872: WHERE POL.po_header_id = p_document_id
8873: AND POL.po_header_id = POH.po_header_id -- JOIN
8874: AND PLL.po_line_id = POL.po_line_id --JOIN, Bug #5415428 - Get the Need by date
8875: AND POL.from_header_id = POH2.po_header_id --JOIN

Line 8927: FROM PO_LINES_GT POL, PO_HEADERS_GT POH, PO_HEADERS_ALL POH2, PO_LINE_LOCATIONS_GT PLL

8923: p_sequence + ROWNUM,
8924: substr(g_linemsg||g_delim||POL.line_num||g_delim||
8925: g_shipmsg||g_delim||PLL.shipment_num||g_delim||l_textline,1,240),
8926: 'PO_SUB_STD_BEFORE_GA_DATE'
8927: FROM PO_LINES_GT POL, PO_HEADERS_GT POH, PO_HEADERS_ALL POH2, PO_LINE_LOCATIONS_GT PLL
8928: WHERE POL.po_header_id = p_document_id
8929: AND POL.po_header_id = POH.po_header_id
8930: AND PLL.po_line_id = POL.po_line_id --JOIN
8931: AND POL.from_header_id = POH2.po_header_id --JOIN

Line 8980: FROM PO_LINES_GT POL, PO_HEADERS_GT POH1, PO_HEADERS_ALL POH2

8976: p_sequence + ROWNUM,
8977: substr(g_linemsg||g_delim||POL.line_num||g_delim
8978: ||l_textline,1,240),
8979: 'PO_SUB_STD_GA_CURR_MISMATCH'
8980: FROM PO_LINES_GT POL, PO_HEADERS_GT POH1, PO_HEADERS_ALL POH2
8981: WHERE POL.po_header_id = p_document_id
8982: AND POL.po_header_id = POH1.po_header_id --JOIN
8983: AND POL.from_header_id = POH2.po_header_id --JOIN
8984: AND POH2.type_lookup_code = 'BLANKET'

Line 9460: po_headers_gt POH,

9456: 1,
9457: 240),
9458: 'PA_SUB_STD_GC_NOT_EN_PUR'
9459: FROM
9460: po_headers_gt POH,
9461: po_lines_gt POL,
9462: po_headers_all POHA
9463: WHERE
9464: POH.po_header_id = p_document_id

Line 9533: po_headers_gt POH,

9529: 1,
9530: 240),
9531: 'PA_SUB_STD_GC_INVALID_SITE'
9532: FROM
9533: po_headers_gt POH,
9534: po_lines_gt POL,
9535: po_headers_all POHA
9536: WHERE
9537: POH.po_header_id = p_document_id

Line 9844: FROM PO_HEADERS_GT POH

9840: -- bug5153099
9841: -- Removed group by clause in subquery.Also removed the checking for
9842: -- global_agreement_flag and the corresponding OR case logic.
9843:
9844: FROM PO_HEADERS_GT POH
9845: WHERE POH.po_header_id = p_document_id
9846: AND POH.type_lookup_code = 'CONTRACT'
9847: AND POH.amount_limit IS NOT NULL
9848: AND ((POH.amount_limit * NVL(POH.rate, 1)) -- amt limit in fn currency --

Line 10135: DELETE FROM po_headers_gt;

10131: -- bug3413891
10132: -- Clean up gt tables to make sure that all the records in GT tables are from
10133: -- the same document
10134:
10135: DELETE FROM po_headers_gt;
10136: DELETE FROM po_lines_gt;
10137: DELETE FROM po_line_locations_gt;
10138: DELETE FROM po_distributions_gt;
10139: DELETE FROM po_releases_gt;

Line 10193: populate_po_headers_gt(l_doc_id, l_return_status);

10189: END IF;
10190: END IF;
10191:
10192: --populate the global headers table
10193: populate_po_headers_gt(l_doc_id, l_return_status);
10194:
10195: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
10196: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
10197: END IF;

Line 10242: populate_po_headers_gt(l_blanket_header_id, l_return_status);

10238:
10239: l_progress := '010';
10240: --populate the global headers table with header of blanket/planned PO
10241: --for which this is a Release
10242: populate_po_headers_gt(l_blanket_header_id, l_return_status);
10243:
10244: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
10245: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
10246: END IF;

Line 10824: * Private Procedure: POPULATE_PO_HEADERS_GT

10820:
10821: END UPDATE_GLOBAL_TEMP_TABLES;
10822:
10823: /**
10824: * Private Procedure: POPULATE_PO_HEADERS_GT
10825: * Requires:
10826: * IN PARAMETERS:
10827: * p_document_id: Id of submitted document
10828: * Modifies:

Line 10829: * Effects: Populates the global temp tables po_headers_gt

10825: * Requires:
10826: * IN PARAMETERS:
10827: * p_document_id: Id of submitted document
10828: * Modifies:
10829: * Effects: Populates the global temp tables po_headers_gt
10830: * Returns:
10831: */
10832: PROCEDURE populate_po_headers_gt(p_document_id IN number,
10833: x_return_status OUT NOCOPY VARCHAR2)IS

Line 10832: PROCEDURE populate_po_headers_gt(p_document_id IN number,

10828: * Modifies:
10829: * Effects: Populates the global temp tables po_headers_gt
10830: * Returns:
10831: */
10832: PROCEDURE populate_po_headers_gt(p_document_id IN number,
10833: x_return_status OUT NOCOPY VARCHAR2)IS
10834:
10835: l_api_name CONSTANT varchar2(40) := 'POPULATE_PO_HEADERS_GT';
10836: l_progress VARCHAR2(3);

Line 10835: l_api_name CONSTANT varchar2(40) := 'POPULATE_PO_HEADERS_GT';

10831: */
10832: PROCEDURE populate_po_headers_gt(p_document_id IN number,
10833: x_return_status OUT NOCOPY VARCHAR2)IS
10834:
10835: l_api_name CONSTANT varchar2(40) := 'POPULATE_PO_HEADERS_GT';
10836: l_progress VARCHAR2(3);
10837:
10838: t_po_header_id NUMBER;
10839: t_segment1 po_headers.segment1%TYPE;

Line 10851: INSERT INTO po_headers_gt(

10847: || l_progress,'Populate HEADERS');
10848: END IF;
10849: END IF;
10850:
10851: INSERT INTO po_headers_gt(
10852: PO_HEADER_ID,
10853: AGENT_ID,
10854: TYPE_LOOKUP_CODE,
10855: LAST_UPDATE_DATE,

Line 11128: from po_headers_gt where po_header_id = p_document_id;

11124:
11125: l_progress := '001';
11126: --SANITY check
11127: SELECT po_header_id, segment1 into t_po_header_id, t_segment1
11128: from po_headers_gt where po_header_id = p_document_id;
11129:
11130: IF g_debug_stmt THEN
11131: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
11132: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'

Line 11162: END POPULATE_PO_HEADERS_GT;

11158: || l_progress || ' SQL CODE is '||sqlcode);
11159: END IF;
11160: END IF;
11161:
11162: END POPULATE_PO_HEADERS_GT;
11163:
11164:
11165: --
11166: -------------------------------------------------------------------------------

Line 11524: * Effects: Populates the global temp tables po_headers_gt

11520: * Requires:
11521: * IN PARAMETERS:
11522: * p_document_id: Id of submitted document
11523: * Modifies:
11524: * Effects: Populates the global temp tables po_headers_gt
11525: * Returns:
11526: */
11527: PROCEDURE populate_releases_gt(p_document_id IN NUMBER,
11528: x_return_status OUT NOCOPY VARCHAR2) IS

Line 11751: * Effects: Populates the global temp tables po_headers_gt

11747: * Requires:
11748: * IN PARAMETERS:
11749: * p_document_id: Id of submitted document
11750: * Modifies:
11751: * Effects: Populates the global temp tables po_headers_gt
11752: * Returns:
11753: */
11754: PROCEDURE populate_req_headers_gt(p_document_id IN NUMBER,
11755: x_return_status OUT NOCOPY VARCHAR2) IS

Line 11926: * Effects: Populates the global temp tables po_headers_gt

11922: * Requires:
11923: * IN PARAMETERS:
11924: * p_document_id: Id of submitted document
11925: * Modifies:
11926: * Effects: Populates the global temp tables po_headers_gt
11927: * Returns:
11928: */
11929: PROCEDURE populate_req_lines_gt(p_document_id IN NUMBER,
11930: x_return_status OUT NOCOPY VARCHAR2) IS

Line 12259: * Effects: Populates the global temp tables po_headers_gt

12255: * Requires:
12256: * IN PARAMETERS:
12257: * p_document_id: Id of submitted document
12258: * Modifies:
12259: * Effects: Populates the global temp tables po_headers_gt
12260: * Returns:
12261: */
12262: PROCEDURE populate_req_distributions_gt(
12263: p_document_id IN NUMBER

Line 13287: delete from po_headers_gt;

13283: END LOOP;
13284:
13285: l_progress := '020';
13286:
13287: delete from po_headers_gt;
13288:
13289: -- For all the entities with null p_release_id(i), get Header status fields into
13290: -- global temprary table while storing sequence into po_headers_gt.PO_HEADER_ID column
13291: l_progress := '030';

Line 13290: -- global temprary table while storing sequence into po_headers_gt.PO_HEADER_ID column

13286:
13287: delete from po_headers_gt;
13288:
13289: -- For all the entities with null p_release_id(i), get Header status fields into
13290: -- global temprary table while storing sequence into po_headers_gt.PO_HEADER_ID column
13291: l_progress := '030';
13292: -- bug 4931241 modified the sql query to avoid FTS on PO_HEADERS_ALL
13293: -- Removed the where clause for document num and vendor_order_num
13294: -- They do not uniquely identify the document. Added validation in group package

Line 13297: INTO po_headers_gt

13293: -- Removed the where clause for document num and vendor_order_num
13294: -- They do not uniquely identify the document. Added validation in group package
13295: FORALL i IN 1..p_count
13296: INSERT
13297: INTO po_headers_gt
13298: ( AGENT_ID, TYPE_LOOKUP_CODE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
13299: SEGMENT1, SUMMARY_FLAG, ENABLED_FLAG,
13300: authorization_status, approved_flag,
13301: closed_code, cancel_flag,

Line 13315: -- global temprary table while storing sequence into po_headers_gt.PO_HEADER_ID column

13311: AND h.po_header_id = p_header_id(i)
13312: ;
13313:
13314: -- For all the entities with non-null p_release_id(i), get Relase status fields into
13315: -- global temprary table while storing sequence into po_headers_gt.PO_HEADER_ID column
13316: l_progress := '035';
13317: FORALL i IN 1..p_count
13318: INSERT
13319: INTO po_headers_gt

Line 13319: INTO po_headers_gt

13315: -- global temprary table while storing sequence into po_headers_gt.PO_HEADER_ID column
13316: l_progress := '035';
13317: FORALL i IN 1..p_count
13318: INSERT
13319: INTO po_headers_gt
13320: ( AGENT_ID, TYPE_LOOKUP_CODE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
13321: SEGMENT1, SUMMARY_FLAG, ENABLED_FLAG,
13322: authorization_status, approved_flag,
13323: closed_code, cancel_flag,

Line 13338: UPDATE po_headers_gt gt

13334:
13335: --IF line ID present at an index, overwrite the status fields with Line Level status
13336: l_progress := '040';
13337: FORALL i IN 1..p_count
13338: UPDATE po_headers_gt gt
13339: SET (closed_code, cancel_flag, user_hold_flag)
13340: =
13341: (SELECT nvl(closed_code, 'OPEN'), NVL(cancel_flag, 'N'), NVL(user_hold_flag, 'N')
13342: FROM po_lines_all s

Line 13350: UPDATE po_headers_gt gt

13346:
13347: --IF line location present at an index, overwrite status fields with Shipment Level status
13348: l_progress := '050';
13349: FORALL i IN 1..p_count
13350: UPDATE po_headers_gt gt
13351: SET (approved_flag, closed_code, cancel_flag)
13352: =
13353: (SELECT nvl(approved_flag, 'N'), nvl(closed_code, 'OPEN'), NVL(cancel_flag, 'N')
13354: FROM po_line_locations_all s

Line 13369: FROM po_headers_gt

13365: authorization_status, approved_flag, closed_code, cancel_flag, frozen_flag, user_hold_flag
13366: BULK COLLECT INTO
13367: x_po_status_rec.authorization_status, x_po_status_rec.approval_flag, x_po_status_rec.closed_code,
13368: x_po_status_rec.cancel_flag, x_po_status_rec.frozen_flag, x_po_status_rec.hold_flag
13369: FROM po_headers_gt
13370: ORDER BY PO_HEADER_ID;
13371:
13372: x_return_status := FND_API.G_RET_STS_SUCCESS;
13373:

Line 14816: , PO_HEADERS_GT POH

14812: FROM
14813: PO_DISTRIBUTIONS_GT POD
14814: , PO_LINE_LOCATIONS_GT POLL
14815: , PO_LINES_ALL POL -- For Releases, PO_LINES_GT doesn't get populated.
14816: , PO_HEADERS_GT POH
14817: WHERE POLL.line_location_id(+) = POD.line_location_id --JOIN
14818: AND POL.po_line_id(+) = POD.po_line_id --JOIN
14819: -- PA distributions don't have associated lines or shipments
14820: AND POH.po_header_id = POD.po_header_id --JOIN

Line 15666: FROM po_headers_gt poh

15662: , 0
15663: , p_sequence + ROWNUM
15664: , substr(l_textline, 1, 240)
15665: , 'PO_CAN_POH_WITH_RCV_TRX'
15666: FROM po_headers_gt poh
15667: WHERE EXISTS
15668: (
15669: SELECT 'Eligible shipment'
15670: FROM po_line_locations_gt poll

Line 16637: FROM po_headers_gt poh

16633: , 0
16634: , p_sequence + ROWNUM
16635: , substr(l_textline,1,240) --Bug5096900
16636: , 'PO_CAN_CGA_WITH_OPEN_STD_REF'
16637: FROM po_headers_gt poh
16638: WHERE EXISTS
16639: (
16640: SELECT 'Open Std PO lines referencing this contract exist'
16641: FROM po_lines_all pol

Line 17449: FROM po_headers_gt poh

17445: , null -- distribution_num
17446: , p_sequence + ROWNUM
17447: , substr(l_textline, 1, 240)
17448: , l_message_name
17449: FROM po_headers_gt poh
17450: WHERE poh.po_header_id = p_doc_level_id
17451: AND chk_unv_invoices(l_invoice_type, poh.po_header_id, NULL, NULL,NULL, NULL, p_origin_doc_id, l_calling_sequence) = 1;
17452:
17453: --