[Home] [Help]
1367:
1368: l_progress := '027';
1369: x_return_status := FND_API.G_RET_STS_SUCCESS;
1370:
1371: -- 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.
1372: IF p_document_type in ('PO','RELEASE') THEN
1373:
1374: FOR ship_rec in (select line_location_id from po_line_locations_gt)
1375:
1370:
1371: -- 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.
1372: IF p_document_type in ('PO','RELEASE') THEN
1373:
1374: FOR ship_rec in (select line_location_id from po_line_locations_gt)
1375:
1376: LOOP
1377: set_lcm_flag(ship_rec.line_location_id,'AFTER',l_return_status);
1378: END LOOP;
2532: FROM PO_RELEASES_GT POR
2533: WHERE POR.po_release_id = p_document_id
2534: AND NOT EXISTS
2535: (SELECT 'Shipment Exist'
2536: FROM PO_LINE_LOCATIONS_GT PLL
2537: WHERE PLL.po_release_id = POR.po_release_id);
2538: -- AND nvl(PLL.cancel_flag,'N') = 'N');
2539: -- bug 3305488
2540:
2558: HLT.location_code
2559: BULK COLLECT INTO
2560: l_line_num,
2561: l_ship_to_location_tbl
2562: FROM PO_LINE_LOCATIONS_GT POLG,
2563: HR_LOCATIONS_ALL HLA,
2564: HR_LOCATIONS_ALL_TL HLT
2565: WHERE POLG.po_release_id = p_document_id
2566: AND POLG.ship_to_location_id=HLA.location_id
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'
2722: , l_dist_qty_tbl --
2723: , l_dist_amt_tbl --
2724: FROM
2725: PO_DISTRIBUTIONS_GT POD --
2726: , PO_LINE_LOCATIONS_GT PLL
2727: WHERE
2728: PLL.line_location_id = POD.line_location_id
2729: AND PLL.po_release_id = p_document_id
2730: AND nvl(PLL.cancel_flag,'N') = 'N'
2832: p_sequence + ROWNUM,
2833: substr(g_shipmsg||g_delim||PLL.shipment_num||g_delim
2834: ||l_textline,1,240),
2835: 'PO_SUB_REL_SHIP_NO_DIST'
2836: FROM PO_LINE_LOCATIONS_GT PLL
2837: WHERE PLL.po_release_id = p_document_id
2838: AND nvl(PLL.cancel_flag,'N') = 'N'
2839: AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
2840: AND NOT EXISTS
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
2960: * PLL2.price_override )
2961: )
2962: )
2963: )
2964: FROM PO_LINE_LOCATIONS_GT PLL2
2965: WHERE PLL2.po_release_id = POR.po_release_id
2966: AND PLL2.shipment_type IN ('BLANKET', 'SCHEDULED')
2967: );
2968:
2999: , ( ( PLL1.quantity
3000: - nvl(PLL1.quantity_cancelled,0) )
3001: * PLL1.price_override ) ) )
3002: INTO l_this_rel_amount
3003: FROM PO_LINE_LOCATIONS_GT PLL1, PO_RELEASES_GT POR1, PO_HEADERS_GT POH
3004: WHERE POR1.po_release_id = p_document_id
3005: AND POR1.po_header_id = POH.po_header_id -- Bug 7188760
3006: AND POH.amount_limit is Not Null -- Bug 7188760
3007: AND PLL1.po_release_id = POR1.po_release_id;
3018: - nvl(PLL2.quantity_cancelled,0) )
3019: * PLL2.price_override ) ) )
3020: , 0 )
3021: INTO l_previous_rel_amount
3022: FROM PO_LINE_LOCATIONS PLL2, PO_RELEASES POR2, PO_HEADERS_GT POH
3023: WHERE PLL2.po_release_id = POR2.po_release_id
3024: AND POR2.po_header_id = POH.po_header_id
3025: AND nvl(POR2.approved_flag, 'N') = 'Y'
3026: AND POH.amount_limit is Not Null -- Bug 7188760
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
3117: )
3118: )
3119: )
3120: --
3121: --Change the table in from clause from PO_LINE_LOCATIONS to PO_LINE_LOCATIONS_GT
3122: FROM PO_LINE_LOCATIONS_GT PLL2
3123: WHERE PLL2.po_line_id = POL.po_line_id
3124: AND PLL2.po_release_id = POR.po_release_id
3125: AND PLL2.shipment_type in ('BLANKET', 'SCHEDULED')
3118: )
3119: )
3120: --
3121: --Change the table in from clause from PO_LINE_LOCATIONS to PO_LINE_LOCATIONS_GT
3122: FROM PO_LINE_LOCATIONS_GT PLL2
3123: WHERE PLL2.po_line_id = POL.po_line_id
3124: AND PLL2.po_release_id = POR.po_release_id
3125: AND PLL2.shipment_type in ('BLANKET', 'SCHEDULED')
3126: )
3238: p_sequence + ROWNUM,
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)
3304: ||g_shipmsg||g_delim||PLL.shipment_num||g_delim
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
3406: ||g_shipmsg||g_delim||PLL.shipment_num||g_delim
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
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
3498: AND PLL.po_line_id = POL.po_line_id
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
3889: p_sequence + ROWNUM,
3890: substr (g_shipmsg||g_delim||PLL.shipment_num||g_delim||l_textline,1,240),
3891: 'PO_SUB_REL_SHIP_INV_MATCH_NE_R'
3892: FROM PO_RELEASES_GT POR,
3893: PO_LINE_LOCATIONS_GT PLL
3894: WHERE POR.po_release_id = PLL.po_release_id
3895: AND POR.po_release_id = p_document_id
3896: AND Nvl(PLL.LCM_FLAG,'N') = 'Y'
3897: AND Nvl(PLL.match_option,'P') <> 'R';
3934: substr (g_shipmsg||g_delim||PLL.shipment_num||g_delim||g_distmsg||g_delim||
3935: POD.distribution_num||g_delim||l_textline, 1,240),
3936: 'PO_SUB_REL_DIST_DEST_TYPE_NE_I'
3937: FROM PO_RELEASES_GT POR,
3938: PO_LINE_LOCATIONS_GT PLL,
3939: PO_DISTRIBUTIONS_GT POD
3940: WHERE POR.po_release_id = POD.po_release_id
3941: AND POD.line_location_id = PLL.line_location_id
3942: AND POR.po_release_id = p_document_id
4076: nvl(POL.line_num,0) line_num,
4077: nvl(POLL.quantity,0) quantity,
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
4111: POL.line_num line_num,
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
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 =
4121: (SELECT min(POLL2.line_location_id)
4122: FROM PO_LINE_LOCATIONS_GT POLL2
4123: WHERE POLL2.po_line_id = POL.po_line_id
4124: AND POLL2.shipment_type = 'STANDARD'
4125: )
4126: GROUP BY POL.unit_price, POLL.quantity, nvl(POLL.quantity_cancelled, 0),
4157: nvl(POL.line_num,0) line_num,
4158: nvl(POLL.quantity,0) quantity,
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
4179: min(POL.line_num),
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
4195: min(POL.line_num),
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
4340: --functional currency and ROU functional currency for currency conversions
4341: BEGIN
4342: SELECT POH.currency_code, nvl(trunc(POH.rate_date), trunc(POLL.creation_date))
4343: INTO l_po_curr, l_rate_date
4344: FROM PO_HEADERS POH, PO_LINE_LOCATIONS_GT POLL
4345: WHERE POLL.line_location_id = l_line_location_id(shipment_line)
4346: AND POLL.po_header_id = POH.po_header_id;
4347: EXCEPTION
4348: WHEN OTHERS THEN
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'
5744: FROM PO_LINE_LOCATIONS_GT PLL
5745: WHERE PLL.po_line_id = POL.po_line_id AND
5746: PLL.shipment_type in ('STANDARD','PLANNED'));
5747:
5748: --Increment the p_sequence with number of errors reported in last query
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
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
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
6313: substr(g_linemsg||g_delim||POL.line_num||g_delim||
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
6410: substr(g_linemsg||g_delim||POL.line_num||g_delim
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
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'
6776: g_shipmsg || g_delim || PLL.shipment_num ||g_delim || l_textline,1,240),
6777: 'PO_PO_PLANNED_ITEM_DATE_REQ'
6778: FROM po_lines_gt POL,
6779: po_headers_gt POH,
6780: po_line_locations_gt PLL,
6781: financials_system_parameters FSP,
6782: mtl_system_items MSI
6783: WHERE POH.po_header_id = p_document_id
6784: AND POL.po_header_id = POH.po_header_id
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
6825: and pol.po_Header_id=p_document_id
7026: 'PO_SUB_VMI_ASL_EXISTS',
7027: 'W'
7028: FROM PO_LINES_GT POL,
7029: PO_HEADERS_GT POH,
7030: PO_LINE_LOCATIONS_GT PLL,
7031: PO_APPROVED_SUPPLIER_LIS_VAL_V PASL,
7032: PO_ASL_ATTRIBUTES PAA,
7033: PO_ASL_STATUS_RULES_V PASR
7034:
7146: 'SHIP_NUM',pos.shipment_num,
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
7194: 'SHIP_NUM',pos.shipment_num,
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
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
7257: AND Nvl(PLL.LCM_FLAG,'N') = 'Y'
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
7308: AND PLL.po_line_id = POL.po_line_id
7524: * PLL2.price_override
7525: )
7526: )
7527: )
7528: FROM PO_LINE_LOCATIONS PLL2
7529: WHERE PLL2.po_header_id = POH.po_header_id
7530: AND PLL2.shipment_type in ('BLANKET', 'SCHEDULED')
7531: )
7532: )
7546: * PLL3.price_override
7547: )
7548: )
7549: )
7550: FROM PO_LINE_LOCATIONS_ALL PLL3
7551: WHERE PLL3.from_header_id = POH.po_header_id
7552: )
7553: )
7554: );
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'
8197: TYPE AslConsignedFlagTab is TABLE of
8198: po_asl_attributes.consigned_from_supplier_flag%TYPE
8199: INDEX by BINARY_INTEGER;
8200: TYPE ConsignedFlagTab is TABLE of
8201: po_line_locations.consigned_flag%TYPE
8202: INDEX by BINARY_INTEGER;
8203:
8204: l_vendor_id NumTab;
8205: l_vendor_site_id NumTab;
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'
8271: AND pll.consigned_flag = 'Y'
8326: l_line_num,
8327: l_shipment_num,
8328: l_line_location_id
8329: FROM PO_HEADERS_GT POH,
8330: PO_LINE_LOCATIONS_GT PLL,
8331: PO_LINES_GT POL
8332: WHERE POH.po_header_id = p_document_id AND
8333: POH.po_header_id = POL.po_header_id AND
8334: -- POH.po_header_id = PLL.po_header_id AND
8572: AND POH.global_agreement_flag = 'Y'
8573: --
8574: AND NOT EXISTS --< Bug 3301427 Start >
8575: (SELECT 'previously approved shipment'
8576: FROM po_line_locations_gt pllg
8577: WHERE pllg.po_line_id = pol.po_line_id
8578: AND pllg.approved_date IS NOT NULL) --< Bug 3301427 End >
8579: AND NOT EXISTS
8580: --SQL Query enabled org assignments of this current purchasing org
8641: AND NVL(POH2.approved_flag, 'N') <> 'Y'
8642: AND NVL(POH2.user_hold_flag, 'N') <> 'Y' --< Bug 3422733 >
8643: AND NOT EXISTS --< Bug 3301427 Start >
8644: (SELECT 'previously approved shipment'
8645: FROM po_line_locations_gt pllg
8646: WHERE pllg.po_line_id = pol.po_line_id
8647: AND pllg.approved_date IS NOT NULL); --< Bug 3301427 End >
8648:
8649: --Increment the p_sequence with number of errors reported in last query
8697: AND POH2.global_agreement_flag = 'Y'
8698: AND POH2.user_hold_flag = 'Y'
8699: AND NOT EXISTS --< Bug 3301427 Start >
8700: (SELECT 'previously approved shipment'
8701: FROM po_line_locations_gt pllg
8702: WHERE pllg.po_line_id = pol.po_line_id
8703: AND pllg.approved_date IS NOT NULL); --< Bug 3301427 End >
8704:
8705: --Increment the p_sequence with number of errors reported in last query
8811: AND POH2.global_agreement_flag = 'Y'
8812: --
8813: AND NOT EXISTS --< Bug 3301427 Start >
8814: (SELECT 'previously approved shipment'
8815: FROM po_line_locations_gt pllg
8816: WHERE pllg.po_line_id = pol.po_line_id
8817: AND pllg.approved_date IS NOT NULL) --< Bug 3301427 End >
8818: AND NOT EXISTS
8819: (SELECT 'Enabled vendor site'
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
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
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'
9063: -- given lines with shipments that reference GA's with amount limits only.
9064:
9065: -- bug3413891
9066: -- 1) Added optimizer hint FULL(POL)
9067: -- 2) Removed the join to PO_LINE_LOCATIONS_GT as it will bring in wrong
9068: -- result if the standard PO line has multiple shipments
9069:
9070: SELECT /*+ FULL(POL) */
9071: POL.line_num
9077: * PLL2.price_override )
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'
9303: AND POH.type_lookup_code = 'BLANKET'
9304: AND POH.global_agreement_flag = 'Y'
9305: AND POL2.min_release_amount IS NOT NULL
9306: AND POL2.min_release_amount >
9307: --SQL What: Querying PO_LINE_LOCATIONS for the total amount of the
9308: --SQL shipments in this PO that reference the current GA line
9309: --SQL Why: This sum determines whether the minimum release amount
9310: --SQL for the GA line has been met
9311: ( SELECT --
9324: * PLL.price_override )
9325: )
9326: )
9327: --
9328: --Changing the query to go to PO_LINE_LOCATIONS_GT instead of PO_LINE_LOCATIONS
9329: FROM PO_LINE_LOCATIONS_GT PLL
9330: WHERE PLL.po_header_id = p_document_id
9331: AND PLL.from_line_id = POL2.po_line_id);
9332:
9325: )
9326: )
9327: --
9328: --Changing the query to go to PO_LINE_LOCATIONS_GT instead of PO_LINE_LOCATIONS
9329: FROM PO_LINE_LOCATIONS_GT PLL
9330: WHERE PLL.po_header_id = p_document_id
9331: AND PLL.from_line_id = POL2.po_line_id);
9332:
9333: --Increment the p_sequence with number of errors reported in last query
9466: AND POL.contract_id = POHA.po_header_id
9467: AND POHA.global_agreement_flag = 'Y'
9468: AND NOT EXISTS --< Bug 3301427 Start >
9469: (SELECT 'previously approved shipment'
9470: FROM po_line_locations_gt pllg
9471: WHERE pllg.po_line_id = pol.po_line_id
9472: AND pllg.approved_date IS NOT NULL) --< Bug 3301427 End >
9473: AND NOT EXISTS (SELECT 1
9474: FROM po_ga_org_assignments PGOA,
9539: AND POL.contract_id = POHA.po_header_id
9540: AND POHA.global_agreement_flag = 'Y'
9541: AND NOT EXISTS --< Bug 3301427 Start >
9542: (SELECT 'previously approved shipment'
9543: FROM po_line_locations_gt pllg
9544: WHERE pllg.po_line_id = pol.po_line_id
9545: AND pllg.approved_date IS NOT NULL) --< Bug 3301427 End >
9546: AND NOT EXISTS (SELECT 1
9547: FROM po_ga_org_assignments PGOA
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;
10141: DELETE FROM po_req_lines_gt;
10273:
10274: --LCM ER start. Populate the lcm flag in the GT tables first
10275: IF p_doc_type in ('PO','RELEASE') THEN
10276:
10277: FOR ship_rec in (select line_location_id from po_line_locations_gt)
10278: LOOP
10279: set_lcm_flag(ship_rec.line_location_id,'BEFORE',l_return_status);
10280: END LOOP;
10281: END IF;
10387: -- SQL Why: Need to propogate line price change to shipment level
10388: -- for Standard PO
10389: -- SQL Join: po_line_id
10390: FORALL i IN 1..p_requested_changes.line_changes.po_line_id.COUNT
10391: UPDATE po_line_locations_gt
10392: SET price_override = nvl(p_requested_changes.line_changes.unit_price(i),price_override)
10393: WHERE po_line_id = p_requested_changes.line_changes.po_line_id(i)
10394: AND nvl(payment_type, 'NULL') NOT IN ('MILESTONE', 'ADVANCE')
10395: --
10409: -- in values or if NULL then with existing values in the table
10410: -- SQL Why: Need to apply requested line level changes to global temp table
10411: -- SQL Join: po_line_location_id
10412: FORALL i IN 1..p_requested_changes.shipment_changes.po_line_location_id.COUNT
10413: UPDATE po_line_locations_gt
10414: SET quantity = nvl(p_requested_changes.shipment_changes.quantity(i),quantity),
10415: promised_date = nvl(p_requested_changes.shipment_changes.promised_date(i),
10416: promised_date),
10417: price_override = nvl(p_requested_changes.shipment_changes.price_override(i),
10447: l_progress := '004';
10448:
10449: --
10450: FORALL i IN 1..p_requested_changes.shipment_changes.po_line_location_id.COUNT
10451: INSERT INTO po_line_locations_gt(
10452: LINE_LOCATION_ID ,
10453: LAST_UPDATE_DATE ,
10454: LAST_UPDATED_BY ,
10455: PO_HEADER_ID ,
10588: SELECT
10589: -- bug3611217
10590: -- Use a sequence number rather than FND_API.G_MISS_NUM
10591: -- since we have added unique constraint on LINE_LOCATION_ID
10592: PO_LINE_LOCATIONS_S.nextval,
10593: poll.LAST_UPDATE_DATE ,
10594: poll.LAST_UPDATED_BY ,
10595: poll.PO_HEADER_ID ,
10596: poll.PO_LINE_ID ,
10760: )
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
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
12778: --Case 4: Optionally, Line Location is specified
12779: OR EXISTS (SELECT null from po_line_locations_all l
12780: WHERE l.line_location_id = p_line_location_id(i)
12781: AND (l.cancel_flag is null or l.cancel_flag <> 'Y')
12782: AND (l.closed_code is NULL or l.closed_code NOT IN ('FINALLY CLOSED')))) ;
12783: -- bug3592160 END
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
13007: -- Bug 3407980: Modified closed_code condition so that it only discards 'FINALLY CLOSED'
13008: OR EXISTS (SELECT null from po_line_locations_all l
13009: WHERE l.line_location_id = p_line_location_id(i)
13010: AND (l.closed_code is NULL or l.closed_code <> 'FINALLY CLOSED')))
13011: ;
13012:
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
13176: -- Bug 3407980: Modified closed_code condition so that it only discards 'FINALLY CLOSED'
13177: OR EXISTS (SELECT null from po_line_locations_all l
13178: WHERE l.line_location_id = p_line_location_id(i)
13179: AND (l.closed_code is NULL or l.closed_code <> 'FINALLY CLOSED')))
13180: ;
13181:
13350: UPDATE po_headers_gt gt
13351: SET (approved_flag, closed_code, cancel_flag)
13352: =
13353: (SELECT nvl(approved_flag, 'N'), nvl(closed_code, 'OPEN'), NVL(cancel_flag, 'N')
13354: FROM po_line_locations_all s
13355: WHERE s.line_location_id = p_line_location_id(i))
13356: WHERE p_line_location_id(i) is not null and gt.po_header_id = l_sequence(i)
13357: ;
13358:
13731: , NULL , 'PO_SUB_SHIP_BILL_GT_ORD_AMT'
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'
13820: , 'PO_SUB_DIST_BILL_GT_ORD_QTY'
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'
13908: , 'PO_SUB_DIST_DLVR_GT_ORD_QTY'
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'
13982: --Name: populate_line_locations_gt
13983: --Pre-reqs:
13984: -- None.
13985: --Modifies:
13986: -- PO_LINE_LOCATIONS_GT
13987: --Locks:
13988: -- None.
13989: --Function:
13990: -- Populates the line locations GTT for submission checks.
14039:
14040: l_progress := '020';
14041:
14042: FORALL i IN 1 .. l_line_location_id_tbl.COUNT
14043: INSERT INTO PO_LINE_LOCATIONS_GT (
14044: line_location_id ,
14045: last_update_date ,
14046: last_updated_by ,
14047: po_header_id ,
14322: retroactive_date ,
14323: payment_type , --
14324: description , --
14325: value_basis --
14326: FROM PO_LINE_LOCATIONS_ALL POLL
14327: WHERE POLL.line_location_id = l_line_location_id_tbl(i)
14328: ;
14329:
14330: l_progress := '900';
14810: , l_shipment_num_tbl
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
15666: FROM po_headers_gt poh
15667: WHERE EXISTS
15668: (
15669: SELECT 'Eligible shipment'
15670: FROM po_line_locations_gt poll
15671: WHERE poll.po_header_id = poh.po_header_id
15672: AND NVL(poll.cancel_flag, 'N') = 'N'
15673: AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
15674: )
15718: FROM po_releases_gt por
15719: WHERE EXISTS
15720: (
15721: SELECT 'Eligible shipment'
15722: FROM po_line_locations_gt poll
15723: WHERE poll.po_release_id = por.po_release_id
15724: AND NVL(poll.cancel_flag, 'N') = 'N'
15725: AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
15726: )
15773: FROM po_lines_gt pol
15774: WHERE EXISTS
15775: (
15776: SELECT 'Eligible shipment'
15777: FROM po_line_locations_gt poll
15778: WHERE poll.po_line_id = pol.po_line_id
15779: AND NVL(poll.cancel_flag, 'N') = 'N'
15780: AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
15781: )
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
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: (
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'
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'
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') --
16444: FROM po_lines_gt pol
16445: WHERE EXISTS
16446: (
16447: SELECT 'Uncancelled Open Releases Exist'
16448: FROM po_line_locations pll
16449: WHERE pll.po_line_id = pol.po_line_id
16450: AND pll.shipment_type = 'BLANKET'
16451: AND NVL(pll.cancel_flag, 'N') = 'N'
16452: AND NVL(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
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
16737: AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
16738: AND EXISTS
16739: (
16740: SELECT 'Uncancelled Open Releases Exist'
16741: FROM po_line_locations poll2
16742: WHERE poll2.source_shipment_id = poll.line_location_id
16743: AND poll2.shipment_type = 'SCHEDULED'
16744: AND NVL(poll2.cancel_flag, 'N') = 'N'
16745: AND NVL(poll2.closed_code, 'OPEN') <> 'FINALLY CLOSED'
16846: , l_line_amt_tbl --
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')
16859: ( ( POL.quantity IS NOT NULL ) --
16860: AND ( round(POL.quantity, 10) <>
16861: (SELECT round(sum(PLL2.quantity) -
16862: sum(nvl(PLL2.quantity_cancelled, 0)), 10)
16863: FROM PO_LINE_LOCATIONS_GT PLL2
16864: WHERE PLL2.po_line_id = POL.po_line_id AND
16865: PLL2.shipment_type in ('STANDARD', 'PLANNED') ) )
16866: )
16867: OR --
16870: ( SELECT round ( sum ( PLL3.amount
16871: - nvl(PLL3.amount_cancelled, 0) )
16872: , 10
16873: )
16874: FROM po_line_locations_gt PLL3
16875: WHERE PLL3.po_line_id = POL.po_line_id
16876: AND PLL3.shipment_type IN ('STANDARD','PLANNED')
16877: )
16878: )
17028: WHEN PLL.payment_type = 'ADVANCE' THEN
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')
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'
17260: , NVL(pll.quantity_financed, 0)
17261: INTO l_quantity_received
17262: , l_quantity_billed
17263: , l_quantity_financed
17264: FROM po_line_locations_all pll
17265: WHERE line_location_id = p_line_location_id;
17266:
17267: d_progress := 20;
17268:
17550: , null -- distribution_num
17551: , p_sequence + ROWNUM
17552: , substr(l_textline, 1, 240)
17553: , l_message_name
17554: FROM po_line_locations_gt poll
17555: WHERE poll.line_location_id=p_doc_level_id
17556: 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;
17557: --
17558: -- Handle Distribution Unreserve(for both SPO/Release)
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
17660: FROM po_line_locations_gt
17661: WHERE line_location_id=p_doc_level_id;
17662: ELSIF p_document_level = g_document_level_DISTRIBUTION THEN
17663: SELECT po_header_id
17664: INTO l_document_id
17777: l_inventory_item_id,
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
17818: PO_DEBUG.debug_var(l_log_head,l_progress,'l_return_status',l_return_status);
17819:
17820: IF l_return_status = 'Y' THEN
17821: IF p_doc_check_status = 'BEFORE' THEN
17822: UPDATE po_line_locations_gt
17823: SET lcm_flag = 'Y'
17824: WHERE line_location_id = p_line_location_id
17825: and lcm_flag is null;
17826:
17831:
17832:
17833: ELSIF p_doc_check_status = 'AFTER' THEN
17834:
17835: UPDATE po_line_locations_all
17836: SET lcm_flag = 'Y'
17837: WHERE line_location_id = p_line_location_id;
17838:
17839: UPDATE po_distributions_all
17842: END IF;
17843:
17844: ELSIF l_return_status = 'N' THEN
17845:
17846: UPDATE po_line_locations_all
17847: SET lcm_flag = null
17848: WHERE line_location_id = p_line_location_id
17849: AND lcm_flag = 'Y';
17850: