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_LINES

Line 66: PROCEDURE populate_po_lines_gt(

62: , p_doc_level_id IN NUMBER
63: );
64:
65: --
66: PROCEDURE populate_po_lines_gt(
67: p_doc_type IN VARCHAR2
68: , p_doc_level IN VARCHAR2
69: , p_doc_level_id IN NUMBER
70: , x_return_status OUT NOCOPY VARCHAR2

Line 560: l_user_id po_lines.last_updated_by%TYPE := -1;

556: l_num_messages NUMBER := 0; -- bug3574165: Changed l_num_errors to l_num_messages
557: l_num_warnings NUMBER := 0; -- bug3574165
558:
559: l_online_report_id NUMBER;
560: l_user_id po_lines.last_updated_by%TYPE := -1;
561: l_login_id po_lines.last_update_login%TYPE := -1;
562: p_sequence po_online_report_text.sequence%TYPE :=0;
563:
564: l_return_status varchar2(1);

Line 561: l_login_id po_lines.last_update_login%TYPE := -1;

557: l_num_warnings NUMBER := 0; -- bug3574165
558:
559: l_online_report_id NUMBER;
560: l_user_id po_lines.last_updated_by%TYPE := -1;
561: l_login_id po_lines.last_update_login%TYPE := -1;
562: p_sequence po_online_report_text.sequence%TYPE :=0;
563:
564: l_return_status varchar2(1);
565:

Line 1450: l_user_id PO_LINES.last_updated_by%TYPE := -1;

1446: l_progress VARCHAR2(3);
1447:
1448: l_num_errors NUMBER := 0;
1449: l_online_report_id NUMBER;
1450: l_user_id PO_LINES.last_updated_by%TYPE := -1;
1451: l_login_id PO_LINES.last_update_login%TYPE := -1;
1452: p_sequence PO_ONLINE_REPORT_TEXT.sequence%TYPE :=0;
1453:
1454: l_return_status VARCHAR2(1);

Line 1451: l_login_id PO_LINES.last_update_login%TYPE := -1;

1447:
1448: l_num_errors NUMBER := 0;
1449: l_online_report_id NUMBER;
1450: l_user_id PO_LINES.last_updated_by%TYPE := -1;
1451: l_login_id PO_LINES.last_update_login%TYPE := -1;
1452: p_sequence PO_ONLINE_REPORT_TEXT.sequence%TYPE :=0;
1453:
1454: l_return_status VARCHAR2(1);
1455:

Line 2676: FROM PO_LINE_LOCATIONS_GT PLL,PO_LINES POL

2672: decode ( POL.order_type_lookup_code --
2673: , 'FIXED PRICE' , 'PO_SUB_REL_SHIP_PRICE_GT_LIMIT'
2674: , 'PO_SUB_REL_SHIP_AMT_GT_LIMIT'
2675: )
2676: FROM PO_LINE_LOCATIONS_GT PLL,PO_LINES POL
2677: WHERE PLL.po_line_id = POL.po_line_id
2678: AND PLL.po_release_id = p_document_id
2679: AND nvl(PLL.cancel_flag,'N')= 'N'
2680: AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'

Line 2890: PO_LINE_LOCATIONS_GT POLL,PO_LINES POL,

2886: ||g_distmsg||g_delim||POD.distribution_num||g_delim
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

Line 3100: FROM PO_LINES_ALL POL,PO_RELEASES_GT POR,PO_LINE_LOCATIONS_GT PLL

3096: l_shipment_num,
3097: l_dist_num,
3098: l_quantity1,
3099: l_quantity2
3100: FROM PO_LINES_ALL POL,PO_RELEASES_GT POR,PO_LINE_LOCATIONS_GT PLL
3101: WHERE PLL.po_release_id = POR.po_release_id
3102: AND PLL.po_release_id = p_document_id
3103: AND POL.po_line_id = PLL.po_line_id
3104: AND POL.min_release_amount is not null

Line 3243: PO_LINES POL, MTL_UOM_CLASSES_TL MTL1,

3239: substr(g_shipmsg||g_delim||POLL.shipment_num||g_delim||l_textline||
3240: MTL1.uom_class||' , '||MTL2.uom_class,1,240),
3241: 'PO_SUB_UOM_CLASS_CONVERSION'
3242: FROM MTL_UOM_CLASS_CONVERSIONS MOU, PO_LINE_LOCATIONS_GT POLL,
3243: PO_LINES POL, MTL_UOM_CLASSES_TL MTL1,
3244: MTL_UOM_CLASSES_TL MTL2
3245: WHERE MOU.inventory_item_id = POL.item_id
3246: AND (NVL(MOU.disable_date, TRUNC(SYSDATE)) + 1) < TRUNC(SYSDATE)
3247: AND POL.po_line_id = POLL.po_line_id

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 3493: FROM po_lines POL,

3489: substr(g_linemsg||g_delim||POL.line_num||g_delim
3490: ||g_shipmsg||g_delim||PLL.shipment_num||g_delim
3491: ||l_textline,1,240),
3492: 'PO_ATO_ITEM_NA'
3493: FROM po_lines POL,
3494: po_line_locations_gt PLL,
3495: financials_system_parameters FSP,
3496: mtl_system_items MSI
3497: WHERE PLL.po_release_id = p_document_id

Line 3548: from po_releases_gt por,po_lines pl,po_line_locations_gt pll,mtl_system_items itm,po_line_types_b plt

3544: 0,
3545: p_sequence + ROWNUM,
3546: substr(g_shipmsg||g_delim||pll.shipment_num||g_delim||l_textline,1,240),
3547: 'PO_ALL_NO_ITEM'
3548: from po_releases_gt por,po_lines pl,po_line_locations_gt pll,mtl_system_items itm,po_line_types_b plt
3549: where itm.inventory_item_id = pl.item_id
3550: and pl.item_id is not null
3551:
3552: and pl.po_line_id = pll.po_line_id

Line 4011: TYPE unit_of_measure IS TABLE of PO_LINES.unit_meas_lookup_code%TYPE;

4007: l_enforce_price_tolerance po_system_parameters.enforce_price_change_allowance%TYPE;
4008: l_enforce_price_amount po_system_parameters.enforce_price_change_amount%TYPE;
4009: l_amount_tolerance po_system_parameters.price_change_amount%TYPE;
4010:
4011: TYPE unit_of_measure IS TABLE of PO_LINES.unit_meas_lookup_code%TYPE;
4012: TYPE NumTab IS TABLE of NUMBER;
4013: l_ship_price_in_base_curr NumTab;
4014: l_ship_unit_of_measure unit_of_measure;
4015: l_ship_num NumTab;

Line 4082: PO_LINES_GT POL,

4078: nvl(POL.item_id,-1) item_id,
4079: nvl( POLL.line_location_id,0) line_loc_id
4080: FROM PO_LINE_LOCATIONS_GT POLL,
4081: PO_LINE_TYPES_B PLT, -- bug3413891
4082: PO_LINES_GT POL,
4083: PO_DISTRIBUTIONS_GT POD
4084: WHERE POLL.po_line_id = POL.po_line_id
4085: AND POLL.line_location_id = POD.line_location_id
4086: AND POLL.po_header_id = p_document_id

Line 4116: PO_LINES_GT POL,

4112: POL.quantity quantity,
4113: nvl(POL.item_id,-1) item_id,
4114: POLL.line_location_id line_loc_id
4115: FROM PO_LINE_LOCATIONS_GT POLL,
4116: PO_LINES_GT POL,
4117: PO_DISTRIBUTIONS_GT POD
4118: WHERE POL.po_header_id = p_document_id
4119: AND POD.line_location_id = POLL.line_location_id
4120: AND POLL.line_location_id =

Line 4163: PO_LINES POL,

4159: nvl(POL.item_id,0) item_id,
4160: nvl( POLL.line_location_id,0) line_loc_id
4161: FROM PO_LINE_LOCATIONS_GT POLL,
4162: PO_LINE_TYPES_B PLT, -- bug3413891
4163: PO_LINES POL,
4164: PO_DISTRIBUTIONS_GT POD --
4165: WHERE POLL.po_line_id = POL.po_line_id
4166: AND POLL.line_location_id = POD.line_location_id
4167: AND POLL.po_release_id = p_document_id

Line 4184: PO_LINES POL

4180: min(POLL.shipment_num),
4181: min(PRL.org_id) --
4182: FROM PO_REQUISITION_LINES_ALL PRL, --
4183: PO_LINE_LOCATIONS_GT POLL,
4184: PO_LINES POL
4185: WHERE PRL.line_location_id = POLL.line_location_id
4186: AND POLL.line_location_id = p_line_location_id
4187: AND PRL.unit_price >= 0
4188: AND POLL.po_line_id = POL.po_line_id

Line 4200: PO_LINES POL,

4196: min(POLL.shipment_num),
4197: min(PRL.org_id) --
4198: FROM PO_REQUISITION_LINES_ALL PRL, --
4199: PO_LINE_LOCATIONS_GT POLL,
4200: PO_LINES POL,
4201: PO_DISTRIBUTIONS PD,
4202: PO_REQ_DISTRIBUTIONS_ALL PRD --
4203: WHERE POLL.line_location_id = p_line_location_id
4204: AND POLL.po_line_id = POL.po_line_id

Line 5349: FROM po_lines_gt POL

5345: , l_textline
5346: , 'PO_SUB_USER_RATE_TYPE'
5347: FROM dual
5348: WHERE exists ( SELECT 'Rate-based lines exist'
5349: FROM po_lines_gt POL
5350: , po_line_types_b PLT
5351: WHERE p_document_id = POL.po_header_id
5352: AND POL.line_type_id = PLT.line_type_id
5353: AND PLT.order_type_lookup_code = 'RATE'

Line 5682: FROM PO_LINES_GT POL

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
5684: --Bug 3289638 Check for any line to exist irrespective of cancel_flag
5685: --AND nvl(POL.cancel_flag,'N') = 'N'
5686: );

Line 5739: FROM PO_LINES_GT POL

5735: 0,
5736: p_sequence + ROWNUM,
5737: substr(g_linemsg||g_delim||POL.line_num||g_delim||l_textline,1,240),
5738: 'PO_SUB_LINE_NO_SHIP'
5739: FROM PO_LINES_GT POL
5740: WHERE POL.po_header_id = p_document_id AND
5741: nvl(POL.cancel_flag,'N') = 'N' AND
5742: nvl(POL.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
5743: NOT EXISTS (SELECT 'Shipments Exist'

Line 5797: FROM PO_LINE_LOCATIONS_GT PLL,PO_LINES_GT POL

5793: p_sequence + ROWNUM,
5794: substr(g_linemsg||g_delim||POL.line_num||g_delim||
5795: g_shipmsg||g_delim||PLL.shipment_num||g_delim||l_textline,1,240),
5796: 'PO_SUB_SHIP_NO_DIST'
5797: FROM PO_LINE_LOCATIONS_GT PLL,PO_LINES_GT POL
5798: WHERE PLL.po_line_id = POL.po_line_id AND
5799: PLL.po_header_id = p_document_id AND
5800: nvl(PLL.cancel_flag, 'N') = 'N' AND
5801: nvl(POL.cancel_flag, 'N') = 'N' AND

Line 5878: FROM PO_DISTRIBUTIONS_GT POD,PO_LINE_LOCATIONS_GT PLL,PO_LINES_GT POL,

5874: ||g_shipmsg||g_delim||PLL.shipment_num||g_delim
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

Line 5937: FROM PO_DISTRIBUTIONS_GT POD,PO_LINE_LOCATIONS_GT PLL,PO_LINES_GT POL,

5933: ||g_shipmsg||g_delim||PLL.shipment_num||g_delim
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

Line 6053: FROM PO_LINES_GT POL,

6049: 0,
6050: p_sequence + ROWNUM,
6051: substr(g_linemsg||g_delim||POL.line_num||g_delim||l_textline,1,240),
6052: 'PO_SUB_REF_UNAPPROVED_CONTRACT'
6053: FROM PO_LINES_GT POL,
6054: PO_HEADERS_ALL POC -- : use all table
6055: WHERE POL.po_header_id = p_document_id
6056: AND POL.contract_id = POC.po_header_id --
6057: AND POC.type_lookup_code = 'CONTRACT'

Line 6111: FROM PO_LINES_GT POL,

6107: 0,
6108: p_sequence + ROWNUM,
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

Line 6206: FROM MTL_UOM_CLASS_CONVERSIONS MOU, PO_LINES_GT POL,

6202: p_sequence + ROWNUM,
6203: substr(g_linemsg||g_delim||POL.line_num||g_delim||l_textline||
6204: MTL1.uom_class||' , '||MTL2.uom_class,1,240),
6205: 'PO_SUB_UOM_CLASS_CONVERSION'
6206: FROM MTL_UOM_CLASS_CONVERSIONS MOU, PO_LINES_GT POL,
6207: MTL_UOM_CLASSES_TL MTL1, MTL_UOM_CLASSES_TL MTL2
6208: WHERE POL.item_id = MOU.inventory_item_id
6209: AND (nvl(MOU.disable_date, TRUNC(SYSDATE))+1) < TRUNC(SYSDATE)
6210: AND POL.po_header_id = p_document_id

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 6501: FROM po_lines_gt POL,

6497: p_sequence + ROWNUM,
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'

Line 6557: FROM po_lines_gt POL,

6553: p_sequence + ROWNUM,
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'

Line 6613: FROM po_lines_gt POL,

6609: p_sequence + ROWNUM,
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

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 6778: FROM po_lines_gt POL,

6774: p_sequence + ROWNUM,
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

Line 6820: po_lines_gt pol,

6816: l_line_num,
6817: l_shipment_num,
6818: l_ship_to_location_tbl
6819: FROM
6820: po_lines_gt pol,
6821: po_line_locations_gt poll,
6822: hr_locations_all hla,
6823: hr_locations_all_tl hlat
6824: WHERE poll.po_header_id=p_document_id

Line 7028: FROM PO_LINES_GT POL,

7024: l_textline,
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,

Line 7107: FROM po_lines_gt pol

7103: p_sequence + ROWNUM,
7104: po_core_s.GET_TRANSLATED_TEXT('PO_INVALID_OKE_CONTRACT_VER_ID',
7105: 'LINE_NUM',pol.line_num),
7106: 'PO_INVALID_OKE_CONTRACT_VER_ID'
7107: FROM po_lines_gt pol
7108: WHERE pol.oke_contract_version_id IS NOT NULL
7109: AND pol.oke_contract_version_id NOT IN (SELECT major_version
7110: FROM oke_k_vers_numbers_v
7111: WHERE chr_id = pol.oke_contract_header_id

Line 7151: po_lines_gt pol

7147: 'DIST_NUM',pod.distribution_num),
7148: 'PO_INVALID_OKE_CONTRACT_LNE_ID'
7149: FROM po_distributions_gt pod,
7150: po_line_locations_gt pos,
7151: po_lines_gt pol
7152: WHERE pod.po_line_id = pol.po_line_id
7153: AND pod.line_location_id = pos.line_location_id
7154: AND pod.oke_contract_line_id IS NOT NULL
7155: AND pod.oke_contract_line_id NOT IN (SELECT id

Line 7199: po_lines_gt pol

7195: 'DIST_NUM',pod.distribution_num),
7196: 'PO_INVALID_OKE_CONTRACT_DLV_ID'
7197: FROM po_distributions_gt pod,
7198: po_line_locations_gt pos,
7199: po_lines_gt pol
7200: WHERE pod.po_line_id = pol.po_line_id
7201: AND pod.line_location_id = pos.line_location_id
7202: AND pod.oke_contract_deliverable_id IS NOT NULL
7203: AND pod.oke_contract_deliverable_id NOT IN (SELECT deliverable_id

Line 7252: PO_LINES_GT POL,

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
7256: AND POH.po_header_id = p_document_id

Line 7303: PO_LINES_GT POL,

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
7307: AND POD.line_location_id = PLL.line_location_id

Line 7609: FROM PO_LINES_GT POL

7605: p_sequence + ROWNUM,
7606: substr(g_linemsg||g_delim||POL.line_num
7607: ||g_delim||l_textline,1,240),
7608: 'PO_SVC_PRICE_LIMIT_LT_PRICE'
7609: FROM PO_LINES_GT POL
7610: WHERE POL.po_header_id = p_document_id
7611: AND trunc(sysdate) <= trunc(nvl(POL.expiration_date, sysdate + 1)) -- bug 3449694
7612: AND nvl(POL.cancel_flag,'N')= 'N'
7613: AND nvl(POL.closed_code,'OPEN') <> 'FINALLY CLOSED'

Line 7723: FROM PO_LINES_GT POL

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);
7725: --AND nvl(POL.cancel_flag,'N') = 'N');
7726: -- bug 3300632
7727:

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 8040: FROM po_lines_gt POL,

8036:
8037: --
8038: CURSOR std_gc_ref_cursor (p_doc_id NUMBER) IS
8039: SELECT 'Y'
8040: FROM po_lines_gt POL,
8041: po_headers_all POHA
8042: WHERE POL.po_header_id = p_doc_id
8043: AND POL.contract_id = POHA.po_header_id
8044: AND POHA.global_agreement_flag = 'Y';

Line 8168: * 2) Document references cannot exist for PO lines with one or

8164: * project in order to enforce the following:
8165: * 1) Consigned status on any new or modified shipment that is not
8166: * partially received or partially invoiced should match the
8167: * consigned setting on the corresponding ASL entry.
8168: * 2) Document references cannot exist for PO lines with one or
8169: * more consigned shipments
8170: * Returns:
8171: * p_sequence: This parameter contains the current count of number of error
8172: * messages inserted

Line 8266: FROM po_lines_gt pol,

8262: p_sequence + ROWNUM,
8263: substr(g_linemsg||g_delim||POL.line_num||g_delim||g_shipmsg
8264: ||g_delim||PLL.shipment_num||':'||g_delim||l_textline,1,240),
8265: 'PO_DOC_REF_SUP_CONS_COEXIST'
8266: FROM po_lines_gt pol,
8267: po_line_locations_gt pll
8268: WHERE pol.po_header_id = p_document_id
8269: AND pol.po_line_id = pll.po_line_id
8270: AND pll.shipment_type = 'STANDARD'

Line 8331: PO_LINES_GT POL

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
8334: -- POH.po_header_id = PLL.po_header_id AND
8335: POL.po_line_id = PLL.po_line_id AND

Line 8537: --< Shared Proc FPJ > Bug 3301427: Only do this check for new SPO lines

8533: END IF;
8534:
8535: --
8536: --Check 1: The GA should be enabled for purchasing in the current OU.
8537: --< Shared Proc FPJ > Bug 3301427: Only do this check for new SPO lines
8538: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_STD_GA_DISABLED');
8539: INSERT into po_online_report_text_gt(online_report_id,
8540: last_update_login,
8541: last_updated_by,

Line 8552: --SQL Querying for PO lines that reference GAs that are not enabled

8548: sequence,
8549: text_line,
8550: message_name)
8551: --
8552: --SQL Querying for PO lines that reference GAs that are not enabled
8553: --SQL for purchasing in this org, to report an error message.
8554: SELECT p_online_report_id,
8555: p_login_id,
8556: p_user_id,

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 8603: --< Shared Proc FPJ > Bug 3301427: Only do this check for new SPO lines

8599: END IF;
8600:
8601: --Check 2: If the standard PO line is referencing a GA then that GA
8602: --should be in an approved status.
8603: --< Shared Proc FPJ > Bug 3301427: Only do this check for new SPO lines
8604: --< Bug 3422733 > Only do this check if GA is not ON HOLD. The ON HOLD check
8605: -- is done later. Avoids showing 2 msgs for Contract that is ON HOLD.
8606:
8607: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_STD_GA_APPROVED');

Line 8620: --SQL What: Querying for PO lines that reference GAs that are not approved.

8616: distribution_num,
8617: sequence,
8618: text_line,
8619: message_name)
8620: --SQL What: Querying for PO lines that reference GAs that are not approved.
8621: --SQL Why: Add appropriate error message to po_online_report_text_gt
8622: SELECT p_online_report_id,
8623: p_login_id,
8624: p_user_id,

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 8663: --< Shared Proc FPJ > Bug 3301427: Only do this check for new SPO lines

8659: END IF;
8660: END IF;
8661:
8662: --Check 3: The GA should not be on hold.
8663: --< Shared Proc FPJ > Bug 3301427: Only do this check for new SPO lines
8664: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_STD_GA_ON_HOLD');
8665: INSERT into po_online_report_text_gt(online_report_id,
8666: last_update_login,
8667: last_updated_by,

Line 8677: --SQL What: Querying for PO lines that reference GAs that are on hold

8673: distribution_num,
8674: sequence,
8675: text_line,
8676: message_name)
8677: --SQL What: Querying for PO lines that reference GAs that are on hold
8678: --SQL Why: Add appropriate error message to po_online_report_text_gt
8679: SELECT p_online_report_id,
8680: p_login_id,
8681: p_user_id,

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 8736: --SQL What: Querying for PO lines whose vendor does not

8732: distribution_num,
8733: sequence,
8734: text_line,
8735: message_name)
8736: --SQL What: Querying for PO lines whose vendor does not
8737: --SQL match the vendor of the referenced GA
8738: --SQL Why: Add appropriate error message to po_online_report_text_gt
8739: SELECT p_online_report_id,
8740: p_login_id,

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 8775: --< Shared Proc FPJ > Bug 3301427: Only do this check for new SPO lines

8771: END IF;
8772:
8773: --Check 5: The vendor site on the PO should match a vendor site on one of
8774: -- the GA's enabled org assignments.
8775: --< Shared Proc FPJ > Bug 3301427: Only do this check for new SPO lines
8776: l_textline :=
8777: FND_MESSAGE.GET_STRING('PO', 'PO_SUB_STD_GA_VDR_SITE_MISMT');
8778: INSERT into po_online_report_text_gt(online_report_id,
8779: last_update_login,

Line 8790: --SQL What: Querying for PO lines whose vendor_site_id does not

8786: distribution_num,
8787: sequence,
8788: text_line,
8789: message_name)
8790: --SQL What: Querying for PO lines whose vendor_site_id does not
8791: --SQL match a valid vendor_site_id in the GA's org assignments
8792: --SQL Why: Add appropriate error message to po_online_report_text_gt
8793: SELECT p_online_report_id,
8794: p_login_id,

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 8855: --SQL What: Querying for PO lines that were not created within

8851: distribution_num,
8852: sequence,
8853: text_line,
8854: message_name)
8855: --SQL What: Querying for PO lines that were not created within
8856: --SQL the effective dates of the referenced GA
8857: --SQL Why: Add appropriate error message to po_online_report_text_gt
8858: SELECT p_online_report_id,
8859: p_login_id,

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 8911: --SQL What: Querying for PO lines that were not created within

8907: distribution_num,
8908: sequence,
8909: text_line,
8910: message_name)
8911: --SQL What: Querying for PO lines that were not created within
8912: --SQL the effective dates of the referenced GA
8913: --SQL Why: Add appropriate error message to po_online_report_text_gt
8914: SELECT p_online_report_id,
8915: p_login_id,

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 8964: --SQL What: Querying for PO lines whose currency code does not

8960: distribution_num,
8961: sequence,
8962: text_line,
8963: message_name)
8964: --SQL What: Querying for PO lines whose currency code does not
8965: --SQL match the currency code of the referenced GA
8966: --SQL Why: Add appropriate error message to po_online_report_text_gt
8967: SELECT p_online_report_id,
8968: p_login_id,

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 9048: FROM PO_LINES_GT POL, PO_HEADERS_ALL POH1, PO_LINE_LOCATIONS_GT PLL1

9044: )
9045: , 0 )
9046: , POH1.amount_limit
9047: BULK COLLECT INTO l_curr_doc_line_num,l_po_amount,l_amount_limit
9048: FROM PO_LINES_GT POL, PO_HEADERS_ALL POH1, PO_LINE_LOCATIONS_GT PLL1
9049: WHERE PLL1.from_header_id = POL.from_header_id
9050: AND POL.po_header_id = p_document_id
9051: AND POL.from_header_id = POH1.po_header_id
9052: AND POH1.type_lookup_code = 'BLANKET'

Line 9082: PO_HEADERS_ALL POH2, PO_LINES_GT POL

9078: )
9079: , 0 )
9080: BULK COLLECT INTO l_prev_doc_line_num, l_prev_rel_amount
9081: FROM PO_LINE_LOCATIONS_ALL PLL2, PO_HEADERS_ALL POH1,
9082: PO_HEADERS_ALL POH2, PO_LINES_GT POL
9083: WHERE POL.po_header_id = p_document_id
9084: AND POL.from_header_id = POH1.po_header_id --JOIN
9085: AND POH1.type_lookup_code = 'BLANKET'
9086: AND POH1.global_agreement_flag = 'Y'

Line 9226: FROM PO_LINES_GT POL1, PO_LINES_ALL POL2, PO_HEADERS_ALL POH

9222: decode ( POL1.order_type_lookup_code --
9223: , 'FIXED PRICE' , 'PO_SUB_PO_LINE_GT_GA_AMT_TOL'
9224: , 'PO_SUB_PO_LINE_GT_GA_PRICE_TOL'
9225: )
9226: FROM PO_LINES_GT POL1, PO_LINES_ALL POL2, PO_HEADERS_ALL POH
9227: WHERE POL1.po_header_id = p_document_id
9228: AND POH.po_header_id = POL1.from_header_id --JOIN
9229: AND POH.type_lookup_code = 'BLANKET'
9230: AND POH.global_agreement_flag = 'Y'

Line 9298: FROM PO_LINES_GT POL1, PO_LINES_ALL POL2, PO_HEADERS_ALL POH

9294: p_sequence + ROWNUM,
9295: substr(g_linemsg||g_delim||POL1.line_num||g_delim
9296: ||l_textline,1,240),
9297: 'PO_SUB_STD_GA_LINE_LESS_MINREL' -- <2710030>
9298: FROM PO_LINES_GT POL1, PO_LINES_ALL POL2, PO_HEADERS_ALL POH
9299: WHERE POL1.po_header_id = p_document_id
9300: AND POL1.from_header_id = POL2.po_header_id --JOIN
9301: AND POL1.from_line_id = POL2.po_line_id --JOIN
9302: AND POL1.from_header_id = POH.po_header_id --JOIN

Line 9426: --< Shared Proc FPJ > Bug 3301427: Only do this check for new SPO lines

9422: END IF;
9423:
9424: -- Check 1: Current OU should still be enabled for purchasing on the GC
9425: -- being referenced
9426: --< Shared Proc FPJ > Bug 3301427: Only do this check for new SPO lines
9427:
9428: l_textline := FND_MESSAGE.get_string ('PO', 'PO_SUB_STD_GC_NOT_EN_PUR');
9429:
9430: INSERT INTO po_online_report_text_gt (

Line 9461: po_lines_gt POL,

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
9465: AND POL.po_header_id = POH.po_header_id

Line 9496: --< Shared Proc FPJ > Bug 3301427: Only do this check for new SPO lines

9492: END IF;
9493:
9494: -- Check 2: Supplier Site should be a purchasing site defined in GC
9495: -- Org Assignments
9496: --< Shared Proc FPJ > Bug 3301427: Only do this check for new SPO lines
9497: /* R12 GCPA
9498: Skip Vendor Site validation for Contracts having "Enable All Sites" is set to Y
9499: */
9500:

Line 9534: po_lines_gt POL,

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
9538: AND POL.po_header_id = POH.po_header_id

Line 9582: -- POs separately because current PO lines (stored in

9578: -- SQL What: For each line that has a contract reference, get the
9579: -- total amount released for that contract on this PO
9580: -- SQL Why: Need to calculate the total amount released for the
9581: -- contract and we are calculating current PO and other
9582: -- POs separately because current PO lines (stored in
9583: -- po_lines_gt) may not go to po_lines_all eventually
9584: --: changed query to use Line qty/amt/price
9585: --instead of summing up the Line Loc values. The results should be
9586: --equivalent for SPOs.

Line 9583: -- po_lines_gt) may not go to po_lines_all eventually

9579: -- total amount released for that contract on this PO
9580: -- SQL Why: Need to calculate the total amount released for the
9581: -- contract and we are calculating current PO and other
9582: -- POs separately because current PO lines (stored in
9583: -- po_lines_gt) may not go to po_lines_all eventually
9584: --: changed query to use Line qty/amt/price
9585: --instead of summing up the Line Loc values. The results should be
9586: --equivalent for SPOs.
9587:

Line 9598: FROM po_lines_gt POL, -- target_line

9594: POHA.amount_limit* NVL(POHA.rate,1)
9595: BULK COLLECT INTO l_curr_doc_line_num,
9596: l_po_amount,
9597: l_amount_limit
9598: FROM po_lines_gt POL, -- target_line
9599: po_headers_all POHA, -- global contract
9600: po_lines_gt POL1, -- all lines in current doc
9601: -- having the same contract ref
9602: po_headers_all POHA1 -- document

Line 9600: po_lines_gt POL1, -- all lines in current doc

9596: l_po_amount,
9597: l_amount_limit
9598: FROM po_lines_gt POL, -- target_line
9599: po_headers_all POHA, -- global contract
9600: po_lines_gt POL1, -- all lines in current doc
9601: -- having the same contract ref
9602: po_headers_all POHA1 -- document
9603: WHERE
9604: POL.po_header_id = p_document_id

Line 9638: FROM po_lines_gt POL, -- target line

9634: , 'FIXED PRICE', POL1.amount
9635: , POL1.quantity * POL1.unit_price)*NVL(POH1.rate,1))
9636: BULK COLLECT INTO l_prev_doc_line_num,
9637: l_prev_rel_amount
9638: FROM po_lines_gt POL, -- target line
9639: po_lines_all POL1, -- all lines from other doc with
9640: -- the same GC ref
9641: po_headers_all POH1, -- headers of lines in POL1
9642: po_headers_all POHA -- global contract

Line 9639: po_lines_all POL1, -- all lines from other doc with

9635: , POL1.quantity * POL1.unit_price)*NVL(POH1.rate,1))
9636: BULK COLLECT INTO l_prev_doc_line_num,
9637: l_prev_rel_amount
9638: FROM po_lines_gt POL, -- target line
9639: po_lines_all POL1, -- all lines from other doc with
9640: -- the same GC ref
9641: po_headers_all POH1, -- headers of lines in POL1
9642: po_headers_all POHA -- global contract
9643: WHERE

Line 9858: po_lines POL1

9854: , POL1.quantity * POL1.unit_price)
9855: * NVL(POH1.rate,1)
9856: )
9857: FROM po_headers POH1,
9858: po_lines POL1
9859: WHERE POL1.contract_id = POH.po_header_id
9860: AND POL1.po_header_id = POH1.po_header_id
9861: AND NVL(POL1.cancel_flag, 'N') = 'N'
9862: ) -- amt released in fn currency

Line 10136: DELETE FROM po_lines_gt;

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;
10140: DELETE FROM po_req_headers_gt;

Line 10203: -- Change populate logic for po_lines_gt to handle document levels

