[Home] [Help]
3153:
3154: l_progress := '027';
3155: x_return_status := FND_API.G_RET_STS_SUCCESS;
3156:
3157: -- LCM ER start. If all the submission checks have been passed successfully then update the lcm_flag of po_line_locations_all and po_distributions_all.
3158: IF p_document_type in ('PO','RELEASE') THEN
3159:
3160: FOR ship_rec in (select line_location_id from po_line_locations_gt)
3161:
3156:
3157: -- LCM ER start. If all the submission checks have been passed successfully then update the lcm_flag of po_line_locations_all and po_distributions_all.
3158: IF p_document_type in ('PO','RELEASE') THEN
3159:
3160: FOR ship_rec in (select line_location_id from po_line_locations_gt)
3161:
3162: LOOP
3163: set_lcm_flag(ship_rec.line_location_id,'AFTER',l_return_status);
3164: END LOOP;
4340: FROM PO_RELEASES_GT POR
4341: WHERE POR.po_release_id = p_document_id
4342: AND NOT EXISTS
4343: (SELECT 'Shipment Exist'
4344: FROM PO_LINE_LOCATIONS_GT PLL
4345: WHERE PLL.po_release_id = POR.po_release_id);
4346: -- AND nvl(PLL.cancel_flag,'N') = 'N');
4347: -- bug 3305488
4348:
4366: HLT.location_code
4367: BULK COLLECT INTO
4368: l_line_num,
4369: l_ship_to_location_tbl
4370: FROM PO_LINE_LOCATIONS_GT POLG,
4371: HR_LOCATIONS_ALL HLA,
4372: HR_LOCATIONS_ALL_TL HLT
4373: WHERE POLG.po_release_id = p_document_id
4374: AND POLG.ship_to_location_id=HLA.location_id
4480: decode ( POL.order_type_lookup_code --
4481: , 'FIXED PRICE' , 'PO_SUB_REL_SHIP_PRICE_GT_LIMIT'
4482: , 'PO_SUB_REL_SHIP_AMT_GT_LIMIT'
4483: )
4484: FROM PO_LINE_LOCATIONS_GT PLL,PO_LINES POL
4485: WHERE PLL.po_line_id = POL.po_line_id
4486: AND PLL.po_release_id = p_document_id
4487: AND nvl(PLL.cancel_flag,'N')= 'N'
4488: AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
4530: , l_dist_qty_tbl --
4531: , l_dist_amt_tbl --
4532: FROM
4533: PO_DISTRIBUTIONS_GT POD --
4534: , PO_LINE_LOCATIONS_GT PLL
4535: WHERE
4536: PLL.line_location_id = POD.line_location_id
4537: AND PLL.po_release_id = p_document_id
4538: AND nvl(PLL.cancel_flag,'N') = 'N'
4640: p_sequence + ROWNUM,
4641: substr(g_shipmsg||g_delim||PLL.shipment_num||g_delim
4642: ||l_textline,1,240),
4643: 'PO_SUB_REL_SHIP_NO_DIST'
4644: FROM PO_LINE_LOCATIONS_GT PLL
4645: WHERE PLL.po_release_id = p_document_id
4646: AND nvl(PLL.cancel_flag,'N') = 'N'
4647: AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
4648: AND NOT EXISTS
4694: ||g_distmsg||g_delim||POD.distribution_num||g_delim
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
4768: * PLL2.price_override )
4769: )
4770: )
4771: )
4772: FROM PO_LINE_LOCATIONS_GT PLL2
4773: WHERE PLL2.po_release_id = POR.po_release_id
4774: AND PLL2.shipment_type IN ('BLANKET', 'SCHEDULED')
4775: );
4776:
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'
4933: l_shipment_num,
4934: l_dist_num,
4935: l_quantity1,
4936: l_quantity2
4937: FROM PO_LINES_ALL POL,PO_RELEASES_GT POR,PO_LINE_LOCATIONS_GT PLL
4938: WHERE PLL.po_release_id = POR.po_release_id
4939: AND PLL.po_release_id = p_document_id
4940: AND POL.po_line_id = PLL.po_line_id
4941: AND POL.min_release_amount is not null
4954: )
4955: )
4956: )
4957: --
4958: --Change the table in from clause from PO_LINE_LOCATIONS to PO_LINE_LOCATIONS_GT
4959: FROM PO_LINE_LOCATIONS_GT PLL2
4960: WHERE PLL2.po_line_id = POL.po_line_id
4961: AND PLL2.po_release_id = POR.po_release_id
4962: AND PLL2.shipment_type in ('BLANKET', 'SCHEDULED')
4955: )
4956: )
4957: --
4958: --Change the table in from clause from PO_LINE_LOCATIONS to PO_LINE_LOCATIONS_GT
4959: FROM PO_LINE_LOCATIONS_GT PLL2
4960: WHERE PLL2.po_line_id = POL.po_line_id
4961: AND PLL2.po_release_id = POR.po_release_id
4962: AND PLL2.shipment_type in ('BLANKET', 'SCHEDULED')
4963: )
5075: p_sequence + ROWNUM,
5076: substr(g_shipmsg||g_delim||POLL.shipment_num||g_delim||l_textline||
5077: MTL1.uom_class||' , '||MTL2.uom_class,1,240),
5078: 'PO_SUB_UOM_CLASS_CONVERSION'
5079: FROM MTL_UOM_CLASS_CONVERSIONS MOU, PO_LINE_LOCATIONS_GT POLL,
5080: PO_LINES POL, MTL_UOM_CLASSES_TL MTL1,
5081: MTL_UOM_CLASSES_TL MTL2
5082: WHERE MOU.inventory_item_id = POL.item_id
5083: AND (NVL(MOU.disable_date, TRUNC(SYSDATE)) + 1) < TRUNC(SYSDATE)
5141: ||g_shipmsg||g_delim||PLL.shipment_num||g_delim
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
5243: ||g_shipmsg||g_delim||PLL.shipment_num||g_delim
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
5327: ||g_shipmsg||g_delim||PLL.shipment_num||g_delim
5328: ||l_textline,1,240),
5329: 'PO_ATO_ITEM_NA'
5330: FROM po_lines POL,
5331: po_line_locations_gt PLL,
5332: financials_system_parameters FSP,
5333: mtl_system_items MSI
5334: WHERE PLL.po_release_id = p_document_id
5335: AND PLL.po_line_id = POL.po_line_id
5381: 0,
5382: p_sequence + ROWNUM,
5383: substr(g_shipmsg||g_delim||pll.shipment_num||g_delim||l_textline,1,240),
5384: 'PO_ALL_NO_ITEM'
5385: from po_releases_gt por,po_lines pl,po_line_locations_gt pll,mtl_system_items itm,po_line_types_b plt
5386: where itm.inventory_item_id = pl.item_id
5387: and pl.item_id is not null
5388:
5389: and pl.po_line_id = pll.po_line_id
5755: p_sequence + ROWNUM,
5756: substr (g_shipmsg||g_delim||PLL.shipment_num||g_delim||l_textline,1,240),
5757: 'PO_SUB_REL_SHIP_INV_MATCH_NE_R'
5758: FROM PO_RELEASES_GT POR,
5759: PO_LINE_LOCATIONS_GT PLL
5760: WHERE POR.po_release_id = PLL.po_release_id
5761: AND POR.po_release_id = p_document_id
5762: AND Nvl(PLL.LCM_FLAG,'N') = 'Y'
5763: AND Nvl(PLL.match_option,'P') <> 'R'
5801: substr (g_shipmsg||g_delim||PLL.shipment_num||g_delim||g_distmsg||g_delim||
5802: POD.distribution_num||g_delim||l_textline, 1,240),
5803: 'PO_SUB_REL_DIST_DEST_TYPE_NE_I'
5804: FROM PO_RELEASES_GT POR,
5805: PO_LINE_LOCATIONS_GT PLL,
5806: PO_DISTRIBUTIONS_GT POD
5807: WHERE POR.po_release_id = POD.po_release_id
5808: AND POD.line_location_id = PLL.line_location_id
5809: AND POR.po_release_id = p_document_id
5999: nvl(POL.line_num,0) line_num,
6000: nvl(POLL.quantity,0) quantity,
6001: nvl(POL.item_id,-1) item_id,
6002: nvl( POLL.line_location_id,0) line_loc_id
6003: FROM PO_LINE_LOCATIONS_GT POLL,
6004: PO_LINE_TYPES_B PLT, -- bug3413891
6005: PO_LINES_GT POL,
6006: PO_DISTRIBUTIONS_GT POD
6007: WHERE POLL.po_line_id = POL.po_line_id
6035: POL.line_num line_num,
6036: POL.quantity quantity,
6037: nvl(POL.item_id,-1) item_id,
6038: POLL.line_location_id line_loc_id
6039: FROM PO_LINE_LOCATIONS_GT POLL,
6040: PO_LINES_GT POL,
6041: PO_DISTRIBUTIONS_GT POD
6042: WHERE POL.po_header_id = p_document_id
6043: AND POD.line_location_id = POLL.line_location_id
6042: WHERE POL.po_header_id = p_document_id
6043: AND POD.line_location_id = POLL.line_location_id
6044: AND POLL.line_location_id =
6045: (SELECT min(POLL2.line_location_id)
6046: FROM PO_LINE_LOCATIONS_GT POLL2
6047: WHERE POLL2.po_line_id = POL.po_line_id
6048: AND POLL2.shipment_type = 'STANDARD'
6049: )
6050: GROUP BY POL.unit_price, POLL.quantity, nvl(POLL.quantity_cancelled, 0),
6084: nvl(POL.line_num,0) line_num,
6085: nvl(POLL.quantity,0) quantity,
6086: nvl(POL.item_id,0) item_id,
6087: nvl( POLL.line_location_id,0) line_loc_id
6088: FROM PO_LINE_LOCATIONS_GT POLL,
6089: PO_LINE_TYPES_B PLT, -- bug3413891
6090: PO_LINES POL,
6091: PO_DISTRIBUTIONS_GT POD --
6092: WHERE POLL.po_line_id = POL.po_line_id
6106: min(POL.line_num),
6107: min(POLL.shipment_num),
6108: min(PRL.org_id) --
6109: FROM PO_REQUISITION_LINES_ALL PRL, --
6110: PO_LINE_LOCATIONS_GT POLL,
6111: PO_LINES POL
6112: WHERE PRL.line_location_id = POLL.line_location_id
6113: AND POLL.line_location_id = p_line_location_id
6114: AND PRL.unit_price >= 0
6124: MIN(POL.line_num) ,
6125: MIN(POLL.shipment_num) ,
6126: MIN(PRL.org_id) --
6127: FROM PO_REQUISITION_LINES_ALL PRL, --
6128: PO_LINE_LOCATIONS_GT POLL ,
6129: PO_LINES POL
6130: WHERE PRL.line_location_id = POLL.line_location_id
6131: AND POLL.line_location_id = p_line_location_id
6132: AND PRL.unit_price >= 0
6141: min(POL.line_num),
6142: min(POLL.shipment_num),
6143: min(PRL.org_id) --
6144: FROM PO_REQUISITION_LINES_ALL PRL, --
6145: PO_LINE_LOCATIONS_GT POLL,
6146: PO_LINES POL,
6147: PO_DISTRIBUTIONS PD,
6148: PO_REQ_DISTRIBUTIONS_ALL PRD --
6149: WHERE POLL.line_location_id = p_line_location_id
6163: min(POL.line_num),
6164: min(POLL.shipment_num),
6165: min(PRL.org_id) --
6166: FROM PO_REQUISITION_LINES_ALL PRL, --
6167: PO_LINE_LOCATIONS_GT POLL,
6168: PO_LINES POL,
6169: PO_DISTRIBUTIONS PD,
6170: PO_REQ_DISTRIBUTIONS_ALL PRD --
6171: WHERE POLL.line_location_id = p_line_location_id
6328: BEGIN
6329: l_progress := '006';
6330: SELECT POH.currency_code, nvl(trunc(POH.rate_date), trunc(POLL.creation_date))
6331: INTO l_po_curr, l_rate_date
6332: FROM PO_HEADERS POH, PO_LINE_LOCATIONS_GT POLL
6333: WHERE POLL.line_location_id = l_line_location_id(shipment_line)
6334: AND POLL.po_header_id = POH.po_header_id;
6335: EXCEPTION
6336: WHEN OTHERS THEN
6341: l_progress := '007';
6342: BEGIN
6343: SELECT POL.ORDER_TYPE_LOOKUP_CODE
6344: INTO l_po_line_type
6345: FROM PO_LINE_LOCATIONS_GT POLL,
6346: PO_LINES_GT POL
6347: WHERE POLL.po_line_id = POL.po_line_id
6348: AND POLL.line_location_id = l_line_location_id(shipment_line);
6349: EXCEPTION
6488: l_progress := '018';
6489: --Get Shipment Price without applying Rate and Qty
6490: SELECT NVL(ROUND(MAX(POLL.price_override) ,l_pou_func_curr_ext_precn) ,-1) Price
6491: INTO l_ship_price_ext_precn
6492: FROM PO_LINE_LOCATIONS_ALL POLL
6493: WHERE POLL.line_location_id = l_line_location_id(shipment_line)
6494: AND NVL(POLL.cancel_flag,'N') <> 'Y'
6495: AND NVL(POLL.closed_code,'OPEN') <> 'FINALLY CLOSED';
6496:
6845: l_progress := '207';
6846: --Get Shipment Price without applying Rate and Qty
6847: SELECT NVL(ROUND(MAX(POLL.price_override) ,l_pou_func_curr_ext_precn) ,-1) Price
6848: INTO l_ship_price_ext_precn
6849: FROM PO_LINE_LOCATIONS_ALL POLL
6850: WHERE POLL.line_location_id = l_line_location_id(shipment_line)
6851: AND NVL(POLL.cancel_flag,'N') <> 'Y'
6852: AND NVL(POLL.closed_code,'OPEN') <> 'FINALLY CLOSED';
6853:
8372: Nvl(POL.CLM_INFO_FLAG,'N')= 'N' AND
8373: -- CLM Aprvl
8374: nvl(POL.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
8375: NOT EXISTS (SELECT 'Shipments Exist'
8376: FROM PO_LINE_LOCATIONS_GT PLL
8377: WHERE PLL.po_line_id = POL.po_line_id AND
8378: PLL.shipment_type in ('STANDARD','PLANNED'));
8379:
8380: --Increment the p_sequence with number of errors reported in last query
8434: substr(g_linemsg||g_delim||Nvl(POL.line_num_display,POL.line_num)||g_delim||
8435: g_shipmsg||g_delim||PLL.shipment_num||g_delim||l_textline,1,240),
8436: Decode(l_clm_document, 'Y', 'PO_CLM_SUB_SHIP_NO_DIST_WARN', 'PO_SUB_SHIP_NO_DIST'),
8437: Decode(l_clm_document, 'Y', 'W', NULL)
8438: FROM PO_LINE_LOCATIONS_GT PLL,PO_LINES_GT POL
8439: WHERE PLL.po_line_id = POL.po_line_id AND
8440: PLL.po_header_id = p_document_id AND
8441: nvl(PLL.cancel_flag, 'N') = 'N' AND
8442: nvl(POL.cancel_flag, 'N') = 'N' AND
8518: ||g_shipmsg||g_delim||PLL.shipment_num||g_delim
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
8577: ||g_shipmsg||g_delim||PLL.shipment_num||g_delim
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
8960: substr(g_linemsg||g_delim||POL.line_num||g_delim||
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
9064: substr(g_linemsg||g_delim||POL.line_num||g_delim
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
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'
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
9442: AND POL.po_header_id = POH.po_header_id
9481: l_shipment_num,
9482: l_ship_to_location_tbl
9483: FROM
9484: po_lines_gt pol,
9485: po_line_locations_gt poll,
9486: hr_locations_all hla,
9487: hr_locations_all_tl hlat
9488: WHERE poll.po_header_id=p_document_id
9489: and pol.po_Header_id=p_document_id
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
9701:
9814: 'SHIP_NUM',pos.shipment_num,
9815: 'DIST_NUM',pod.distribution_num),
9816: 'PO_INVALID_OKE_CONTRACT_LNE_ID'
9817: FROM po_distributions_gt pod,
9818: po_line_locations_gt pos,
9819: po_lines_gt pol
9820: WHERE pod.po_line_id = pol.po_line_id
9821: AND pod.line_location_id = pos.line_location_id
9822: AND pod.oke_contract_line_id IS NOT NULL
9863: 'SHIP_NUM',pos.shipment_num,
9864: 'DIST_NUM',pod.distribution_num),
9865: 'PO_INVALID_OKE_CONTRACT_DLV_ID'
9866: FROM po_distributions_gt pod,
9867: po_line_locations_gt pos,
9868: po_lines_gt pol
9869: WHERE pod.po_line_id = pol.po_line_id
9870: AND pod.line_location_id = pos.line_location_id
9871: AND pod.oke_contract_deliverable_id IS NOT NULL
9888: -- the Sum of pay items price will be equal to the line price.
9889: IF NOT l_is_complex_po THEN
9890: --Bug 9430831 start.Submission Check for unit_price and price_override mismatch
9891: FOR price_rec in (select pol.line_num,poll.shipment_num,pol.unit_price, poll.price_override,rownum
9892: from po_line_locations_gt poll, po_lines_gt pol
9893: where pol.po_header_id = p_document_id AND
9894: pol.po_line_id = poll.po_line_id AND
9895: pol.unit_price <> poll.price_override AND
9896: nvl(pol.cancel_flag,'N') = 'N' AND
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
9976: AND POH.po_header_id = p_document_id
9977: AND Nvl(PLL.LCM_FLAG,'N') = 'Y'
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
10028: AND POD.line_location_id = PLL.line_location_id
10029: AND PLL.po_line_id = POL.po_line_id
10083: substr(l_textline,1,240),
10084: 'PO_PDOI_INVALID_FUND_VAL_ENC'
10085: FROM
10086: PO_DISTRIBUTIONS_GT POD
10087: , PO_LINE_LOCATIONS_GT POLL
10088: , PO_LINES_GT POL
10089: WHERE POLL.line_location_id(+) = POD.line_location_id --JOIN
10090: AND POL.po_line_id(+) = POD.po_line_id --JOIN
10091: -- PA distributions don't have associated lines or shipments
10614: * PLL2.price_override,l_precision
10615: )
10616: )
10617: )
10618: FROM PO_LINE_LOCATIONS PLL2
10619: WHERE PLL2.po_header_id = POH.po_header_id
10620: AND PLL2.shipment_type in ('BLANKET', 'SCHEDULED')
10621: )
10622: )
10636: * PLL3.price_override,l_precision
10637: )
10638: )
10639: )
10640: FROM PO_LINE_LOCATIONS_ALL PLL3
10641: WHERE PLL3.from_header_id = POH.po_header_id
10642: )
10643: )
10644: );
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'
11138: substr(g_linemsg||g_delim||POL.line_num||g_delim
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
11413: TYPE AslConsignedFlagTab is TABLE of
11414: po_asl_attributes.consigned_from_supplier_flag%TYPE
11415: INDEX by BINARY_INTEGER;
11416: TYPE ConsignedFlagTab is TABLE of
11417: po_line_locations.consigned_flag%TYPE
11418: INDEX by BINARY_INTEGER;
11419:
11420: l_vendor_id NumTab;
11421: l_vendor_site_id NumTab;
11479: -- substr(g_linemsg||g_delim||POL.line_num||g_delim||g_shipmsg
11480: -- ||g_delim||PLL.shipment_num||':'||g_delim||l_textline,1,240),
11481: -- 'PO_DOC_REF_SUP_CONS_COEXIST'
11482: -- FROM po_lines_gt pol,
11483: -- po_line_locations_gt pll
11484: -- WHERE pol.po_header_id = p_document_id
11485: -- AND pol.po_line_id = pll.po_line_id
11486: -- AND pll.shipment_type = 'STANDARD'
11487: -- AND pll.consigned_flag = 'Y'
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
11550: -- POH.po_header_id = PLL.po_header_id AND
11788: AND POH.global_agreement_flag = 'Y'
11789: --
11790: AND NOT EXISTS --< Bug 3301427 Start >
11791: (SELECT 'previously approved shipment'
11792: FROM po_line_locations_gt pllg
11793: WHERE pllg.po_line_id = pol.po_line_id
11794: AND pllg.approved_date IS NOT NULL) --< Bug 3301427 End >
11795: AND NOT EXISTS
11796: --SQL Query enabled org assignments of this current purchasing org
11857: AND NVL(POH2.approved_flag, 'N') <> 'Y'
11858: AND NVL(POH2.user_hold_flag, 'N') <> 'Y' --< Bug 3422733 >
11859: AND NOT EXISTS --< Bug 3301427 Start >
11860: (SELECT 'previously approved shipment'
11861: FROM po_line_locations_gt pllg
11862: WHERE pllg.po_line_id = pol.po_line_id
11863: AND pllg.approved_date IS NOT NULL); --< Bug 3301427 End >
11864:
11865: --Increment the p_sequence with number of errors reported in last query
11913: AND POH2.global_agreement_flag = 'Y'
11914: AND POH2.user_hold_flag = 'Y'
11915: AND NOT EXISTS --< Bug 3301427 Start >
11916: (SELECT 'previously approved shipment'
11917: FROM po_line_locations_gt pllg
11918: WHERE pllg.po_line_id = pol.po_line_id
11919: AND pllg.approved_date IS NOT NULL); --< Bug 3301427 End >
11920:
11921: --Increment the p_sequence with number of errors reported in last query
12035: AND POH2.global_agreement_flag = 'Y'
12036: --
12037: AND NOT EXISTS --< Bug 3301427 Start >
12038: (SELECT 'previously approved shipment'
12039: FROM po_line_locations_gt pllg
12040: WHERE pllg.po_line_id = pol.po_line_id
12041: AND pllg.approved_date IS NOT NULL) --< Bug 3301427 End >
12042: AND NOT EXISTS
12043: (SELECT 'Enabled vendor site'
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
12282: )
12283: , 0 )
12284: , POH1.amount_limit
12285: BULK COLLECT INTO l_curr_doc_line_num,l_po_amount,l_amount_limit
12286: FROM PO_LINES_GT POL, PO_HEADERS_ALL POH1, PO_LINE_LOCATIONS_GT PLL1
12287: WHERE PLL1.from_header_id = POL.from_header_id
12288: AND POL.po_header_id = p_document_id
12289: AND POL.from_header_id = POH1.po_header_id
12290: AND POH1.type_lookup_code = 'BLANKET'
12302: -- given lines with shipments that reference GA's with amount limits only.
12303:
12304: -- bug3413891
12305: -- 1) Added optimizer hint FULL(POL)
12306: -- 2) Removed the join to PO_LINE_LOCATIONS_GT as it will bring in wrong
12307: -- result if the standard PO line has multiple shipments
12308:
12309: SELECT /*+ FULL(POL) */
12310: POL.line_num
12316: * PLL2.price_override )
12317: )
12318: , 0 )
12319: BULK COLLECT INTO l_prev_doc_line_num, l_prev_rel_amount
12320: FROM PO_LINE_LOCATIONS_ALL PLL2, PO_HEADERS_ALL POH1,
12321: PO_HEADERS_ALL POH2, PO_LINES_GT POL
12322: WHERE POL.po_header_id = p_document_id
12323: AND POL.from_header_id = POH1.po_header_id --JOIN
12324: AND POH1.type_lookup_code = 'BLANKET'
12543: AND POH.type_lookup_code = 'BLANKET'
12544: AND POH.global_agreement_flag = 'Y'
12545: AND POL2.min_release_amount IS NOT NULL
12546: AND POL2.min_release_amount >
12547: --SQL What: Querying PO_LINE_LOCATIONS for the total amount of the
12548: --SQL shipments in this PO that reference the current GA line
12549: --SQL Why: This sum determines whether the minimum release amount
12550: --SQL for the GA line has been met
12551: ( SELECT --
12564: * PLL.price_override )
12565: )
12566: )
12567: --
12568: --Changing the query to go to PO_LINE_LOCATIONS_GT instead of PO_LINE_LOCATIONS
12569: FROM PO_LINE_LOCATIONS_GT PLL
12570: WHERE PLL.po_header_id = p_document_id
12571: AND PLL.from_line_id = POL2.po_line_id);
12572:
12565: )
12566: )
12567: --
12568: --Changing the query to go to PO_LINE_LOCATIONS_GT instead of PO_LINE_LOCATIONS
12569: FROM PO_LINE_LOCATIONS_GT PLL
12570: WHERE PLL.po_header_id = p_document_id
12571: AND PLL.from_line_id = POL2.po_line_id);
12572:
12573: --Increment the p_sequence with number of errors reported in last query
12713: AND POL.contract_id = POHA.po_header_id
12714: AND POHA.global_agreement_flag = 'Y'
12715: AND NOT EXISTS --< Bug 3301427 Start >
12716: (SELECT 'previously approved shipment'
12717: FROM po_line_locations_gt pllg
12718: WHERE pllg.po_line_id = pol.po_line_id
12719: AND pllg.approved_date IS NOT NULL) --< Bug 3301427 End >
12720: AND NOT EXISTS (SELECT 1
12721: FROM po_ga_org_assignments PGOA,
12789: AND POL.contract_id = POHA.po_header_id
12790: AND POHA.global_agreement_flag = 'Y'
12791: AND NOT EXISTS --< Bug 3301427 Start >
12792: (SELECT 'previously approved shipment'
12793: FROM po_line_locations_gt pllg
12794: WHERE pllg.po_line_id = pol.po_line_id
12795: AND pllg.approved_date IS NOT NULL) --< Bug 3301427 End >
12796: AND NOT EXISTS (SELECT 1
12797: FROM po_ga_org_assignments PGOA
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
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;
13489: DELETE FROM po_req_headers_gt;
13490: DELETE FROM po_req_lines_gt;
13631:
13632: --LCM ER start. Populate the lcm flag in the GT tables first
13633: IF p_doc_type in ('PO','RELEASE') THEN
13634:
13635: FOR ship_rec in (select line_location_id from po_line_locations_gt)
13636: LOOP
13637: set_lcm_flag(ship_rec.line_location_id,'BEFORE',l_return_status);
13638: END LOOP;
13639: END IF;
13745: -- SQL Why: Need to propogate line price change to shipment level
13746: -- for Standard PO
13747: -- SQL Join: po_line_id
13748: FORALL i IN 1..p_requested_changes.line_changes.po_line_id.COUNT
13749: UPDATE po_line_locations_gt
13750: SET price_override = nvl(p_requested_changes.line_changes.unit_price(i),price_override)
13751: WHERE po_line_id = p_requested_changes.line_changes.po_line_id(i)
13752: AND nvl(payment_type, 'NULL') NOT IN ('MILESTONE', 'ADVANCE')
13753: --
13767: -- in values or if NULL then with existing values in the table
13768: -- SQL Why: Need to apply requested line level changes to global temp table
13769: -- SQL Join: po_line_location_id
13770: FORALL i IN 1..p_requested_changes.shipment_changes.po_line_location_id.COUNT
13771: UPDATE po_line_locations_gt
13772: SET quantity = nvl(p_requested_changes.shipment_changes.quantity(i),quantity),
13773: promised_date = nvl(p_requested_changes.shipment_changes.promised_date(i),
13774: promised_date),
13775: price_override = nvl(p_requested_changes.shipment_changes.price_override(i),
13805: l_progress := '004';
13806:
13807: --
13808: FORALL i IN 1..p_requested_changes.shipment_changes.po_line_location_id.COUNT
13809: INSERT INTO po_line_locations_gt(
13810: LINE_LOCATION_ID ,
13811: LAST_UPDATE_DATE ,
13812: LAST_UPDATED_BY ,
13813: PO_HEADER_ID ,
13946: SELECT
13947: -- bug3611217
13948: -- Use a sequence number rather than FND_API.G_MISS_NUM
13949: -- since we have added unique constraint on LINE_LOCATION_ID
13950: PO_LINE_LOCATIONS_S.nextval,
13951: poll.LAST_UPDATE_DATE ,
13952: poll.LAST_UPDATED_BY ,
13953: poll.PO_HEADER_ID ,
13954: poll.PO_LINE_ID ,
14118: )
14119: -- Note: the value basis decode assumes Milestone Pay Items are Amount
14120: -- Milestones, since payment type is not changeable on Qty-based lines
14121: --
14122: FROM po_line_locations poll
14123: , po_lines_all pol --
14124: WHERE poll.line_location_id =
14125: p_requested_changes.shipment_changes.parent_line_location_id(i)
14126: AND p_requested_changes.shipment_changes.po_line_location_id(i) IS NULL
16294: AND (l.cancel_flag is null or l.cancel_flag <> 'Y')
16295: AND (l.closed_code is NULL or l.closed_code NOT IN ('FINALLY CLOSED'))))
16296: AND (p_line_location_id(i) IS NULL
16297: --Case 4: Optionally, Line Location is specified
16298: OR EXISTS (SELECT null from po_line_locations_all l
16299: WHERE l.line_location_id = p_line_location_id(i)
16300: AND (l.cancel_flag is null or l.cancel_flag <> 'Y')
16301: AND (l.closed_code is NULL or l.closed_code NOT IN ('FINALLY CLOSED')))) ;
16302: -- bug3592160 END
16528: AND (l.closed_code is NULL or l.closed_code <> 'FINALLY CLOSED')))
16529: AND (p_line_location_id(i) IS NULL
16530: --Case 4: Optionally, Line Location is specified
16531: -- Bug 3407980: Modified closed_code condition so that it only discards 'FINALLY CLOSED'
16532: OR EXISTS (SELECT null from po_line_locations_all l
16533: WHERE l.line_location_id = p_line_location_id(i)
16534: AND (l.closed_code is NULL or l.closed_code <> 'FINALLY CLOSED')))
16535: ;
16536:
16697: AND (l.closed_code is NULL or l.closed_code <> 'FINALLY CLOSED')))
16698: AND (p_line_location_id(i) IS NULL
16699: --Case 4: Optionally, Line Location is specified
16700: -- Bug 3407980: Modified closed_code condition so that it only discards 'FINALLY CLOSED'
16701: OR EXISTS (SELECT null from po_line_locations_all l
16702: WHERE l.line_location_id = p_line_location_id(i)
16703: AND (l.closed_code is NULL or l.closed_code <> 'FINALLY CLOSED')))
16704: ;
16705:
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
16879: WHERE s.line_location_id = p_line_location_id(i))
16880: WHERE p_line_location_id(i) is not null and gt.po_header_id = l_sequence(i)
16881: ;
16882:
17255: , NULL , 'PO_SUB_SHIP_BILL_GT_ORD_AMT'
17256: , 'PO_SUB_SHIP_BILL_GT_ORD_QTY'
17257: )
17258: FROM
17259: PO_LINE_LOCATIONS_GT POLL
17260: , PO_LINES_ALL POL -- For releases, PO_LINES_GT table isn't populated.
17261: WHERE POLL.po_line_id = POL.po_line_id
17262: AND nvl(POLL.cancel_flag, 'N') = 'N'
17263: AND nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
17343: , 'PO_SUB_DIST_BILL_GT_ORD_QTY'
17344: )
17345: FROM
17346: PO_DISTRIBUTIONS_GT POD
17347: , PO_LINE_LOCATIONS_GT POLL
17348: , PO_LINES_ALL POL -- For releases, PO_LINES_GT table isn't populated.
17349: WHERE POD.line_location_id = POLL.line_location_id
17350: AND POL.po_line_id = POLL.po_line_id
17351: AND nvl(POLL.cancel_flag, 'N') = 'N'
17430: , 'PO_SUB_DIST_DLVR_GT_ORD_QTY'
17431: )
17432: FROM
17433: PO_DISTRIBUTIONS_GT POD
17434: , PO_LINE_LOCATIONS_GT POLL
17435: , PO_LINES_ALL POL -- For releases, PO_LINES_GT table isn't populated.
17436: WHERE POD.line_location_id = POLL.line_location_id
17437: AND POL.po_line_id = POLL.po_line_id
17438: AND nvl(POLL.cancel_flag,'N') = 'N'
17504: --Name: populate_line_locations_gt
17505: --Pre-reqs:
17506: -- None.
17507: --Modifies:
17508: -- PO_LINE_LOCATIONS_GT
17509: --Locks:
17510: -- None.
17511: --Function:
17512: -- Populates the line locations GTT for submission checks.
17580: End;
17581: END IF;
17582:
17583: FORALL i IN 1 .. l_line_location_id_tbl.COUNT
17584: INSERT INTO PO_LINE_LOCATIONS_GT (
17585: line_location_id ,
17586: last_update_date ,
17587: last_updated_by ,
17588: po_header_id ,
17884: poll.CLM_PROMISE_PERIOD_UOM ,
17885: poll.CLM_POP_DURATION ,
17886: poll.CLM_POP_DURATION_UOM
17887:
17888: FROM PO_LINE_LOCATIONS_MERGE_V POLL
17889: WHERE POLL.LINE_LOCATION_ID = l_line_location_id_tbl(i)
17890: AND POLL.DRAFT_ID = p_draft_id
17891: /*-- CLM Aprvl
17892: FROM PO_LINE_LOCATIONS_ALL POLL
17888: FROM PO_LINE_LOCATIONS_MERGE_V POLL
17889: WHERE POLL.LINE_LOCATION_ID = l_line_location_id_tbl(i)
17890: AND POLL.DRAFT_ID = p_draft_id
17891: /*-- CLM Aprvl
17892: FROM PO_LINE_LOCATIONS_ALL POLL
17893: WHERE POLL.line_location_id = l_line_location_id_tbl(i)
17894: -- CLM Aprvl*/
17895: --
17896: AND (l_draft_type <> 'PAR'
18414: , l_shipment_num_tbl
18415: , l_distribution_num_tbl
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
19277: FROM po_headers_gt poh
19278: WHERE EXISTS
19279: (
19280: SELECT 'Eligible shipment'
19281: FROM po_line_locations_gt poll
19282: WHERE poll.po_header_id = poh.po_header_id
19283: AND NVL(poll.cancel_flag, 'N') = 'N'
19284: AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
19285: )
19329: FROM po_releases_gt por
19330: WHERE EXISTS
19331: (
19332: SELECT 'Eligible shipment'
19333: FROM po_line_locations_gt poll
19334: WHERE poll.po_release_id = por.po_release_id
19335: AND NVL(poll.cancel_flag, 'N') = 'N'
19336: AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
19337: )
19384: FROM po_lines_gt pol
19385: WHERE EXISTS
19386: (
19387: SELECT 'Eligible shipment'
19388: FROM po_line_locations_gt poll
19389: WHERE poll.po_line_id = pol.po_line_id
19390: AND NVL(poll.cancel_flag, 'N') = 'N'
19391: AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
19392: )
19459: || poll.shipment_num || g_delim || l_textline, 1, 240)
19460: )
19461: , DECODE(p_document_type, g_document_type_RELEASE, 'PO_CAN_RELS_WITH_RCV_TRX',
19462: 'PO_CAN_POLL_WITH_RCV_TRX')
19463: FROM po_lines_gt pol, po_line_locations_gt poll
19464: WHERE pol.po_line_id = poll.po_line_id
19465: AND NVL(poll.cancel_flag, 'N') = 'N'
19466: AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
19467: AND EXISTS
19499: , 0
19500: , p_sequence + ROWNUM
19501: , substr(g_shipmsg || g_delim || poll.shipment_num || g_delim || l_textline, 1, 240)
19502: , 'PO_CAN_RELS_WITH_RCV_TRX'
19503: FROM po_line_locations_gt poll
19504: WHERE EXISTS
19505: (
19506: SELECT 'Eligible shipment'
19507: FROM po_releases_gt por
19649: substr(g_linemsg || g_delim || pol.line_num || g_delim || g_shipmsg || g_delim
19650: || poll.shipment_num || g_delim ||
19651: DECODE(poll.payment_type , NULL, l_text_normal_po, l_text_complex_po), 1, 240))
19652: , DECODE(poll.payment_type, NULL, 'PO_CAN_POLL_WITH_ASN', 'PO_CAN_POLL_WITH_PENDING_WCR')
19653: FROM po_lines_gt pol, po_line_locations_gt poll
19654: WHERE NVL(poll.cancel_flag, 'N') = 'N'
19655: AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
19656: AND EXISTS
19657: (
19696: , 0
19697: , p_sequence + ROWNUM
19698: , substr(g_shipmsg || g_delim || poll.shipment_num || g_delim || l_textline, 1, 240)
19699: , 'PO_CAN_POLL_WITH_ASN'
19700: FROM po_releases_gt por, po_line_locations_gt poll
19701: WHERE poll.po_release_id = por.po_release_id
19702: AND NVL(poll.cancel_flag, 'N') = 'N'
19703: AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
19704: AND EXISTS
19758: , substr(g_linemsg || g_delim || pol.line_num || g_delim || g_shipmsg || g_delim
19759: || poll.shipment_num || g_delim ||
19760: DECODE(poll.payment_type , NULL, l_text_normal_po, l_text_complex_po), 1, 240)
19761: , DECODE(poll.payment_type, NULL, 'PO_CAN_POLL_WITH_ASN', 'PO_CAN_POLL_WITH_PENDING_WCR')
19762: FROM po_lines_gt pol, po_line_locations_gt poll
19763: WHERE poll.po_line_id = pol.po_line_id
19764: AND NVL(poll.cancel_flag, 'N') = 'N'
19765: AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
19766: AND EXISTS
19820: , substr(g_linemsg || g_delim || pol.line_num || g_delim || g_shipmsg || g_delim
19821: || poll.shipment_num || g_delim ||
19822: DECODE(poll.payment_type , NULL, l_text_normal_po, l_text_complex_po), 1, 240)
19823: , DECODE(poll.payment_type, NULL, 'PO_CAN_POLL_WITH_ASN', 'PO_CAN_POLL_WITH_PENDING_WCR')
19824: FROM po_lines_gt pol, po_line_locations_gt poll
19825: WHERE poll.po_line_id = pol.po_line_id
19826: AND NVL(poll.cancel_flag, 'N') = 'N'
19827: AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
19828: AND EXISTS
19867: , 0
19868: , p_sequence + ROWNUM
19869: , substr(g_shipmsg || g_delim || poll.shipment_num || g_delim || l_textline, 1, 240)
19870: , 'PO_CAN_POLL_WITH_ASN'
19871: FROM po_line_locations_gt poll
19872: WHERE EXISTS
19873: (
19874: SELECT 'Eligible shipment'
19875: FROM po_releases_gt por
20004: , 'QTY1', round(sum(NVL(pod.quantity_delivered, 0)),5) /* Bug:13427569 rounded to 5 digits */
20005: )
20006: ), 1, 240)
20007: , 'PO_CAN_POLL_REC_NOT_DEL'
20008: FROM po_lines_gt pol, po_line_locations_gt poll, po_distributions_gt pod
20009: WHERE pod.line_location_id = poll.line_location_id
20010: AND pol.po_line_id = poll.po_line_id
20011: AND pol.order_type_lookup_code NOT IN ('RATE', 'FIXED PRICE')
20012: AND NVL(poll.cancel_flag, 'N') = 'N'
20127: , 'QTY1', sum(NVL(pod.amount_delivered, 0))
20128: )
20129: ), 1, 240)
20130: , 'PO_CAN_POLL_AMT_REC_NOT_DEL'
20131: FROM po_lines_gt pol, po_line_locations_gt poll, po_distributions_gt pod
20132: WHERE pod.line_location_id = poll.line_location_id
20133: AND pol.po_line_id = poll.po_line_id
20134: AND pol.order_type_lookup_code IN ('RATE', 'FIXED PRICE')
20135: AND NVL(poll.cancel_flag, 'N') = 'N'
20262: , g_linemsg || g_delim || pol.line_num || g_delim || g_shipmsg || g_delim
20263: || poll.shipment_num || g_delim || g_distmsg || g_delim || l_textline
20264: ), 1, 240)
20265: , 'PO_CAN_POLL_INVALID_ACCT_FLEX'
20266: FROM po_lines_gt pol, po_line_locations_gt poll
20267: , po_distributions_gt pod, gl_code_combinations gcc
20268: WHERE pod.line_location_id = poll.line_location_id
20269: AND pol.po_line_id = poll.po_line_id
20270: AND poll.shipment_type IN ('STANDARD', 'PLANNED', 'PREPAYMENT') --
20365: FROM po_lines_gt pol
20366: WHERE EXISTS
20367: (
20368: SELECT 'Uncancelled Open Releases Exist'
20369: FROM po_line_locations pll
20370: WHERE pll.po_line_id = pol.po_line_id
20371: AND pll.shipment_type = 'BLANKET'
20372: AND NVL(pll.cancel_flag, 'N') = 'N'
20373: AND NVL(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
20651: , p_sequence + ROWNUM
20652: , substr(g_linemsg || g_delim || pol.line_num || g_delim || g_shipmsg
20653: || poll.shipment_num || g_delim || l_textline,1,240) --Bug5096900
20654: , 'PO_CAN_POLL_PLAN_WITH_OPEN_REL'
20655: FROM po_lines_gt pol, po_line_locations_gt poll
20656: WHERE poll.po_line_id = pol.po_line_id
20657: AND NVL(poll.cancel_flag, 'N') = 'N'
20658: AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
20659: AND EXISTS
20658: AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
20659: AND EXISTS
20660: (
20661: SELECT 'Uncancelled Open Releases Exist'
20662: FROM po_line_locations poll2
20663: WHERE poll2.source_shipment_id = poll.line_location_id
20664: AND poll2.shipment_type = 'SCHEDULED'
20665: AND NVL(poll2.cancel_flag, 'N') = 'N'
20666: AND NVL(poll2.closed_code, 'OPEN') <> 'FINALLY CLOSED'
20771: , l_line_amt_tbl --
20772: , l_lineloc_qty_tbl --
20773: , l_lineloc_amt_tbl --
20774: FROM
20775: PO_LINE_LOCATIONS_GT PLL
20776: , PO_LINES_GT POL
20777: WHERE
20778: POL.po_line_id = PLL.po_line_id
20779: AND PLL.shipment_type in ('STANDARD', 'PLANNED')
20784: ( ( POL.quantity IS NOT NULL ) --
20785: AND ( round(POL.quantity, 10) <>
20786: (SELECT round(sum(PLL2.quantity) -
20787: sum(nvl(PLL2.quantity_cancelled, 0)), 10)
20788: FROM PO_LINE_LOCATIONS_GT PLL2
20789: WHERE PLL2.po_line_id = POL.po_line_id AND
20790: PLL2.shipment_type in ('STANDARD', 'PLANNED') ) )
20791: )
20792: OR --
20795: ( SELECT round ( sum ( PLL3.amount
20796: - nvl(PLL3.amount_cancelled, 0) )
20797: , 10
20798: )
20799: FROM po_line_locations_gt PLL3
20800: WHERE PLL3.po_line_id = POL.po_line_id
20801: AND PLL3.shipment_type IN ('STANDARD','PLANNED')
20802: )
20803: )
20957: WHEN PLL.payment_type = 'ADVANCE' THEN
20958: PLL.amount
20959: END) financing_advance_amount
20960: FROM
20961: PO_LINE_LOCATIONS_GT PLL
20962: , PO_LINES_GT POL
20963: WHERE
20964: POL.po_line_id = PLL.po_line_id
20965: AND ( (l_is_financing_flag = 'N' AND PLL.shipment_type = 'STANDARD')
21087: , l_lineloc_amt_tbl
21088: , l_dist_qty_tbl
21089: , l_dist_amt_tbl
21090: , l_line_id_tbl -- bug 16474947
21091: FROM PO_DISTRIBUTIONS_GT POD,PO_LINE_LOCATIONS_GT PLL, PO_LINES_GT POL
21092: WHERE PLL.po_line_id = POL.po_line_id
21093: AND POD.line_location_id = PLL.line_location_id
21094: AND PLL.po_header_id = p_document_id
21095: AND nvl(PLL.cancel_flag,'N') = 'N'
21247: , NVL(pll.quantity_financed, 0)
21248: INTO l_quantity_received
21249: , l_quantity_billed
21250: , l_quantity_financed
21251: FROM po_line_locations_all pll
21252: WHERE line_location_id = p_line_location_id;
21253:
21254: d_progress := 20;
21255:
21537: , null -- distribution_num
21538: , p_sequence + ROWNUM
21539: , substr(l_textline, 1, 240)
21540: , l_message_name
21541: FROM po_line_locations_gt poll
21542: WHERE poll.line_location_id=p_doc_level_id
21543: AND chk_unv_invoices(l_invoice_type, poll.po_header_id, poll.po_release_id, NULL,poll.line_location_id, NULL, p_origin_doc_id, l_calling_sequence) = 1;
21544: --
21545: -- Handle Distribution Unreserve(for both SPO/Release)
21643: WHERE po_line_id=p_doc_level_id;
21644: ELSIF p_document_level = g_document_level_SHIPMENT THEN
21645: SELECT po_header_id
21646: INTO l_document_id
21647: FROM po_line_locations_gt
21648: WHERE line_location_id=p_doc_level_id;
21649: ELSIF p_document_level = g_document_level_DISTRIBUTION THEN
21650: SELECT po_header_id
21651: INTO l_document_id
21741: l_consigned_flag VARCHAR2(20);
21742: l_outsourced_assembly VARCHAR2(20);
21743: l_log_head VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_CHECKS_PVT.Set_LCM_Flag';
21744: l_progress VARCHAR2(3) := '000';
21745: l_line_location_id po_line_locations_all.line_location_id%TYPE; --
21747: BEGIN
21748: IF g_debug_stmt THEN
21749: PO_DEBUG.debug_begin(l_log_head);
21771: l_ship_to_organization_id,
21772: l_consigned_flag,
21773: l_outsourced_assembly,
21774: l_line_location_id
21775: FROM po_line_locations_all poll,
21776: po_lines_all pol,
21777: po_headers_all poh
21778: WHERE poh.po_header_id = pol.po_header_id
21779: AND pol.po_line_id = poll.po_line_id
21814: PO_DEBUG.debug_var(l_log_head,l_progress,'l_return_status',l_return_status);
21815:
21816: IF l_return_status = 'Y' THEN
21817: IF p_doc_check_status = 'BEFORE' THEN
21818: UPDATE po_line_locations_gt
21819: SET lcm_flag = 'Y'
21820: WHERE line_location_id = p_line_location_id
21821: and lcm_flag is null;
21822:
21827:
21828:
21829: ELSIF p_doc_check_status = 'AFTER' THEN
21830:
21831: UPDATE po_line_locations_all
21832: SET lcm_flag = 'Y'
21833: WHERE line_location_id = p_line_location_id;
21834:
21835: UPDATE po_distributions_all
21838: END IF;
21839:
21840: ELSIF l_return_status = 'N' THEN
21841:
21842: UPDATE po_line_locations_all
21843: SET lcm_flag = null
21844: WHERE line_location_id = p_line_location_id
21845: AND lcm_flag = 'Y';
21846:
22798: ||g_shipmsg||g_delim||PLL.shipment_num||g_delim||g_distmsg||g_delim||POD.DISTRIBUTION_NUM||g_delim||L_TEXTLINE,1,240),
22799: 'PO_CHARGE_NOT_NULL'
22800: FROM
22801: PO_DISTRIBUTIONS_GT POD,
22802: PO_LINE_LOCATIONS_GT PLL,
22803: PO_LINES_GT POL
22804: WHERE POD.CODE_COMBINATION_ID IS NULL
22805: AND POD.PO_HEADER_ID = P_DOCUMENT_ID
22806: AND NVL(POD.DISTRIBUTION_TYPE, 'STANDARD') <> 'AGREEMENT'
22847: ||g_shipmsg||g_delim||PLL.shipment_num||g_delim||g_distmsg||g_delim||POD.DISTRIBUTION_NUM||g_delim||L_TEXTLINE,1,240),
22848: 'PO_ALL_NO_BUDGET_FLEX'
22849: FROM
22850: PO_DISTRIBUTIONS_GT POD,
22851: PO_LINE_LOCATIONS_GT PLL,
22852: PO_LINES_GT POL
22853: WHERE POD.BUDGET_ACCOUNT_ID IS NULL
22854: AND POD.PO_HEADER_ID = P_DOCUMENT_ID
22855: AND NVL(POD.DISTRIBUTION_TYPE, 'STANDARD') <> 'AGREEMENT'
22954: ),
22955: 'PO_LINE_SHIP_UOM_MISMATCH'
22956:
22957: FROM
22958: PO_LINE_LOCATIONS_ALL PLL,
22959: PO_LINES_ALL POL
22960: WHERE POL.PO_LINE_ID=PLL.PO_LINE_ID
22961: AND NVL(PLL.unit_meas_lookup_code,-1) <> NVL(POL.unit_meas_lookup_code,-1)
22962: AND PLL.PO_RELEASE_ID = P_DOCUMENT_ID;
22992: , 'SHIP_NUM', PLL.SHIPMENT_NUM
22993: ),
22994: 'PO_LINE_SHIP_UOM_MISMATCH'
22995: FROM
22996: PO_LINE_LOCATIONS_ALL PLL,
22997: PO_LINES_ALL POL
22998: WHERE POL.PO_LINE_ID=PLL.PO_LINE_ID
22999: AND NVL(PLL.unit_meas_lookup_code,-1)<>NVL(POL.unit_meas_lookup_code,-1)
23000: -- bug 12597958 : bypassing check for amount based lines.
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
23498: AND ph.po_header_id = pll.po_header_id
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
23557: AND ph.po_header_id = pl.po_header_id
23558: AND pl.po_line_id=pll.po_line_id
23900: Nvl2( PoD.code_combination_id, 'PO_RI_INVALID_CHARGE_ACC_ID', 'PO_CHARGE_NOT_NULL' )
23901: FROM
23902: PO_DISTRIBUTIONS_GT POD,
23903: gl_sets_of_books sob,
23904: PO_LINE_LOCATIONS_GT POLL
23905: WHERE
23906: POD.PO_RELEASE_ID = P_DOCUMENT_ID
23907: AND POD.line_location_id = POLL.line_location_id
23908: AND POD.line_location_id IS NOT NULL
23960: Nvl2( PoD.budget_account_id, 'PO_RI_INVALID_BUDGET_ACC_ID', 'PO_BUDGET_NOT_NULL' )
23961: FROM
23962: PO_DISTRIBUTIONS_GT POD,
23963: gl_sets_of_books sob,
23964: PO_LINE_LOCATIONS_GT POLL
23965: WHERE
23966: POD.PO_RELEASE_ID = P_DOCUMENT_ID
23967: AND POD.line_location_id = POLL.line_location_id
23968: AND POD.line_location_id IS NOT NULL
24020: Nvl2( PoD.accrual_account_id, 'PO_RI_INVALID_ACCRUAL_ACC_ID', 'PO_ACCRUAL_NOT_NULL' )
24021: FROM
24022: PO_DISTRIBUTIONS_GT POD,
24023: gl_sets_of_books sob,
24024: PO_LINE_LOCATIONS_GT POLL
24025: WHERE
24026: POD.PO_RELEASE_ID = P_DOCUMENT_ID
24027: AND POD.line_location_id = POLL.line_location_id
24028: AND POD.line_location_id IS NOT NULL
24078: Nvl2( PoD.variance_account_id, 'PO_RI_INVALID_VARIANCE_ACC_ID', 'PO_VARIANCE_NOT_NULL' )
24079: FROM
24080: PO_DISTRIBUTIONS_GT POD,
24081: gl_sets_of_books sob,
24082: PO_LINE_LOCATIONS_GT POLL
24083: WHERE
24084: POD.PO_RELEASE_ID = P_DOCUMENT_ID
24085: AND POD.line_location_id = POLL.line_location_id
24086: AND POD.line_location_id IS NOT NULL
24147: Nvl2( PoD.code_combination_id, 'PO_RI_INVALID_CHARGE_ACC_ID', 'PO_CHARGE_NOT_NULL' )
24148: FROM
24149: PO_DISTRIBUTIONS_GT POD,
24150: gl_sets_of_books sob,
24151: PO_LINE_LOCATIONS_GT POLL,
24152: PO_LINES_GT POL
24153: WHERE
24154: POD.PO_HEADER_ID = P_DOCUMENT_ID
24155: AND POD.line_location_id = POLL.line_location_id
24210: Nvl2( PoD.budget_account_id, 'PO_RI_INVALID_BUDGET_ACC_ID', 'PO_BUDGET_NOT_NULL' )
24211: FROM
24212: PO_DISTRIBUTIONS_GT POD,
24213: gl_sets_of_books sob,
24214: PO_LINE_LOCATIONS_GT POLL,
24215: PO_LINES_GT POL
24216: WHERE
24217: POD.PO_HEADER_ID = P_DOCUMENT_ID
24218: AND POD.po_release_id IS NULL
24277: Nvl2( PoD.accrual_account_id, 'PO_RI_INVALID_ACCRUAL_ACC_ID', 'PO_ACCRUAL_NOT_NULL' )
24278: FROM
24279: PO_DISTRIBUTIONS_GT POD,
24280: gl_sets_of_books sob,
24281: PO_LINE_LOCATIONS_GT POLL,
24282: PO_LINES_GT POL
24283: WHERE
24284: POD.PO_HEADER_ID = P_DOCUMENT_ID
24285: AND POD.line_location_id = POLL.line_location_id
24339: Nvl2( PoD.variance_account_id, 'PO_RI_INVALID_VARIANCE_ACC_ID', 'PO_VARIANCE_NOT_NULL' )
24340: FROM
24341: PO_DISTRIBUTIONS_GT POD,
24342: gl_sets_of_books sob,
24343: PO_LINE_LOCATIONS_GT POLL,
24344: PO_LINES_GT POL
24345: WHERE
24346: POD.PO_HEADER_ID = P_DOCUMENT_ID
24347: AND POD.line_location_id = POLL.line_location_id
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
24584: AND PLL.po_header_id = POH.po_header_id
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
24718: AND POL.po_header_id = POH.po_header_id
24719: AND POLL.po_header_id = POH.po_header_id
25042: AND pol.clm_pop_exception_reason IS NULL
25043: AND pol.clm_delivery_event_code IS NULL
25044: AND 12 < (SELECT Months_between (Max(clm_period_perf_end_date),
25045: Min(clm_period_perf_start_date))
25046: FROM po_line_locations_gt poll
25047: WHERE poll.po_line_id = pol.po_line_id
25048: AND poll.po_header_id = pol.po_header_id);
25049:
25050: p_sequence := p_sequence + SQL%ROWCOUNT;
25105: pll.shipment_num,
25106: pda.distribution_num,
25107: po_core_s3.Get_fiscal_year(
25108: (SELECT Min(pll.clm_period_perf_start_date)
25109: FROM po_line_locations_gt poll
25110: WHERE poll.po_line_id = pll.po_line_id
25111: AND poll.po_header_id = pll.po_header_id),
25112: pda.set_of_books_id) Min_start_date_fiscal_year,
25113: po_core_s3.Get_fiscal_year(pll.clm_period_perf_end_date,
25112: pda.set_of_books_id) Min_start_date_fiscal_year,
25113: po_core_s3.Get_fiscal_year(pll.clm_period_perf_end_date,
25114: pda.set_of_books_id) end_date_fiscal_year
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,
25127: AND pol.clm_delivery_event_code IS NULL
25128: AND 12 >= (SELECT Months_between (
25129: Max(clm_period_perf_end_date),
25130: Min(clm_period_perf_start_date))
25131: FROM po_line_locations_gt poll
25132: WHERE poll.po_line_id = pol.po_line_id
25133: AND poll.po_header_id = pol.po_header_id))
25134: lines
25135: WHERE pda.po_line_id = lines.po_line_id
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
25194: AND poll.po_header_id = pol.po_header_id
25823: FROM po_distributions_gt POD,
25824: po_req_distributions_all PRD,
25825: po_requisition_lines_all PRL,
25826: po_requisition_headers_all PRH,
25827: po_line_locations_gt POLL,
25828: po_lines_gt POL
25829: WHERE POD.po_header_id = p_document_id
25830: AND POD.req_distribution_id = PRD.distribution_id
25831: AND PRD.requisition_line_id = PRL.requisition_line_id
26318: ' of Line '||POL.line_num||' , '|| L_TEXTLINE,
26319: 'PO_RI_INVALID_ACCRUE_ON_RCT'
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
26369: ' of Line '||POL.line_num||' , '|| L_TEXTLINE,
26370: 'PO_RI_INVALID_ACCRUE_ON_RCT'
26371: FROM
26372: PO_DISTRIBUTIONS_GT POD,
26373: PO_LINE_LOCATIONS_GT POLL,
26374: PO_LINES POL,
26375: PO_RELEASES_GT POR
26376:
26377: WHERE