[Home] [Help]
132: period_name gl_period_statuses.period_name%type,
133: acceptance_required_flag po_system_parameters.acceptance_required_flag%type); /* Bug 7518967 : Default Acceptance Required Check ER */
134:
135: --
136: TYPE po_line_id_tbl IS TABLE OF PO_LINES_ALL.po_line_id%TYPE INDEX BY PLS_INTEGER;
137: TYPE interface_header_id_tbl IS TABLE OF PO_ATTR_VALUES_INTERFACE.interface_header_id%TYPE INDEX BY PLS_INTEGER;
138: TYPE interface_line_id_tbl IS TABLE OF PO_ATTR_VALUES_INTERFACE.interface_line_id%TYPE INDEX BY PLS_INTEGER;
139: --
140:
309: , pli.supplier_part_auxid supplier_part_auxid
310: , pli.catalog_name catalog_name
311: --
312: FROM po_headers_interface phi,
313: po_lines_interface pli,
314: po_distributions_interface pdi
315: WHERE phi.interface_header_id = pli.interface_header_id
316: AND pli.interface_line_id = pdi.interface_line_id
317: AND phi.interface_header_id = x_interface_header_id
352: g_rate_for_req_fields NUMBER; --
353: g_line_requesting_ou_id PO_REQUISITION_LINES_ALL.org_id%TYPE; --
354:
355: /* Global variable to hold number of req lines
356: ** in the po_lines_interface table. Thie determines
357: ** if we copy project_id, task_id from req to rfq
358: */
359: g_req_lines_to_process number:=0;
360:
905: -- and prl.auction_line_number<>pli.auction_line_number)" in the
906: --following sql.
907:
908: l_progress:='060';
909: update po_lines_interface pli
910: set pli.requisition_line_id= null
911: where pli.interface_header_id= x_interface_header_id
912: and exists
913: (select requisition_line_id
930: p_token => l_progress,
931: p_message => 'Sourcing to PO FPH-1: before the update for withdrawn reqs');
932: END IF;
933:
934: UPDATE po_lines_interface pli
935: SET pli.requisition_line_id= null
936: WHERE pli.interface_header_id= x_interface_header_id
937: AND NOT EXISTS
938: (SELECT requisition_line_id
957: * project/task reference from RFQ to Quote
958: */
959: SELECT count(*)
960: INTO g_req_lines_to_process
961: FROM po_lines_interface
962: WHERE interface_header_id = x_interface_header_id;
963:
964: l_progress:='090';
965:
1368: END IF;
1369:
1370: select distinct 'Y'
1371: into X_line_found
1372: from po_lines_interface
1373: where interface_header_id = x_interface_header_id
1374: and line_num is not null;
1375:
1376: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
1375:
1376: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
1377: PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1378: p_token => l_progress,
1379: p_message => 'Create PO : Line found in po_lines_interfaces');
1380: END IF;
1381:
1382: EXCEPTION
1383: WHEN OTHERS THEN
2667: )
2668: IS
2669: x_po_line_id number;
2670: x_po_line_type_id number;
2671: x_line_num po_lines.line_num%type;
2672: x_po_item_id number;
2673: x_order_type_lookup_code varchar2(25);
2674: l_purchase_basis PO_LINE_TYPES_B.purchase_basis%TYPE; --
2675: x_po_item_revision po_lines.item_revision%type;
2671: x_line_num po_lines.line_num%type;
2672: x_po_item_id number;
2673: x_order_type_lookup_code varchar2(25);
2674: l_purchase_basis PO_LINE_TYPES_B.purchase_basis%TYPE; --
2675: x_po_item_revision po_lines.item_revision%type;
2676: x_po_unit_meas_lookup_code po_lines.unit_meas_lookup_code%type;
2677: x_po_unit_price number;
2678: x_po_transaction_reason_code po_lines.transaction_reason_code%type;
2679: x_price_break_lookup_code po_lines.price_break_lookup_code%type;
2672: x_po_item_id number;
2673: x_order_type_lookup_code varchar2(25);
2674: l_purchase_basis PO_LINE_TYPES_B.purchase_basis%TYPE; --
2675: x_po_item_revision po_lines.item_revision%type;
2676: x_po_unit_meas_lookup_code po_lines.unit_meas_lookup_code%type;
2677: x_po_unit_price number;
2678: x_po_transaction_reason_code po_lines.transaction_reason_code%type;
2679: x_price_break_lookup_code po_lines.price_break_lookup_code%type;
2680: x_quantity number := '';
2674: l_purchase_basis PO_LINE_TYPES_B.purchase_basis%TYPE; --
2675: x_po_item_revision po_lines.item_revision%type;
2676: x_po_unit_meas_lookup_code po_lines.unit_meas_lookup_code%type;
2677: x_po_unit_price number;
2678: x_po_transaction_reason_code po_lines.transaction_reason_code%type;
2679: x_price_break_lookup_code po_lines.price_break_lookup_code%type;
2680: x_quantity number := '';
2681: x_requisition_header_id number := ''; /* Used for copying attachments */
2682:
2675: x_po_item_revision po_lines.item_revision%type;
2676: x_po_unit_meas_lookup_code po_lines.unit_meas_lookup_code%type;
2677: x_po_unit_price number;
2678: x_po_transaction_reason_code po_lines.transaction_reason_code%type;
2679: x_price_break_lookup_code po_lines.price_break_lookup_code%type;
2680: x_quantity number := '';
2681: x_requisition_header_id number := ''; /* Used for copying attachments */
2682:
2683: x_line_location_id number := null;
2687:
2688: x_quote_header_id number := null;
2689: x_quote_line_id number := null;
2690: x_match_blanket_line varchar2(1) := null;
2691: x_unit_price po_lines.unit_price%TYPE := null;
2692: l_base_unit_price po_lines.base_unit_price%TYPE := null; --
2693:
2694: /* obtain currency info to adjust precision */
2695: x_precision number :='';
2688: x_quote_header_id number := null;
2689: x_quote_line_id number := null;
2690: x_match_blanket_line varchar2(1) := null;
2691: x_unit_price po_lines.unit_price%TYPE := null;
2692: l_base_unit_price po_lines.base_unit_price%TYPE := null; --
2694: /* obtain currency info to adjust precision */
2695: x_precision number :='';
2696: x_ext_precision number :='';
2700: x_task_id number :='';
2701: x_req_dist_id number :='';
2702: --togeorge 11/17/2000
2703: --Bug# 1369049
2704: --Added logic to default tax_name in po_lines
2705: x_tax_id ap_tax_codes.tax_id%type;
2706: x_tax_type ap_tax_codes.tax_type%type;
2707: x_description ap_tax_codes.description%type;
2708: x_allow_tax_code_override_flag gl_tax_option_accounts.allow_tax_code_override_flag%type;
2705: x_tax_id ap_tax_codes.tax_id%type;
2706: x_tax_type ap_tax_codes.tax_type%type;
2707: x_description ap_tax_codes.description%type;
2708: x_allow_tax_code_override_flag gl_tax_option_accounts.allow_tax_code_override_flag%type;
2709: x_tax_name po_lines.tax_name%type;
2710: x_ship_to_location_id number:= 0;
2711: x_ship_to_loc_org_id mtl_system_items.organization_id%TYPE;
2712: x_ship_org_code varchar2(3);
2713:
2720: -- bug# 3345108
2721: -- comment out most of the changes done by bug 2219743.
2722: -- bug# 2219743
2723: /**
2724: x_secondary_qty po_lines.secondary_quantity%type := NULL;
2725: x_item_number VARCHAR2(240);
2726: x_process_org VARCHAR2(1);
2727: x_dummy VARCHAR2(240);
2728: x_product VARCHAR2(3) := 'GMI';
2748: --sourcing entities need to be suppressed due to the grouping of lines.
2749: x_attch_suppress_flag varchar2(1) :='N';
2750: --
2751:
2752: l_db_quantity po_lines.quantity%TYPE := null; --bug#2723479
2753:
2754: -- Bug 2735840 START
2755: l_uom_convert varchar2(2) := fnd_profile.value('PO_REQ_BPA_UOM_CONVERT');
2756: l_ga_uom PO_LINES.unit_meas_lookup_code%TYPE;
2752: l_db_quantity po_lines.quantity%TYPE := null; --bug#2723479
2753:
2754: -- Bug 2735840 START
2755: l_uom_convert varchar2(2) := fnd_profile.value('PO_REQ_BPA_UOM_CONVERT');
2756: l_ga_uom PO_LINES.unit_meas_lookup_code%TYPE;
2757: l_quantity_in_ga_uom PO_LINES_INTERFACE.quantity%TYPE;
2758: l_conversion_rate number :=1;
2759: -- Bug 2735840 END
2760:
2753:
2754: -- Bug 2735840 START
2755: l_uom_convert varchar2(2) := fnd_profile.value('PO_REQ_BPA_UOM_CONVERT');
2756: l_ga_uom PO_LINES.unit_meas_lookup_code%TYPE;
2757: l_quantity_in_ga_uom PO_LINES_INTERFACE.quantity%TYPE;
2758: l_conversion_rate number :=1;
2759: -- Bug 2735840 END
2760:
2761: -- Bug 2875346.
2776: l_outside_operation_flag PO_LINE_TYPES_B.outside_operation_flag%TYPE;
2777: l_receiving_flag PO_LINE_TYPES_B.receiving_flag%TYPE;
2778: l_receive_close_tolerance PO_LINE_TYPES_B.receive_close_tolerance%TYPE;
2779: --
2780: l_negotiated_by_preparer_flag po_lines_all.negotiated_by_preparer_flag%type; --
2782: l_global_agreement_flag po_headers_all.global_agreement_flag%type; --
2783:
2784: -- oneoff 3201308 start
2795:
2796: l_routing_name RCV_ROUTING_HEADERS.routing_name%TYPE; --
2797:
2798: l_progress VARCHAR2(3) := '000'; --< Bug 3210331 >
2799: l_manual_price_change_flag po_lines_all.manual_price_change_flag%TYPE := NULL; --bug 3495772
2800: l_from_type_lookup_code PO_HEADERS.type_lookup_code%type;--bug#3612701
2801:
2802: --
2803: x_secondary_unit_def MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
2801:
2802: --
2803: x_secondary_unit_def MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
2804: x_secondary_uom MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
2805: x_secondary_quantity_def PO_LINES.SECONDARY_QUANTITY%TYPE;
2806: x_preferred_grade_def MTL_GRADES.GRADE_CODE%TYPE;
2807: l_quantity_temp PO_LINES.QUANTITY%TYPE;
2808: --
2809:
2803: x_secondary_unit_def MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
2804: x_secondary_uom MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
2805: x_secondary_quantity_def PO_LINES.SECONDARY_QUANTITY%TYPE;
2806: x_preferred_grade_def MTL_GRADES.GRADE_CODE%TYPE;
2807: l_quantity_temp PO_LINES.QUANTITY%TYPE;
2808: --
2809:
2810: l_requesting_ou_id PO_REQUISITION_LINES_ALL.org_id%TYPE; --
2811: l_rate_for_req_fields GL_DAILY_RATES.conversion_rate%TYPE; --
2912: /*
2913: ** Check to see if the po line exists
2914: ** Note that we do not need to check if the line exists in the
2915: ** interface table since we are in the process of inserting
2916: ** lines into the po lines table on a record by record basis.
2917: */
2918: IF(g_document_subtype='STANDARD' or g_document_subtype='PLANNED' or
2919: g_document_type = 'RFQ'
2920: --
2945: x_po_unit_price,
2946: x_po_transaction_reason_code,
2947: x_price_break_lookup_code,
2948: l_manual_price_change_flag --bug 3495772
2949: FROM PO_LINES_ALL --
2951: AND LINE_NUM = interface.line_num
2952: FOR UPDATE OF quantity;
2953:
2968: /* Bug 2534534 Reverting the fix done in 1951084 */
2969: -- BEGIN
2970: SELECT po_line_id
2971: INTO x_po_line_id
2972: FROM po_lines_all --
2974: AND line_num = interface.line_num
2975: FOR UPDATE OF quantity;
2976:
3019:
3020: /**
3021: --mchandak 02/11/2002
3022: --Bug# 2219743
3023: --Added logic to default secondary qnty,UOM and grade in po_lines
3024: --if common purchasing is installed for OPM dual item
3025: IF(g_document_subtype='STANDARD' or g_document_subtype='PLANNED') AND NOT GML_PO_FOR_PROCESS.check_po_for_proc
3026: THEN
3027: x_retvar := FND_INSTALLATION.get_app_info(x_product,x_opm_installed,x_dummy,x_dummy);
3121: --bug#3612701 modified the sql to fetch type lookup code
3122: --of the source document as well.
3123: SELECT pol.unit_meas_lookup_code,poh.type_lookup_code
3124: INTO l_ga_uom,l_from_type_lookup_code
3125: FROM po_lines_all pol,po_headers_all poh
3126: WHERE pol.po_line_id = interface.from_line_id
3127: and poh.po_header_id=interface.from_header_id
3128: and poh.po_header_id=pol.po_header_id;
3129: --bug#3612701
3683:
3684:
3685: l_progress:='330';
3686:
3687: SELECT po_lines_s.nextval
3688: INTO x_po_line_id
3689: FROM sys.dual;
3690:
3691: l_progress:='340';
3852: -- if the source document is global agreement.
3853: IF l_type_lookup_code='BLANKET' and l_global_agreement_flag='Y' THEN
3854: l_progress := '395';
3855: SELECT negotiated_by_preparer_flag into l_negotiated_by_preparer_flag
3856: FROM po_lines_all
3857: WHERE po_line_id=interface.from_line_id;
3858: --if the source document is quotation.
3859: ELSIF l_type_lookup_code='QUOTATION' THEN
3860: l_negotiated_by_preparer_flag := 'Y';
3955: -- bug 4887900 END
3956:
3957: -- Added note_to_vendor - iali 08/26/99
3958: /*Bug 1391523 . Added market price to the INSERT statement */
3959: INSERT INTO po_lines_all --
3961: last_update_date,
3962: last_updated_by,
3963: po_header_id,
4004: oke_contract_header_id,
4005: oke_contract_version_id,
4006: --togeorge 11/17/2000
4007: --Bug# 1369049
4008: --Added logic to default tax_name in po_lines
4009: tax_name,
4010: -- start of 1548597
4011: secondary_unit_of_measure,
4012: secondary_quantity,
4123: interface.oke_contract_header_id,
4124: interface.oke_contract_version_id,
4125: --togeorge 11/17/2000
4126: --Bug# 1369049
4127: --Added logic to default tax_name in po_lines
4128: x_tax_name,
4129: --
4130: -- don't insert secondary unit/quantity/grade from interface record.
4131: -- start of 1548597
4505: l_progress := '540';
4506: begin
4507: select pl.quantity
4508: into l_db_quantity
4509: from po_lines_all pl --
4511: exception
4512: when others then
4513: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
4606:
4607: -- Bug 2879460 Update the price on the PO only with the price
4608: -- from the pricing API and not with the interface price
4609:
4610: UPDATE po_lines_all
4611: -- Bug 3417479
4612: -- SET unit_price = x_unit_price,
4613: -- base_unit_price = l_base_unit_price,
4614: SET unit_price = nvl(x_unit_price, unit_price),
4638: -- because x_po_line_id is the blanket line_id for 'Release'
4639: -- Introducing the 'If' statement for checking that its not a release
4640: IF (g_document_subtype <> 'RELEASE') THEN
4641:
4642: /** If FSP org and item combination is dual uom control, update the po lines secondary quantity
4643: with the default conversion based on the PO lines quantity **/
4644:
4645: --
4646:
4639: -- Introducing the 'If' statement for checking that its not a release
4640: IF (g_document_subtype <> 'RELEASE') THEN
4641:
4642: /** If FSP org and item combination is dual uom control, update the po lines secondary quantity
4643: with the default conversion based on the PO lines quantity **/
4644:
4645: --
4646:
4647: UPDATE po_lines_all --
4643: with the default conversion based on the PO lines quantity **/
4644:
4645: --
4646:
4647: UPDATE po_lines_all --
4649: last_update_date = interface.last_update_date,
4650: last_updated_by = interface.last_updated_by,
4651: last_update_login = interface.last_update_login,
4670: interface.item_id,
4671: x_secondary_unit_def,
4672: x_secondary_quantity_def) ;
4673:
4674: UPDATE po_lines_all
4675: SET secondary_quantity = x_secondary_quantity_def,
4676: secondary_unit_of_measure = x_secondary_unit_def
4677: WHERE po_line_id = x_po_line_id ;
4678:
4783: PO_NEGOTIATIONS_SV2.convert_text_to_attachment
4784: ( p_long_text => l_job_long_description
4785: , p_description => NULL
4786: , p_category_id => 33 -- To Supplier
4787: , p_to_entity_name => 'PO_LINES'
4788: , p_to_pk1_value => x_po_line_id
4789: , p_who_rec => l_who_rec
4790: );
4791:
4885: '',
4886: '',
4887: '',
4888: '',
4889: 'PO_LINES',
4890: x_po_line_id,
4891: '',
4892: '',
4893: '',
4915: '',
4916: '',
4917: '',
4918: '',
4919: 'PO_LINES',
4920: x_po_line_id,
4921: '',
4922: '',
4923: '',
5000: p_message => 'Before selecting one-time attachment');
5001: END IF;
5002:
5003: --SQL What: Locate the one-time location attachment currently under
5004: -- the PO_LINES entity by it's unique iP identifier prefix
5005: --SQL Why: Need the attached_document_id to move the attachment
5006:
5007: SELECT fad.attached_document_id
5008: INTO l_one_time_att_doc_id
5007: SELECT fad.attached_document_id
5008: INTO l_one_time_att_doc_id
5009: FROM fnd_attached_documents fad,
5010: fnd_documents_tl fdt
5011: WHERE fad.entity_name = 'PO_LINES'
5012: AND fad.pk1_value = to_char(x_po_line_id)
5013: AND fad.document_id = fdt.document_id
5014: AND fdt.language = USERENV('LANG')
5015: AND fdt.description like 'POR:%'; -- iP unique identifier
5293:
5294: --
5295: x_shipment_uom MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
5296: x_secondary_unit_of_measure MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
5297: x_secondary_quantity PO_LINES.SECONDARY_QUANTITY%TYPE;
5298: x_secondary_uom_code MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
5299: --
5300:
5301: l_matching_basis PO_LINE_TYPES.matching_basis%TYPE; --
5394: IF (g_document_subtype='RELEASE') THEN -- Bug 2735840
5395: l_progress := '020';
5396: select unit_meas_lookup_code
5397: into x_po_uom
5398: from po_lines_all pol , --
5400: where pol.po_header_id = poh.po_header_id
5401: and pol.po_header_id = interface.po_header_id
5402: and pol.line_num = interface.line_num;
5404: ELSE -- Autocreating a PO that references a GA
5405: l_progress := '030';
5406: SELECT unit_meas_lookup_code
5407: INTO x_po_uom
5408: FROM po_lines_all
5409: WHERE po_line_id = interface.from_line_id;
5410: END IF; -- g_document_subtype
5411: -- Bug 2735840 END
5412: EXCEPTION
5477: BEGIN
5478: SELECT poll.line_location_id,poll.secondary_unit_of_measure --
5479: INTO x_line_location_id,x_secondary_unit_of_measure
5480: FROM po_line_locations_all poll, --
5481: po_lines_all pol --
5483: AND poll.po_line_id = pol.po_line_id
5484: AND poll.shipment_num = interface.shipment_num
5485: AND pol.line_num = interface.line_num
5511: INTO x_line_location_id,
5512: l_manual_price_change_flag, --bug 3495772
5513: x_secondary_unit_of_measure
5514: FROM po_line_locations_all poll, --
5515: po_lines_all pol, --
5517: WHERE poll.po_header_id = interface.po_header_id
5518: AND poll.po_line_id = pol.po_line_id
5519: AND poll.shipment_num = interface.shipment_num
5549:
5550: -- Find out if the line is using cumulative or non-cumlative pricing
5551: SELECT decode(price_break_lookup_code, 'CUMULATIVE', 'Y', 'N')
5552: INTO x_price_break_type
5553: FROM po_lines_all --
5555:
5556: IF (x_price_break_type = 'Y') THEN
5557: x_cumulative_flag := TRUE;
5622: l_progress := '110';
5623: begin
5624: SELECT unit_price
5625: INTO x_price
5626: FROM po_lines_all --
5628: exception
5629: when others then
5630: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
5920: -- bug: 404191
5921: -- Get the taxable_flag based on the following priority
5922: -- 1. preferences (global.po_taxable_flag)
5923: -- 2. ship_to_org (x_ship_to_org_taxable_flag)
5924: -- 2. item-org (po_lines.taxable_flag)
5925: -- 3. PO default (po_startup_values.taxable_flag)
5926: --
5927: l_progress:='220';
5928: x_item_org_taxable_flag := item.taxable_flag;
6728: IF (g_document_subtype='RELEASE') THEN -- Bug 2735840
6729: l_progress := '090';
6730: select unit_meas_lookup_code
6731: into x_po_uom
6732: from po_lines_all pol , --
6734: where pol.po_header_id = poh.po_header_id
6735: and pol.po_header_id = interface.po_header_id
6736: and pol.line_num = interface.line_num;
6738: ELSE -- Autocreating a PO that references a GA
6739: l_progress := '100';
6740: SELECT unit_meas_lookup_code
6741: INTO x_po_uom
6742: FROM po_lines_all
6743: WHERE po_line_id = interface.from_line_id;
6744: END IF; -- g_document_subtype
6745: -- Bug 2735840 END
6746:
7891: l_new_ccid_generated BOOLEAN := FALSE;
7892: l_FB_ERROR_MSG VARCHAR2(2000);
7893:
7894: l_return_status VARCHAR2(1);
7895: l_interface_line_id PO_LINES_INTERFACE.interface_line_id%TYPE;
7896: l_old_interface_line_id PO_LINES_INTERFACE.interface_line_id%TYPE := -1;
7897: l_interface_distribution_id PO_DISTRIBUTIONS_INTERFACE.interface_distribution_id%TYPE;
7898: l_item_category_id PO_LINES_INTERFACE.category_id%TYPE;
7899: l_req_charge_account_id PO_DISTRIBUTIONS_INTERFACE.charge_account_id%TYPE;
7892: l_FB_ERROR_MSG VARCHAR2(2000);
7893:
7894: l_return_status VARCHAR2(1);
7895: l_interface_line_id PO_LINES_INTERFACE.interface_line_id%TYPE;
7896: l_old_interface_line_id PO_LINES_INTERFACE.interface_line_id%TYPE := -1;
7897: l_interface_distribution_id PO_DISTRIBUTIONS_INTERFACE.interface_distribution_id%TYPE;
7898: l_item_category_id PO_LINES_INTERFACE.category_id%TYPE;
7899: l_req_charge_account_id PO_DISTRIBUTIONS_INTERFACE.charge_account_id%TYPE;
7900: l_req_variance_account_id PO_DISTRIBUTIONS_INTERFACE.variance_account_id%TYPE;
7894: l_return_status VARCHAR2(1);
7895: l_interface_line_id PO_LINES_INTERFACE.interface_line_id%TYPE;
7896: l_old_interface_line_id PO_LINES_INTERFACE.interface_line_id%TYPE := -1;
7897: l_interface_distribution_id PO_DISTRIBUTIONS_INTERFACE.interface_distribution_id%TYPE;
7898: l_item_category_id PO_LINES_INTERFACE.category_id%TYPE;
7899: l_req_charge_account_id PO_DISTRIBUTIONS_INTERFACE.charge_account_id%TYPE;
7900: l_req_variance_account_id PO_DISTRIBUTIONS_INTERFACE.variance_account_id%TYPE;
7901: l_destination_organization_id PO_DISTRIBUTIONS_INTERFACE.destination_organization_id%TYPE;
7902: l_destination_ou_id PO_HEADERS_ALL.org_id%TYPE;
7900: l_req_variance_account_id PO_DISTRIBUTIONS_INTERFACE.variance_account_id%TYPE;
7901: l_destination_organization_id PO_DISTRIBUTIONS_INTERFACE.destination_organization_id%TYPE;
7902: l_destination_ou_id PO_HEADERS_ALL.org_id%TYPE;
7903:
7904: l_item_id PO_LINES_INTERFACE.item_id%TYPE;
7905: l_category_id PO_LINES_INTERFACE.category_id%TYPE;
7906: l_destination_type_code PO_DISTRIBUTIONS_ALL.destination_type_code%TYPE;
7907: --l_ship_to_organization_id NUMBER;
7908: l_ship_to_location_id PO_DISTRIBUTIONS_INTERFACE.deliver_to_location_id%TYPE;
7901: l_destination_organization_id PO_DISTRIBUTIONS_INTERFACE.destination_organization_id%TYPE;
7902: l_destination_ou_id PO_HEADERS_ALL.org_id%TYPE;
7903:
7904: l_item_id PO_LINES_INTERFACE.item_id%TYPE;
7905: l_category_id PO_LINES_INTERFACE.category_id%TYPE;
7906: l_destination_type_code PO_DISTRIBUTIONS_ALL.destination_type_code%TYPE;
7907: --l_ship_to_organization_id NUMBER;
7908: l_ship_to_location_id PO_DISTRIBUTIONS_INTERFACE.deliver_to_location_id%TYPE;
7909: l_deliver_to_person_id PO_DISTRIBUTIONS_INTERFACE.deliver_to_person_id%TYPE;
7906: l_destination_type_code PO_DISTRIBUTIONS_ALL.destination_type_code%TYPE;
7907: --l_ship_to_organization_id NUMBER;
7908: l_ship_to_location_id PO_DISTRIBUTIONS_INTERFACE.deliver_to_location_id%TYPE;
7909: l_deliver_to_person_id PO_DISTRIBUTIONS_INTERFACE.deliver_to_person_id%TYPE;
7910: l_line_type_id PO_LINES_INTERFACE.line_type_id%TYPE;
7911: l_vendor_id PO_VENDORS.vendor_id%TYPE;
7912: l_agent_id PO_HEADERS.agent_id%TYPE;
7913: l_expenditure_organization_id PO_DISTRIBUTIONS_INTERFACE.expenditure_organization_id%TYPE;
7914: l_project_id PO_DISTRIBUTIONS_INTERFACE.project_id%TYPE;
7937: l_dest_variance_account_flex VARCHAR2(2000);
7938: --< New start_workflow parameters in FPJ End >
7939:
7940: -- Bug 3463242 START
7941: l_req_line_id PO_LINES_INTERFACE.requisition_line_id%TYPE;
7942: --
7943: l_requesting_ou_id PO_REQUISITION_LINES_ALL.org_id%TYPE;
7944: l_unit_price PO_LINES_INTERFACE.unit_price%TYPE;
7945: l_base_unit_price PO_LINES_INTERFACE.base_unit_price%TYPE;
7940: -- Bug 3463242 START
7941: l_req_line_id PO_LINES_INTERFACE.requisition_line_id%TYPE;
7942: --
7943: l_requesting_ou_id PO_REQUISITION_LINES_ALL.org_id%TYPE;
7944: l_unit_price PO_LINES_INTERFACE.unit_price%TYPE;
7945: l_base_unit_price PO_LINES_INTERFACE.base_unit_price%TYPE;
7946: l_amount PO_LINES_INTERFACE.amount%TYPE;
7947: l_quantity_dummy PO_LINES_INTERFACE.quantity%TYPE;
7948: l_order_type_lookup_code PO_LINE_TYPES_B.order_type_lookup_code%TYPE;
7941: l_req_line_id PO_LINES_INTERFACE.requisition_line_id%TYPE;
7942: --
7943: l_requesting_ou_id PO_REQUISITION_LINES_ALL.org_id%TYPE;
7944: l_unit_price PO_LINES_INTERFACE.unit_price%TYPE;
7945: l_base_unit_price PO_LINES_INTERFACE.base_unit_price%TYPE;
7946: l_amount PO_LINES_INTERFACE.amount%TYPE;
7947: l_quantity_dummy PO_LINES_INTERFACE.quantity%TYPE;
7948: l_order_type_lookup_code PO_LINE_TYPES_B.order_type_lookup_code%TYPE;
7949: l_po_currency_code PO_HEADERS_INTERFACE.currency_code%TYPE;
7942: --
7943: l_requesting_ou_id PO_REQUISITION_LINES_ALL.org_id%TYPE;
7944: l_unit_price PO_LINES_INTERFACE.unit_price%TYPE;
7945: l_base_unit_price PO_LINES_INTERFACE.base_unit_price%TYPE;
7946: l_amount PO_LINES_INTERFACE.amount%TYPE;
7947: l_quantity_dummy PO_LINES_INTERFACE.quantity%TYPE;
7948: l_order_type_lookup_code PO_LINE_TYPES_B.order_type_lookup_code%TYPE;
7949: l_po_currency_code PO_HEADERS_INTERFACE.currency_code%TYPE;
7950: l_req_header_rate_type PO_HEADERS_INTERFACE.rate_type%TYPE;
7943: l_requesting_ou_id PO_REQUISITION_LINES_ALL.org_id%TYPE;
7944: l_unit_price PO_LINES_INTERFACE.unit_price%TYPE;
7945: l_base_unit_price PO_LINES_INTERFACE.base_unit_price%TYPE;
7946: l_amount PO_LINES_INTERFACE.amount%TYPE;
7947: l_quantity_dummy PO_LINES_INTERFACE.quantity%TYPE;
7948: l_order_type_lookup_code PO_LINE_TYPES_B.order_type_lookup_code%TYPE;
7949: l_po_currency_code PO_HEADERS_INTERFACE.currency_code%TYPE;
7950: l_req_header_rate_type PO_HEADERS_INTERFACE.rate_type%TYPE;
7951: l_req_header_rate_date PO_HEADERS_INTERFACE.rate_date%TYPE;
7951: l_req_header_rate_date PO_HEADERS_INTERFACE.rate_date%TYPE;
7952: l_req_header_rate PO_HEADERS_INTERFACE.rate%TYPE;
7953: l_dist_rate PO_DISTRIBUTIONS_INTERFACE.rate%TYPE;
7954: l_rate_for_req_fields PO_HEADERS_INTERFACE.rate%TYPE;
7955: l_po_func_unit_price PO_LINES_ALL.unit_price%TYPE;
7956: -- Bug 3463242 END
7957:
7958: l_item_in_linv_pou VARCHAR2(1):= 'Y'; -- Bug 3433867
7959:
8004: phi.rate,
8005: pdi.rate
8006: -- Bug 3463242 END
8007: FROM PO_DISTRIBUTIONS_INTERFACE pdi,
8008: PO_LINES_INTERFACE pli,
8009: PO_HEADERS_INTERFACE phi,
8010: PO_REQUISITION_LINES_ALL prl, --
8011: MTL_PARAMETERS mp,
8012: HR_ORGANIZATION_INFORMATION hoi,
8334: END IF;
8335:
8336: IF l_interface_line_id <> l_old_interface_line_id THEN
8337: l_progress := '120';
8338: UPDATE po_lines_interface
8339: SET transaction_flow_header_id = l_transaction_flow_header_id
8340: WHERE interface_line_id = l_interface_line_id;
8341:
8342: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
8643: pli.item_id,
8644: phi.vendor_id,
8645: phi.vendor_site_id,
8646: pdi.destination_organization_id
8647: from po_lines_interface pli,
8648: po_headers_interface phi,
8649: po_distributions_interface pdi
8650: where phi.interface_header_id = x_interface_header_id
8651: and phi.interface_header_id = pli.interface_header_id
8674: phi.rate,
8675: phi.rate_date,
8676: phi.document_subtype,
8677: pli.unit_price --
8678: FROM po_lines_interface pli,
8679: po_headers_interface phi,
8680: po_line_types plt
8681: WHERE phi.interface_header_id = x_interface_header_id
8682: AND phi.interface_header_id = pli.interface_header_id
8698: -- If the line is OPM item with dual uom control then the shipment corresponding
8699: -- to the extra PO line created by Sourcing does not have secondary uom and
8700: -- secondary quantity with ship to organization as process org.
8701: -- default secondary UOM and secondary quantity in such a case (non req backed lines)
8702: -- the requisition line id would be null in po_lines_interface .
8703: -- need to handle that situation in the default_opm_attributes cursor.
8704: -- in case of non req back lines from sourcing , ship_to_organization_id is NULL.
8705: -- so get it from distribution interface table.
8706:
8950: l_progress:='090';
8951:
8952: SELECT min(interface_line_id)
8953: INTO x_min_interface_line_id
8954: FROM po_lines_interface pli,
8955: po_headers_interface phi
8956: WHERE phi.interface_header_id=pli.interface_header_id
8957: AND phi.interface_header_id = x_interface_header_id;
8958:
9071: null),
9072: phi.document_subtype,
9073: l_shipping_control --
9074: FROM po_headers_interface phi2,
9075: po_lines_interface pli
9076: WHERE phi2.interface_header_id = phi.interface_header_id
9077: AND pli.interface_header_id=phi2.interface_header_id
9078: AND pli.interface_line_id = x_min_interface_line_id)
9079: WHERE interface_header_id = x_interface_header_id;
9130: -- for x_uom_convert, since UOM checking is now handled in
9131: -- source_blanket_line.
9132:
9133: l_progress:='120';
9134: UPDATE po_lines_interface pli2
9135: SET (
9136: line_num,
9137: item_id,
9138: category_id,
9234: nvl(pli.preferred_grade,prl.preferred_grade),
9235: -- end of 1548597
9236: prl.drop_ship_flag, --
9237: prl.vmi_flag -- VMI FPH
9238: FROM po_lines_interface pli,
9239: po_headers_interface phi,
9240: po_requisition_lines_all prl, --
9241: po_line_types plt,
9242: po_lines_all pol --
9238: FROM po_lines_interface pli,
9239: po_headers_interface phi,
9240: po_requisition_lines_all prl, --
9241: po_line_types plt,
9242: po_lines_all pol --
9244: AND pli.interface_header_id = phi.interface_header_id
9245: AND phi.interface_header_id = x_interface_header_id
9246: AND pli.requisition_line_id = prl.requisition_line_id(+)
9262:
9263: /* CONSIGNED FPI Start */
9264: ELSE -- CONSIGNED FPI
9265: l_progress:='130';
9266: UPDATE po_lines_interface pli2
9267: SET (
9268: po_header_id,
9269: negotiated_by_preparer_flag,
9270: firm_flag
9272: (SELECT
9273: x_po_header_id,
9274: nvl(pli.negotiated_by_preparer_flag,'N'),
9275: nvl(pli.firm_flag,'N')
9276: FROM po_lines_interface pli,
9277: po_headers_interface phi
9278: WHERE pli.interface_line_id = pli2.interface_line_id
9279: AND pli.interface_header_id = phi.interface_header_id
9280: AND phi.interface_header_id = x_interface_header_id);
9310: -- Added note_to_vendor - iali 08/26/99
9311: /*Bug 1391523 . Added market price to the update statement */
9312:
9313: l_progress:='150';
9314: UPDATE po_lines_interface pli2
9315: SET (
9316: line_num,
9317: item_id,
9318: job_id, --
9429: nvl(pli.expiration_date, prl.assignment_end_date),
9430: nvl(pli.contractor_first_name, prl.candidate_first_name),
9431: nvl(pli.contractor_last_name, prl.candidate_last_name)
9432: --
9433: FROM po_lines_interface pli,
9434: po_headers_interface phi,
9435: po_requisition_lines_all prl, --
9436: po_line_types plt
9437: WHERE pli.interface_line_id = pli2.interface_line_id
9577: decode(pli.consigned_flag,'Y',null,
9578: prd.oke_contract_deliverable_id)
9579: FROM po_requisition_lines_all prl, --
9580: po_req_distributions_all prd, --
9581: po_lines_interface pli,
9582: po_headers_interface phi
9583: WHERE prd.requisition_line_id = prl.requisition_line_id
9584: AND prl.requisition_line_id = pli.requisition_line_id -- Bug:1563888
9585: AND pli.interface_header_id = phi.interface_header_id
9764: x_vendor_product_num,
9765: x_purchasing_uom);
9766:
9767: if (x_vendor_product_num is not null) then
9768: update po_lines_interface
9769: set vendor_product_num = x_vendor_product_num
9770: where rowid = x_rowid;
9771:
9772: end if;
9834: DELETE po_line_locations_interface
9835: WHERE interface_header_id = x_interface_header_id;
9836: --
9837:
9838: DELETE po_lines_interface
9839: WHERE interface_header_id = x_interface_header_id;
9840:
9841:
9842: DELETE po_headers_interface
9903: x_bid_line_number number;
9904: x_row_id varchar2(25) := null;
9905: --
9906:
9907: x_vmi_flag PO_LINES_INTERFACE.VMI_FLAG%TYPE; /* VMI FPH */
9908: x_drop_ship_flag PO_LINES_INTERFACE.DROP_SHIP_FLAG%TYPE; --
9909:
9910: x_source_doc_id number; -- FPI GA
9911: x_source_doc_line_id number; -- FPI GA
9904: x_row_id varchar2(25) := null;
9905: --
9906:
9907: x_vmi_flag PO_LINES_INTERFACE.VMI_FLAG%TYPE; /* VMI FPH */
9908: x_drop_ship_flag PO_LINES_INTERFACE.DROP_SHIP_FLAG%TYPE; --
9910: x_source_doc_id number; -- FPI GA
9911: x_source_doc_line_id number; -- FPI GA
9912:
9911: x_source_doc_line_id number; -- FPI GA
9912:
9913: x_consigned_flag VARCHAR2(1) := 'N'; --CONSIGNED FPI
9914: x_create_new_line VARCHAR2(1) := 'N'; --GA FPI
9915: l_supplier_ref_number PO_LINES_INTERFACE.supplier_ref_number%TYPE; --
9917: l_contract_id PO_LINES_ALL.contract_id%TYPE; --
9918:
9919: /* Bug 3201308 start */
9913: x_consigned_flag VARCHAR2(1) := 'N'; --CONSIGNED FPI
9914: x_create_new_line VARCHAR2(1) := 'N'; --GA FPI
9915: l_supplier_ref_number PO_LINES_INTERFACE.supplier_ref_number%TYPE; --
9916:
9917: l_contract_id PO_LINES_ALL.contract_id%TYPE; --
9919: /* Bug 3201308 start */
9920: l_needby_prf varchar2(1);
9921: l_shipto_prf varchar2(1);
9941:
9942: /* Bug 1949160. Created a cursor to retrieve requisition line-id */
9943: CURSOR interface_lines_temp IS
9944: SELECT pli.requisition_line_id
9945: FROM po_lines_interface pli
9946: WHERE pli.interface_header_id = x_interface_header_id
9947: -- bug 4000047: start: requisition lines should be entered
9948: -- into PO the same order they appear in the requisition
9949: ORDER BY pli.requisition_line_id;
9977: pli.from_line_id, -- FPI GA
9978: pli.consigned_flag, -- CONSIGNED FPI
9979: pli.contract_id, --
9980: pli.supplier_ref_number --
9981: FROM po_lines_interface pli,
9982: po_distributions_interface pld
9983: WHERE pli.interface_header_id=x_interface_header_id
9984: AND pli.interface_line_id=pld.interface_line_id
9985: AND pld.interface_distribution_id =
10070: -- use requisition numbers
10071:
10072: l_progress:='035';
10073:
10074: UPDATE po_lines_interface pli
10075: SET pli.shipment_num = 1,
10076: pli.line_num =
10077: (
10078: SELECT prl.line_num
10098: EXIT WHEN interface_lines_temp%NOTFOUND;
10099:
10100: l_progress := '050';
10101:
10102: UPDATE po_lines_interface pli
10103: SET pli.line_num = x_count,
10104: pli.shipment_num = 1
10105: WHERE pli.requisition_line_id = x_requisition_line_id
10106: AND pli.interface_header_id = x_interface_header_id
10198: SELECT MIN(pli.line_num),
10199: pli.po_line_id
10200: INTO x_po_line_num,
10201: x_po_line_id
10202: FROM po_lines_interface pli
10203: WHERE pli.interface_header_id = x_interface_header_id
10204: AND pli.line_num IS NOT NULL
10205: AND pli.line_type_id = x_line_type_id
10206: AND NVL(pli.item_id, -1) = NVL(x_item_id, -1)
10245: p_token => l_progress,
10246: p_message => 'Group_interface_lines progress is :'|| l_progress);
10247: END IF; -- debug logging
10248:
10249: UPDATE po_lines_interface pli
10250: SET pli.line_num = x_po_line_num
10251: WHERE pli.interface_header_id = x_interface_header_id
10252: AND pli.requisition_line_id = x_requisition_line_id;
10253:
10268: -- use requisition numbers
10269:
10270: l_progress := '100';
10271:
10272: UPDATE po_lines_interface pli
10273: SET pli.shipment_num =
10274: ( SELECT prl.line_num
10275: FROM po_requisition_lines_all prl
10276: WHERE prl.requisition_line_id = pli.requisition_line_id )
10290: EXIT WHEN interface_lines_temp%NOTFOUND;
10291:
10292: l_progress := '110';
10293:
10294: UPDATE po_lines_interface pli
10295: SET pli.shipment_num = x_count
10296: WHERE pli.requisition_line_id = x_requisition_line_id
10297: AND pli.interface_header_id = x_interface_header_id
10298: AND pli.shipment_num IS NULL;
10329:
10330: SELECT NVL(max(pl.line_num),0)
10331: INTO x_line_num
10332: FROM po_headers_all ph,
10333: po_lines_all pl
10334: WHERE pl.po_header_id = ph.po_header_id
10335: AND ph.segment1 = x_document_num
10336: AND ph.type_lookup_code =
10337: DECODE(g_document_type, 'RFQ', g_document_type, x_document_subtype)
10349: EXIT WHEN interface_lines_temp%NOTFOUND;
10350:
10351: l_progress := '150';
10352:
10353: UPDATE po_lines_interface pli
10354: SET pli.line_num = x_line_num + x_count,
10355: pli.shipment_num = 1
10356: WHERE pli.requisition_line_id = x_requisition_line_id
10357: AND pli.interface_header_id = x_interface_header_id
10451: SELECT MIN(pli.line_num),
10452: pli.po_line_id
10453: INTO x_po_line_num,
10454: x_po_line_id
10455: FROM po_lines_interface pli
10456: WHERE pli.interface_header_id = x_interface_header_id
10457: AND pli.line_num IS NOT NULL
10458: AND pli.line_type_id = x_line_type_id
10459: AND NVL(pli.item_id, -1) = NVL(x_item_id, -1)
10495: p_token => l_progress,
10496: p_message => 'Group_interface_lines: Line_num is :'|| x_po_line_num);
10497: END IF;
10498:
10499: UPDATE po_lines_interface pli
10500: SET pli.line_num = x_po_line_num
10501: WHERE pli.interface_header_id = x_interface_header_id
10502: AND pli.requisition_line_id = x_requisition_line_id;
10503:
10544: -- Prior to this fix, for Releases, the shipment number was
10545: -- derived by adding requisition line number to the current
10546: -- maximum shipment number. Due to this shipment numbers
10547: -- were getting skipped. Hence commented the following update
10548: -- statement and now updating the po_lines_interface in a loop using
10549: -- a cursor.
10550:
10551: OPEN interface_lines_temp;
10552: LOOP
10556: EXIT WHEN interface_lines_temp%NOTFOUND;
10557:
10558: l_progress := '220';
10559:
10560: UPDATE po_lines_interface pli
10561: SET pli.shipment_num = x_shipment_num + x_count
10562: WHERE pli.requisition_line_id = x_requisition_line_id
10563: AND pli.interface_header_id = x_interface_header_id
10564: AND pli.line_num IS NOT NULL
10593: -- Get maximum line number in interface table, to be used later
10594:
10595: SELECT NVL(max(pli.line_num), 0)
10596: INTO l_max_iface_line_num
10597: FROM po_lines_interface pli
10598: WHERE pli.interface_header_id = x_interface_header_id;
10599:
10600: --
10601:
10650: -- to DEFAULT.
10651:
10652: -- Do not group; simply add 1 to each successive interface line
10653:
10654: UPDATE po_lines_interface pli
10655: SET pli.line_num = l_max_iface_line_num + 1
10656: WHERE pli.rowid = x_row_id
10657: AND pli.line_num IS NULL;
10658:
10736: SELECT pol.po_line_id,
10737: pol.line_num
10738: INTO x_po_line_id,
10739: x_po_line_num
10740: FROM po_lines_all pol,
10741: po_headers_all poh
10742: WHERE poh.segment1 = x_document_num
10743: AND pol.line_num = x_interface_line_num
10744: AND poh.type_lookup_code =
10868: SELECT line_num
10869: , po_line_id
10870: INTO x_po_line_num
10871: , x_po_line_id
10872: FROM po_lines_all POL2
10873: , po_headers_all POH
10874: , po_line_types_b PLT --
10875: WHERE POH.segment1 = x_document_num
10876: AND POH.po_header_id = POL2.po_header_id
10884: --
10885: --
10886: AND pol2.line_num =
10887: (SELECT /*+ NO_UNNEST */ MIN(line_num)
10888: FROM po_lines_all pol --
10890: AND NVL(CANCEL_FLAG,'N') = 'N'
10891: AND LINE_TYPE_ID = x_line_type_id
10892: AND nvl(pol.ITEM_ID, -1) = nvl(x_item_id, -1) -- bgu, For one time item
10982: SELECT line_num,
10983: po_line_id
10984: INTO x_po_line_num,
10985: x_po_line_id
10986: FROM po_lines_all POL2,
10987: po_headers_all POH
10988: WHERE POH.segment1 = x_document_num
10989: AND POH.po_header_id = POL2.po_header_id
10990: AND NVL(poh.org_id, -99) = NVL(g_purchasing_ou_id, -99) --
10991: AND POH.type_lookup_code = 'RFQ'
10992: AND pol2.line_num =
10993: (
10994: SELECT /*+ NO_UNNEST */ MIN(line_num)
10995: FROM PO_LINES_ALL pol
10996: WHERE pol.po_header_id = poh.po_header_id
10997: AND LINE_TYPE_ID = x_line_type_id
10998: AND nvl(pol.ITEM_ID, -1) = nvl(x_item_id, -1)
10999: AND nvl(pol.ITEM_DESCRIPTION,'null') =
11055: -- SQL Join: business logic for combining two lines
11056:
11057: SELECT min(pli.line_num)
11058: INTO x_po_line_num
11059: FROM po_lines_interface pli
11060: , po_requisition_lines_all prl
11061: , po_line_types_b PLT --
11062: WHERE pli.interface_header_id = x_interface_header_id
11063: AND pli.line_num is not null
11214: END IF;
11215:
11216: SELECT MIN(pli.line_num)
11217: INTO x_po_line_num
11218: FROM po_lines_interface pli
11219: WHERE pli.interface_header_id = x_interface_header_id
11220: AND pli.line_num is not null
11221: AND pli.line_type_id = x_line_type_id
11222: AND nvl(pli.item_id, -1) = nvl(x_item_id, -1)
11267:
11268: -- Since get_shipment_num will need it, we update the
11269: -- line number here.
11270:
11271: UPDATE po_lines_interface pli
11272: SET pli.line_num = x_po_line_num
11273: WHERE pli.interface_header_id = x_interface_header_id
11274: AND pli.requisition_line_id = x_requisition_line_id;
11275:
11320: --by a req and the shipment num would be 1. There can't be two
11321: --similar negotiation lines not backed by a req, having the
11322: --same bid number and bid line number. If that happens we don't
11323: --group them to a single line. Also no need to select from
11324: --po_lines table as we are not supporting add to functionality.
11325:
11326: l_progress := '400';
11327:
11328: UPDATE po_lines_interface pli2
11324: --po_lines table as we are not supporting add to functionality.
11325:
11326: l_progress := '400';
11327:
11328: UPDATE po_lines_interface pli2
11329: SET (pli2.line_num, pli2.shipment_num) =
11330: (
11331: SELECT (NVL(max(pli.line_num), 0) + 1), 1
11332: FROM po_lines_interface pli
11328: UPDATE po_lines_interface pli2
11329: SET (pli2.line_num, pli2.shipment_num) =
11330: (
11331: SELECT (NVL(max(pli.line_num), 0) + 1), 1
11332: FROM po_lines_interface pli
11333: WHERE pli.interface_header_id = x_interface_header_id
11334: )
11335: WHERE pli2.rowid = x_row_id;
11336:
11353:
11354: SELECT NVL(max(pl.line_num), 0)
11355: INTO x_line_num
11356: FROM po_headers_all ph,
11357: po_lines_all pl
11358: WHERE pl.po_header_id = ph.po_header_id
11359: AND ph.segment1 = x_document_num
11360: AND NVL(ph.org_id, -99) = NVL(g_purchasing_ou_id, -99)
11361: AND ph.type_lookup_code =
11367: l_progress:='420';
11368: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
11369: PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
11370: p_token => l_progress,
11371: p_message => 'Before select max line_num from po_lines_interface');
11372: END IF;
11373:
11374: SELECT NVL(max(pli.line_num), 0)
11375: INTO x_int_line_num
11372: END IF;
11373:
11374: SELECT NVL(max(pli.line_num), 0)
11375: INTO x_int_line_num
11376: FROM po_lines_interface pli
11377: WHERE pli.interface_header_id = x_interface_header_id;
11378:
11379: IF (x_line_num >= x_int_line_num) THEN
11380: x_line_num := x_line_num;
11392: END IF;
11393:
11394: IF (x_requisition_line_id IS NOT NULL) THEN
11395:
11396: UPDATE po_lines_interface pli
11397: SET pli.line_num = x_line_num + 1,
11398: pli.shipment_num = 1
11399: WHERE pli.interface_header_id = x_interface_header_id
11400: AND pli.requisition_line_id = x_requisition_line_id;
11402: ELSE
11403:
11404: -- no backing req line; use rowid
11405:
11406: UPDATE po_lines_interface pli
11407: SET pli.line_num = x_line_num + 1,
11408: pli.shipment_num = 1
11409: WHERE pli.rowid = x_row_id;
11410:
11514: SELECT min(line_num)
11515: , po_line_id
11516: INTO x_po_line_num
11517: , x_po_line_id
11518: FROM po_lines_interface PLI
11519: , po_line_types_b PLT --
11520: WHERE pli.interface_header_id = x_interface_header_id
11521: AND pli.line_num is not null
11522: AND pli.LINE_TYPE_ID = x_line_type_id
11557: END;
11558:
11559: l_progress := '470';
11560:
11561: UPDATE po_lines_interface pli
11562: SET pli.line_num = x_po_line_num
11563: WHERE pli.interface_header_id = x_interface_header_id
11564: AND pli.requisition_line_id = x_requisition_line_id;
11565:
11567:
11568: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
11569: PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
11570: p_token => l_progress,
11571: p_message => 'After update of po_lines_interface line_num');
11572: END IF;
11573:
11574: ELSE
11575:
11584: BEGIN
11585:
11586: SELECT pol.po_line_id
11587: INTO x_po_line_id
11588: FROM po_lines_all pol,
11589: po_headers_all poh,
11590: po_lines_interface pli
11591: WHERE pol.po_header_id = poh.po_header_id
11592: AND poh.segment1 = x_document_num
11586: SELECT pol.po_line_id
11587: INTO x_po_line_id
11588: FROM po_lines_all pol,
11589: po_headers_all poh,
11590: po_lines_interface pli
11591: WHERE pol.po_header_id = poh.po_header_id
11592: AND poh.segment1 = x_document_num
11593: AND NVL(poh.org_id, -99) = NVL(g_purchasing_ou_id, -99)
11594: AND poh.type_lookup_code = 'BLANKET' -- Bug# 1746943
12156:
12157: SELECT POL.po_line_id
12158: INTO l_po_line_id
12159: FROM po_headers_interface PHI,
12160: po_lines_all POL
12161: WHERE PHI.interface_header_id = x_interface_header_id
12162: AND PHI.po_header_id = POL.po_header_id
12163: AND POL.line_num = x_po_line_num;
12164: ELSE
12181: ,PLL.line_location_id
12182: INTO x_po_shipment_num,
12183: x_line_location_to_check
12184: FROM PO_LINE_LOCATIONS_ALL PLL, --
12185: PO_LINES_ALL POL, --
12187: WHERE POL.PO_LINE_ID = l_po_line_id -- bug2788115
12188: AND POL.po_line_id = PLL.po_line_id --
12189: AND POL.line_type_id = PLT.line_type_id --
12295: decode(psp.expense_accrual_code,'PERIOD END', 'N', 'Y')),
12296: 'INVENTORY', 'Y',
12297: 'SHOP FLOOR','Y')
12298: INTO x_receipt_required_flag
12299: FROM po_lines_interface pli,
12300: po_headers_interface phi,
12301: po_requisition_lines_all prl, --
12302: mtl_system_items msi,
12303: po_line_types plt,
12358: SELECT PLI.shipment_num
12359: ,PLI.requisition_line_id
12360: INTO x_po_shipment_num
12361: ,x_req_line_to_check
12362: FROM PO_LINES_INTERFACE PLI,
12363: PO_REQUISITION_LINES_ALL PRL, --
12364: --bug 1942696 hr_location changes to reflect the new view
12365: MTL_SYSTEM_ITEMS MSI ,
12366: PO_LINE_TYPES PLT ,
12480: to improve the performance
12481: */
12482: SELECT PLI.shipment_num
12483: INTO x_po_shipment_num
12484: FROM PO_LINES_INTERFACE PLI,
12485: PO_REQUISITION_LINES_ALL PRL, --
12486: MTL_SYSTEM_ITEMS MSI ,
12487: PO_LINE_TYPES PLT ,
12488: PO_SYSTEM_PARAMETERS_ALL PSP , --
12607:
12608: l_progress:='190';
12609: SELECT PLI.shipment_num
12610: INTO x_po_shipment_num
12611: FROM PO_LINES_INTERFACE PLI,
12612: PO_REQUISITION_LINES_ALL PRL, --
12613: --bug 1942696 hr_location changes to reflect the new view
12614: MTL_SYSTEM_ITEMS MSI ,
12615: PO_LINE_TYPES PLT ,
12747: */
12748:
12749: if x_requisition_line_id is not null then
12750: l_progress := '010';
12751: update po_lines_interface
12752: set shipment_num= x_po_shipment_num
12753: where interface_header_id=x_interface_header_id
12754: and requisition_line_id=x_requisition_line_id;
12755:
12754: and requisition_line_id=x_requisition_line_id;
12755:
12756: else
12757: l_progress := '015';
12758: update po_lines_interface
12759: set shipment_num= x_po_shipment_num
12760: where interface_header_id=x_interface_header_id
12761: and rowid=x_row_id;
12762: end if;
12796: l_progress := '030';
12797: select nvl(max(line_num),0)
12798: into x_line_num
12799: from po_headers_all ph, --
12800: po_lines_all pl --
12802: and ph.segment1 = x_document_num
12803: AND NVL(ph.org_id, -99) = NVL(g_purchasing_ou_id, -99); --
12804:
12804:
12805: l_progress := '040';
12806: select nvl(max(line_num),0)
12807: into x_int_line_num
12808: from po_lines_interface pli
12809: where pli.interface_header_id = x_interface_header_id;
12810:
12811: IF (x_line_num >= x_int_line_num) THEN
12812: x_line_num := x_line_num;
12814: x_line_num := x_int_line_num;
12815: END IF;
12816:
12817: l_progress := '050';
12818: update po_lines_interface
12819: set line_num = x_line_num + 1
12820: where interface_header_id = x_interface_header_id
12821: and requisition_line_id = x_requisition_line_id;
12822: -- Bug 6072900
12844: ** assigned in the interface table.
12845: */
12846: select nvl(max(shipment_num),0)
12847: into x_int_shipment_num
12848: from po_lines_interface pli
12849: where pli.interface_header_id = x_interface_header_id
12850: and pli.line_num = x_po_line_num;
12851:
12852: -- End of fix. Bug 605715, lpo, 01/05/98
12878: ** assigned in the interface table.
12879: */
12880: select nvl(max(shipment_num),0)
12881: into x_int_shipment_num
12882: from po_lines_interface pli
12883: where pli.interface_header_id = x_interface_header_id;
12884:
12885: -- End of fix. Bug 605715, lpo, 01/05/98
12886:
12895: END IF;
12896:
12897: if x_requisition_line_id is not null then
12898: l_progress := '110';
12899: update po_lines_interface
12900: set shipment_num = x_shipment_num + 1
12901: where interface_header_id=x_interface_header_id
12902: and requisition_line_id=x_requisition_line_id;
12903: else
12901: where interface_header_id=x_interface_header_id
12902: and requisition_line_id=x_requisition_line_id;
12903: else
12904: l_progress := '120';
12905: update po_lines_interface
12906: set shipment_num = x_shipment_num + 1
12907: where interface_header_id=x_interface_header_id
12908: and rowid=x_row_id;
12909: end if;
12995: that of requisition line. For others, both item_id and description
12996: should match.
12997: - If there is no BPO line matching the description, then just match
12998: the item_id.
12999: - For any case above, if more than one candidate BPO lines pass the
13000: conditions, then pick the one having the minimum line_num
13001: See Bug 2082757 for details.
13002: ===========================================================================*/
13003:
13024: p_item_rev_control IN NUMBER,
13025: x_allow_different_uoms IN VARCHAR2 -- Bug 2707576
13026: ) IS
13027: SELECT MIN(pol.line_num)
13028: FROM po_lines_all pol,
13029: po_requisition_lines_all prl --
13030: WHERE pol.po_header_id = p_po_header_id
13031: AND prl.requisition_line_id = p_requisition_line_id
13032: AND NVL(pol.cancel_flag,'N') = 'N'
13096: p_item_rev_control IN NUMBER,
13097: x_allow_different_uoms IN VARCHAR2 -- Bug 2707576
13098: ) IS
13099: SELECT MIN(pol.line_num)
13100: FROM po_lines_all pol,
13101: po_requisition_lines_all prl --
13102: WHERE pol.po_header_id = p_po_header_id
13103: AND prl.requisition_line_id = p_requisition_line_id
13104: AND NVL(pol.cancel_flag,'N') = 'N'
13219: /*============================================================================
13220: Name : CREATE_PRICE_BREAK
13221: Type : Private
13222: Function : This procedure is called from 'create_line'. This procedure inserts
13223: records from po_lines_interface table to po_line_locations_all table
13224: for the price break information.
13225: Pre-req : None
13226: Parameters:
13227: IN : p_po_line_id IN NUMBER REQUIRED
13240: l_ship_org_id_line mtl_system_items.organization_id%type;
13241: l_ship_org_code varchar2(3);
13242: l_ship_org_name varchar2(60);
13243:
13244: l_value_basis PO_LINES_ALL.order_type_lookup_code%TYPE; --
13246: BEGIN
13247: -- Standard start of API savepoint
13248: SAVEPOINT create_price_break_pvt;
13291: -- Get value basis from line
13292:
13293: SELECT pol.order_type_lookup_code
13294: INTO l_value_basis
13295: FROM po_lines_all pol
13296: WHERE pol.po_line_id = p_po_line_id;
13297:
13298: l_progress := '025';
13299:
13627: END IF;
13628:
13629: SELECT count(*)
13630: INTO l_num_config_id_lines
13631: FROM po_lines_interface PLI, po_requisition_lines PRL
13632: WHERE PLI.interface_header_id = p_interface_header_id
13633: AND PLI.requisition_line_id = PRL.requisition_line_id -- JOIN
13634: AND PRL.supplier_ref_number IS NOT NULL;
13635:
13978: -- populated, use that information. Otherwise, create a default
13979: -- payitem. Also create DELIVERY and ADVANCE payitems as necessary.
13980: --Parameters:
13981: -- IN:
13982: -- p_interface_line_id: id of the line in po_lines_interface
13983: -- p_po_line_id: id of the line in po_lines_all
13984: -- p_precision: precision of the currency desired. Used to round amounts.
13985: -- p_ext_precision: extended precision of the currency desired.
13986: -- Used to round prices
13979: -- payitem. Also create DELIVERY and ADVANCE payitems as necessary.
13980: --Parameters:
13981: -- IN:
13982: -- p_interface_line_id: id of the line in po_lines_interface
13983: -- p_po_line_id: id of the line in po_lines_all
13984: -- p_precision: precision of the currency desired. Used to round amounts.
13985: -- p_ext_precision: extended precision of the currency desired.
13986: -- Used to round prices
13987: -- OUT:
14006:
14007: d_progress NUMBER;
14008: d_module VARCHAR2(70) := 'po.plsql.PO_INTERFACE_S.create_payitems';
14009:
14010: l_po_header_id PO_LINES_ALL.po_header_id%TYPE;
14011: l_line_value_basis PO_LINES_ALL.order_type_lookup_code%TYPE;
14012: l_line_matching_basis PO_LINES_ALL.matching_basis%TYPE;
14013: l_line_unit_price PO_LINES_ALL.unit_price%TYPE;
14014: l_line_quantity PO_LINES_ALL.quantity%TYPE;
14007: d_progress NUMBER;
14008: d_module VARCHAR2(70) := 'po.plsql.PO_INTERFACE_S.create_payitems';
14009:
14010: l_po_header_id PO_LINES_ALL.po_header_id%TYPE;
14011: l_line_value_basis PO_LINES_ALL.order_type_lookup_code%TYPE;
14012: l_line_matching_basis PO_LINES_ALL.matching_basis%TYPE;
14013: l_line_unit_price PO_LINES_ALL.unit_price%TYPE;
14014: l_line_quantity PO_LINES_ALL.quantity%TYPE;
14015: l_line_amount PO_LINES_ALL.amount%TYPE;
14008: d_module VARCHAR2(70) := 'po.plsql.PO_INTERFACE_S.create_payitems';
14009:
14010: l_po_header_id PO_LINES_ALL.po_header_id%TYPE;
14011: l_line_value_basis PO_LINES_ALL.order_type_lookup_code%TYPE;
14012: l_line_matching_basis PO_LINES_ALL.matching_basis%TYPE;
14013: l_line_unit_price PO_LINES_ALL.unit_price%TYPE;
14014: l_line_quantity PO_LINES_ALL.quantity%TYPE;
14015: l_line_amount PO_LINES_ALL.amount%TYPE;
14016: l_line_purchase_basis PO_LINES_ALL.purchase_basis%TYPE;
14009:
14010: l_po_header_id PO_LINES_ALL.po_header_id%TYPE;
14011: l_line_value_basis PO_LINES_ALL.order_type_lookup_code%TYPE;
14012: l_line_matching_basis PO_LINES_ALL.matching_basis%TYPE;
14013: l_line_unit_price PO_LINES_ALL.unit_price%TYPE;
14014: l_line_quantity PO_LINES_ALL.quantity%TYPE;
14015: l_line_amount PO_LINES_ALL.amount%TYPE;
14016: l_line_purchase_basis PO_LINES_ALL.purchase_basis%TYPE;
14017:
14010: l_po_header_id PO_LINES_ALL.po_header_id%TYPE;
14011: l_line_value_basis PO_LINES_ALL.order_type_lookup_code%TYPE;
14012: l_line_matching_basis PO_LINES_ALL.matching_basis%TYPE;
14013: l_line_unit_price PO_LINES_ALL.unit_price%TYPE;
14014: l_line_quantity PO_LINES_ALL.quantity%TYPE;
14015: l_line_amount PO_LINES_ALL.amount%TYPE;
14016: l_line_purchase_basis PO_LINES_ALL.purchase_basis%TYPE;
14017:
14018: l_payment_type PO_LINE_LOCATIONS_ALL.payment_type%TYPE;
14011: l_line_value_basis PO_LINES_ALL.order_type_lookup_code%TYPE;
14012: l_line_matching_basis PO_LINES_ALL.matching_basis%TYPE;
14013: l_line_unit_price PO_LINES_ALL.unit_price%TYPE;
14014: l_line_quantity PO_LINES_ALL.quantity%TYPE;
14015: l_line_amount PO_LINES_ALL.amount%TYPE;
14016: l_line_purchase_basis PO_LINES_ALL.purchase_basis%TYPE;
14017:
14018: l_payment_type PO_LINE_LOCATIONS_ALL.payment_type%TYPE;
14019: l_shipment_type PO_LINE_LOCATIONS_ALL.shipment_type%TYPE;
14012: l_line_matching_basis PO_LINES_ALL.matching_basis%TYPE;
14013: l_line_unit_price PO_LINES_ALL.unit_price%TYPE;
14014: l_line_quantity PO_LINES_ALL.quantity%TYPE;
14015: l_line_amount PO_LINES_ALL.amount%TYPE;
14016: l_line_purchase_basis PO_LINES_ALL.purchase_basis%TYPE;
14017:
14018: l_payment_type PO_LINE_LOCATIONS_ALL.payment_type%TYPE;
14019: l_shipment_type PO_LINE_LOCATIONS_ALL.shipment_type%TYPE;
14020: l_payitem_quantity PO_LINE_LOCATIONS_ALL.quantity%TYPE;
14102: , l_line_unit_price
14103: , l_line_quantity
14104: , l_line_amount
14105: , l_line_purchase_basis
14106: FROM po_lines_all pol
14107: WHERE pol.po_line_id = p_po_line_id;
14108:
14109: d_progress := 20;
14110:
14707: --Parameters:
14708: -- IN:
14709: -- p_req_line_id: id of the requisition line that is the source of
14710: -- the po line; null if no backing req.
14711: -- p_po_line_id: id of the line in po_lines_all
14712: -- p_interface_line_id: id of the line in po_lines_interface
14713: -- p_precision: precision of the currency desired. Used to round amounts.
14714: -- p_ext_precision: extended precision of the currency desired.
14715: -- Used to round prices
14708: -- IN:
14709: -- p_req_line_id: id of the requisition line that is the source of
14710: -- the po line; null if no backing req.
14711: -- p_po_line_id: id of the line in po_lines_all
14712: -- p_interface_line_id: id of the line in po_lines_interface
14713: -- p_precision: precision of the currency desired. Used to round amounts.
14714: -- p_ext_precision: extended precision of the currency desired.
14715: -- Used to round prices
14716: -- OUT: