[Home] [Help]
762: -- p_activity_status : Workflow activity status
763: -- p_additional_where_clause : Its derived from the p_item_type and p_activity_name
764: ---------------------------------------------------------------------------------------------------
765: Procedure InsertTable_OOH_Header (p_hold_source_id OE_HOLD_SOURCES_ALL.hold_source_id%type
766: ,p_header_id OE_ORDER_HEADERS_ALL.header_id%type
767: ,p_org_id OE_ORDER_HEADERS_ALL.org_id%type
768: ,p_hold_entity_where_clause VARCHAR2
769: ,p_item_type VARCHAR2
770: ,p_activity_name VARCHAR2
763: -- p_additional_where_clause : Its derived from the p_item_type and p_activity_name
764: ---------------------------------------------------------------------------------------------------
765: Procedure InsertTable_OOH_Header (p_hold_source_id OE_HOLD_SOURCES_ALL.hold_source_id%type
766: ,p_header_id OE_ORDER_HEADERS_ALL.header_id%type
767: ,p_org_id OE_ORDER_HEADERS_ALL.org_id%type
768: ,p_hold_entity_where_clause VARCHAR2
769: ,p_item_type VARCHAR2
770: ,p_activity_name VARCHAR2
771: ,p_activity_status VARCHAR2
808: , h.HEADER_ID
809: , NULL
810: , ''N''
811: , h.org_id
812: FROM OE_ORDER_HEADERS_ALL h
813: WHERE h.OPEN_FLAG = ''Y''
814: and h.org_id = :l_org_id
815: and h.header_id = :header_id
816: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
895: , h.HEADER_ID
896: , NULL
897: , ''N''
898: , h.org_id
899: FROM OE_ORDER_HEADERS_ALL h
900: WHERE h.OPEN_FLAG = ''Y''
901: and h.org_id = :l_org_id
902: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
903: and not exists ( select ''x''
963:
964: ELSIF l_sql_rowcount > 0 THEN
965:
966: l_wf_sqlmt := 'SELECT count(*)
967: FROM OE_ORDER_HEADERS_ALL h
968: WHERE h.OPEN_FLAG = ''Y''
969: AND nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
970: AND NOT EXISTS ( select ''x''
971: from oe_order_holds_ALL oh
1028: -- p_additional_where_clause : Its derived from the p_item_type and p_activity_name
1029: ---------------------------------------------------------------------------------------------------
1030: Procedure InsertTable_OOH_Line (p_hold_source_id OE_HOLD_SOURCES_ALL.hold_source_id%type,
1031: p_line_id OE_ORDER_LINES_ALL.line_id%type,
1032: p_org_id OE_ORDER_HEADERS_ALL.org_id%type,
1033: p_hold_entity_where_clause VARCHAR2,
1034: p_item_type VARCHAR2,
1035: p_activity_name VARCHAR2,
1036: p_activity_status VARCHAR2,
1073: , h.HEADER_ID
1074: , ol.line_id
1075: , ''N''
1076: , h.org_id
1077: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
1078: WHERE h.OPEN_FLAG = ''Y''
1079: and h.header_id = ol.header_id
1080: and h.org_id = :l_org_id
1081: and ol.line_id = :line_id
1176: , h.HEADER_ID
1177: , ol.line_id
1178: , ''N''
1179: , h.org_id
1180: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
1181: WHERE h.OPEN_FLAG = ''Y''
1182: and h.header_id = ol.header_id
1183: and h.org_id = :l_org_id
1184: and ol.OPEN_FLAG = ''Y''
1256: RAISE FND_API.G_EXC_ERROR;
1257:
1258: ELSIF l_sql_rowcount > 0 THEN
1259: l_wf_sqlmt := 'SELECT count(*)
1260: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
1261: WHERE h.OPEN_FLAG = ''Y''
1262: AND h.header_id = ol.header_id
1263: AND ol.OPEN_FLAG = ''Y''
1264: AND NVL(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
1314: -- p_hold_source_id : Hold Source Id of the hold
1315: -- p_header_id : Line Id if the hold is applied using action -> apply hold for a specific Hold
1316: ---------------------------------------------------------------------------------------------------
1317: Procedure PaymentType_Hold (p_hold_source_rec OE_HOLDS_PVT.Hold_source_Rec_Type
1318: ,p_org_id OE_ORDER_HEADERS_ALL.org_id%type
1319: ,p_item_type VARCHAR2
1320: ,p_activity_name VARCHAR2
1321: ,p_activity_status VARCHAR2
1322: ,p_additional_where_clause VARCHAR2
1428: IF p_hold_source_rec.header_id IS NOT NULL and p_hold_source_rec.line_id IS NULL THEN
1429: l_sqlmt := l_sqlmt||' AND OP.header_id = '||p_hold_source_rec.header_id;
1430:
1431: l_sqlmt1 := ' UNION Select header_id
1432: FROM OE_ORDER_HEADERS_ALL OH
1433: WHERE PAYMENT_TYPE_CODE = :hold_entity_id
1434: AND header_id = :header_id';
1435:
1436: l_sqlmt := l_sqlmt||l_sqlmt1;
1441:
1442: ELSIF (p_hold_source_rec.header_id IS NULL AND p_hold_source_rec.line_id IS NULL) THEN
1443:
1444: l_sqlmt1 := ' UNION Select header_id
1445: FROM OE_ORDER_HEADERS_ALL OH
1446: WHERE PAYMENT_TYPE_CODE = :hold_entity_id';
1447:
1448: l_sqlmt := l_sqlmt||l_sqlmt1;
1449:
1455:
1456: ELSIF p_hold_source_rec.hold_entity_code = 'C' and p_hold_source_rec.hold_entity_code2 ='P' THEN
1457:
1458: l_sqlmt := 'Select OP.header_id
1459: FROM OE_PAYMENTS OP,OE_ORDER_HEADERS_ALL OH
1460: WHERE OP.line_id IS NULL
1461: AND OP.header_id= OH.header_id
1462: AND OH.sold_to_org_id= :hold_entity_id
1463: AND OP.PAYMENT_TYPE_CODE = :hold_entity_id2';
1465: IF p_hold_source_rec.header_id IS NOT NULL and p_hold_source_rec.line_id IS NULL THEN
1466: l_sqlmt := l_sqlmt||' AND OP.header_id = '||p_hold_source_rec.header_id;
1467:
1468: l_sqlmt1 := ' UNION Select header_id
1469: FROM OE_ORDER_HEADERS_ALL OH
1470: WHERE sold_to_org_id= :hold_entity_id
1471: AND PAYMENT_TYPE_CODE = :hold_entity_id2
1472: AND header_id = :header_id';
1473:
1480:
1481: ELSIF (p_hold_source_rec.header_id IS NULL AND p_hold_source_rec.line_id IS NULL) THEN
1482:
1483: l_sqlmt1 := ' UNION Select header_id
1484: FROM OE_ORDER_HEADERS_ALL OH
1485: WHERE sold_to_org_id= :hold_entity_id
1486: AND PAYMENT_TYPE_CODE = :hold_entity_id2';
1487:
1488: l_sqlmt := l_sqlmt||l_sqlmt1;
1562: WHERE EXISTS (SELECT NULL
1563: FROM oe_order_holds ooh,
1564: oe_hold_sources ohs,
1565: oe_hold_definitions ohd,
1566: oe_order_headers_all h,
1567: oe_order_sources oos
1568: WHERE ooh.header_id = h.header_id
1569: -- AND ohd.activity_name IS NULL Bug 6791587
1570: AND oos.aia_enabled_flag = 'Y'
2843: WHERE EXISTS (SELECT NULL
2844: FROM oe_order_holds ooh,
2845: oe_hold_sources ohs,
2846: oe_hold_definitions ohd,
2847: oe_order_headers_all h,
2848: oe_order_sources oos
2849: WHERE ohd.activity_name IS NULL
2850: AND ohd.hold_id = ohs.hold_id
2851: AND ooh.header_id = h.header_id
2860: WHERE EXISTS (SELECT NULL
2861: FROM oe_order_holds ooh,
2862: oe_hold_sources ohs,
2863: oe_hold_definitions ohd,
2864: oe_order_headers_all h,
2865: oe_order_sources oos
2866: WHERE ohd.activity_name IS NULL
2867: AND ohd.hold_id = ohs.hold_id
2868: AND h.order_source_id = oos.order_source_id
2958: , h.HEADER_ID
2959: , ol.line_id
2960: , 'N'
2961: , l_org_id
2962: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
2963: WHERE h.OPEN_FLAG = 'Y'
2964: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
2965: and h.SOLD_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
2966: and h.header_id = ol.header_id
3000: , h.HEADER_ID
3001: , ol.line_id
3002: , 'N'
3003: , l_org_id
3004: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
3005: WHERE h.OPEN_FLAG = 'Y'
3006: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
3007: and h.SOLD_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
3008: and h.header_id = ol.header_id
3045: , h.HEADER_ID
3046: , ol.line_id
3047: , 'N'
3048: , l_org_id
3049: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol,
3050: ra_terms_b rt
3051: WHERE h.OPEN_FLAG = 'Y'
3052: and h.header_id = p_hold_source_rec.hold_entity_id2
3053: and h.header_id = ol.header_id
3089: , h.HEADER_ID
3090: , ol.line_id
3091: , 'N'
3092: , l_org_id
3093: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol,
3094: ra_terms_b rt
3095: WHERE h.OPEN_FLAG = 'Y'
3096: and h.header_id = p_hold_source_rec.hold_entity_id2
3097: and h.header_id = ol.header_id
3136: , h.HEADER_ID
3137: , ol.line_id
3138: , 'N'
3139: , l_org_id
3140: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
3141: WHERE h.OPEN_FLAG = 'Y'
3142: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
3143: and h.header_id = ol.header_id
3144: and ol.INVOICE_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
3178: , h.HEADER_ID
3179: , ol.line_id
3180: , 'N'
3181: , l_org_id
3182: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
3183: WHERE h.OPEN_FLAG = 'Y'
3184: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
3185: and h.header_id = ol.header_id
3186: and ol.INVOICE_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
3222: , h.HEADER_ID
3223: , ol.line_id
3224: , 'N'
3225: , l_org_id
3226: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
3227: WHERE h.OPEN_FLAG = 'Y'
3228: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
3229: and h.header_id = ol.header_id
3230: and ol.SHIP_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
3264: , h.HEADER_ID
3265: , ol.line_id
3266: , 'N'
3267: , l_org_id
3268: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
3269: WHERE h.OPEN_FLAG = 'Y'
3270: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
3271: and h.header_id = ol.header_id
3272: and ol.SHIP_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
3308: , h.HEADER_ID
3309: , ol.line_id
3310: , 'N'
3311: , l_org_id
3312: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
3313: WHERE h.OPEN_FLAG = 'Y'
3314: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
3315: and h.header_id = ol.header_id
3316: and ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id2
3350: , h.HEADER_ID
3351: , ol.line_id
3352: , 'N'
3353: , l_org_id
3354: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
3355: WHERE h.OPEN_FLAG = 'Y'
3356: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
3357: and h.header_id = ol.header_id
3358: and ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id2
3394: , h.HEADER_ID
3395: , ol.line_id
3396: , 'N'
3397: , l_org_id
3398: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
3399: WHERE h.OPEN_FLAG = 'Y'
3400: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
3401: and h.header_id = ol.header_id
3402: and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id2
3436: , h.HEADER_ID
3437: , ol.line_id
3438: , 'N'
3439: , l_org_id
3440: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
3441: WHERE h.OPEN_FLAG = 'Y'
3442: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
3443: and h.header_id = ol.header_id
3444: and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id2
3481: , h.HEADER_ID
3482: , ol.line_id
3483: , 'N'
3484: , l_org_id
3485: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
3486: WHERE h.OPEN_FLAG = 'Y'
3487: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
3488: and h.header_id = ol.header_id
3489: and ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id
3523: , h.HEADER_ID
3524: , ol.line_id
3525: , 'N'
3526: , l_org_id
3527: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
3528: WHERE h.OPEN_FLAG = 'Y'
3529: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
3530: and h.header_id = ol.header_id
3531: and ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id
3567: , h.HEADER_ID
3568: , ol.line_id
3569: , 'N'
3570: , l_org_id
3571: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
3572: WHERE h.OPEN_FLAG = 'Y'
3573: --AND nvl(h.CANCELLED_FLAG, 'N') = 'N'
3574: AND h.header_id = ol.header_id
3575: AND ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id
3609: , h.HEADER_ID
3610: , ol.line_id
3611: , 'N'
3612: , l_org_id
3613: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
3614: WHERE h.OPEN_FLAG = 'Y'
3615: --AND nvl(h.CANCELLED_FLAG, 'N') = 'N'
3616: AND h.header_id = ol.header_id
3617: AND ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id
3653: , h.HEADER_ID
3654: , ol.line_id
3655: , 'N'
3656: , l_org_id
3657: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
3658: WHERE h.OPEN_FLAG = 'Y'
3659: --AND nvl(h.CANCELLED_FLAG, 'N') = 'N'
3660: AND ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id
3661: and ol.line_id = p_hold_source_rec.line_id
3695: , h.HEADER_ID
3696: , ol.line_id
3697: , 'N'
3698: , l_org_id
3699: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
3700: WHERE h.OPEN_FLAG = 'Y'
3701: --AND nvl(h.CANCELLED_FLAG, 'N') = 'N'
3702: AND ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id
3703: AND h.header_id = ol.header_id
3739: , h.HEADER_ID
3740: , ol.line_id
3741: , 'N'
3742: , l_org_id
3743: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
3744: WHERE h.OPEN_FLAG = 'Y'
3745: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
3746: and h.header_id = ol.header_id
3747: and ol.INVOICE_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
3781: , h.HEADER_ID
3782: , ol.line_id
3783: , 'N'
3784: , l_org_id
3785: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
3786: WHERE h.OPEN_FLAG = 'Y'
3787: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
3788: and h.header_id = ol.header_id
3789: and ol.INVOICE_TO_ORG_ID= p_hold_source_rec.hold_entity_id2
3826: , h.HEADER_ID
3827: , ol.line_id
3828: , 'N'
3829: , l_org_id
3830: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
3831: WHERE h.OPEN_FLAG = 'Y'
3832: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
3833: and h.header_id = ol.header_id
3834: and ol.SHIP_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
3868: , h.HEADER_ID
3869: , ol.line_id
3870: , 'N'
3871: , l_org_id
3872: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
3873: WHERE h.OPEN_FLAG = 'Y'
3874: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
3875: and h.header_id = ol.header_id
3876: and ol.SHIP_TO_ORG_ID= p_hold_source_rec.hold_entity_id2
3912: , h.HEADER_ID
3913: , ol.line_id
3914: , 'N'
3915: , l_org_id
3916: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
3917: WHERE h.OPEN_FLAG = 'Y'
3918: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
3919: and h.header_id = ol.header_id
3920: and ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id2
3954: , h.HEADER_ID
3955: , ol.line_id
3956: , 'N'
3957: , l_org_id
3958: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
3959: WHERE h.OPEN_FLAG = 'Y'
3960: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
3961: and h.header_id = ol.header_id
3962: and ol.SHIP_FROM_ORG_ID= p_hold_source_rec.hold_entity_id2
3999: , h.HEADER_ID
4000: , ol.line_id
4001: , 'N'
4002: , l_org_id
4003: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
4004: WHERE h.OPEN_FLAG = 'Y'
4005: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
4006: and h.header_id = ol.header_id
4007: and ol.BLANKET_LINE_NUMBER = p_hold_source_rec.hold_entity_id2
4041: , h.HEADER_ID
4042: , ol.line_id
4043: , 'N'
4044: , l_org_id
4045: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
4046: WHERE h.OPEN_FLAG = 'Y'
4047: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
4048: and h.header_id = ol.header_id
4049: and ol.BLANKET_LINE_NUMBER = p_hold_source_rec.hold_entity_id2
4085: , h.HEADER_ID
4086: , ol.line_id
4087: , 'N'
4088: , l_org_id
4089: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
4090: WHERE h.OPEN_FLAG = 'Y'
4091: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
4092: and h.header_id = ol.header_id
4093: and ol.line_id = p_hold_source_rec.line_id
4126: , h.HEADER_ID
4127: , ol.line_id
4128: , 'N'
4129: , l_org_id
4130: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
4131: WHERE h.OPEN_FLAG = 'Y'
4132: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
4133: and h.header_id = ol.header_id
4134: and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
4169: , h.HEADER_ID
4170: , NULL
4171: , 'N'
4172: , l_org_id
4173: FROM OE_ORDER_HEADERS_ALL h
4174: WHERE h.OPEN_FLAG = 'Y'
4175: and h.header_id = p_hold_source_rec.hold_entity_id
4176: -- QUOTING change
4177: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4205: , h.HEADER_ID
4206: , p_hold_source_rec.line_id
4207: , 'N'
4208: , l_org_id
4209: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
4210: WHERE h.OPEN_FLAG = 'Y'
4211: and h.header_id = p_hold_source_rec.hold_entity_id
4212: and h.header_id = ol.header_id
4213: and ol.line_id = p_hold_source_rec.line_id
4249: , h.HEADER_ID
4250: , NULL
4251: , 'N'
4252: , l_org_id
4253: FROM OE_ORDER_HEADERS_ALL h
4254: WHERE h.OPEN_FLAG = 'Y'
4255: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
4256: and h.header_id = p_hold_source_rec.header_id
4257: and h.SOLD_TO_ORG_ID = p_hold_source_rec.hold_entity_id
4287: , h.HEADER_ID
4288: , NULL
4289: , 'N'
4290: , l_org_id
4291: FROM OE_ORDER_HEADERS_ALL h
4292: WHERE h.OPEN_FLAG = 'Y'
4293: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
4294: and h.SOLD_TO_ORG_ID = p_hold_source_rec.hold_entity_id
4295: -- QUOTING change
4326: , h.HEADER_ID
4327: , ol.line_id
4328: , 'N'
4329: , l_org_id
4330: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
4331: WHERE h.OPEN_FLAG = 'Y'
4332: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
4333: and h.header_id = ol.header_id
4334: and ol.line_id = p_hold_source_rec.line_id
4367: , h.HEADER_ID
4368: , ol.line_id
4369: , 'N'
4370: , l_org_id
4371: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
4372: WHERE h.OPEN_FLAG = 'Y'
4373: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
4374: and h.header_id = ol.header_id
4375: and ol.INVOICE_TO_ORG_ID = p_hold_source_rec.hold_entity_id
4409: , h.HEADER_ID
4410: , ol.line_id
4411: , 'N'
4412: , l_org_id
4413: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
4414: WHERE h.OPEN_FLAG = 'Y'
4415: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
4416: and h.header_id = ol.header_id
4417: and ol.line_id = p_hold_source_rec.line_id
4450: , h.HEADER_ID
4451: , ol.line_id
4452: , 'N'
4453: , l_org_id
4454: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
4455: WHERE h.OPEN_FLAG = 'Y'
4456: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
4457: and h.header_id = ol.header_id
4458: and ol.SHIP_TO_ORG_ID = p_hold_source_rec.hold_entity_id
4492: , h.HEADER_ID
4493: , ol.line_id
4494: , 'N'
4495: , l_org_id
4496: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
4497: WHERE h.OPEN_FLAG = 'Y'
4498: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
4499: and h.header_id = ol.header_id
4500: and ol.line_id = p_hold_source_rec.line_id
4533: , h.HEADER_ID
4534: , ol.line_id
4535: , 'N'
4536: , l_org_id
4537: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
4538: WHERE h.OPEN_FLAG = 'Y'
4539: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
4540: and h.header_id = ol.header_id
4541: and ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id
4575: , h.HEADER_ID
4576: , ol.line_id
4577: , 'N'
4578: , l_org_id
4579: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
4580: WHERE h.OPEN_FLAG = 'Y'
4581: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
4582: and h.header_id = ol.header_id
4583: and ol.line_id = p_hold_source_rec.line_id
4616: , h.HEADER_ID
4617: , ol.line_id
4618: , 'N'
4619: , l_org_id
4620: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
4621: WHERE h.OPEN_FLAG = 'Y'
4622: --and nvl(h.CANCELLED_FLAG, 'N') = 'N'
4623: and h.header_id = ol.header_id
4624: and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
8065: WHERE EXISTS (SELECT NULL
8066: FROM oe_order_holds ooh,
8067: oe_hold_sources ohs,
8068: oe_hold_definitions ohd,
8069: oe_order_headers_all h,
8070: oe_order_sources oos
8071: WHERE ohd.hold_id = ohs.hold_id
8072: -- AND ohd.activity_name IS NULL Bug 6791587
8073: AND ooh.header_id = h.header_id
8082: WHERE EXISTS (SELECT NULL
8083: FROM oe_order_holds ooh,
8084: oe_hold_sources ohs,
8085: oe_hold_definitions ohd,
8086: oe_order_headers_all h,
8087: oe_order_sources oos
8088: WHERE ohd.hold_id = ohs.hold_id
8089: -- AND ohd.activity_name IS NULL Bug 6791587
8090: AND h.order_source_id = oos.order_source_id
8111: WHERE EXISTS (SELECT NULL
8112: FROM oe_order_holds_all ooh,
8113: oe_hold_sources_all ohs,
8114: oe_hold_definitions ohd,
8115: oe_order_headers_all h,
8116: oe_order_sources oos
8117: WHERE ohd.hold_id = ohs.hold_id
8118: AND ooh.header_id = h.header_id
8119: AND ooh.org_id = p_org_id
8130: WHERE EXISTS (SELECT NULL
8131: FROM oe_order_holds_all ooh,
8132: oe_hold_sources_all ohs,
8133: oe_hold_definitions ohd,
8134: oe_order_headers_all h,
8135: oe_order_sources oos
8136: WHERE ohd.hold_id = ohs.hold_id
8137: AND ooh.org_id = p_org_id
8138: AND ooh.org_id = ohs.org_id
8247: , h.HEADER_ID
8248: , ol.line_id
8249: , ''N''
8250: , h.org_id --ER#7479609 :l_org_id
8251: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
8252: WHERE h.OPEN_FLAG = ''Y''
8253: --and nvl(h.CANCELLED_FLAG, ''N'') = ''N''
8254: and h.SOLD_TO_ORG_ID = :hold_entity_id2
8255: and h.header_id = ol.header_id
8324: , h.HEADER_ID
8325: , ol.line_id
8326: , ''N''
8327: , h.org_id --ER#7479609 :l_org_id
8328: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
8329: WHERE h.OPEN_FLAG = ''Y''
8330: --and nvl(h.CANCELLED_FLAG, ''N'') = ''N''
8331: and h.SOLD_TO_ORG_ID = :hold_entity_id2
8332: and h.header_id = ol.header_id
8384: RAISE FND_API.G_EXC_ERROR;
8385: ELSIF l_sql_rowcount > 0 THEN
8386: SELECT count(*)
8387: into l_parent_count
8388: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
8389: WHERE h.OPEN_FLAG = 'Y'
8390: and h.SOLD_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
8391: and h.header_id = ol.header_id
8392: and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
8573: , h.HEADER_ID
8574: , ol.line_id
8575: , ''N''
8576: , h.org_id --ER#7479609 :l_org_id
8577: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol,
8578: ra_terms_b rt
8579: WHERE h.OPEN_FLAG = ''Y''
8580: and h.header_id = :hold_entity_id2
8581: and h.header_id = ol.header_id
8645: , h.HEADER_ID
8646: , ol.line_id
8647: , ''N''
8648: , h.org_id --ER#7479609 :l_org_id
8649: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol,
8650: ra_terms_b rt
8651: WHERE h.OPEN_FLAG = ''Y''
8652: and h.header_id = :hold_entity_id2
8653: and h.header_id = ol.header_id
8706: RAISE FND_API.G_EXC_ERROR;
8707: ELSIF l_sql_rowcount > 0 THEN
8708: SELECT count(*)
8709: into l_parent_count
8710: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol , ra_terms_b rt
8711: WHERE h.OPEN_FLAG = 'Y'
8712: and h.header_id = p_hold_source_rec.hold_entity_id2
8713: and h.header_id = ol.header_id
8714: and ol.INVOICE_TO_ORG_ID = p_hold_source_rec.hold_entity_id
8790: , h.HEADER_ID
8791: , ol.line_id
8792: , ''N''
8793: , h.org_id --ER#7479609 :l_org_id
8794: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
8795: WHERE h.OPEN_FLAG = ''Y''
8796:
8797: and h.header_id = ol.header_id
8798: and h.org_id = :l_org_id --ER#7479609
8864: , h.HEADER_ID
8865: , ol.line_id
8866: , ''N''
8867: , h.org_id --ER#7479609 :l_org_id
8868: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
8869: WHERE h.OPEN_FLAG = ''Y''
8870:
8871: and h.header_id = ol.header_id
8872: and h.org_id = :l_org_id --ER#7479609
8923: RAISE FND_API.G_EXC_ERROR;
8924: ELSIF l_sql_rowcount > 0 THEN
8925: SELECT count(*)
8926: into l_parent_count
8927: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
8928: WHERE h.OPEN_FLAG = 'Y'
8929: and h.header_id = ol.header_id
8930: and ol.INVOICE_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
8931: and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
9005: , h.HEADER_ID
9006: , ol.line_id
9007: , ''N''
9008: , h.org_id --ER#7479609 :l_org_id
9009: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
9010: WHERE h.OPEN_FLAG = ''Y''
9011:
9012: and h.header_id = ol.header_id
9013: and h.org_id = :l_org_id --ER#7479609
9078: , h.HEADER_ID
9079: , ol.line_id
9080: , ''N''
9081: , h.org_id --ER#7479609 :l_org_id
9082: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
9083: WHERE h.OPEN_FLAG = ''Y''
9084:
9085: and h.header_id = ol.header_id
9086: and h.org_id = :l_org_id --ER#7479609
9136: RAISE FND_API.G_EXC_ERROR;
9137: ELSIF l_sql_rowcount > 0 THEN
9138: SELECT count(*)
9139: into l_parent_count
9140: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
9141: WHERE h.OPEN_FLAG = 'Y'
9142: and h.header_id = ol.header_id
9143: and ol.SHIP_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
9144: and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
9218: , h.HEADER_ID
9219: , ol.line_id
9220: , ''N''
9221: , h.org_id --ER#7479609 :l_org_id
9222: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
9223: WHERE h.OPEN_FLAG = ''Y''
9224:
9225: and h.header_id = ol.header_id
9226: and h.org_id = :l_org_id --ER#7479609
9292: , h.HEADER_ID
9293: , ol.line_id
9294: , ''N''
9295: , h.org_id --ER#7479609 :l_org_id
9296: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
9297: WHERE h.OPEN_FLAG = ''Y''
9298:
9299: and h.header_id = ol.header_id
9300: and h.org_id = :l_org_id --ER#7479609
9350: RAISE FND_API.G_EXC_ERROR;
9351: ELSIF l_sql_rowcount > 0 THEN
9352: SELECT count(*)
9353: into l_parent_count
9354: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
9355: WHERE h.OPEN_FLAG = 'Y'
9356: and h.header_id = ol.header_id
9357: and ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id2
9358: and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
9433: , h.HEADER_ID
9434: , ol.line_id
9435: , ''N''
9436: , h.org_id --ER#7479609 :l_org_id
9437: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
9438: WHERE h.OPEN_FLAG = ''Y''
9439:
9440: and h.header_id = ol.header_id
9441: and h.org_id = :l_org_id --ER#7479609
9506: , h.HEADER_ID
9507: , ol.line_id
9508: , ''N''
9509: , h.org_id --ER#7479609 :l_org_id
9510: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
9511: WHERE h.OPEN_FLAG = ''Y''
9512:
9513: and h.header_id = ol.header_id
9514: and h.org_id = :l_org_id --ER#7479609
9564: RAISE FND_API.G_EXC_ERROR;
9565: ELSIF l_sql_rowcount > 0 THEN
9566: SELECT count(*)
9567: into l_parent_count
9568: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
9569: WHERE h.OPEN_FLAG = 'Y'
9570: and h.header_id = ol.header_id
9571: and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id2
9572: and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
9650: , h.HEADER_ID
9651: , ol.line_id
9652: , ''N''
9653: , h.org_id --ER#7479609 :l_org_id
9654: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
9655: WHERE h.OPEN_FLAG = ''Y''
9656:
9657: and h.header_id = ol.header_id
9658: and h.org_id = :l_org_id
9723: , h.HEADER_ID
9724: , ol.line_id
9725: , ''N''
9726: , p_org_id --ER#7479609 :l_org_id
9727: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
9728: WHERE h.OPEN_FLAG = ''Y''
9729:
9730: and h.header_id = ol.header_id
9731: and h.org_id = :l_org_id --ER#7479609
9782: RAISE FND_API.G_EXC_ERROR;
9783: ELSIF l_sql_rowcount > 0 THEN
9784: SELECT count(*)
9785: into l_parent_count
9786: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
9787: WHERE h.OPEN_FLAG = 'Y'
9788: and h.header_id = ol.header_id
9789: and ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id
9790: and h.SOLD_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
9954: , h.HEADER_ID
9955: , ol.line_id
9956: , ''N''
9957: , h.org_id --ER#7479609 :l_org_id
9958: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
9959: WHERE h.OPEN_FLAG = ''Y''
9960:
9961: AND h.header_id = ol.header_id
9962: and h.org_id = :l_org_id --ER#7479609
10028: , h.HEADER_ID
10029: , ol.line_id
10030: , ''N''
10031: , p_org_id --ER#7479609 :l_org_id
10032: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
10033: WHERE h.OPEN_FLAG = ''Y''
10034:
10035: AND h.header_id = ol.header_id
10036: and h.org_id = :l_org_id --ER#7479609
10087: RAISE FND_API.G_EXC_ERROR;
10088: ELSIF l_sql_rowcount > 0 THEN
10089: SELECT count(*)
10090: into l_parent_count
10091: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
10092: WHERE h.OPEN_FLAG = 'Y'
10093: AND h.header_id = ol.header_id
10094: AND ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id
10095: AND ol.INVOICE_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
10170: , h.HEADER_ID
10171: , ol.line_id
10172: , ''N''
10173: , h.org_id --ER#7479609 :l_org_id
10174: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
10175: WHERE h.OPEN_FLAG = ''Y''
10176: AND h.org_id = :l_org_id --ER#7479609
10177: AND ol.SHIP_FROM_ORG_ID = :hold_entity_id
10178: and ol.line_id = :line_id
10243: , h.HEADER_ID
10244: , ol.line_id
10245: , ''N''
10246: , h.org_id --ER#7479609 :l_org_id
10247: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
10248: WHERE h.OPEN_FLAG = ''Y''
10249: AND h.org_id = :l_org_id --ER#7479609
10250: AND ol.SHIP_FROM_ORG_ID = :hold_entity_id
10251: AND h.header_id = ol.header_id
10301: RAISE FND_API.G_EXC_ERROR;
10302: ELSIF l_sql_rowcount > 0 THEN
10303: SELECT count(*)
10304: into l_parent_count
10305: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
10306: WHERE h.OPEN_FLAG = 'Y'
10307: AND ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id
10308: AND h.header_id = ol.header_id
10309: AND ol.SHIP_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
10387: , h.HEADER_ID
10388: , ol.line_id
10389: , ''N''
10390: , h.org_id --ER#7479609 :l_org_id
10391: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
10392: WHERE h.OPEN_FLAG = ''Y''
10393: and h.org_id = :l_org_id --ER#7479609
10394: and h.header_id = ol.header_id
10395: and ol.INVOICE_TO_ORG_ID = :hold_entity_id2
10460: , h.HEADER_ID
10461: , ol.line_id
10462: , ''N''
10463: , h.org_id --ER#7479609 :l_org_id
10464: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
10465: WHERE h.OPEN_FLAG = ''Y''
10466: and h.org_id = :l_org_id --ER#7479609
10467: and h.header_id = ol.header_id
10468: and ol.INVOICE_TO_ORG_ID= :hold_entity_id2
10518: RAISE FND_API.G_EXC_ERROR;
10519: ELSIF l_sql_rowcount > 0 THEN
10520: SELECT count(*)
10521: into l_parent_count
10522: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
10523: WHERE h.OPEN_FLAG = 'Y'
10524: and h.header_id = ol.header_id
10525: and ol.INVOICE_TO_ORG_ID= p_hold_source_rec.hold_entity_id2
10526: and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
10711: , h.HEADER_ID
10712: , ol.line_id
10713: , ''N''
10714: , h.org_id --ER#7479609 :l_org_id
10715: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
10716: WHERE h.OPEN_FLAG = ''Y''
10717: and h.org_id = :l_org_id --ER#7479609
10718: and h.header_id = ol.header_id
10719: and ol.SHIP_TO_ORG_ID = :hold_entity_id2
10784: , h.HEADER_ID
10785: , ol.line_id
10786: , ''N''
10787: , h.org_id --ER#7479609 :l_org_id
10788: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
10789: WHERE h.OPEN_FLAG = ''Y''
10790: and h.org_id = :l_org_id --ER#7479609
10791: and h.header_id = ol.header_id
10792: and ol.SHIP_TO_ORG_ID= :hold_entity_id2
10843: RAISE FND_API.G_EXC_ERROR;
10844: ELSIF l_sql_rowcount > 0 THEN
10845: SELECT count(*)
10846: into l_parent_count
10847: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
10848: WHERE h.OPEN_FLAG = 'Y'
10849: and h.header_id = ol.header_id
10850: and ol.SHIP_TO_ORG_ID= p_hold_source_rec.hold_entity_id2
10851: and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
10926: , h.HEADER_ID
10927: , ol.line_id
10928: , ''N''
10929: , h.org_id --ER#7479609 :l_org_id
10930: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
10931: WHERE h.OPEN_FLAG = ''Y''
10932: and h.org_id = :l_org_id --ER#7479609
10933: and h.header_id = ol.header_id
10934: and ol.SHIP_FROM_ORG_ID = :hold_entity_id2
10999: , h.HEADER_ID
11000: , ol.line_id
11001: , ''N''
11002: , h.org_id --ER#7479609 :l_org_id
11003: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
11004: WHERE h.OPEN_FLAG = ''Y''
11005: and h.org_id = :l_org_id --ER#7479609
11006: and h.header_id = ol.header_id
11007: and ol.SHIP_FROM_ORG_ID= :hold_entity_id2
11057: RAISE FND_API.G_EXC_ERROR;
11058: ELSIF l_sql_rowcount > 0 THEN
11059: SELECT count(*)
11060: into l_parent_count
11061: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
11062: WHERE h.OPEN_FLAG = 'Y'
11063: and h.header_id = ol.header_id
11064: and ol.SHIP_FROM_ORG_ID= p_hold_source_rec.hold_entity_id2
11065: and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
11140: , h.HEADER_ID
11141: , ol.line_id
11142: , ''N''
11143: , h.org_id --ER#7479609 :l_org_id
11144: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
11145: WHERE h.OPEN_FLAG = ''Y''
11146: and h.org_id = :l_org_id --ER#7479609
11147: and h.header_id = ol.header_id
11148: and ol.BLANKET_LINE_NUMBER = :hold_entity_id2
11213: , h.HEADER_ID
11214: , ol.line_id
11215: , ''N''
11216: , h.org_id --ER#7479609 :l_org_id
11217: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
11218: WHERE h.OPEN_FLAG = ''Y''
11219: and h.org_id = :l_org_id --ER#7479609
11220: and h.header_id = ol.header_id
11221: and ol.BLANKET_LINE_NUMBER = :hold_entity_id2
11271: RAISE FND_API.G_EXC_ERROR;
11272: ELSIF l_sql_rowcount > 0 THEN
11273: SELECT count(*)
11274: into l_parent_count
11275: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
11276: WHERE h.OPEN_FLAG = 'Y'
11277: and h.header_id = ol.header_id
11278: and ol.BLANKET_LINE_NUMBER = p_hold_source_rec.hold_entity_id2
11279: and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
11353: , h.HEADER_ID
11354: , ol.line_id
11355: , ''N''
11356: , h.org_id --ER#7479609 :l_org_id
11357: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
11358: WHERE h.OPEN_FLAG = ''Y''
11359: and h.org_id = :l_org_id --ER#7479609
11360: and h.header_id = ol.header_id
11361: and ol.line_id = :line_id
11425: , h.HEADER_ID
11426: , ol.line_id
11427: , ''N''
11428: , h.org_id --ER#7479609 :l_org_id
11429: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
11430: WHERE h.OPEN_FLAG = ''Y''
11431: and h.org_id = :l_org_id --ER#7479609
11432: and h.header_id = ol.header_id
11433: and ol.BLANKET_NUMBER = :hold_entity_id
11482: RAISE FND_API.G_EXC_ERROR;
11483: ELSIF l_sql_rowcount > 0 THEN
11484: SELECT count(*)
11485: into l_parent_count
11486: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
11487: WHERE h.OPEN_FLAG = 'Y'
11488: and h.header_id = ol.header_id
11489: and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
11490: and ol.OPEN_FLAG = 'Y'
11546: , h.HEADER_ID
11547: , NULL
11548: , ''N''
11549: , h.org_id --ER#7479609 :l_org_id
11550: FROM OE_ORDER_HEADERS_ALL h
11551: WHERE h.OPEN_FLAG = ''Y''
11552: and h.org_id = :l_org_id --ER#7479609
11553: and h.header_id = :hold_entity_id
11554: -- QUOTING change
11602: , h.HEADER_ID
11603: , :line_id
11604: , ''N''
11605: , h.org_id --ER#7479609 :l_org_id
11606: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
11607: WHERE h.OPEN_FLAG = ''Y''
11608: and h.org_id = :l_org_id --ER#7479609
11609: and h.header_id = :hold_entity_id
11610: and h.header_id = ol.header_id
11663: ELSIF l_sql_rowcount > 0 THEN
11664: x_is_hold_applied := TRUE;
11665: SELECT count(*)
11666: into l_parent_count
11667: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
11668: WHERE h.OPEN_FLAG = 'Y'
11669: and h.header_id = p_hold_source_rec.hold_entity_id
11670: and h.header_id = ol.header_id
11671: and ol.line_id = p_hold_source_rec.line_id
11751: , h.HEADER_ID
11752: , NULL
11753: , ''N''
11754: , h.org_id --ER#7479609 :l_org_id
11755: FROM OE_ORDER_HEADERS_ALL h
11756: WHERE h.OPEN_FLAG = ''Y''
11757: and h.org_id = :l_org_id --ER#7479609
11758: and h.header_id = :header_id
11759: and h.SOLD_TO_ORG_ID = :hold_entity_id
11809: , h.HEADER_ID
11810: , NULL
11811: , ''N''
11812: , h.org_id --ER#7479609 :l_org_id
11813: FROM OE_ORDER_HEADERS_ALL h
11814: WHERE h.OPEN_FLAG = ''Y''
11815:
11816: and h.SOLD_TO_ORG_ID = :hold_entity_id
11817: and h.org_id = :l_org_id --ER#7479609
11855: RAISE FND_API.G_EXC_ERROR;
11856: ELSIF l_sql_rowcount > 0 THEN
11857: SELECT count(*)
11858: into l_parent_count
11859: FROM OE_ORDER_HEADERS_ALL h
11860: WHERE h.OPEN_FLAG = 'Y'
11861:
11862: and h.SOLD_TO_ORG_ID = p_hold_source_rec.hold_entity_id
11863: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
12189: , h.HEADER_ID
12190: , ol.line_id
12191: , ''N''
12192: , h.org_id --ER#7479609 :l_org_id
12193: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
12194: WHERE h.OPEN_FLAG = ''Y''
12195: and h.org_id = :l_org_id
12196: and h.header_id = ol.header_id
12197: and ol.line_id = :line_id
12261: , h.HEADER_ID
12262: , ol.line_id
12263: , ''N''
12264: , h.org_id --ER#7479609 :l_org_id
12265: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
12266: WHERE h.OPEN_FLAG = ''Y''
12267: and h.org_id = :l_org_id --ER#7479609
12268: and h.header_id = ol.header_id
12269: and ol.INVOICE_TO_ORG_ID = :hold_entity_id
12317: RAISE FND_API.G_EXC_ERROR;
12318: ELSIF l_sql_rowcount > 0 THEN
12319: SELECT count(*)
12320: into l_parent_count
12321: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
12322: WHERE h.OPEN_FLAG = 'Y'
12323: and h.header_id = ol.header_id
12324: and ol.INVOICE_TO_ORG_ID = p_hold_source_rec.hold_entity_id
12325: and ol.OPEN_FLAG = 'Y'
12402: , h.HEADER_ID
12403: , ol.line_id
12404: , ''N''
12405: , h.org_id --ER#7479609 :l_org_id
12406: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
12407: WHERE h.OPEN_FLAG = ''Y''
12408: and h.org_id = :l_org_id --ER#7479609
12409: and h.header_id = ol.header_id
12410: and ol.line_id = :line_id
12473: , h.HEADER_ID
12474: , ol.line_id
12475: , ''N''
12476: , h.org_id --ER#7479609 :l_org_id
12477: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
12478: WHERE h.OPEN_FLAG = ''Y''
12479: and h.org_id = :l_org_id --ER#7479609
12480: and h.header_id = ol.header_id
12481: and ol.SHIP_TO_ORG_ID = :hold_entity_id
12529: RAISE FND_API.G_EXC_ERROR;
12530: ELSIF l_sql_rowcount > 0 THEN
12531: SELECT count(*)
12532: into l_parent_count
12533: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
12534: WHERE h.OPEN_FLAG = 'Y'
12535: and h.header_id = ol.header_id
12536: and ol.SHIP_TO_ORG_ID = p_hold_source_rec.hold_entity_id
12537: and ol.OPEN_FLAG = 'Y'
12613: , h.HEADER_ID
12614: , ol.line_id
12615: , ''N''
12616: , h.org_id --ER#7479609 :l_org_id
12617: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
12618: WHERE h.OPEN_FLAG = ''Y''
12619: and h.org_id = :l_org_id --ER#7479609
12620: and h.header_id = ol.header_id
12621: and ol.line_id = :line_id
12684: , h.HEADER_ID
12685: , ol.line_id
12686: , ''N''
12687: , h.org_id --ER#7479609 :l_org_id
12688: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
12689: WHERE h.OPEN_FLAG = ''Y''
12690: and h.org_id = :l_org_id --ER#7479609
12691: and h.header_id = ol.header_id
12692: and ol.SHIP_FROM_ORG_ID = :hold_entity_id
12740: RAISE FND_API.G_EXC_ERROR;
12741: ELSIF l_sql_rowcount > 0 THEN
12742: SELECT count(*)
12743: into l_parent_count
12744: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
12745: WHERE h.OPEN_FLAG = 'Y'
12746: and h.header_id = ol.header_id
12747: and ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id
12748: and ol.OPEN_FLAG = 'Y'
12824: , h.HEADER_ID
12825: , ol.line_id
12826: , ''N''
12827: , h.org_id --ER#7479609 :l_org_id
12828: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
12829: WHERE h.OPEN_FLAG = ''Y''
12830: and h.org_id = :l_org_id --ER#7479609
12831: and h.header_id = ol.header_id
12832: and ol.line_id = :line_id
12896: , h.HEADER_ID
12897: , ol.line_id
12898: , ''N''
12899: , h.org_id --ER#7479609 :l_org_id
12900: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
12901: WHERE h.OPEN_FLAG = ''Y''
12902: and h.org_id = :l_org_id --ER#7479609
12903: and h.header_id = ol.header_id
12904: and ol.INVENTORY_ITEM_ID = :hold_entity_id
12951: RAISE FND_API.G_EXC_ERROR;
12952: ELSIF l_sql_rowcount > 0 THEN
12953: SELECT count(*)
12954: into l_parent_count
12955: FROM OE_ORDER_HEADERS_ALL h, OE_ORDER_LINES_ALL ol
12956: WHERE h.OPEN_FLAG = 'Y'
12957: and h.header_id = ol.header_id
12958: and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
12959: and ol.OPEN_FLAG = 'Y'