10199: l_progress := '005';
10200:
10201: --populate the global line table
10202: --
10203: -- Change populate logic for po_lines_gt to handle document levels
10204:
10205: populate_po_lines_gt(
10206: p_doc_type => p_doc_type
10207: , p_doc_level => p_doc_level

Line 10205: populate_po_lines_gt(

10201: --populate the global line table
10202: --
10203: -- Change populate logic for po_lines_gt to handle document levels
10204:
10205: populate_po_lines_gt(
10206: p_doc_type => p_doc_type
10207: , p_doc_level => p_doc_level
10208: , p_doc_level_id => p_doc_level_id
10209: , x_return_status => l_return_status

Line 10364: UPDATE po_lines_gt

10360: -- in values or if NULL then with existing values in the table
10361: -- SQL Why: Need to apply requested line level changes to global temp table
10362: -- SQL Join: po_line_id
10363: FORALL i IN 1..p_requested_changes.line_changes.po_line_id.COUNT
10364: UPDATE po_lines_gt
10365: SET unit_price = nvl(p_requested_changes.line_changes.unit_price(i),unit_price),
10366: vendor_product_num = nvl(p_requested_changes.line_changes.vendor_product_num(i),
10367: vendor_product_num),
10368: --

Line 10765: , po_lines_all pol --

10761: -- Note: the value basis decode assumes Milestone Pay Items are Amount
10762: -- Milestones, since payment type is not changeable on Qty-based lines
10763: --
10764: FROM po_line_locations poll
10765: , po_lines_all pol --
10766: WHERE poll.line_location_id =
10767: p_requested_changes.shipment_changes.parent_line_location_id(i)
10768: AND p_requested_changes.shipment_changes.po_line_location_id(i) IS NULL
10769: AND poll.po_line_id = pol.po_line_id; --

Line 11168: --Name: populate_po_lines_gt

11164:
11165: --
11166: -------------------------------------------------------------------------------
11167: --Start of Comments
11168: --Name: populate_po_lines_gt
11169: --Pre-reqs:
11170: -- None.
11171: --Modifies:
11172: -- PO_LINES_GT

Line 11172: -- PO_LINES_GT

11168: --Name: populate_po_lines_gt
11169: --Pre-reqs:
11170: -- None.
11171: --Modifies:
11172: -- PO_LINES_GT
11173: --Locks:
11174: -- None.
11175: --Function:
11176: -- Populates the lines GTT for submission checks.

Line 11195: PROCEDURE populate_po_lines_gt(

11191: --Testing:
11192: --
11193: --End of Comments
11194: -------------------------------------------------------------------------------
11195: PROCEDURE populate_po_lines_gt(
11196: p_doc_type IN VARCHAR2
11197: , p_doc_level IN VARCHAR2
11198: , p_doc_level_id IN NUMBER
11199: , x_return_status OUT NOCOPY VARCHAR2

Line 11203: l_api_name CONSTANT varchar2(40) := 'POPULATE_PO_LINES_GT';

11199: , x_return_status OUT NOCOPY VARCHAR2
11200: )
11201: IS
11202:
11203: l_api_name CONSTANT varchar2(40) := 'POPULATE_PO_LINES_GT';
11204: l_progress VARCHAR2(3);
11205:
11206: l_line_id_tbl po_tbl_number;
11207:

Line 11231: INSERT INTO po_lines_gt(

11227:
11228: l_progress := '020';
11229:
11230: FORALL i IN 1 .. l_line_id_tbl.COUNT
11231: INSERT INTO po_lines_gt(
11232: PO_LINE_ID ,
11233: LAST_UPDATE_DATE ,
11234: LAST_UPDATED_BY ,
11235: PO_HEADER_ID ,

Line 11486: FROM po_lines_all pol

11482: AMOUNT , --
11483: ORDER_TYPE_LOOKUP_CODE , --
11484: PURCHASE_BASIS , --
11485: MATCHING_BASIS --
11486: FROM po_lines_all pol
11487: WHERE pol.po_line_id = l_line_id_tbl(i)
11488: ;
11489:
11490: l_progress := '030';

Line 11514: END POPULATE_PO_LINES_GT;

11510: || l_progress || ' SQL CODE is '||sqlcode);
11511: END IF;
11512: END IF;
11513:
11514: END POPULATE_PO_LINES_GT;
11515: --
11516:
11517:
11518: /**

Line 12773: OR EXISTS (SELECT null from po_lines_all l

12769: AND (h.closed_code is NULL or h.closed_code NOT IN ('FINALLY CLOSED'))
12770: AND (h.frozen_flag is NULL or h.frozen_flag <> 'Y')))
12771: AND (p_line_id(i) IS NULL
12772: --Case 3: Optionally, Line is specified
12773: OR EXISTS (SELECT null from po_lines_all l
12774: WHERE l.po_line_id = p_line_id(i)
12775: AND (l.cancel_flag is null or l.cancel_flag <> 'Y')
12776: AND (l.closed_code is NULL or l.closed_code NOT IN ('FINALLY CLOSED'))))
12777: AND (p_line_location_id(i) IS NULL

Line 13002: OR EXISTS (SELECT null from po_lines_all l

12998: AND (h.hold_flag is NULL or h.hold_flag <> 'Y')))
12999: AND (p_line_id(i) IS NULL
13000: --Case 3: Optionally, Line is specified
13001: -- Bug 3407980: Modified closed_code condition so that it only discards 'FINALLY CLOSED'
13002: OR EXISTS (SELECT null from po_lines_all l
13003: WHERE l.po_line_id = p_line_id(i)
13004: AND (l.closed_code is NULL or l.closed_code <> 'FINALLY CLOSED')))
13005: AND (p_line_location_id(i) IS NULL
13006: --Case 4: Optionally, Line Location is specified

Line 13171: OR EXISTS (SELECT null from po_lines_all l

13167: AND (h.hold_flag is NULL or h.hold_flag <> 'Y')))
13168: AND (p_line_id(i) IS NULL
13169: --Case 3: Optionally, Line is specified
13170: -- Bug 3407980: Modified closed_code condition so that it only discards 'FINALLY CLOSED'
13171: OR EXISTS (SELECT null from po_lines_all l
13172: WHERE l.po_line_id = p_line_id(i)
13173: AND (l.closed_code is NULL or l.closed_code <> 'FINALLY CLOSED')))
13174: AND (p_line_location_id(i) IS NULL
13175: --Case 4: Optionally, Line Location is specified

Line 13342: FROM po_lines_all s

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
13343: WHERE s.po_line_id = p_line_id(i))
13344: WHERE p_line_id(i) is not null and gt.po_header_id = l_sequence(i)
13345: ;
13346:

Line 13736: , PO_LINES_ALL POL -- For releases, PO_LINES_GT table isn't populated.

13732: , 'PO_SUB_SHIP_BILL_GT_ORD_QTY'
13733: )
13734: FROM
13735: PO_LINE_LOCATIONS_GT POLL
13736: , PO_LINES_ALL POL -- For releases, PO_LINES_GT table isn't populated.
13737: WHERE POLL.po_line_id = POL.po_line_id
13738: AND nvl(POLL.cancel_flag, 'N') = 'N'
13739: AND nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
13740: AND ( --

Line 13825: , PO_LINES_ALL POL -- For releases, PO_LINES_GT table isn't populated.

13821: )
13822: FROM
13823: PO_DISTRIBUTIONS_GT POD
13824: , PO_LINE_LOCATIONS_GT POLL
13825: , PO_LINES_ALL POL -- For releases, PO_LINES_GT table isn't populated.
13826: WHERE POD.line_location_id = POLL.line_location_id
13827: AND POL.po_line_id = POLL.po_line_id
13828: AND nvl(POLL.cancel_flag, 'N') = 'N'
13829: AND nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'

Line 13913: , PO_LINES_ALL POL -- For releases, PO_LINES_GT table isn't populated.

13909: )
13910: FROM
13911: PO_DISTRIBUTIONS_GT POD
13912: , PO_LINE_LOCATIONS_GT POLL
13913: , PO_LINES_ALL POL -- For releases, PO_LINES_GT table isn't populated.
13914: WHERE POD.line_location_id = POLL.line_location_id
13915: AND POL.po_line_id = POLL.po_line_id
13916: AND nvl(POLL.cancel_flag,'N') = 'N'
13917: AND nvl(POLL.closed_code,'OPEN') <> 'FINALLY CLOSED'

Line 14815: , PO_LINES_ALL POL -- For Releases, PO_LINES_GT doesn't get populated.

14811: , l_distribution_num_tbl
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

Line 15773: FROM po_lines_gt pol

15769: , 0
15770: , p_sequence + ROWNUM
15771: , substr(g_linemsg || g_delim || pol.line_num || g_delim || l_textline, 1, 240)
15772: , 'PO_CAN_POL_WITH_RCV_TRX'
15773: FROM po_lines_gt pol
15774: WHERE EXISTS
15775: (
15776: SELECT 'Eligible shipment'
15777: FROM po_line_locations_gt poll

Line 15848: FROM po_lines_gt pol, po_line_locations_gt poll

15844: || poll.shipment_num || g_delim || l_textline, 1, 240)
15845: )
15846: , DECODE(p_document_type, g_document_type_RELEASE, 'PO_CAN_RELS_WITH_RCV_TRX',
15847: 'PO_CAN_POLL_WITH_RCV_TRX')
15848: FROM po_lines_gt pol, po_line_locations_gt poll
15849: WHERE pol.po_line_id = poll.po_line_id
15850: AND NVL(poll.cancel_flag, 'N') = 'N'
15851: AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
15852: AND EXISTS

Line 15973: FROM po_lines_gt pol, po_line_locations_gt poll

15969: substr(g_linemsg || g_delim || pol.line_num || g_delim || g_shipmsg || g_delim
15970: || poll.shipment_num || g_delim ||
15971: DECODE(poll.payment_type , NULL, l_text_normal_po, l_text_complex_po), 1, 240))
15972: , DECODE(poll.payment_type, NULL, 'PO_CAN_POLL_WITH_ASN', 'PO_CAN_POLL_WITH_PENDING_WCR')
15973: FROM po_lines_gt pol, po_line_locations_gt poll
15974: WHERE NVL(poll.cancel_flag, 'N') = 'N'
15975: AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
15976: AND EXISTS
15977: (

Line 16087: FROM po_lines_gt pol, po_line_locations_gt poll, po_distributions_gt pod

16083: , 'QTY1', sum(NVL(pod.quantity_delivered, 0))
16084: )
16085: ), 1, 240)
16086: , 'PO_CAN_POLL_REC_NOT_DEL'
16087: FROM po_lines_gt pol, po_line_locations_gt poll, po_distributions_gt pod
16088: WHERE pod.line_location_id = poll.line_location_id
16089: AND pol.po_line_id = poll.po_line_id
16090: AND pol.order_type_lookup_code NOT IN ('RATE', 'FIXED PRICE')
16091: AND NVL(poll.cancel_flag, 'N') = 'N'

Line 16210: FROM po_lines_gt pol, po_line_locations_gt poll, po_distributions_gt pod

16206: , 'QTY1', sum(NVL(pod.amount_delivered, 0))
16207: )
16208: ), 1, 240)
16209: , 'PO_CAN_POLL_AMT_REC_NOT_DEL'
16210: FROM po_lines_gt pol, po_line_locations_gt poll, po_distributions_gt pod
16211: WHERE pod.line_location_id = poll.line_location_id
16212: AND pol.po_line_id = poll.po_line_id
16213: AND pol.order_type_lookup_code IN ('RATE', 'FIXED PRICE')
16214: AND NVL(poll.cancel_flag, 'N') = 'N'

Line 16345: FROM po_lines_gt pol, po_line_locations_gt poll

16341: , g_linemsg || g_delim || pol.line_num || g_delim || g_shipmsg || g_delim
16342: || poll.shipment_num || g_delim || g_distmsg || g_delim || l_textline
16343: ), 1, 240)
16344: , 'PO_CAN_POLL_INVALID_ACCT_FLEX'
16345: FROM po_lines_gt pol, po_line_locations_gt poll
16346: , po_distributions_gt pod, gl_code_combinations gcc
16347: WHERE pod.line_location_id = poll.line_location_id
16348: AND pol.po_line_id = poll.po_line_id
16349: AND poll.shipment_type IN ('STANDARD', 'PLANNED', 'PREPAYMENT') --

Line 16444: FROM po_lines_gt pol

16440: , 0
16441: , p_sequence + ROWNUM
16442: , substr(g_linemsg || g_delim || pol.line_num || g_delim || l_textline,1,240) --Bug5096900
16443: , 'PO_CAN_POL_PLAN_WITH_OPEN_REL'
16444: FROM po_lines_gt pol
16445: WHERE EXISTS
16446: (
16447: SELECT 'Uncancelled Open Releases Exist'
16448: FROM po_line_locations pll

Line 16541: FROM po_lines_gt pol

16537: , 0
16538: , p_sequence + ROWNUM
16539: , substr(g_linemsg || g_delim || pol.line_num || g_delim || l_textline,1,240) --Bug5096900
16540: , 'PO_CAN_GAL_WITH_OPEN_STD_REF'
16541: FROM po_lines_gt pol
16542: WHERE EXISTS
16543: (
16544: SELECT 'Uncancelled Std PO lines referencing this GA line exist'
16545: FROM po_lines_all pol2

Line 16544: SELECT 'Uncancelled Std PO lines referencing this GA line exist'

16540: , 'PO_CAN_GAL_WITH_OPEN_STD_REF'
16541: FROM po_lines_gt pol
16542: WHERE EXISTS
16543: (
16544: SELECT 'Uncancelled Std PO lines referencing this GA line exist'
16545: FROM po_lines_all pol2
16546: WHERE pol2.from_line_id = pol.po_line_id
16547: AND NVL(pol2.cancel_flag, 'N') = 'N'
16548: AND NVL(pol2.closed_code, 'OPEN') <> 'FINALLY CLOSED'

Line 16545: FROM po_lines_all pol2

16541: FROM po_lines_gt pol
16542: WHERE EXISTS
16543: (
16544: SELECT 'Uncancelled Std PO lines referencing this GA line exist'
16545: FROM po_lines_all pol2
16546: WHERE pol2.from_line_id = pol.po_line_id
16547: AND NVL(pol2.cancel_flag, 'N') = 'N'
16548: AND NVL(pol2.closed_code, 'OPEN') <> 'FINALLY CLOSED'
16549: );

Line 16640: SELECT 'Open Std PO lines referencing this contract exist'

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
16642: WHERE pol.contract_id = poh.po_header_id
16643: AND NVL(pol.cancel_flag, 'N') = 'N'
16644: AND NVL(pol.closed_code, 'OPEN') <> 'FINALLY CLOSED'

Line 16641: FROM po_lines_all pol

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
16642: WHERE pol.contract_id = poh.po_header_id
16643: AND NVL(pol.cancel_flag, 'N') = 'N'
16644: AND NVL(pol.closed_code, 'OPEN') <> 'FINALLY CLOSED'
16645: );

Line 16734: FROM po_lines_gt pol, po_line_locations_gt poll

16730: , p_sequence + ROWNUM
16731: , substr(g_linemsg || g_delim || pol.line_num || g_delim || g_shipmsg
16732: || poll.shipment_num || g_delim || l_textline,1,240) --Bug5096900
16733: , 'PO_CAN_POLL_PLAN_WITH_OPEN_REL'
16734: FROM po_lines_gt pol, po_line_locations_gt poll
16735: WHERE poll.po_line_id = pol.po_line_id
16736: AND NVL(poll.cancel_flag, 'N') = 'N'
16737: AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
16738: AND EXISTS

Line 16851: , PO_LINES_GT POL

16847: , l_lineloc_qty_tbl --
16848: , l_lineloc_amt_tbl --
16849: FROM
16850: PO_LINE_LOCATIONS_GT PLL
16851: , PO_LINES_GT POL
16852: WHERE
16853: POL.po_line_id = PLL.po_line_id
16854: AND PLL.shipment_type in ('STANDARD', 'PLANNED')
16855: AND POL.po_header_id = p_document_id

Line 17033: , PO_LINES_GT POL

17029: PLL.amount
17030: END) financing_advance_amount
17031: FROM
17032: PO_LINE_LOCATIONS_GT PLL
17033: , PO_LINES_GT POL
17034: WHERE
17035: POL.po_line_id = PLL.po_line_id
17036: AND ( (l_is_financing_flag = 'N' AND PLL.shipment_type = 'STANDARD')
17037: OR (l_is_financing_flag = 'Y' and PLL.shipment_type = 'PREPAYMENT'))

Line 17135: FROM PO_DISTRIBUTIONS_GT POD,PO_LINE_LOCATIONS_GT PLL, PO_LINES_GT POL

17131: , l_lineloc_qty_tbl
17132: , l_lineloc_amt_tbl
17133: , l_dist_qty_tbl
17134: , l_dist_amt_tbl
17135: FROM PO_DISTRIBUTIONS_GT POD,PO_LINE_LOCATIONS_GT PLL, PO_LINES_GT POL
17136: WHERE PLL.po_line_id = POL.po_line_id
17137: AND POD.line_location_id = PLL.line_location_id
17138: AND PLL.po_header_id = p_document_id
17139: AND nvl(PLL.cancel_flag,'N') = 'N'

Line 17484: FROM po_lines_gt pol

17480: , null -- distribution_num
17481: , p_sequence + ROWNUM
17482: , substr(l_textline, 1, 240)
17483: , l_message_name
17484: FROM po_lines_gt pol
17485: WHERE pol.po_line_id=p_doc_level_id
17486: AND chk_unv_invoices(l_invoice_type, pol.po_header_id, NULL, pol.po_line_id,NULL, NULL, p_origin_doc_id, 'CHECK_PO_LINE_FINAL_CLOSE') = 1;
17487: END IF;
17488: --

Line 17655: FROM po_lines_gt

17651: l_document_id := p_doc_level_id;
17652: ELSIF p_document_level = g_document_level_LINE THEN
17653: SELECT po_header_id
17654: INTO l_document_id
17655: FROM po_lines_gt
17656: WHERE po_line_id=p_doc_level_id;
17657: ELSIF p_document_level = g_document_level_SHIPMENT THEN
17658: SELECT po_header_id
17659: INTO l_document_id

Line 17782: po_lines_all pol,

17778: l_ship_to_organization_id,
17779: l_consigned_flag,
17780: l_outsourced_assembly
17781: FROM po_line_locations_all poll,
17782: po_lines_all pol,
17783: po_headers_all poh
17784: WHERE poh.po_header_id = pol.po_header_id
17785: AND pol.po_line_id = poll.po_line_id
17786: AND poll.line_location_id = p_line_location_id;