[Home] [Help]
83: l_new_qty NUMBER;
84: l_new_po_qty NUMBER;
85: l_old_price NUMBER;
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;
95: l_source_doc_header_id NUMBER;
96: l_source_doc_line_num NUMBER;
97: l_deliver_to_loc_id NUMBER;
98: l_destination_org_id NUMBER;
99: l_req_currency_code po_requisition_lines_all.currency_code%TYPE;
100: l_req_rate_type po_requisition_lines_all.rate_type%TYPE;
101: l_org_id NUMBER;
102: l_creation_date DATE;
103: l_supplier_id NUMBER;
96: l_source_doc_line_num NUMBER;
97: l_deliver_to_loc_id NUMBER;
98: l_destination_org_id NUMBER;
99: l_req_currency_code po_requisition_lines_all.currency_code%TYPE;
100: l_req_rate_type po_requisition_lines_all.rate_type%TYPE;
101: l_org_id NUMBER;
102: l_creation_date DATE;
103: l_supplier_id NUMBER;
104: l_supplier_site_id NUMBER;
104: l_supplier_site_id NUMBER;
105: l_order_header_id NUMBER;
106: l_order_line_id NUMBER;
107: l_line_type_id NUMBER;
108: l_item_revision po_requisition_lines_all.item_revision%TYPE;
109: l_item_id NUMBER;
110: l_category_id NUMBER;
111: l_supplier_item_num po_requisition_lines_all.supplier_ref_number%TYPE;
112: l_in_price NUMBER;
107: l_line_type_id NUMBER;
108: l_item_revision po_requisition_lines_all.item_revision%TYPE;
109: l_item_id NUMBER;
110: l_category_id NUMBER;
111: l_supplier_item_num po_requisition_lines_all.supplier_ref_number%TYPE;
112: l_in_price NUMBER;
113:
114: -- output values
115: l_new_base_unit_price NUMBER;
115: l_new_base_unit_price NUMBER;
116: l_new_price NUMBER;
117: l_new_curr_price NUMBER;
118: l_discount NUMBER;
119: l_currency_code po_requisition_lines_all.currency_code%TYPE;
120: l_rate_type po_requisition_lines_all.rate_type%TYPE;
121: l_rate_date DATE;
122: l_rate NUMBER;
123: l_price_break_id NUMBER;
116: l_new_price NUMBER;
117: l_new_curr_price NUMBER;
118: l_discount NUMBER;
119: l_currency_code po_requisition_lines_all.currency_code%TYPE;
120: l_rate_type po_requisition_lines_all.rate_type%TYPE;
121: l_rate_date DATE;
122: l_rate NUMBER;
123: l_price_break_id NUMBER;
124:
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
139: AND prla.blanket_po_header_id IS NOT NULL;
209: l_quantity_received,
210: l_accrue_on_receipt_flag,
211: l_quantity_billed
212: FROM
213: po_requisition_lines_all prla,
214: po_line_locations_all plla
215: WHERE prla.requisition_line_id = l_req_line_id
216: AND prla.line_location_id = plla.line_location_id;
217:
396: l_req_user_id NUMBER;
397: l_req_header_id NUMBER;
398: l_req_num po_requisition_headers_all.segment1%TYPE;
399: l_requester_id NUMBER;
400: l_matching_basis po_requisition_lines_all.matching_basis%TYPE;
401: l_old_amount NUMBER;
402: l_new_amount NUMBER;
403: l_old_cur_amount NUMBER;
404: l_new_cur_amount NUMBER;
445: l_progress := '001';
446:
447: SELECT matching_basis, quantity, amount, currency_amount
448: INTO l_matching_basis, l_old_quantity, l_old_amount, l_old_cur_amount
449: FROM po_requisition_lines_all
450: WHERE requisition_line_id = l_line_id;
451:
452: -- handle amount based lines
453: IF (l_matching_basis = 'AMOUNT') THEN
692: FROM
693: po_lines_all pla,
694: po_line_locations_all plla,
695: po_distributions_all pda,
696: po_requisition_lines_all prla,
697: po_req_distributions_all prda
698: WHERE
699: pla.po_header_id = p_header_id
700: AND pla.po_line_id = p_po_change_table(s).document_line_id
718: l_err_req_dist_id
719: FROM
720: po_line_locations_all plla,
721: po_distributions_all pda,
722: po_requisition_lines_all prla,
723: po_req_distributions_all prda
724: WHERE
725: plla.po_release_id = p_release_id
726: AND plla.line_location_id = p_po_change_table(s).document_line_location_id
789: FROM
790: po_lines_all pla,
791: po_line_locations_all plla,
792: po_distributions_all pda,
793: po_requisition_lines_all prla,
794: po_req_distributions_all prda
795: WHERE
796: pla.po_header_id = p_header_id
797: AND pla.po_line_id = p_po_change_table(s).document_line_id
815: l_err_req_dist_id
816: FROM
817: po_line_locations_all plla,
818: po_distributions_all pda,
819: po_requisition_lines_all prla,
820: po_req_distributions_all prda
821: WHERE
822: plla.po_release_id = p_release_id
823: AND plla.line_location_id = p_po_change_table(s).document_line_location_id
1153: *the new functionally currency price.
1154: */
1155: FUNCTION calculate_newunitprice(p_req_line_id NUMBER, p_new_price NUMBER) RETURN NUMBER
1156: IS
1157: l_transaction_currency po_requisition_lines_all.currency_code%TYPE;
1158: l_functional_currency gl_sets_of_books.currency_code%TYPE;
1159: l_rate_type po_requisition_lines_all.rate_type%TYPE;
1160: l_conversion_date DATE;
1161: l_rate NUMBER;
1155: FUNCTION calculate_newunitprice(p_req_line_id NUMBER, p_new_price NUMBER) RETURN NUMBER
1156: IS
1157: l_transaction_currency po_requisition_lines_all.currency_code%TYPE;
1158: l_functional_currency gl_sets_of_books.currency_code%TYPE;
1159: l_rate_type po_requisition_lines_all.rate_type%TYPE;
1160: l_conversion_date DATE;
1161: l_rate NUMBER;
1162: l_unit_price NUMBER;
1163: l_denominator NUMBER;
1168: BEGIN
1169:
1170: SELECT currency_code
1171: INTO l_transaction_currency
1172: FROM po_requisition_lines_all
1173: WHERE requisition_line_id = p_req_line_id ;
1174:
1175: SELECT currency_code, fsp.set_of_books_id
1176: INTO l_functional_currency, l_set_of_books_id
1181:
1182: IF(l_transaction_currency <> l_functional_currency) THEN
1183: SELECT nvl(rate_type,' ')
1184: INTO l_rate_type
1185: FROM po_requisition_lines_all
1186: WHERE requisition_line_id = p_req_line_id;
1187:
1188: IF(l_rate_type <> 'User') THEN
1189: SELECT rate_date
1187:
1188: IF(l_rate_type <> 'User') THEN
1189: SELECT rate_date
1190: INTO l_conversion_date
1191: FROM po_requisition_lines_all
1192: WHERE requisition_line_id = p_req_line_id;
1193:
1194: gl_currency_api.get_triangulation_rate(l_set_of_books_id,
1195: l_transaction_currency,
1204: (p_new_price / l_denominator) * l_numerator;
1205: ELSE
1206: SELECT rate
1207: INTO l_rate
1208: FROM po_requisition_lines_all
1209: WHERE requisition_line_id = p_req_line_id;
1210:
1211: l_unit_price := p_new_price * l_rate;
1212: END IF;
1424: l_current_hdr_id,
1425: l_current_rel_id
1426: FROM
1427: po_line_locations_all plla,
1428: po_requisition_lines_all prla
1429: WHERE prla.line_location_id = plla.line_location_id
1430: AND prla.requisition_line_id = p_req_change_table(l_current_row_id).document_line_id;
1431:
1432: --Refresh l_po_change_table
1461: l_hdr_id,
1462: l_rel_id
1463: FROM
1464: po_line_locations_all plla,
1465: po_requisition_lines_all prla
1466: WHERE prla.line_location_id = plla.line_location_id
1467: AND prla.requisition_line_id = p_req_change_table(i).document_line_id;
1468:
1469:
1745: l_new_total,
1746: l_rec_tax,
1747: l_nonrec_tax
1748: FROM
1749: po_requisition_lines_all prl,
1750: po_req_distributions_all prd
1751: WHERE prd.distribution_id = p_dist_id
1752: AND prd.requisition_line_id = prl.requisition_line_id;
1753:
1854: AND new_price IS NOT NULL;
1855: EXCEPTION WHEN OTHERS THEN
1856: SELECT nvl(currency_unit_price, unit_price)
1857: INTO l_price
1858: FROM po_requisition_lines_all
1859: WHERE requisition_line_id = l_line_id;
1860: END;
1861: l_progress := '001';
1862: --INNER LOOP: After getting the most recent price, update child distribution records with tax information.
2019:
2020: BEGIN
2021: SELECT nvl(currency_unit_price, unit_price)
2022: INTO l_price
2023: FROM po_requisition_lines_all
2024: WHERE requisition_line_id = l_line_id;
2025: EXCEPTION WHEN OTHERS THEN
2026: l_price := NULL;
2027: END;
2116: l_preparer_id NUMBER;
2117: l_po_ship_to_loc_id NUMBER;
2118: l_po_ship_to_org_id NUMBER;
2119: l_item_id NUMBER;
2120: l_req_uom po_requisition_lines_all.unit_meas_lookup_code%TYPE;
2121: l_po_uom po_line_locations_all.unit_meas_lookup_code%TYPE;
2122: l_po_to_req_rate NUMBER;
2123: l_release_num NUMBER;
2124: l_old_start_date DATE;
2206: po_headers_all pha,
2207: po_lines_all pla,
2208: po_line_locations_all plla,
2209: po_req_distributions_all prda,
2210: po_requisition_lines_all prla,
2211: po_requisition_headers_all prha,
2212: po_distributions_all pda
2213: WHERE
2214: prda.distribution_id = req_change_record_tbl(req_index).document_distribution_id
2286: FROM
2287: po_headers_all pha,
2288: po_lines_all pla,
2289: po_line_locations_all plla,
2290: po_requisition_lines_all prla,
2291: po_requisition_headers_all prha
2292: WHERE
2293: prla.requisition_line_id = req_change_record_tbl(req_index).document_line_id
2294: AND prla.line_location_id = plla.line_location_id
2378: po_lines_all pla,
2379: po_line_locations_all plla,
2380: po_distributions_all pda,
2381: po_req_distributions_all prda,
2382: po_requisition_lines_all prla,
2383: po_requisition_headers_all prha
2384: WHERE
2385: prda.distribution_id = req_change_record_tbl(req_index).document_distribution_id
2386: AND prda.distribution_id = pda.req_distribution_id
2459: po_headers_all pha,
2460: po_releases_all pra,
2461: po_lines_all pla,
2462: po_line_locations_all plla,
2463: po_requisition_lines_all prla,
2464: po_requisition_headers_all prha
2465: WHERE
2466: prla.requisition_line_id = req_change_record_tbl(req_index).document_line_id
2467: AND prla.line_location_id = plla.line_location_id
2673: l_progress VARCHAR2(3) := '000';
2674: l_auction_display_number NUMBER;
2675: l_auction_line_number NUMBER;
2676: l_reqs_in_pool_flag VARCHAR2(1);
2677: l_source_type_code po_requisition_lines_all.source_type_code%TYPE;
2678: l_return_status VARCHAR2(1);
2679: l_po_header_id NUMBER;
2680: l_po_release_id NUMBER;
2681: l_po_line_id NUMBER;
2733: l_modified_by_agent,
2734: l_transferred_to_oe_flag,
2735: l_cancelled
2736: FROM
2737: po_requisition_lines_all prla,
2738: po_requisition_headers_all prha
2739: WHERE
2740: prla.requisition_line_id = p_req_line_id AND
2741: prla.requisition_header_id = prha.requisition_header_id;
2791: l_po_line_loc_id,
2792: l_req_change_pending_flag,
2793: l_modified_by_agent
2794: FROM
2795: po_requisition_lines_all prla,
2796: po_requisition_headers_all prha
2797: WHERE
2798: prla.requisition_line_id = p_req_line_id AND
2799: prla.requisition_header_id = prha.requisition_header_id;
2828: INTO l_count
2829: FROM
2830: po_req_distributions_all prda,
2831: po_distributions_all pda,
2832: po_requisition_lines_all prla
2833: WHERE pda.line_location_id = prla.line_location_id
2834: AND prla.requisition_line_id = p_req_line_id
2835: AND pda.req_distribution_id = prda.distribution_id(+ );
2836:
2871: FROM
2872: po_headers_all pha,
2873: po_lines_all pla,
2874: po_line_locations_all plla,
2875: po_requisition_lines_all prla
2876: WHERE
2877: prla.requisition_line_id = p_req_line_id
2878: AND prla.line_location_id = plla.line_location_id
2879: AND plla.po_line_id = pla.po_line_id
3193: l_api_name VARCHAR2(50) := 'IS_REQ_LINE_CHANGEABLE';
3194: l_progress VARCHAR2(100) := '000';
3195: l_line_changeable_flag VARCHAR2(1);
3196: l_line_loc_id NUMBER;
3197: l_source_type_code po_requisition_lines_all.source_type_code%TYPE;
3198: l_auction_display_number po_requisition_lines_all.auction_display_number%TYPE;
3199: l_auction_line_number NUMBER;
3200: l_reqs_in_pool_flag po_requisition_lines_all.reqs_in_pool_flag%TYPE;
3201: l_authorization_status po_headers_all.authorization_status%TYPE;
3194: l_progress VARCHAR2(100) := '000';
3195: l_line_changeable_flag VARCHAR2(1);
3196: l_line_loc_id NUMBER;
3197: l_source_type_code po_requisition_lines_all.source_type_code%TYPE;
3198: l_auction_display_number po_requisition_lines_all.auction_display_number%TYPE;
3199: l_auction_line_number NUMBER;
3200: l_reqs_in_pool_flag po_requisition_lines_all.reqs_in_pool_flag%TYPE;
3201: l_authorization_status po_headers_all.authorization_status%TYPE;
3202: l_frozen_flag VARCHAR2(1);
3196: l_line_loc_id NUMBER;
3197: l_source_type_code po_requisition_lines_all.source_type_code%TYPE;
3198: l_auction_display_number po_requisition_lines_all.auction_display_number%TYPE;
3199: l_auction_line_number NUMBER;
3200: l_reqs_in_pool_flag po_requisition_lines_all.reqs_in_pool_flag%TYPE;
3201: l_authorization_status po_headers_all.authorization_status%TYPE;
3202: l_frozen_flag VARCHAR2(1);
3203: l_closed_code po_headers_all.closed_code%TYPE;
3204: l_cancel_flag po_headers_all.cancel_flag%TYPE;
3201: l_authorization_status po_headers_all.authorization_status%TYPE;
3202: l_frozen_flag VARCHAR2(1);
3203: l_closed_code po_headers_all.closed_code%TYPE;
3204: l_cancel_flag po_headers_all.cancel_flag%TYPE;
3205: l_catalog_type po_requisition_lines_all.catalog_type%TYPE;
3206: l_allow_price_override_flag po_lines_all.allow_price_override_flag%TYPE;
3207: l_accrue_on_receipt_flag po_line_locations_all.accrue_on_receipt_flag%TYPE;
3208: l_qty_received NUMBER;
3209: l_qty_billed NUMBER;
3211: l_release_id NUMBER;
3212: l_return_status VARCHAR2(1);
3213: l_line_type po_line_types.purchase_basis%TYPE;
3214: l_global_agreement_flag po_headers_all.global_agreement_flag%TYPE;
3215: l_order_type_lookup_code po_requisition_lines_all.order_type_lookup_code%TYPE ;
3216: l_po_header_id po_headers_all.po_header_id%TYPE;
3217: l_template_id po_requisition_lines_all.noncat_template_id%TYPE;
3218: l_price_editable_flag por_noncat_templates_all_b.price_editable_flag%TYPE;
3219: l_amount_editable_flag por_noncat_templates_all_b.amount_editable_flag%TYPE;
3213: l_line_type po_line_types.purchase_basis%TYPE;
3214: l_global_agreement_flag po_headers_all.global_agreement_flag%TYPE;
3215: l_order_type_lookup_code po_requisition_lines_all.order_type_lookup_code%TYPE ;
3216: l_po_header_id po_headers_all.po_header_id%TYPE;
3217: l_template_id po_requisition_lines_all.noncat_template_id%TYPE;
3218: l_price_editable_flag por_noncat_templates_all_b.price_editable_flag%TYPE;
3219: l_amount_editable_flag por_noncat_templates_all_b.amount_editable_flag%TYPE;
3220: -- added for retroactive pricing checks
3221: l_retropricing VARCHAR2(20) := '';
3219: l_amount_editable_flag por_noncat_templates_all_b.amount_editable_flag%TYPE;
3220: -- added for retroactive pricing checks
3221: l_retropricing VARCHAR2(20) := '';
3222: l_amount_based_service_line BOOLEAN := FALSE;
3223: l_destination_type_code po_requisition_lines_all.destination_type_code%TYPE;
3224: l_is_on_complex_work_po VARCHAR2(1);
3225: l_transferred_to_oe_flag varchar2(1);
3226: BEGIN
3227:
3262: l_template_id,
3263: l_destination_type_code,
3264: l_transferred_to_oe_flag
3265: FROM
3266: po_requisition_lines_all prla,
3267: po_requisition_headers_all prha
3268: WHERE
3269: prla.requisition_line_id = p_req_line_id AND
3270: prla.requisition_header_id = prha.requisition_header_id;
3318: p_end_date_changeable_flag := 'Y';
3319:
3320: SELECT order_type_lookup_code
3321: INTO l_order_type_lookup_code
3322: FROM po_requisition_lines_all
3323: WHERE requisition_line_id = p_req_line_id;
3324:
3325: IF (l_line_type = 'SERVICES') THEN
3326: p_date_changeable_flag := 'Y';
3349:
3350: SELECT pha.global_agreement_flag, pha.po_header_id
3351: INTO l_global_agreement_flag, l_po_header_id
3352: FROM po_headers_all pha,
3353: po_requisition_lines_all prla
3354: WHERE pha.po_header_id (+ ) = prla.blanket_po_header_id
3355: AND prla.requisition_line_id = p_req_line_id;
3356:
3357: IF(l_global_agreement_flag = 'Y') THEN
3357: IF(l_global_agreement_flag = 'Y') THEN
3358: BEGIN
3359: SELECT pla.allow_price_override_flag
3360: INTO l_allow_price_override_flag
3361: FROM po_requisition_lines_all prl,
3362: po_headers_all pha,
3363: po_lines_all pla
3364: WHERE pha.po_header_id = l_po_header_id
3365: AND pla.po_header_id = pha.po_header_id
3460: FROM
3461: po_headers_all pha,
3462: po_lines_all pla,
3463: po_line_locations_all plla,
3464: po_requisition_lines_all prla
3465: WHERE pha.po_header_id = plla.po_header_id
3466: AND pla.po_line_id = plla.po_line_id
3467: AND plla.line_location_id = prla.line_location_id
3468: AND prla.requisition_line_id = p_req_line_id;
3489: l_frozen_flag
3490: FROM
3491: po_lines_all pla,
3492: po_line_locations_all plla,
3493: po_requisition_lines_all prla,
3494: po_releases_all pra
3495: WHERE pla.po_line_id = plla.po_line_id
3496: AND plla.line_location_id = prla.line_location_id
3497: AND prla.requisition_line_id = p_req_line_id
3537: INTO l_count
3538: FROM
3539: po_req_distributions_all prda,
3540: po_distributions_all pda,
3541: po_requisition_lines_all prla
3542: WHERE pda.line_location_id = prla.line_location_id
3543: AND prla.requisition_line_id = p_req_line_id
3544: AND pda.req_distribution_id = prda.distribution_id(+ );
3545:
3566: l_progress := '005';
3567: SELECT COUNT(1)
3568: INTO l_count
3569: FROM
3570: po_requisition_lines_all prla,
3571: po_line_locations_all plla,
3572: po_line_locations_all plla2
3573: WHERE plla.line_location_id = prla.line_location_id
3574: AND prla.requisition_line_id = p_req_line_id
3897: l_req_date,
3898: l_req_currency_price
3899: FROM
3900: po_requisition_headers_all prha,
3901: po_requisition_lines_all prla
3902: WHERE prla.requisition_line_id = p_cancel_table.req_line_id(i)
3903: AND prla.requisition_header_id = prha.requisition_header_id;
3904:
3905: IF(l_line_loc_id IS NOT NULL) THEN
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;
4089: l_po_uom po_line_locations_all.unit_meas_lookup_code%TYPE;
4090: l_po_to_req_rate NUMBER;
4091:
4092: l_po_return_code VARCHAR2(100) := '';
4142: prla.requisition_header_id hdr_id,
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
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
4183: WHERE
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
4203: WHERE
4204: pcr.change_request_group_id = grp_id AND
4205: (pcr.new_quantity IS NOT NULL OR pcr.new_amount IS NOT NULL) AND
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
4221: WHERE
4388: l_ship_to_org_id,
4389: l_ship_to_loc_id
4390: FROM
4391: po_req_distributions_all prda,
4392: po_requisition_lines_all prla,
4393: po_line_locations_all plla,
4394: po_distributions_all pda,
4395: po_lines_all pla,
4396: po_headers_all pha
4588: l_fc_req_distr_num,
4589: l_fc_req_line_id,
4590: l_fc_req_line_num
4591: FROM
4592: po_requisition_lines_all prla,
4593: po_req_distributions_all prda,
4594: po_distributions_all pda
4595: WHERE
4596: pda.po_distribution_id = l_fc_out_tbl.distribution_id(x)
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;
4764: BEGIN
5128: prl.requisition_header_id
5129: INTO
5130: l_req_line_id,
5131: l_req_header_id
5132: FROM po_requisition_lines_all prl
5133: WHERE prl.requisition_line_id = p_req_line_id;
5134:
5135: l_progress := '001';
5136:
5274: l_req_quantity,
5275: l_req_date
5276: FROM
5277: po_requisition_headers_all prha,
5278: po_requisition_lines_all prla
5279: WHERE prla.requisition_line_id = p_cancel_table.req_line_id(i)
5280: AND prla.requisition_header_id = prha.requisition_header_id;
5281:
5282: /* if(l_line_loc_id is not null) then
5505: l_old_req_quantity,
5506: l_old_amount,
5507: l_preparer_id
5508: FROM
5509: po_requisition_lines_all prla,
5510: po_requisition_headers_all prha,
5511: po_req_distributions_all prda
5512: WHERE
5513: prha.requisition_header_id = p_req_hdr_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;
5707: BEGIN
5824: IS
5825:
5826: CURSOR req_lines_cursor(x_requisition_header_id NUMBER) IS
5827: SELECT requisition_line_id
5828: FROM po_requisition_lines_all
5829: WHERE requisition_header_id = x_requisition_header_id
5830: AND SOURCE_TYPE_CODE = 'INVENTORY';
5831:
5832:
5900: INTO l_open_quantity
5901: FROM oe_order_lines_all oel
5902: ,oe_order_headers_all oeh
5903: ,wsh_delivery_details w
5904: ,po_requisition_lines_all pol
5905: WHERE
5906: oel.header_id = oeh.header_id
5907: AND oel.line_id = w.source_line_id
5908: AND w.source_code = 'OE'
5919: END;
5920: BEGIN
5921: SELECT QUANTITY, nvl(QUANTITY_DELIVERED,0)
5922: INTO l_quantity, l_quantity_delivered
5923: FROM po_requisition_lines_all
5924: WHERE REQUISITION_LINE_ID=l_req_line_id;
5925: EXCEPTION
5926: WHEN OTHERS THEN
5927: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5975: INTO l_open_quantity
5976: FROM oe_order_lines_all oel
5977: ,oe_order_headers_all oeh
5978: ,wsh_delivery_details w
5979: ,po_requisition_lines_all pol
5980: WHERE
5981: oel.header_id = oeh.header_id
5982: AND oel.line_id = w.source_line_id
5983: AND w.source_code = 'OE'
5994: END;
5995: BEGIN
5996: SELECT QUANTITY, nvl(QUANTITY_DELIVERED,0)
5997: INTO l_quantity, l_quantity_delivered
5998: FROM po_requisition_lines_all
5999: WHERE REQUISITION_LINE_ID=p_req_line_id;
6000: EXCEPTION
6001: WHEN OTHERS THEN
6002: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6100: --algo
6101: -- for the given req line the given attribute needs to be chnaged
6102: -- for quantity .. input is the delta qunatity = new qunatity-old quantity
6103: -- hence the new line quantity shall be existing qunatity+delta quantity
6104: -- this is applicable to both mtl_supply and po_requisition_lines_all
6105:
6106: -- for need by date.. input is new need by date so update this value to
6107: -- both the tables
6108: IF p_req_line_id IS NOT NULL AND p_delta_quantity IS NOT NULL THEN
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;
6214: l_po_uom po_line_locations_all.unit_meas_lookup_code%TYPE;
6215: l_po_to_req_rate NUMBER;
6216:
6217: l_po_return_code VARCHAR2(100) := '';
6266: prla.requisition_header_id hdr_id,
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
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
6314: pcr.action_type = 'MODIFICATION' AND
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
6333: pcr.new_quantity IS NOT NULL AND
6334: -- (pcr.new_quantity IS NOT NULL OR pcr.new_need_by_date IS NOT NULL) AND
6592: l_fc_req_distr_num,
6593: l_fc_req_line_id,
6594: l_fc_req_line_num
6595: FROM
6596: po_requisition_lines_all prla,
6597: po_req_distributions_all prda,
6598: po_distributions_all pda
6599: WHERE
6600: pda.po_distribution_id = l_fc_out_tbl.distribution_id(x)
6754: l_progress varchar2(3) := '000';
6755:
6756: /*Procedure Call_Process_Order_for_IReq -- Specification definition
6757: ( P_API_Version IN NUMBER
6758: , P_internal_req_line_id IN PO_Requisition_Lines_All.Requisition_Line_id%TYPE
6759: , P_internal_req_header_id IN PO_Requisition_Headers_All.Requisition_Header_id%TYPE
6760: , P_Mode IN VARCHAR2
6761: , P_Cancel_ISO IN BOOLEAN DEFAULT FALSE
6762: , P_Cancel_ISO_lines IN BOOLEAN DEFAULT FALSE
6782: INTO l_open_quantity
6783: FROM oe_order_lines_all oel
6784: ,oe_order_headers_all oeh
6785: ,wsh_delivery_details w
6786: ,po_requisition_lines_all pol
6787: WHERE
6788: oel.header_id = oeh.header_id
6789: AND oel.line_id = w.source_line_id
6790: AND w.source_code = 'OE'