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 1526: p_po_change_requests => l_po_change_table,

1522: x_msg_data => l_return_msg,
1523: p_po_header_id => l_current_hdr_id,
1524: p_po_release_id => l_current_rel_id,
1525: p_revision_num => l_current_rev_num,
1526: p_po_change_requests => l_po_change_table,
1527: x_online_report_id => l_online_report_id,
1528: x_pos_errors => l_pos_errors_tbl,
1529: x_doc_check_error_msg => l_doc_check_rec_type);
1530: x_retmsg := 'VC0032';

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

1595: END IF;
1596: END validate_changes;
1597:
1598: /*
1599: *Simple API to insert change records into PO_CHANGE_REQUESTS table
1600: */
1601: PROCEDURE insert_reqchange(p_change_table change_tbl_type,
1602: p_chn_req_grp_id NUMBER)
1603: IS

Line 1613: INSERT INTO po_change_requests

1609: l_req_user_id := fnd_global.user_id;
1610: FOR i IN 1..p_change_table.count
1611: LOOP
1612:
1613: INSERT INTO po_change_requests
1614: (
1615: change_request_group_id,
1616: change_request_id,
1617: initiator,

Line 1799: FROM po_change_requests

1795: SELECT
1796: change_request_id,
1797: new_quantity,
1798: document_distribution_id
1799: FROM po_change_requests
1800: WHERE change_request_group_id = grp_id
1801: AND document_line_id = line_id
1802: AND request_level = 'DISTRIBUTION';
1803:

Line 1806: FROM po_change_requests

1802: AND request_level = 'DISTRIBUTION';
1803:
1804: CURSOR l_line_id_with_qty_chn_csr(grp_id NUMBER) IS
1805: SELECT DISTINCT document_line_id
1806: FROM po_change_requests
1807: WHERE change_request_group_id = grp_id
1808: AND new_quantity IS NOT NULL;
1809:
1810: CURSOR l_line_id_with_price_chn_csr(grp_id NUMBER) IS

Line 1815: FROM po_change_requests

1811: SELECT
1812: change_request_id,
1813: document_line_id,
1814: new_price
1815: FROM po_change_requests
1816: WHERE change_request_group_id = grp_id
1817: AND new_price IS NOT NULL;
1818:
1819: CURSOR l_dist_id_csr(line_id NUMBER) IS

Line 1832: FROM po_change_requests

1828: SELECT
1829: change_request_id,
1830: recoverable_tax,
1831: nonrecoverable_tax
1832: FROM po_change_requests
1833: WHERE change_request_group_id = p_chn_req_grp_id
1834: AND document_distribution_id = l_dist_id;
1835:
1836:

Line 1850: FROM po_change_requests

1846:
1847: BEGIN
1848: SELECT nvl(new_currency_unit_price, new_price)
1849: INTO l_price
1850: FROM po_change_requests
1851: WHERE change_request_group_id = p_chn_req_grp_id
1852: AND document_line_id = l_line_id
1853: AND request_level = 'LINE'
1854: AND new_price IS NOT NULL;

Line 1877: UPDATE po_change_requests

1873: FROM po_req_distributions_all
1874: WHERE distribution_id = l_dist_id;
1875: calculate_disttax(1.0, l_cal_disttax_status, l_dist_id, l_price, l_quantity, NULL, l_rec_tax, l_nonrec_tax);
1876: END IF;
1877: UPDATE po_change_requests
1878: SET recoverable_tax = l_rec_tax,
1879: nonrecoverable_tax = l_nonrec_tax
1880: WHERE change_request_id = l_id;
1881:

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

1912: l_dist_rec_tax,
1913: l_dist_nonrec_tax;
1914: CLOSE l_dist_exist_chn_csr;
1915:
1916: IF(l_temp_id IS NOT NULL) THEN -- Distribution exist in po_change_requests table
1917: l_rec_tax := l_rec_tax + l_dist_rec_tax;
1918: l_nonrec_tax := l_nonrec_tax + l_dist_nonrec_tax;
1919: ELSE -- Distribution does NOT exist in change table, thus need to calculate
1920: 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 1929: UPDATE po_change_requests

1925: END LOOP;
1926: CLOSE l_dist_id_csr;
1927:
1928: l_progress := '003';
1929: UPDATE po_change_requests
1930: SET recoverable_tax = l_rec_tax,
1931: nonrecoverable_tax = l_nonrec_tax
1932: WHERE change_request_id = l_id;
1933: END LOOP;

Line 1968: FROM po_change_requests

1964: l_temp_id NUMBER;
1965:
1966: CURSOR l_line_id_with_qty_chn_csr(grp_id NUMBER) IS
1967: SELECT DISTINCT document_line_id
1968: FROM po_change_requests
1969: WHERE change_request_group_id = grp_id
1970: AND new_quantity IS NOT NULL;
1971:
1972:

Line 1986: FROM po_change_requests

1982: SELECT
1983: change_request_id,
1984: recoverable_tax,
1985: nonrecoverable_tax
1986: FROM po_change_requests
1987: WHERE change_request_group_id = p_chn_req_grp_id
1988: AND document_distribution_id = l_dist_id;
1989:
1990: CURSOR l_dist_with_chn_csr(grp_id NUMBER, line_id NUMBER) IS

Line 1995: FROM po_change_requests

1991: SELECT
1992: change_request_id,
1993: new_quantity,
1994: document_distribution_id
1995: FROM po_change_requests
1996: WHERE change_request_group_id = grp_id
1997: AND document_line_id = line_id
1998: AND request_level = 'LINE';
1999:

Line 2053: UPDATE po_change_requests

2049: po_debug.debug_stmt(l_log_head, l_progress,'Updating taxes in po_change_request table');
2050: END IF;
2051:
2052:
2053: UPDATE po_change_requests
2054: SET recoverable_tax = l_rec_tax,
2055: nonrecoverable_tax = l_nonrec_tax
2056: WHERE change_request_id = l_id;
2057:

Line 2099: l_po_request_level po_change_requests.request_level%TYPE;

2095: l_po_distribution_id NUMBER;
2096: l_po_distribution_number NUMBER;
2097: l_recoverable_tax NUMBER;
2098: l_non_recoverable_tax NUMBER;
2099: l_po_request_level po_change_requests.request_level%TYPE;
2100: l_req_request_level po_change_requests.request_level%TYPE;
2101: l_req_header_id NUMBER;
2102: l_req_num po_requisition_headers_all.segment1%TYPE;
2103: l_req_line_number NUMBER;

Line 2100: l_req_request_level po_change_requests.request_level%TYPE;

2096: l_po_distribution_number NUMBER;
2097: l_recoverable_tax NUMBER;
2098: l_non_recoverable_tax NUMBER;
2099: l_po_request_level po_change_requests.request_level%TYPE;
2100: l_req_request_level po_change_requests.request_level%TYPE;
2101: l_req_header_id NUMBER;
2102: l_req_num po_requisition_headers_all.segment1%TYPE;
2103: l_req_line_number NUMBER;
2104: l_req_dist_number NUMBER;

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

3611:
3612: /*-------------------------------------------------------------------------------------------------
3613: *This API is called directly from the UI. It will have PLSQL tables as input, which contain change/cancel requests
3614: *1. Validate the requests
3615: *2. If ALL valid, same them into PO_CHANGE_REQUESTS table
3616: *x_return_status = FND_API.G_RET_STS_SUCCESS => Everything is Valid, and records are saved into change table
3617: * FND_API.G_RET_STS_ERROR => Caught Errors, thus no records are saved into change table
3618: * FND_API.G_RET_STS_UNEXP_ERROR => Unexpected Errors Occur in the API
3619: *x_retMsg will indicate details/location of errors.

Line 3653: DELETE FROM po_change_requests

3649:
3650:
3651: BEGIN
3652:
3653: DELETE FROM po_change_requests
3654: WHERE document_header_id = p_req_hdr_id
3655: AND request_status = 'SYSTEMSAVE'
3656: AND initiator = 'REQUESTER';
3657:

Line 3836: **simply insert records into PO_CHANGE_REQUESTS table

3832:
3833: /*--------------------------------------------------------------
3834: **Save_ReqCancel: takes in a PLSQL table as input, containing
3835: **cancellation request. No Validation is done here. This API
3836: **simply insert records into PO_CHANGE_REQUESTS table
3837: ---------------------------------------------------------------*/
3838: PROCEDURE save_reqcancel(p_api_version IN NUMBER,
3839: x_return_status OUT NOCOPY VARCHAR2,
3840: p_req_hdr_id IN NUMBER,

Line 3860: l_po_num po_change_requests.ref_po_num%TYPE;

3856: l_req_price NUMBER;
3857: l_req_currency_price NUMBER;
3858: l_req_quantity NUMBER;
3859: l_req_date DATE;
3860: l_po_num po_change_requests.ref_po_num%TYPE;
3861: l_po_release_num NUMBER;
3862: BEGIN
3863: x_retmsg := '000';
3864: l_req_user_id := fnd_global.user_id;

Line 3869: DELETE FROM po_change_requests

3865:
3866:
3867: IF(p_grp_id IS NULL) THEN
3868: SELECT po_chg_request_seq.nextval INTO p_change_request_group_id FROM dual;
3869: DELETE FROM po_change_requests
3870: WHERE document_header_id = p_req_hdr_id
3871: AND initiator = 'REQUESTER'
3872: AND request_status = 'SYSTEMSAVE';
3873: ELSE

Line 3937: INSERT INTO po_change_requests

3933: WHERE po_release_id = l_po_release_id;
3934: END IF;
3935: END IF;
3936: x_retmsg := '002';
3937: INSERT INTO po_change_requests
3938: (
3939: change_request_group_id,
3940: change_request_id,
3941: initiator,

Line 4084: l_request_reason po_change_requests.request_reason%TYPE;

4080: l_old_amount NUMBER;
4081: l_new_amount NUMBER;
4082: l_price_break VARCHAR2(1);
4083:
4084: l_request_reason po_change_requests.request_reason%TYPE;
4085: l_cancel_errtable po_req_change_err_table;
4086: l_cal_disttax_status VARCHAR2(1);
4087: l_item_id NUMBER;
4088: l_req_uom po_requisition_lines_all.unit_meas_lookup_code%TYPE;

Line 4112: FROM po_change_requests

4108: new_quantity,
4109: new_price,
4110: new_need_by_date,
4111: request_reason
4112: FROM po_change_requests
4113: WHERE change_request_group_id = grp_id
4114: AND action_type = 'MODIFICATION';
4115:
4116: CURSOR l_cancel_csr(grp_id NUMBER) IS

Line 4121: FROM po_change_requests

4117: SELECT
4118: document_header_id,
4119: document_line_id,
4120: request_reason
4121: FROM po_change_requests
4122: WHERE change_request_group_id = grp_id
4123: AND action_type = 'CANCELLATION';
4124:
4125:

Line 4133: po_change_requests

4129: document_distribution_id dist_id,
4130: document_header_id hdr_id,
4131: document_num req_num
4132: FROM
4133: po_change_requests
4134: WHERE
4135: change_request_group_id = grp_id AND
4136: (new_quantity IS NOT NULL OR new_amount IS NOT NULL) AND
4137: action_type = 'MODIFICATION'

Line 4147: po_change_requests pcr,

4143: prha.segment1 req_num
4144: FROM
4145: po_req_distributions_all prda,
4146: po_requisition_lines_all prla,
4147: po_change_requests pcr,
4148: po_requisition_headers_all prha
4149: WHERE
4150: prha.requisition_header_id = prla.requisition_header_id AND
4151: prla.requisition_line_id = prda.requisition_line_id AND

Line 4162: po_change_requests pcr,

4158: CURSOR l_changed_po_dists_csr(grp_id NUMBER) IS
4159: SELECT -- any quantity or amount change
4160: pda.po_distribution_id
4161: FROM
4162: po_change_requests pcr,
4163: po_req_distributions_all prda,
4164: po_distributions_all pda,
4165: po_headers_all pha
4166: WHERE

Line 4178: po_change_requests pcr,

4174: UNION
4175: SELECT -- select distributions that are effected with any line change
4176: pda.po_distribution_id
4177: FROM
4178: po_change_requests pcr,
4179: po_requisition_lines_all prla,
4180: po_req_distributions_all prda,
4181: po_distributions_all pda,
4182: po_headers_all pha

Line 4198: po_change_requests pcr,

4194: CURSOR l_changed_rel_dists_csr(grp_id NUMBER) IS
4195: SELECT -- any quantity or amount change
4196: pda.po_distribution_id
4197: FROM
4198: po_change_requests pcr,
4199: po_req_distributions_all prda,
4200: po_distributions_all pda,
4201: po_requisition_lines_all prla,
4202: po_line_locations_all plla

Line 4216: po_change_requests pcr,

4212: UNION -- select distributions that are effected with any line change
4213: SELECT
4214: pda.po_distribution_id
4215: FROM
4216: po_change_requests pcr,
4217: po_requisition_lines_all prla,
4218: po_req_distributions_all prda,
4219: po_distributions_all pda,
4220: po_line_locations_all plla

Line 4412: FROM po_change_requests

4408: BEGIN
4409:
4410: SELECT new_amount
4411: INTO l_new_amount
4412: FROM po_change_requests
4413: WHERE
4414: change_request_group_id = p_group_id AND
4415: document_distribution_id = l_req_dist_id AND
4416: new_amount IS NOT NULL;

Line 4427: FROM po_change_requests

4423: l_price_break := 'N';
4424: BEGIN
4425: SELECT new_quantity
4426: INTO l_new_quantity
4427: FROM po_change_requests
4428: WHERE change_request_group_id = p_group_id
4429: AND document_distribution_id = l_req_dist_id
4430: AND new_quantity IS NOT NULL;
4431:

Line 4453: FROM po_change_requests

4449: --Obtain most recent price
4450: BEGIN
4451: SELECT new_price
4452: INTO l_new_price
4453: FROM po_change_requests
4454: WHERE change_request_group_id = p_group_id
4455: AND document_line_id = l_req_line_id
4456: AND new_price IS NOT NULL;
4457:

Line 4466: FROM po_change_requests

4462: EXCEPTION WHEN no_data_found THEN
4463: BEGIN
4464: SELECT new_need_by_date
4465: INTO l_new_need_by_date
4466: FROM po_change_requests
4467: WHERE change_request_group_id = p_group_id
4468: AND document_line_id = l_req_line_id
4469: AND new_need_by_date IS NOT NULL;
4470:

Line 4693: UPDATE po_change_requests

4689: /*
4690: * If all requests are valid, update status to "NEW" and kick off workflow
4691: */
4692: IF(x_errtable.req_line_id.count = 0) THEN
4693: UPDATE po_change_requests
4694: SET request_status = 'NEW'
4695: WHERE change_request_group_id = p_group_id
4696: AND request_status = 'SYSTEMSAVE';
4697:

Line 4759: po_change_requests pcr,

4755: pcr.document_line_id,
4756: prla.line_location_id,
4757: pcr.change_request_id
4758: FROM
4759: po_change_requests pcr,
4760: po_requisition_lines_all prla
4761: WHERE pcr.action_type = 'CANCELLATION'
4762: AND pcr.change_request_group_id = grp_id
4763: AND pcr.document_line_id = prla.requisition_line_id;

Line 4791: UPDATE po_change_requests

4787: FETCH l_cancels_csr INTO
4788: l_req_hdr_id, l_line_id, l_line_location_id, l_chn_req_id;
4789: EXIT WHEN l_cancels_csr%notfound;
4790: IF(l_line_location_id IS NULL) THEN
4791: UPDATE po_change_requests
4792: SET request_status = 'ACCEPTED'
4793: WHERE change_request_id = l_chn_req_id;
4794: ELSE
4795: l_workflow_needed := 'Y';

Line 4812: UPDATE po_change_requests

4808:
4809: --If all requests are valid, update status to "NEW", and kick off Workflow
4810:
4811: IF(p_errtable.req_line_id.count = 0) THEN
4812: UPDATE po_change_requests
4813: SET request_status = 'NEW'
4814: WHERE change_request_group_id = p_group_id
4815: AND request_status = 'SYSTEMSAVE';
4816:

Line 5195: **simply insert records into PO_CHANGE_REQUESTS table

5191:
5192: /*--------------------------------------------------------------
5193: **Save_IReqCancel: takes in a PLSQL table as input, containing
5194: **cancellation request. No Validation is done here. This API
5195: **simply insert records into PO_CHANGE_REQUESTS table
5196: ---------------------------------------------------------------*/
5197:
5198:
5199: PROCEDURE save_ireqcancel(p_api_version IN NUMBER,

Line 5221: l_po_num po_change_requests.ref_po_num%TYPE;

5217: l_req_price NUMBER;
5218: l_req_currency_price NUMBER;
5219: l_req_quantity NUMBER;
5220: l_req_date DATE;
5221: l_po_num po_change_requests.ref_po_num%TYPE;
5222: l_po_release_num NUMBER;
5223: l_log_head CONSTANT VARCHAR2(100) := c_log_head || l_api_name;
5224:
5225: BEGIN

Line 5236: DELETE FROM po_change_requests

5232: END IF;
5233:
5234: IF(p_grp_id IS NULL) THEN
5235: SELECT po_chg_request_seq.nextval INTO p_change_request_group_id FROM dual;
5236: DELETE FROM po_change_requests
5237: WHERE document_header_id = p_req_hdr_id
5238: AND initiator = 'REQUESTER'
5239: AND request_status = 'SYSTEMSAVE';
5240:

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

5238: AND initiator = 'REQUESTER'
5239: AND request_status = 'SYSTEMSAVE';
5240:
5241: IF g_debug_stmt THEN
5242: po_debug.debug_var(l_log_head, l_progress,'In PO_CHANGE_REQUESTS records deleted=', SQL%rowcount);
5243: po_debug.debug_var(l_log_head, l_progress, 'p_change_request_group_id=', p_change_request_group_id);
5244: END IF;
5245:
5246: ELSE

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

5248: END IF;
5249: l_progress := '001';
5250:
5251: IF g_debug_stmt THEN
5252: po_debug.debug_var(l_log_head, l_progress,'In PO_CHANGE_REQUESTS records deleted=', SQL%rowcount);
5253: po_debug.debug_var(l_log_head, l_progress, 'p_cancel_table.req_line_id.count=', p_cancel_table.req_line_id.count);
5254: END IF;
5255:
5256:

Line 5315: INSERT INTO po_change_requests

5311: end if;
5312: end if;
5313: */
5314: l_progress := '002';
5315: INSERT INTO po_change_requests
5316: (
5317: change_request_group_id,
5318: change_request_id,
5319: initiator,

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

5363: l_req_date
5364: );
5365:
5366: IF g_debug_stmt THEN
5367: po_debug.debug_var(l_log_head, l_progress,'NO of records inderted in po_change_requests =', SQL%rowcount);
5368: po_debug.debug_var(l_log_head, l_progress, 'p_change_request_group_id=', p_change_request_group_id);
5369: po_debug.debug_var(l_log_head, l_progress,'l_chn_req_id =', l_chn_req_id );
5370: po_debug.debug_stmt(l_log_head, l_progress,'INITIATOR =REQUESTER' );
5371: po_debug.debug_stmt(l_log_head, l_progress, 'ACTION_TYPE=CANCELLATION' );

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

5403:
5404: /*-------------------------------------------------------------------------------------------------
5405: *This API is called directly from the UI. It will have PLSQL tables as input, which contain change/cancel requests
5406: *1. Validate the requests
5407: *2. If ALL valid, same them into PO_CHANGE_REQUESTS table
5408: *x_return_status = FND_API.G_RET_STS_SUCCESS => Everything is Valid, and records are saved into change table
5409: * FND_API.G_RET_STS_ERROR => Caught Errors, thus no records are saved into change table
5410: * FND_API.G_RET_STS_UNEXP_ERROR => Unexpected Errors Occur in the API
5411: *x_retMsg will indicate details/location of errors.

Line 5460: DELETE FROM po_change_requests

5456: po_debug.debug_var(l_log_head, l_progress, 'p_req_hdr_id', p_req_hdr_id);
5457: END IF;
5458:
5459:
5460: DELETE FROM po_change_requests
5461: WHERE document_header_id = p_req_hdr_id
5462: AND request_status = 'SYSTEMSAVE'
5463: AND initiator = 'REQUESTER';
5464:

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

5462: AND request_status = 'SYSTEMSAVE'
5463: AND initiator = 'REQUESTER';
5464:
5465: IF g_debug_stmt THEN
5466: po_debug.debug_var(l_log_head, l_progress,'NO of rows deleted from PO_CHANGE_REQUESTS', SQL%rowcount);
5467: END IF;
5468:
5469:
5470:

Line 5702: po_change_requests pcr,

5698: pcr.document_line_id,
5699: prla.line_location_id,
5700: pcr.change_request_id
5701: FROM
5702: po_change_requests pcr,
5703: po_requisition_lines_all prla
5704: WHERE pcr.action_type = 'CANCELLATION'
5705: AND pcr.change_request_group_id = grp_id
5706: AND pcr.document_line_id = prla.requisition_line_id;

Line 5737: update po_change_requests

5733: FETCH l_cancels_csr INTO
5734: l_req_hdr_id, l_line_id, l_line_location_id, l_chn_req_id;
5735: EXIT WHEN l_cancels_csr%notfound;
5736: /* if(l_line_location_id is null) then
5737: update po_change_requests
5738: set request_status = 'ACCEPTED'
5739: where change_request_id = l_chn_req_id;
5740: else */--since only lines on so is cancellable so wf is always needed
5741: l_workflow_needed := 'Y';

Line 5757: UPDATE po_change_requests

5753:
5754:
5755:
5756:
5757: UPDATE po_change_requests
5758: SET request_status = 'NEW'
5759: WHERE change_request_group_id = p_group_id
5760: AND request_status = 'SYSTEMSAVE';
5761:

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

5847: -- return Error to the SO (OM) API.
5848:
5849: BEGIN
5850:
5851: SELECT COUNT(*) INTO l_count FROM po_change_requests
5852: WHERE request_status in ( 'NEW' , 'MGR_PRE_APP' , 'MGR_APP')
5853: AND DOCUMENT_TYPE= 'REQ'
5854: AND REQUEST_LEVEL= 'LINE'
5855: AND DOCUMENT_LINE_ID=p_req_line_id;

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

5865: IF g_debug_stmt THEN
5866: po_debug.debug_begin(l_log_head);
5867: po_debug.debug_var(l_log_head, l_progress, 'p_req_hdr_id', p_req_hdr_id);
5868: po_debug.debug_var(l_log_head, l_progress, 'p_req_line_id', p_req_line_id);
5869: po_debug.debug_var(l_log_head, l_progress, 'No of po_change_requests on req line', l_count);
5870: END IF;
5871:
5872: return;
5873: END IF;

Line 6080: FROM PO_change_requests

6076: -- return Error to the SO (OM) API.
6077:
6078: BEGIN
6079: SELECT count(*) INTO l_count
6080: FROM PO_change_requests
6081: WHERE request_status in ( 'NEW' , 'MGR_PRE_APP' , 'MGR_APP')
6082: AND DOCUMENT_TYPE= 'REQ'
6083: AND REQUEST_LEVEL= 'LINE'
6084: AND DOCUMENT_LINE_ID=p_req_line_id;

Line 6209: l_request_reason po_change_requests.request_reason%TYPE;

6205: l_old_amount NUMBER;
6206: l_new_amount NUMBER;
6207: l_new_price NUMBER;
6208: l_distribution_id NUMBER;
6209: l_request_reason po_change_requests.request_reason%TYPE;
6210: l_cancel_errtable po_req_change_err_table;
6211: l_cal_disttax_status VARCHAR2(1);
6212: l_item_id NUMBER;
6213: l_req_uom po_requisition_lines_all.unit_meas_lookup_code%TYPE;

Line 6236: FROM po_change_requests

6232: document_distribution_id,
6233: new_quantity,
6234: new_need_by_date,
6235: request_reason
6236: FROM po_change_requests
6237: WHERE change_request_group_id = grp_id
6238: AND action_type = 'MODIFICATION';
6239:
6240: CURSOR l_cancel_csr(grp_id NUMBER) IS

Line 6245: FROM po_change_requests

6241: SELECT
6242: document_header_id,
6243: document_line_id,
6244: request_reason
6245: FROM po_change_requests
6246: WHERE change_request_group_id = grp_id
6247: AND action_type = 'CANCELLATION';
6248:
6249:

Line 6257: po_change_requests

6253: document_distribution_id dist_id,
6254: document_header_id hdr_id,
6255: document_num req_num
6256: FROM
6257: po_change_requests
6258: WHERE
6259: change_request_group_id = grp_id AND
6260: new_quantity IS NOT NULL AND
6261: action_type = 'MODIFICATION'

Line 6271: po_change_requests pcr,

6267: prha.segment1 req_num
6268: FROM
6269: po_req_distributions_all prda,
6270: po_requisition_lines_all prla,
6271: po_change_requests pcr,
6272: po_requisition_headers_all prha
6273: WHERE
6274: prha.requisition_header_id = prla.requisition_header_id AND
6275: prla.requisition_line_id = prda.requisition_line_id AND

Line 6286: po_change_requests pcr

6282: CURSOR l_changed_req_dists_csr(grp_id NUMBER) IS
6283: SELECT -- any quantity change
6284: pcr.document_distribution_id
6285: FROM
6286: po_change_requests pcr
6287: WHERE
6288: pcr.change_request_group_id = grp_id AND
6289: pcr.new_quantity IS NOT NULL AND
6290: pcr.action_type = 'MODIFICATION';

Line 6298: po_change_requests pcr,

6294: /* CURSOR l_changed_req_dists_csr(grp_id NUMBER) IS
6295: SELECT -- any quantity or amount change
6296: prda.distribution_id
6297: FROM
6298: po_change_requests pcr,
6299: po_req_distributions_all prda
6300: WHERE
6301: pcr.change_request_group_id = grp_id AND
6302: pcr.new_quantity IS NOT NULL AND

Line 6309: po_change_requests pcr,

6305: ; UNION
6306: SELECT -- select distributions that are effected with any line change
6307: prda.distribution_id
6308: FROM
6309: po_change_requests pcr,
6310: po_requisition_lines_all prla,
6311: po_req_distributions_all prda
6312: WHERE
6313: pcr.change_request_group_id = grp_id AND

Line 6328: po_change_requests pcr,

6324: prla.requisition_header_id,
6325: prla.unit_price,
6326: nvl(pcr.new_quantity, pcr.old_quantity)
6327: FROM
6328: po_change_requests pcr,
6329: po_req_distributions_all prda,
6330: po_requisition_lines_all prla
6331: WHERE
6332: pcr.change_request_group_id = grp_id AND

Line 6706: UPDATE po_change_requests

6702: po_debug.debug_stmt(l_log_head, x_retmsg, 'all change requests are valid, updating status to "NEW" and kick off workflow');
6703: END IF;
6704:
6705:
6706: UPDATE po_change_requests
6707: SET request_status = 'NEW'
6708: WHERE change_request_group_id = p_group_id
6709: AND request_status = 'SYSTEMSAVE';
6710: