205:
206:
207: TYPE line_record IS RECORD (
208:
209: req_line_id po_requisition_lines.requisition_line_id%TYPE,
210: line_num po_requisition_lines.line_num%TYPE,
211: item_num mtl_system_items_kfv.concatenated_segments%TYPE,
212: item_revision po_requisition_lines.item_revision%TYPE,
213: item_desc po_requisition_lines.item_description%TYPE,
206:
207: TYPE line_record IS RECORD (
208:
209: req_line_id po_requisition_lines.requisition_line_id%TYPE,
210: line_num po_requisition_lines.line_num%TYPE,
211: item_num mtl_system_items_kfv.concatenated_segments%TYPE,
212: item_revision po_requisition_lines.item_revision%TYPE,
213: item_desc po_requisition_lines.item_description%TYPE,
214: uom mtl_units_of_measure.unit_of_measure_tl%TYPE,
208:
209: req_line_id po_requisition_lines.requisition_line_id%TYPE,
210: line_num po_requisition_lines.line_num%TYPE,
211: item_num mtl_system_items_kfv.concatenated_segments%TYPE,
212: item_revision po_requisition_lines.item_revision%TYPE,
213: item_desc po_requisition_lines.item_description%TYPE,
214: uom mtl_units_of_measure.unit_of_measure_tl%TYPE,
215: quantity po_requisition_lines.quantity%TYPE,
216: unit_price po_requisition_lines.unit_price%TYPE,
209: req_line_id po_requisition_lines.requisition_line_id%TYPE,
210: line_num po_requisition_lines.line_num%TYPE,
211: item_num mtl_system_items_kfv.concatenated_segments%TYPE,
212: item_revision po_requisition_lines.item_revision%TYPE,
213: item_desc po_requisition_lines.item_description%TYPE,
214: uom mtl_units_of_measure.unit_of_measure_tl%TYPE,
215: quantity po_requisition_lines.quantity%TYPE,
216: unit_price po_requisition_lines.unit_price%TYPE,
217: line_amount NUMBER,
211: item_num mtl_system_items_kfv.concatenated_segments%TYPE,
212: item_revision po_requisition_lines.item_revision%TYPE,
213: item_desc po_requisition_lines.item_description%TYPE,
214: uom mtl_units_of_measure.unit_of_measure_tl%TYPE,
215: quantity po_requisition_lines.quantity%TYPE,
216: unit_price po_requisition_lines.unit_price%TYPE,
217: line_amount NUMBER,
218: need_by_date po_requisition_lines.need_by_date%TYPE,
219: location hr_locations.location_code%TYPE,
212: item_revision po_requisition_lines.item_revision%TYPE,
213: item_desc po_requisition_lines.item_description%TYPE,
214: uom mtl_units_of_measure.unit_of_measure_tl%TYPE,
215: quantity po_requisition_lines.quantity%TYPE,
216: unit_price po_requisition_lines.unit_price%TYPE,
217: line_amount NUMBER,
218: need_by_date po_requisition_lines.need_by_date%TYPE,
219: location hr_locations.location_code%TYPE,
220: requestor per_people_f.full_name%TYPE,
214: uom mtl_units_of_measure.unit_of_measure_tl%TYPE,
215: quantity po_requisition_lines.quantity%TYPE,
216: unit_price po_requisition_lines.unit_price%TYPE,
217: line_amount NUMBER,
218: need_by_date po_requisition_lines.need_by_date%TYPE,
219: location hr_locations.location_code%TYPE,
220: requestor per_people_f.full_name%TYPE,
221: sugg_supplier po_requisition_lines.suggested_vendor_name%TYPE,
222: sugg_site po_requisition_lines.suggested_vendor_location%TYPE,
217: line_amount NUMBER,
218: need_by_date po_requisition_lines.need_by_date%TYPE,
219: location hr_locations.location_code%TYPE,
220: requestor per_people_f.full_name%TYPE,
221: sugg_supplier po_requisition_lines.suggested_vendor_name%TYPE,
222: sugg_site po_requisition_lines.suggested_vendor_location%TYPE,
223: txn_curr_code po_requisition_lines.currency_code%TYPE,
224: curr_unit_price po_requisition_lines.currency_unit_price%TYPE,
225: order_type po_lookup_codes.displayed_field%TYPE,
218: need_by_date po_requisition_lines.need_by_date%TYPE,
219: location hr_locations.location_code%TYPE,
220: requestor per_people_f.full_name%TYPE,
221: sugg_supplier po_requisition_lines.suggested_vendor_name%TYPE,
222: sugg_site po_requisition_lines.suggested_vendor_location%TYPE,
223: txn_curr_code po_requisition_lines.currency_code%TYPE,
224: curr_unit_price po_requisition_lines.currency_unit_price%TYPE,
225: order_type po_lookup_codes.displayed_field%TYPE,
226: source_type_code po_requisition_lines.source_type_code%TYPE,
219: location hr_locations.location_code%TYPE,
220: requestor per_people_f.full_name%TYPE,
221: sugg_supplier po_requisition_lines.suggested_vendor_name%TYPE,
222: sugg_site po_requisition_lines.suggested_vendor_location%TYPE,
223: txn_curr_code po_requisition_lines.currency_code%TYPE,
224: curr_unit_price po_requisition_lines.currency_unit_price%TYPE,
225: order_type po_lookup_codes.displayed_field%TYPE,
226: source_type_code po_requisition_lines.source_type_code%TYPE,
227: line_location_id po_requisition_lines.line_location_id%TYPE,
220: requestor per_people_f.full_name%TYPE,
221: sugg_supplier po_requisition_lines.suggested_vendor_name%TYPE,
222: sugg_site po_requisition_lines.suggested_vendor_location%TYPE,
223: txn_curr_code po_requisition_lines.currency_code%TYPE,
224: curr_unit_price po_requisition_lines.currency_unit_price%TYPE,
225: order_type po_lookup_codes.displayed_field%TYPE,
226: source_type_code po_requisition_lines.source_type_code%TYPE,
227: line_location_id po_requisition_lines.line_location_id%TYPE,
228: cancel_flag po_requisition_lines.cancel_flag%TYPE
222: sugg_site po_requisition_lines.suggested_vendor_location%TYPE,
223: txn_curr_code po_requisition_lines.currency_code%TYPE,
224: curr_unit_price po_requisition_lines.currency_unit_price%TYPE,
225: order_type po_lookup_codes.displayed_field%TYPE,
226: source_type_code po_requisition_lines.source_type_code%TYPE,
227: line_location_id po_requisition_lines.line_location_id%TYPE,
228: cancel_flag po_requisition_lines.cancel_flag%TYPE
229: );
230:
223: txn_curr_code po_requisition_lines.currency_code%TYPE,
224: curr_unit_price po_requisition_lines.currency_unit_price%TYPE,
225: order_type po_lookup_codes.displayed_field%TYPE,
226: source_type_code po_requisition_lines.source_type_code%TYPE,
227: line_location_id po_requisition_lines.line_location_id%TYPE,
228: cancel_flag po_requisition_lines.cancel_flag%TYPE
229: );
230:
231: TYPE history_record IS RECORD (
224: curr_unit_price po_requisition_lines.currency_unit_price%TYPE,
225: order_type po_lookup_codes.displayed_field%TYPE,
226: source_type_code po_requisition_lines.source_type_code%TYPE,
227: line_location_id po_requisition_lines.line_location_id%TYPE,
228: cancel_flag po_requisition_lines.cancel_flag%TYPE
229: );
230:
231: TYPE history_record IS RECORD (
232:
361: select to_char(sum(decode(pcr1.action_type, 'CANCELLATION', 0, nvl(pcr1.new_price, prl.unit_price)*
362: nvl(pcr2.new_quantity,prd.req_line_quantity)*prd.nonrecoverable_tax
363: /(prl.unit_price*prd.req_line_quantity))), FND_CURRENCY.GET_FORMAT_MASK(l_currency_code,30))
364: into l_new_tax_amount
365: from po_requisition_lines_all prl,
366: po_req_distributions_all prd,
367: po_change_requests pcr1,
368: po_change_requests pcr2
369: where prl.requisition_line_id=pcr1.document_line_id(+)
380: select to_char(sum(decode(pcr1.action_type, 'CANCELLATION', 0, nvl(pcr1.new_price, prl.unit_price)*
381: nvl(pcr2.new_quantity,prd.req_line_quantity))),
382: FND_CURRENCY.GET_FORMAT_MASK(l_currency_code,30))
383: into l_new_req_amount
384: from po_requisition_lines_all prl,
385: po_req_distributions_all prd,
386: po_change_requests pcr1,
387: po_change_requests pcr2
388: where prl.requisition_line_id=pcr1.document_line_id(+)
525: end if;
526:
527: SELECT to_char(nvl(sum(nonrecoverable_tax), 0), FND_CURRENCY.GET_FORMAT_MASK(l_currency_code,30))
528: INTO l_new_tax_amount
529: FROM po_requisition_lines rl,
530: po_req_distributions rd
531: WHERE rl.requisition_header_id = l_document_id
532: AND rd.requisition_line_id = rl.requisition_line_id
533: AND NVL(rl.modified_by_agent_flag, 'N') = 'N'
534: and NVL(rl.cancel_flag, 'N')='N';
535:
536: SELECT to_char(nvl(SUM(quantity * unit_price), 0), FND_CURRENCY.GET_FORMAT_MASK(l_currency_code,30))
537: into l_new_req_amount
538: FROM po_requisition_lines
539: WHERE requisition_header_id = l_document_id
540: AND NVL(cancel_flag,'N') = 'N'
541: AND NVL(modified_by_agent_flag, 'N') = 'N';
542: */
604:
605: l_item_type wf_items.item_type%TYPE;
606: l_item_key wf_items.item_key%TYPE;
607:
608: l_document_id po_requisition_lines.requisition_header_id%TYPE;
609: l_org_id po_requisition_lines.org_id%TYPE;
610:
611: l_line line_record;
612:
605: l_item_type wf_items.item_type%TYPE;
606: l_item_key wf_items.item_key%TYPE;
607:
608: l_document_id po_requisition_lines.requisition_header_id%TYPE;
609: l_org_id po_requisition_lines.org_id%TYPE;
610:
611: l_line line_record;
612:
613: l_num_lines NUMBER := 0;
673: PLC.DISPLAYED_FIELD,
674: rql.source_type_code,
675: rql.line_location_id,
676: rql.cancel_flag
677: FROM po_requisition_lines rql,
678: mtl_system_items_kfv msi,
679: hr_locations_all hrt,
680: per_all_people_f per,
681: mtl_units_of_measure muom,
777: l_max_lines := to_number(fnd_profile.value('PO_NOTIF_LINES_LIMIT'));
778:
779: select count(1)
780: into l_num_lines
781: from po_requisition_lines
782: where requisition_header_id = l_document_id;
783:
784: l_document := l_document || '