eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PO_RCO_VALIDATION_PVT dependencies on PO_CHANGE_REQUESTS

Line 90: l_document_num po_change_requests.document_num%TYPE;

86: l_old_curr_price NUMBER;
87: l_req_uom po_requisition_lines_all.unit_meas_lookup_code%TYPE;
88: l_req_user_id NUMBER;
89: l_document_header_id NUMBER;
90: l_document_num po_change_requests.document_num%TYPE;
91: l_document_revision_num NUMBER;
92: l_document_line_number NUMBER;
93: l_requester_id NUMBER;
94:

Line 134: FROM po_change_requests pcr,

130: pcr.document_revision_num,
131: pcr.document_line_id,
132: pcr.document_line_number,
133: pcr.requester_id
134: FROM po_change_requests pcr,
135: po_requisition_lines_all prla
136: WHERE pcr.change_request_group_id = grp_id
137: AND pcr.action_type = 'MODIFICATION'
138: AND prla.requisition_line_id = pcr.document_line_id

Line 221: FROM po_change_requests

217:
218: BEGIN
219: SELECT new_need_by_date
220: INTO l_new_date
221: FROM po_change_requests
222: WHERE new_need_by_date IS NOT NULL
223: AND change_request_group_id = p_chn_grp_id
224: AND document_line_id = l_req_line_id;
225: EXCEPTION WHEN OTHERS THEN

Line 233: FROM po_change_requests

229: l_progress := '002';
230:
231: SELECT nvl(SUM(new_quantity), 0)
232: INTO l_new_qty
233: FROM po_change_requests
234: WHERE new_quantity IS NOT NULL
235: AND change_request_group_id = p_chn_grp_id
236: AND document_line_id = l_req_line_id
237: AND action_type = 'MODIFICATION'

Line 245: FROM po_change_requests

241: INTO l_old_qty
242: FROM po_req_distributions_all
243: WHERE requisition_line_id = l_req_line_id
244: AND distribution_id NOT IN(SELECT document_distribution_id
245: FROM po_change_requests
246: WHERE new_quantity IS NOT NULL
247: AND change_request_group_id = p_chn_grp_id
248: AND document_line_id = l_req_line_id
249: AND action_type = 'MODIFICATION'

Line 314: INSERT INTO po_change_requests

310: l_progress := '004';
311: IF(l_old_curr_price IS NULL) THEN
312: l_new_curr_price := NULL;
313: END IF;
314: INSERT INTO po_change_requests
315: (
316: change_request_group_id,
317: change_request_id,
318: initiator,

Line 413: FROM po_change_requests

409: document_num,
410: document_line_id,
411: document_line_number,
412: requester_id
413: FROM po_change_requests
414: WHERE action_type = 'MODIFICATION'
415: AND change_request_group_id = grp_id;
416:
417: CURSOR l_line_qty_chn_csr(line_id NUMBER, grp_id NUMBER) IS

Line 419: FROM po_change_requests

415: AND change_request_group_id = grp_id;
416:
417: CURSOR l_line_qty_chn_csr(line_id NUMBER, grp_id NUMBER) IS
418: SELECT change_request_id
419: FROM po_change_requests
420: WHERE document_line_id = line_id
421: AND change_request_group_id = grp_id
422: AND new_quantity IS NOT NULL
423: AND request_level = 'DISTRIBUTION';

Line 427: FROM po_change_requests

423: AND request_level = 'DISTRIBUTION';
424:
425: CURSOR l_line_amt_chn_csr(line_id NUMBER, grp_id NUMBER) IS
426: SELECT change_request_id
427: FROM po_change_requests
428: WHERE document_line_id = line_id
429: AND change_request_group_id = grp_id
430: AND new_amount IS NOT NULL
431: AND request_level = 'DISTRIBUTION';

Line 467: FROM po_change_requests

463: SELECT SUM(amount)
464: INTO l_new_amount
465: FROM (
466: SELECT new_amount amount
467: FROM po_change_requests
468: WHERE change_request_group_id = p_chn_grp_id
469: AND document_line_id = l_line_id
470: AND new_amount IS NOT NULL
471: AND request_level = 'DISTRIBUTION'

Line 478: FROM po_change_requests

474: FROM po_req_distributions_all
475: WHERE requisition_line_id = l_line_id
476: AND distribution_id NOT IN
477: (SELECT document_distribution_id
478: FROM po_change_requests
479: WHERE change_request_group_id = p_chn_grp_id
480: AND document_line_id = l_line_id
481: AND new_amount IS NOT NULL
482: AND request_level = 'DISTRIBUTION')

Line 489: FROM po_change_requests

485: SELECT SUM(amount)
486: INTO l_new_cur_amount
487: FROM (
488: SELECT new_currency_amount amount
489: FROM po_change_requests
490: WHERE change_request_group_id = p_chn_grp_id
491: AND document_line_id = l_line_id
492: AND new_currency_amount IS NOT NULL
493: AND request_level = 'DISTRIBUTION'

Line 500: FROM po_change_requests

496: FROM po_req_distributions_all
497: WHERE requisition_line_id = l_line_id
498: AND distribution_id NOT IN
499: (SELECT document_distribution_id
500: FROM po_change_requests
501: WHERE change_request_group_id = p_chn_grp_id
502: AND document_line_id = l_line_id
503: AND new_currency_amount IS NOT NULL
504: AND request_level = 'DISTRIBUTION')

Line 528: FROM po_change_requests

524: SELECT SUM(quantity)
525: INTO l_new_quantity
526: FROM (
527: SELECT new_quantity quantity
528: FROM po_change_requests
529: WHERE change_request_group_id = p_chn_grp_id
530: AND document_line_id = l_line_id
531: AND new_quantity IS NOT NULL
532: AND request_level = 'DISTRIBUTION'

Line 539: FROM po_change_requests

535: FROM po_req_distributions_all
536: WHERE requisition_line_id = l_line_id
537: AND distribution_id NOT IN
538: (SELECT document_distribution_id
539: FROM po_change_requests
540: WHERE change_request_group_id = p_chn_grp_id
541: AND document_line_id = l_line_id
542: AND new_quantity IS NOT NULL
543: AND request_level = 'DISTRIBUTION')

Line 553: INSERT INTO po_change_requests

549: END IF;
550:
551: l_progress := '004';
552:
553: INSERT INTO po_change_requests
554: (
555: change_request_group_id,
556: change_request_id,
557: initiator,

Line 1563: p_po_change_requests => l_po_change_table,

1559: x_msg_data => l_return_msg,
1560: p_po_header_id => l_current_hdr_id,
1561: p_po_release_id => l_current_rel_id,
1562: p_revision_num => l_current_rev_num,
1563: p_po_change_requests => l_po_change_table,
1564: x_online_report_id => l_online_report_id,
1565: x_pos_errors => l_pos_errors_tbl,
1566: x_doc_check_error_msg => l_doc_check_rec_type);
1567: x_retmsg := 'VC0032';

Line 1636: *Simple API to insert change records into PO_CHANGE_REQUESTS table

1632: END IF;
1633: END validate_changes;
1634:
1635: /*
1636: *Simple API to insert change records into PO_CHANGE_REQUESTS table
1637: */
1638: PROCEDURE insert_reqchange(p_change_table change_tbl_type,
1639: p_chn_req_grp_id NUMBER)
1640: IS

Line 1650: INSERT INTO po_change_requests

1646: l_req_user_id := fnd_global.user_id;
1647: FOR i IN 1..p_change_table.count
1648: LOOP
1649:
1650: INSERT INTO po_change_requests
1651: (
1652: change_request_group_id,
1653: change_request_id,
1654: initiator,

Line 1836: FROM po_change_requests

1832: SELECT
1833: change_request_id,
1834: new_quantity,
1835: document_distribution_id
1836: FROM po_change_requests
1837: WHERE change_request_group_id = grp_id
1838: AND document_line_id = line_id
1839: AND request_level = 'DISTRIBUTION';
1840:

Line 1843: FROM po_change_requests

1839: AND request_level = 'DISTRIBUTION';
1840:
1841: CURSOR l_line_id_with_qty_chn_csr(grp_id NUMBER) IS
1842: SELECT DISTINCT document_line_id
1843: FROM po_change_requests
1844: WHERE change_request_group_id = grp_id
1845: AND new_quantity IS NOT NULL;
1846:
1847: CURSOR l_line_id_with_price_chn_csr(grp_id NUMBER) IS

Line 1852: FROM po_change_requests

1848: SELECT
1849: change_request_id,
1850: document_line_id,
1851: new_price
1852: FROM po_change_requests
1853: WHERE change_request_group_id = grp_id
1854: AND new_price IS NOT NULL;
1855:
1856: CURSOR l_dist_id_csr(line_id NUMBER) IS

Line 1869: FROM po_change_requests

1865: SELECT
1866: change_request_id,
1867: recoverable_tax,
1868: nonrecoverable_tax
1869: FROM po_change_requests
1870: WHERE change_request_group_id = p_chn_req_grp_id
1871: AND document_distribution_id = l_dist_id;
1872:
1873:

Line 1887: FROM po_change_requests

1883:
1884: BEGIN
1885: SELECT nvl(new_currency_unit_price, new_price)
1886: INTO l_price
1887: FROM po_change_requests
1888: WHERE change_request_group_id = p_chn_req_grp_id
1889: AND document_line_id = l_line_id
1890: AND request_level = 'LINE'
1891: AND new_price IS NOT NULL;

Line 1914: UPDATE po_change_requests

1910: FROM po_req_distributions_all
1911: WHERE distribution_id = l_dist_id;
1912: calculate_disttax(1.0, l_cal_disttax_status, l_dist_id, l_price, l_quantity, NULL, l_rec_tax, l_nonrec_tax);
1913: END IF;
1914: UPDATE po_change_requests
1915: SET recoverable_tax = l_rec_tax,
1916: nonrecoverable_tax = l_nonrec_tax
1917: WHERE change_request_id = l_id;
1918:

Line 1953: IF(l_temp_id IS NOT NULL) THEN -- Distribution exist in po_change_requests table

1949: l_dist_rec_tax,
1950: l_dist_nonrec_tax;
1951: CLOSE l_dist_exist_chn_csr;
1952:
1953: IF(l_temp_id IS NOT NULL) THEN -- Distribution exist in po_change_requests table
1954: l_rec_tax := l_rec_tax + l_dist_rec_tax;
1955: l_nonrec_tax := l_nonrec_tax + l_dist_nonrec_tax;
1956: ELSE -- Distribution does NOT exist in change table, thus need to calculate
1957: calculate_disttax(1.0, l_cal_disttax_status, l_dist_id, l_price, l_quantity, NULL, l_dist_rec_tax, l_dist_nonrec_tax);

Line 1966: UPDATE po_change_requests

1962: END LOOP;
1963: CLOSE l_dist_id_csr;
1964:
1965: l_progress := '003';
1966: UPDATE po_change_requests
1967: SET recoverable_tax = l_rec_tax,
1968: nonrecoverable_tax = l_nonrec_tax
1969: WHERE change_request_id = l_id;
1970: END LOOP;

Line 2005: FROM po_change_requests

2001: l_temp_id NUMBER;
2002:
2003: CURSOR l_line_id_with_qty_chn_csr(grp_id NUMBER) IS
2004: SELECT DISTINCT document_line_id
2005: FROM po_change_requests
2006: WHERE change_request_group_id = grp_id
2007: AND new_quantity IS NOT NULL;
2008:
2009:

Line 2023: FROM po_change_requests

2019: SELECT
2020: change_request_id,
2021: recoverable_tax,
2022: nonrecoverable_tax
2023: FROM po_change_requests
2024: WHERE change_request_group_id = p_chn_req_grp_id
2025: AND document_distribution_id = l_dist_id;
2026:
2027: CURSOR l_dist_with_chn_csr(grp_id NUMBER, line_id NUMBER) IS

Line 2032: FROM po_change_requests

2028: SELECT
2029: change_request_id,
2030: new_quantity,
2031: document_distribution_id
2032: FROM po_change_requests
2033: WHERE change_request_group_id = grp_id
2034: AND document_line_id = line_id
2035: AND request_level = 'LINE';
2036:

Line 2090: UPDATE po_change_requests

2086: po_debug.debug_stmt(l_log_head, l_progress,'Updating taxes in po_change_request table');
2087: END IF;
2088:
2089:
2090: UPDATE po_change_requests
2091: SET recoverable_tax = l_rec_tax,
2092: nonrecoverable_tax = l_nonrec_tax
2093: WHERE change_request_id = l_id;
2094:

Line 2136: l_po_request_level po_change_requests.request_level%TYPE;

2132: l_po_distribution_id NUMBER;
2133: l_po_distribution_number NUMBER;
2134: l_recoverable_tax NUMBER;
2135: l_non_recoverable_tax NUMBER;
2136: l_po_request_level po_change_requests.request_level%TYPE;
2137: l_req_request_level po_change_requests.request_level%TYPE;
2138: l_req_header_id NUMBER;
2139: l_req_num po_requisition_headers_all.segment1%TYPE;
2140: l_req_line_number NUMBER;

Line 2137: l_req_request_level po_change_requests.request_level%TYPE;

2133: l_po_distribution_number NUMBER;
2134: l_recoverable_tax NUMBER;
2135: l_non_recoverable_tax NUMBER;
2136: l_po_request_level po_change_requests.request_level%TYPE;
2137: l_req_request_level po_change_requests.request_level%TYPE;
2138: l_req_header_id NUMBER;
2139: l_req_num po_requisition_headers_all.segment1%TYPE;
2140: l_req_line_number NUMBER;
2141: l_req_dist_number NUMBER;

Line 3696: *2. If ALL valid, same them into PO_CHANGE_REQUESTS table

3692:
3693: /*-------------------------------------------------------------------------------------------------
3694: *This API is called directly from the UI. It will have PLSQL tables as input, which contain change/cancel requests
3695: *1. Validate the requests
3696: *2. If ALL valid, same them into PO_CHANGE_REQUESTS table
3697: *x_return_status = FND_API.G_RET_STS_SUCCESS => Everything is Valid, and records are saved into change table
3698: * FND_API.G_RET_STS_ERROR => Caught Errors, thus no records are saved into change table
3699: * FND_API.G_RET_STS_UNEXP_ERROR => Unexpected Errors Occur in the API
3700: *x_retMsg will indicate details/location of errors.

Line 3734: DELETE FROM po_change_requests

3730:
3731:
3732: BEGIN
3733:
3734: DELETE FROM po_change_requests
3735: WHERE document_header_id = p_req_hdr_id
3736: AND request_status = 'SYSTEMSAVE'
3737: AND initiator = 'REQUESTER';
3738:

Line 3917: **simply insert records into PO_CHANGE_REQUESTS table

3913:
3914: /*--------------------------------------------------------------
3915: **Save_ReqCancel: takes in a PLSQL table as input, containing
3916: **cancellation request. No Validation is done here. This API
3917: **simply insert records into PO_CHANGE_REQUESTS table
3918: ---------------------------------------------------------------*/
3919: PROCEDURE save_reqcancel(p_api_version IN NUMBER,
3920: x_return_status OUT NOCOPY VARCHAR2,
3921: p_req_hdr_id IN NUMBER,

Line 3941: l_po_num po_change_requests.ref_po_num%TYPE;

3937: l_req_price NUMBER;
3938: l_req_currency_price NUMBER;
3939: l_req_quantity NUMBER;
3940: l_req_date DATE;
3941: l_po_num po_change_requests.ref_po_num%TYPE;
3942: l_po_release_num NUMBER;
3943: BEGIN
3944: x_retmsg := '000';
3945: l_req_user_id := fnd_global.user_id;

Line 3950: DELETE FROM po_change_requests

3946:
3947:
3948: IF(p_grp_id IS NULL) THEN
3949: SELECT po_chg_request_seq.nextval INTO p_change_request_group_id FROM dual;
3950: DELETE FROM po_change_requests
3951: WHERE document_header_id = p_req_hdr_id
3952: AND initiator = 'REQUESTER'
3953: AND request_status = 'SYSTEMSAVE';
3954: ELSE

Line 4018: INSERT INTO po_change_requests

4014: WHERE po_release_id = l_po_release_id;
4015: END IF;
4016: END IF;
4017: x_retmsg := '002';
4018: INSERT INTO po_change_requests
4019: (
4020: change_request_group_id,
4021: change_request_id,
4022: initiator,

Line 4167: l_request_reason po_change_requests.request_reason%TYPE;

4163: l_price_break VARCHAR2(1);
4164: -- l_set_of_books_id NUMBER;
4165: -- l_gl_period GL_PERIOD_STATUSES.PERIOD_NAME%TYPE;
4166:
4167: l_request_reason po_change_requests.request_reason%TYPE;
4168: l_cancel_errtable po_req_change_err_table;
4169: l_cal_disttax_status VARCHAR2(1);
4170: l_item_id NUMBER;
4171: l_req_uom po_requisition_lines_all.unit_meas_lookup_code%TYPE;

Line 4195: FROM po_change_requests

4191: new_quantity,
4192: new_price,
4193: new_need_by_date,
4194: request_reason
4195: FROM po_change_requests
4196: WHERE change_request_group_id = grp_id
4197: AND action_type = 'MODIFICATION';
4198:
4199: CURSOR l_cancel_csr(grp_id NUMBER) IS

Line 4204: FROM po_change_requests

4200: SELECT
4201: document_header_id,
4202: document_line_id,
4203: request_reason
4204: FROM po_change_requests
4205: WHERE change_request_group_id = grp_id
4206: AND action_type = 'CANCELLATION';
4207:
4208:

Line 4216: po_change_requests

4212: document_distribution_id dist_id,
4213: document_header_id hdr_id,
4214: document_num req_num
4215: FROM
4216: po_change_requests
4217: WHERE
4218: change_request_group_id = grp_id AND
4219: (new_quantity IS NOT NULL OR new_amount IS NOT NULL) AND
4220: action_type = 'MODIFICATION'

Line 4230: po_change_requests pcr,

4226: prha.segment1 req_num
4227: FROM
4228: po_req_distributions_all prda,
4229: po_requisition_lines_all prla,
4230: po_change_requests pcr,
4231: po_requisition_headers_all prha
4232: WHERE
4233: prha.requisition_header_id = prla.requisition_header_id AND
4234: prla.requisition_line_id = prda.requisition_line_id AND

Line 4245: po_change_requests pcr,

4241: CURSOR l_changed_po_dists_csr(grp_id NUMBER) IS
4242: SELECT -- any quantity or amount change
4243: pda.po_distribution_id
4244: FROM
4245: po_change_requests pcr,
4246: po_req_distributions_all prda,
4247: po_distributions_all pda,
4248: po_headers_all pha
4249: WHERE

Line 4261: po_change_requests pcr,

4257: UNION
4258: SELECT -- select distributions that are effected with any line change
4259: pda.po_distribution_id
4260: FROM
4261: po_change_requests pcr,
4262: po_requisition_lines_all prla,
4263: po_req_distributions_all prda,
4264: po_distributions_all pda,
4265: po_headers_all pha

Line 4281: po_change_requests pcr,

4277: CURSOR l_changed_rel_dists_csr(grp_id NUMBER) IS
4278: SELECT -- any quantity or amount change
4279: pda.po_distribution_id
4280: FROM
4281: po_change_requests pcr,
4282: po_req_distributions_all prda,
4283: po_distributions_all pda,
4284: po_requisition_lines_all prla,
4285: po_line_locations_all plla

Line 4299: po_change_requests pcr,

4295: UNION -- select distributions that are effected with any line change
4296: SELECT
4297: pda.po_distribution_id
4298: FROM
4299: po_change_requests pcr,
4300: po_requisition_lines_all prla,
4301: po_req_distributions_all prda,
4302: po_distributions_all pda,
4303: po_line_locations_all plla

Line 4545: FROM po_change_requests

4541: BEGIN
4542:
4543: SELECT new_amount
4544: INTO l_new_amount
4545: FROM po_change_requests
4546: WHERE
4547: change_request_group_id = p_group_id AND
4548: document_distribution_id = l_req_dist_id AND
4549: new_amount IS NOT NULL;

Line 4560: FROM po_change_requests

4556: l_price_break := 'N';
4557: BEGIN
4558: SELECT new_quantity
4559: INTO l_new_quantity
4560: FROM po_change_requests
4561: WHERE change_request_group_id = p_group_id
4562: AND document_distribution_id = l_req_dist_id
4563: AND new_quantity IS NOT NULL;
4564:

Line 4586: FROM po_change_requests

4582: --Obtain most recent price
4583: BEGIN
4584: SELECT new_price
4585: INTO l_new_price
4586: FROM po_change_requests
4587: WHERE change_request_group_id = p_group_id
4588: AND document_line_id = l_req_line_id
4589: AND new_price IS NOT NULL;
4590:

Line 4599: FROM po_change_requests

4595: EXCEPTION WHEN no_data_found THEN
4596: BEGIN
4597: SELECT new_need_by_date
4598: INTO l_new_need_by_date
4599: FROM po_change_requests
4600: WHERE change_request_group_id = p_group_id
4601: AND document_line_id = l_req_line_id
4602: AND new_need_by_date IS NOT NULL;
4603:

Line 4829: UPDATE po_change_requests

4825: /*
4826: * If all requests are valid, update status to "NEW" and kick off workflow
4827: */
4828: IF(x_errtable.req_line_id.count = 0) THEN
4829: UPDATE po_change_requests
4830: SET request_status = 'NEW'
4831: WHERE change_request_group_id = p_group_id
4832: AND request_status = 'SYSTEMSAVE';
4833:

Line 4895: po_change_requests pcr,

4891: pcr.document_line_id,
4892: prla.line_location_id,
4893: pcr.change_request_id
4894: FROM
4895: po_change_requests pcr,
4896: po_requisition_lines_all prla
4897: WHERE pcr.action_type = 'CANCELLATION'
4898: AND pcr.change_request_group_id = grp_id
4899: AND pcr.document_line_id = prla.requisition_line_id;

Line 4927: UPDATE po_change_requests

4923: FETCH l_cancels_csr INTO
4924: l_req_hdr_id, l_line_id, l_line_location_id, l_chn_req_id;
4925: EXIT WHEN l_cancels_csr%notfound;
4926: IF(l_line_location_id IS NULL) THEN
4927: UPDATE po_change_requests
4928: SET request_status = 'ACCEPTED'
4929: WHERE change_request_id = l_chn_req_id;
4930: ELSE
4931: l_workflow_needed := 'Y';

Line 4948: UPDATE po_change_requests

4944:
4945: --If all requests are valid, update status to "NEW", and kick off Workflow
4946:
4947: IF(p_errtable.req_line_id.count = 0) THEN
4948: UPDATE po_change_requests
4949: SET request_status = 'NEW'
4950: WHERE change_request_group_id = p_group_id
4951: AND request_status = 'SYSTEMSAVE';
4952:

Line 5353: **simply insert records into PO_CHANGE_REQUESTS table

5349:
5350: /*--------------------------------------------------------------
5351: **Save_IReqCancel: takes in a PLSQL table as input, containing
5352: **cancellation request. No Validation is done here. This API
5353: **simply insert records into PO_CHANGE_REQUESTS table
5354: ---------------------------------------------------------------*/
5355:
5356:
5357: PROCEDURE save_ireqcancel(p_api_version IN NUMBER,

Line 5379: l_po_num po_change_requests.ref_po_num%TYPE;

5375: l_req_price NUMBER;
5376: l_req_currency_price NUMBER;
5377: l_req_quantity NUMBER;
5378: l_req_date DATE;
5379: l_po_num po_change_requests.ref_po_num%TYPE;
5380: l_po_release_num NUMBER;
5381: l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
5382:
5383: BEGIN

Line 5394: DELETE FROM po_change_requests

5390: END IF;
5391:
5392: IF(p_grp_id IS NULL) THEN
5393: SELECT po_chg_request_seq.nextval INTO p_change_request_group_id FROM dual;
5394: DELETE FROM po_change_requests
5395: WHERE document_header_id = p_req_hdr_id
5396: AND initiator = 'REQUESTER'
5397: AND request_status = 'SYSTEMSAVE';
5398:

Line 5400: po_debug.debug_var(l_log_head, l_progress,'In PO_CHANGE_REQUESTS records deleted=', SQL%rowcount);

5396: AND initiator = 'REQUESTER'
5397: AND request_status = 'SYSTEMSAVE';
5398:
5399: IF g_debug_stmt THEN
5400: po_debug.debug_var(l_log_head, l_progress,'In PO_CHANGE_REQUESTS records deleted=', SQL%rowcount);
5401: po_debug.debug_var(l_log_head, l_progress, 'p_change_request_group_id=', p_change_request_group_id);
5402: END IF;
5403:
5404: ELSE

Line 5410: po_debug.debug_var(l_log_head, l_progress,'In PO_CHANGE_REQUESTS records deleted=', SQL%rowcount);

5406: END IF;
5407: l_progress := '001';
5408:
5409: IF g_debug_stmt THEN
5410: po_debug.debug_var(l_log_head, l_progress,'In PO_CHANGE_REQUESTS records deleted=', SQL%rowcount);
5411: po_debug.debug_var(l_log_head, l_progress, 'p_cancel_table.req_line_id.count=', p_cancel_table.req_line_id.count);
5412: END IF;
5413:
5414:

Line 5473: INSERT INTO po_change_requests

5469: end if;
5470: end if;
5471: */
5472: l_progress := '002';
5473: INSERT INTO po_change_requests
5474: (
5475: change_request_group_id,
5476: change_request_id,
5477: initiator,

Line 5525: po_debug.debug_var(l_log_head, l_progress,'NO of records inderted in po_change_requests =', SQL%rowcount);

5521: l_req_date
5522: );
5523:
5524: IF g_debug_stmt THEN
5525: po_debug.debug_var(l_log_head, l_progress,'NO of records inderted in po_change_requests =', SQL%rowcount);
5526: po_debug.debug_var(l_log_head, l_progress, 'p_change_request_group_id=', p_change_request_group_id);
5527: po_debug.debug_var(l_log_head, l_progress,'l_chn_req_id =', l_chn_req_id );
5528: po_debug.debug_stmt(l_log_head, l_progress,'INITIATOR =REQUESTER' );
5529: po_debug.debug_stmt(l_log_head, l_progress, 'ACTION_TYPE=CANCELLATION' );

Line 5565: *2. If ALL valid, same them into PO_CHANGE_REQUESTS table

5561:
5562: /*-------------------------------------------------------------------------------------------------
5563: *This API is called directly from the UI. It will have PLSQL tables as input, which contain change/cancel requests
5564: *1. Validate the requests
5565: *2. If ALL valid, same them into PO_CHANGE_REQUESTS table
5566: *x_return_status = FND_API.G_RET_STS_SUCCESS => Everything is Valid, and records are saved into change table
5567: * FND_API.G_RET_STS_ERROR => Caught Errors, thus no records are saved into change table
5568: * FND_API.G_RET_STS_UNEXP_ERROR => Unexpected Errors Occur in the API
5569: *x_retMsg will indicate details/location of errors.

Line 5618: DELETE FROM po_change_requests

5614: po_debug.debug_var(l_log_head, l_progress, 'p_req_hdr_id', p_req_hdr_id);
5615: END IF;
5616:
5617:
5618: DELETE FROM po_change_requests
5619: WHERE document_header_id = p_req_hdr_id
5620: AND request_status = 'SYSTEMSAVE'
5621: AND initiator = 'REQUESTER';
5622:

Line 5624: po_debug.debug_var(l_log_head, l_progress,'NO of rows deleted from PO_CHANGE_REQUESTS', SQL%rowcount);

5620: AND request_status = 'SYSTEMSAVE'
5621: AND initiator = 'REQUESTER';
5622:
5623: IF g_debug_stmt THEN
5624: po_debug.debug_var(l_log_head, l_progress,'NO of rows deleted from PO_CHANGE_REQUESTS', SQL%rowcount);
5625: END IF;
5626:
5627:
5628:

Line 5860: po_change_requests pcr,

5856: pcr.document_line_id,
5857: prla.line_location_id,
5858: pcr.change_request_id
5859: FROM
5860: po_change_requests pcr,
5861: po_requisition_lines_all prla
5862: WHERE pcr.action_type = 'CANCELLATION'
5863: AND pcr.change_request_group_id = grp_id
5864: AND pcr.document_line_id = prla.requisition_line_id;

Line 5895: update po_change_requests

5891: FETCH l_cancels_csr INTO
5892: l_req_hdr_id, l_line_id, l_line_location_id, l_chn_req_id;
5893: EXIT WHEN l_cancels_csr%notfound;
5894: /* if(l_line_location_id is null) then
5895: update po_change_requests
5896: set request_status = 'ACCEPTED'
5897: where change_request_id = l_chn_req_id;
5898: else */--since only lines on so is cancellable so wf is always needed
5899: l_workflow_needed := 'Y';

Line 5915: UPDATE po_change_requests

5911:
5912:
5913:
5914:
5915: UPDATE po_change_requests
5916: SET request_status = 'NEW'
5917: WHERE change_request_group_id = p_group_id
5918: AND request_status = 'SYSTEMSAVE';
5919:

Line 6003: SELECT COUNT(*) INTO l_count FROM po_change_requests

5999: -- return Error to the SO (OM) API.
6000:
6001: BEGIN
6002:
6003: SELECT COUNT(*) INTO l_count FROM po_change_requests
6004: WHERE request_status in ( 'NEW' , 'MGR_PRE_APP' , 'MGR_APP')
6005: AND DOCUMENT_TYPE= 'REQ'
6006: AND REQUEST_LEVEL= 'LINE'
6007: AND DOCUMENT_LINE_ID=p_req_line_id;

Line 6021: po_debug.debug_var(l_log_head, l_progress, 'No of po_change_requests on req line', l_count);

6017: IF g_debug_stmt THEN
6018: po_debug.debug_begin(l_log_head);
6019:
6020: po_debug.debug_var(l_log_head, l_progress, 'p_req_line_id', p_req_line_id);
6021: po_debug.debug_var(l_log_head, l_progress, 'No of po_change_requests on req line', l_count);
6022: END IF;
6023:
6024: return;
6025: END IF;

Line 6131: FROM PO_change_requests

6127: -- return Error to the SO (OM) API.
6128:
6129: BEGIN
6130: SELECT count(*) INTO l_count
6131: FROM PO_change_requests
6132: WHERE request_status in ( 'NEW' , 'MGR_PRE_APP' , 'MGR_APP')
6133: AND DOCUMENT_TYPE= 'REQ'
6134: AND REQUEST_LEVEL= 'LINE'
6135: AND DOCUMENT_LINE_ID=p_req_line_id;

Line 6260: l_request_reason po_change_requests.request_reason%TYPE;

6256: l_old_amount NUMBER;
6257: l_new_amount NUMBER;
6258: l_new_price NUMBER;
6259: l_distribution_id NUMBER;
6260: l_request_reason po_change_requests.request_reason%TYPE;
6261: l_cancel_errtable po_req_change_err_table;
6262: l_cal_disttax_status VARCHAR2(1);
6263: l_item_id NUMBER;
6264: l_req_uom po_requisition_lines_all.unit_meas_lookup_code%TYPE;

Line 6287: FROM po_change_requests

6283: document_distribution_id,
6284: new_quantity,
6285: new_need_by_date,
6286: request_reason
6287: FROM po_change_requests
6288: WHERE change_request_group_id = grp_id
6289: AND action_type = 'MODIFICATION';
6290:
6291: CURSOR l_cancel_csr(grp_id NUMBER) IS

Line 6296: FROM po_change_requests

6292: SELECT
6293: document_header_id,
6294: document_line_id,
6295: request_reason
6296: FROM po_change_requests
6297: WHERE change_request_group_id = grp_id
6298: AND action_type = 'CANCELLATION';
6299:
6300:

Line 6308: po_change_requests

6304: document_distribution_id dist_id,
6305: document_header_id hdr_id,
6306: document_num req_num
6307: FROM
6308: po_change_requests
6309: WHERE
6310: change_request_group_id = grp_id AND
6311: new_quantity IS NOT NULL AND
6312: action_type = 'MODIFICATION'

Line 6322: po_change_requests pcr,

6318: prha.segment1 req_num
6319: FROM
6320: po_req_distributions_all prda,
6321: po_requisition_lines_all prla,
6322: po_change_requests pcr,
6323: po_requisition_headers_all prha
6324: WHERE
6325: prha.requisition_header_id = prla.requisition_header_id AND
6326: prla.requisition_line_id = prda.requisition_line_id AND

Line 6337: po_change_requests pcr

6333: CURSOR l_changed_req_dists_csr(grp_id NUMBER) IS
6334: SELECT -- any quantity change
6335: pcr.document_distribution_id
6336: FROM
6337: po_change_requests pcr
6338: WHERE
6339: pcr.change_request_group_id = grp_id AND
6340: pcr.new_quantity IS NOT NULL AND
6341: pcr.action_type = 'MODIFICATION';

Line 6349: po_change_requests pcr,

6345: /* CURSOR l_changed_req_dists_csr(grp_id NUMBER) IS
6346: SELECT -- any quantity or amount change
6347: prda.distribution_id
6348: FROM
6349: po_change_requests pcr,
6350: po_req_distributions_all prda
6351: WHERE
6352: pcr.change_request_group_id = grp_id AND
6353: pcr.new_quantity IS NOT NULL AND

Line 6360: po_change_requests pcr,

6356: ; UNION
6357: SELECT -- select distributions that are effected with any line change
6358: prda.distribution_id
6359: FROM
6360: po_change_requests pcr,
6361: po_requisition_lines_all prla,
6362: po_req_distributions_all prda
6363: WHERE
6364: pcr.change_request_group_id = grp_id AND

Line 6379: po_change_requests pcr,

6375: prla.requisition_header_id,
6376: prla.unit_price,
6377: nvl(pcr.new_quantity, pcr.old_quantity)
6378: FROM
6379: po_change_requests pcr,
6380: po_req_distributions_all prda,
6381: po_requisition_lines_all prla
6382: WHERE
6383: pcr.change_request_group_id = grp_id AND

Line 6757: UPDATE po_change_requests

6753: po_debug.debug_stmt(l_log_head, x_retmsg, 'all change requests are valid, updating status to "NEW" and kick off workflow');
6754: END IF;
6755:
6756:
6757: UPDATE po_change_requests
6758: SET request_status = 'NEW'
6759: WHERE change_request_group_id = p_group_id
6760: AND request_status = 'SYSTEMSAVE';
6761:

Line 6983: SELECT COUNT(*) INTO l_count FROM po_change_requests

6979: -- return Error to the SO (OM) API.
6980:
6981: BEGIN
6982:
6983: SELECT COUNT(*) INTO l_count FROM po_change_requests
6984: WHERE request_status in ( 'NEW' , 'MGR_PRE_APP' , 'MGR_APP')
6985: AND DOCUMENT_TYPE= 'REQ'
6986: AND REQUEST_LEVEL= 'LINE'
6987: AND DOCUMENT_LINE_ID=p_req_line_id;

Line 7001: po_debug.debug_var(l_log_head, l_progress, 'No of po_change_requests on req line', l_count);

6997: IF g_debug_stmt THEN
6998: po_debug.debug_begin(l_log_head);
6999:
7000: po_debug.debug_var(l_log_head, l_progress, 'p_req_line_id', p_req_line_id);
7001: po_debug.debug_var(l_log_head, l_progress, 'No of po_change_requests on req line', l_count);
7002: END IF;
7003:
7004: return;
7005: END IF;

Line 7130: FROM PO_change_requests

7126: -- return Error to the SO (OM) API.
7127:
7128: BEGIN
7129: SELECT count(*) INTO l_count
7130: FROM PO_change_requests
7131: WHERE request_status in ( 'NEW' , 'MGR_PRE_APP' , 'MGR_APP')
7132: AND DOCUMENT_TYPE= 'REQ'
7133: AND REQUEST_LEVEL= 'LINE'
7134: AND DOCUMENT_LINE_ID=p_req_line_id;