[Home] [Help]
1326: l_progress := '003';
1327: --Get the unique id to be used for this document
1328: SELECT PO_ONLINE_REPORT_TEXT_S.nextval
1329: INTO l_online_report_id
1330: FROM sys.dual;
1331:
1332: l_progress := '004';
1333: --Get User ID and Login ID
1334: l_user_id := FND_GLOBAL.USER_ID;
3285:
3286: --Get the unique id to be used for this document
3287: SELECT PO_ONLINE_REPORT_TEXT_S.nextval
3288: INTO l_online_report_id
3289: FROM sys.dual;
3290:
3291: l_progress := '004';
3292:
3293: --Get User ID and Login ID
5487: 0,
5488: p_sequence + ROWNUM,
5489: substr(l_textline,1,240),
5490: 'PO_PDOI_INVALID_VENDOR'
5491: FROM dual
5492: where not exists (select 'Y'
5493: from PO_HEADERS_GT POH, po_vendors pov
5494: WHERE POH.po_header_id = l_agreement_id
5495: AND pov.vendor_id = poh.vendor_id
5532: 0,
5533: p_sequence + ROWNUM,
5534: substr(l_textline,1,240),
5535: 'PO_PDOI_INVALID_VENDOR_SITE'
5536: FROM dual
5537: where not exists (select 'Y'
5538: from PO_HEADERS_GT POH, po_vendor_sites povs
5539: WHERE POH.po_header_id = l_agreement_id
5540: AND povs.vendor_site_id = poh.vendor_site_id
5576: 0,
5577: p_sequence + ROWNUM,
5578: substr(l_textline,1,240),
5579: 'PO_PDOI_INVALID_VDR_CNTCT'
5580: FROM dual
5581: --Start of code changes for the bug 16244229
5582: WHERE NOT EXISTS ( SELECT 'Y'
5583: FROM
5584: AP_SUPPLIER_CONTACTS PVC ,
5847: 0,
5848: p_sequence + ROWNUM,
5849: l_textline,
5850: 'PO_BUYER_INACTIVE'
5851: FROM dual
5852: where not exists (select 'inactive buyer'
5853: from PO_RELEASES_GT POR,
5854: PO_BUYERS_V POB --
5855: where por.agent_id = pob.employee_id);
5975: ** passed to the UomC function.
5976: */
5977:
5978: /*Bug4302950 :The shipments were updated with wrong price when supplier submits
5979: change request from ISP to split shipment quantity between two individual shipments.
5980: As the ISP doesnot handle change in distributions,replacing the shipments
5981: quantity with the sum of the distributions quantity in the calculation
5982: of price override so that the shipment price will reflect the correct value.*/
5983:
6372: /* Check if there exist any Req Line which have different currency from PO.
6373: If NOT found then setting l_is_same_foreign_currency to Y */
6374: SELECT 'Y'
6375: INTO l_is_same_foreign_currency
6376: FROM DUAL
6377: WHERE NOT EXISTS
6378: (SELECT DISTINCT 1
6379: FROM PO_REQUISITION_LINES_ALL PRL,
6380: FINANCIALS_SYSTEM_PARAMS_ALL FSP,
7679: , NULL
7680: , p_sequence + ROWNUM
7681: , l_textline
7682: , 'PO_SUB_USER_RATE_TYPE'
7683: FROM dual
7684: WHERE exists ( SELECT 'Rate-based lines exist'
7685: FROM po_lines_gt POL
7686: , po_line_types_b PLT
7687: WHERE p_document_id = POL.po_header_id
7782: 0,
7783: p_sequence + ROWNUM,
7784: substr(l_textline,1,240),
7785: 'PO_PDOI_INVALID_VENDOR'
7786: FROM dual
7787: where not exists (select 'Y'
7788: from PO_HEADERS_GT POH, po_vendors pov
7789: WHERE POH.po_header_id = p_document_id
7790: AND pov.vendor_id = poh.vendor_id
7826: 0,
7827: p_sequence + ROWNUM,
7828: substr(l_textline,1,240),
7829: 'PO_PDOI_INVALID_VENDOR_SITE'
7830: FROM dual
7831: where not exists (select 'Y'
7832: from PO_HEADERS_GT POH, po_vendor_sites povs
7833: WHERE POH.po_header_id = p_document_id
7834: AND povs.vendor_site_id = poh.vendor_site_id
7869: 0,
7870: p_sequence + ROWNUM,
7871: substr(l_textline,1,240),
7872: 'PO_PDOI_INVALID_VDR_CNTCT'
7873: FROM dual
7874: --Start of code changes for the bug 16244229
7875: WHERE NOT EXISTS ( SELECT 'Y'
7876: FROM
7877: AP_SUPPLIER_CONTACTS PVC ,
7969: 0,
7970: p_sequence + ROWNUM,
7971: substr(l_textline,1,240),
7972: 'PO_CONTACT_WITHOUT_VALID_USER'
7973: FROM dual
7974: WHERE NOT EXISTS (SELECT 'Y'
7975: FROM po_supplier_contacts_val_v pscv,
7976: po_headers_gt poh,
7977: fnd_user fu
8139: 0,
8140: p_sequence + ROWNUM,
8141: l_textline,
8142: 'PO_BUYER_INACTIVE'
8143: FROM dual
8144: where not exists (select 'inactive buyer'
8145: from PO_HEADERS_GT POH,
8146: PO_BUYERS_V POB --
8147: where poh.agent_id = pob.employee_id);
8643: 0,
8644: p_sequence + ROWNUM,
8645: substr(l_textline,1,240),
8646: 'PO_SUB_STD_CONTRACT_AMT_LIMIT'
8647: FROM DUAL;
8648:
8649: --Increment the p_sequence with number of errors reported in last query
8650: p_sequence := p_sequence + SQL%ROWCOUNT;
8651:
11607: NVL(l_asl_consigned_flag(i), 'N') = 'Y')
11608: THEN
11609: SELECT count('Y')
11610: INTO l_count_expense_dist
11611: FROM DUAL
11612: WHERE EXISTS(SELECT 'Y'
11613: FROM PO_DISTRIBUTIONS_GT
11614: WHERE LINE_LOCATION_ID = l_line_location_id(i)
11615: AND DESTINATION_TYPE_CODE = 'EXPENSE');
16179: -- Indicates API return status as 'S', 'E' or 'U'.
16180: --x_po_status_rec
16181: -- Table x_po_status_rec.updateable_flag will be 'Y' or 'N' for each input entity
16182: --Notes:
16183: -- The implementation of updatable_flag involves a fake "update dual" statement to
16184: -- optimize performance.
16185: --End of Comments
16186: -------------------------------------------------------------------------------
16187:
16231: || l_progress, 'Entering Procedure '||l_api_name);
16232: END IF;
16233: END IF;
16234:
16235: --To obtimize performance, Execute a fake "update dual" in BULK. The WHERE clause
16236: -- of the fake update statement checks if the current entity is updatable or not.
16237: -- One dual row updated <==> where clause is true <==> current entity is updatable.
16238: -- Later, Examine BULK_ROWCOUNT in a loop to determine updatable_flag
16239: l_progress := '010';
16233: END IF;
16234:
16235: --To obtimize performance, Execute a fake "update dual" in BULK. The WHERE clause
16236: -- of the fake update statement checks if the current entity is updatable or not.
16237: -- One dual row updated <==> where clause is true <==> current entity is updatable.
16238: -- Later, Examine BULK_ROWCOUNT in a loop to determine updatable_flag
16239: l_progress := '010';
16240:
16241: -- bug3592160 START
16254: );
16255:
16256: l_progress := '015';
16257:
16258: -- The original approach was to do a fake UPDATE on DUAL table. However, this
16259: -- is causing locking issue. Therefore, BULK INSERT is used instead of
16260: -- BULK UPDATE
16261: l_procedure_id := PO_CORE_S.get_session_gt_nextval;
16262:
16266: num1
16267: )
16268: SELECT l_procedure_id,
16269: 1 -- Dummy Value
16270: FROM DUAL
16271: WHERE (p_release_id(i) IS NOT NULL
16272: --Case 1: No Release is specified, PO Header has to be specified
16273: -- Through any of HeaderId, DocNum-and-SubType, or VendorOrderNum
16274: OR EXISTS (select null from po_headers_all h
16310: l_progress := '030';
16311: FOR i IN 1..p_count LOOP
16312:
16313: IF SQL%BULK_ROWCOUNT(i) > 0 THEN
16314: -- Updateable Header/Line/Shipment found in the fake "update dual" stmt
16315: x_po_status_rec.updatable_flag(i) := 'Y';
16316:
16317: -- This document is updatable, lock the document if p_lock_flag=Y
16318: l_progress := '040';
16416: -- Indicates API return status as 'S', 'E' or 'U'.
16417: --x_po_status_rec
16418: -- Table x_po_status_rec.updateable_flag will be 'Y' or 'N' for each input entity
16419: --Notes:
16420: -- The implementation of reservable_flag involves a fake "update dual" statement to
16421: -- optimize performance.
16422: --End of Comments
16423: -------------------------------------------------------------------------------
16424:
16456: || l_progress, 'Entering Procedure '||l_api_name);
16457: END IF;
16458: END IF;
16459:
16460: --To obtimize performance, Execute a fake "update dual" in BULK. The WHERE clause
16461: -- of the fake update statement checks if the current entity is reservable or not.
16462: -- One dual row updated <==> where clause is true <==> current entity is reservable.
16463: -- Later, Examine BULK_ROWCOUNT in a loop to determine reservable_flag
16464: l_progress := '010';
16458: END IF;
16459:
16460: --To obtimize performance, Execute a fake "update dual" in BULK. The WHERE clause
16461: -- of the fake update statement checks if the current entity is reservable or not.
16462: -- One dual row updated <==> where clause is true <==> current entity is reservable.
16463: -- Later, Examine BULK_ROWCOUNT in a loop to determine reservable_flag
16464: l_progress := '010';
16465:
16466: -- bug3592160 START
16480:
16481: l_progress := '015';
16482:
16483:
16484: -- The original approach was to do a fake UPDATE on DUAL table. However, this
16485: -- is causing locking issue. Therefore, BULK INSERT is used instead of
16486: -- BULK UPDATE
16487: l_procedure_id := PO_CORE_S.get_session_gt_nextval;
16488:
16492: num1
16493: )
16494: SELECT l_procedure_id,
16495: 1 -- Dummy Value
16496: FROM DUAL
16497: WHERE (p_release_id(i) IS NOT NULL
16498: --Case 1: No Release is specified, PO Header has to be specified
16499: -- Through any of HeaderId, DocNum-and-SubType, or VendorOrderNum
16500: -- Bug 3407980: Modified closed_code condition so that it only discards 'FINALLY CLOSED'
16543: l_progress := '030';
16544: FOR i IN 1..p_count LOOP
16545:
16546: IF SQL%BULK_ROWCOUNT(i) > 0 THEN
16547: -- Reservable Header/Line/Shipment found in the fake "update dual" stmt
16548: x_po_status_rec.reservable_flag(i) := 'Y';
16549: ELSE
16550: x_po_status_rec.reservable_flag(i) := 'N';
16551: END IF; --END of IF SQL%BULK_ROWCOUNT(i) > 0
16594: -- Indicates API return status as 'S', 'E' or 'U'.
16595: --x_po_status_rec
16596: -- Table x_po_status_rec.updateable_flag will be 'Y' or 'N' for each input entity
16597: --Notes:
16598: -- The implementation of unreservable_flag involves a fake "update dual" statement to
16599: -- optimize performance.
16600: --End of Comments
16601: -------------------------------------------------------------------------------
16602:
16634: || l_progress, 'Entering Procedure '||l_api_name);
16635: END IF;
16636: END IF;
16637:
16638: --To obtimize performance, Execute a fake "update dual" in BULK. The WHERE clause
16639: -- of the fake update statement checks if the current entity is unreservable or not.
16640: -- One dual row updated <==> where clause is true <==> current entity is unreservable.
16641: -- Later, Examine BULK_ROWCOUNT in a loop to determine unreservable_flag
16642: l_progress := '010';
16636: END IF;
16637:
16638: --To obtimize performance, Execute a fake "update dual" in BULK. The WHERE clause
16639: -- of the fake update statement checks if the current entity is unreservable or not.
16640: -- One dual row updated <==> where clause is true <==> current entity is unreservable.
16641: -- Later, Examine BULK_ROWCOUNT in a loop to determine unreservable_flag
16642: l_progress := '010';
16643:
16644: -- bug3592160 START
16658:
16659: l_progress := '015';
16660:
16661: -- SQL What: Checks if current PO Header/Line/Shipment is in unreservable status
16662: -- The original approach was to do a fake UPDATE on DUAL table. However, this
16663: -- is causing locking issue. Therefore, BULK INSERT is used instead of
16664: -- BULK UPDATE
16665: l_procedure_id := PO_CORE_S.get_session_gt_nextval;
16666:
16670: num1
16671: )
16672: SELECT l_procedure_id,
16673: 1 -- Dummy Value
16674: FROM DUAL
16675: WHERE (p_release_id(i) IS NOT NULL
16676: --Case 1: No Release is specified, PO Header has to be specified
16677: -- Through any of HeaderId, DocNum-and-SubType, or VendorOrderNum
16678: -- Bug 3407980: Modified closed_code condition so that it only discards 'FINALLY CLOSED'
16712: l_progress := '030';
16713: FOR i IN 1..p_count LOOP
16714:
16715: IF SQL%BULK_ROWCOUNT(i) > 0 THEN
16716: -- Unreservable Header/Line/Shipment found in the fake "update dual" stmt
16717: x_po_status_rec.unreservable_flag(i) := 'Y';
16718: ELSE
16719: x_po_status_rec.unreservable_flag(i) := 'N';
16720: END IF; --END of IF SQL%BULK_ROWCOUNT(i) > 0
18480: -----------------------------------------
18481:
18482: SELECT PO_SESSION_GT_S.nextval
18483: INTO l_dates_key
18484: FROM DUAL
18485: ;
18486:
18487: l_progress := '220';
18488:
21186: , NULL , 'PO_SUB_PO_SHIP_NE_DIST_AMT'
21187: , 'PO_SUB_PO_SHIP_NE_DIST_QTY'
21188: ),
21189: Decode(l_draft_type,'PAR','W',NULL) --WARNING FOR PAR and ERROR FOR OTHERS
21190: FROM DUAL --bug 16474947
21191: WHERE
21192: ( (l_draft_type <> 'PAR') OR
21193: (l_draft_type = 'PAR' AND NOT EXISTS (SELECT 1 FROM po_lines_draft_all WHERE po_line_id = l_line_id_tbl(i)
21194: AND draft_id = l_draft_id AND change_status = 'NEW'))
22060: l_progress := '010';
22061: IF p_online_report_id is NULL or p_online_report_id = 0 THEN
22062: SELECT PO_ONLINE_REPORT_TEXT_S.nextval
22063: INTO p_online_report_id
22064: FROM sys.dual;
22065: p_sequence := 0;
22066: ELSE
22067: BEGIN
22068: SELECT Nvl(Max(SEQUENCE), 0)
22108: p_sequence,
22109: substr(FND_MESSAGE.GET_STRING('PO', 'PO_CO_INACTIVE'),1,240),
22110: 'PO_BUYER_INACTIVE',
22111: 'E'
22112: FROM dual;
22113:
22114: po_debug.debug_stmt(l_log_head , l_progress, 'Contracting Officer is Invalid');
22115: IF itemtype is not NULL and itemkey is not NULL THEN
22116: PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,l_log_head || 'Contracting Officer is not valid');
25874: -- Check if non-MIPR references(PR or PAR) exists on Award
25875: BEGIN
25876: SELECT 'Y'
25877: INTO l_non_mipr_ref_exists
25878: FROM dual
25879: WHERE EXISTS(
25880: SELECT 1
25881: FROM po_distributions_gt POD
25882: WHERE POD.po_header_id = p_document_id
25922: SYSDATE,
25923: p_sequence + ROWNUM,
25924: l_textline,
25925: 'PO_MIPR_CAR_EXEMPT'
25926: FROM dual
25927: WHERE EXISTS(
25928: SELECT 1
25929: FROM po_clm_cars
25930: WHERE po_header_id = p_document_id
26015: p_sequence + ROWNUM,
26016: l_textline,
26017: 'PO_MIPR_MULTI_OWN_REF',
26018: 'W'
26019: FROM dual
26020: WHERE EXISTS(
26021: SELECT 1
26022: FROM po_session_gt
26023: WHERE key = l_procedure_id
26187: p_sequence + ROWNUM,
26188: l_textline,
26189: 'PO_MIPR_OTHERS_AND_PR_REF',
26190: 'W'
26191: FROM dual
26192: WHERE l_non_mipr_ref_exists = 'Y'; -- Non-Mipr reference exists
26193:
26194: p_sequence := p_sequence + SQL%ROWCOUNT;
26195:
26217: p_sequence + ROWNUM,
26218: l_textline,
26219: 'PO_MIPR_MULTI_REQ_OFF_REF',
26220: 'W'
26221: FROM dual
26222: WHERE EXISTS(
26223: SELECT 1
26224: FROM po_session_gt
26225: WHERE key = l_procedure_id