[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:
310: , pli.supplier_part_auxid supplier_part_auxid
311: , pli.catalog_name catalog_name
312: --
313: FROM po_headers_interface phi,
314: po_lines_interface pli,
315: po_distributions_interface pdi
316: WHERE phi.interface_header_id = pli.interface_header_id
317: AND pli.interface_line_id = pdi.interface_line_id
318: AND phi.interface_header_id = x_interface_header_id
353: g_rate_for_req_fields NUMBER; --
354: g_line_requesting_ou_id PO_REQUISITION_LINES_ALL.org_id%TYPE; --
355:
356: /* Global variable to hold number of req lines
357: ** in the po_lines_interface table. Thie determines
358: ** if we copy project_id, task_id from req to rfq
359: */
360: g_req_lines_to_process number:=0;
361:
1097: -- and prl.auction_line_number<>pli.auction_line_number)" in the
1098: --following sql.
1099:
1100: l_progress:='060';
1101: update po_lines_interface pli
1102: set pli.requisition_line_id= null
1103: where pli.interface_header_id= x_interface_header_id
1104: and exists
1105: (select requisition_line_id
1122: p_token => l_progress,
1123: p_message => 'Sourcing to PO FPH-1: before the update for withdrawn reqs');
1124: END IF;
1125:
1126: UPDATE po_lines_interface pli
1127: SET pli.requisition_line_id= null
1128: WHERE pli.interface_header_id= x_interface_header_id
1129: AND NOT EXISTS
1130: (SELECT requisition_line_id
1149: * project/task reference from RFQ to Quote
1150: */
1151: SELECT count(*)
1152: INTO g_req_lines_to_process
1153: FROM po_lines_interface
1154: WHERE interface_header_id = x_interface_header_id;
1155:
1156: l_progress:='090';
1157:
1582: END IF;
1583:
1584: select distinct 'Y'
1585: into X_line_found
1586: from po_lines_interface
1587: where interface_header_id = x_interface_header_id
1588: and line_num is not null;
1589:
1590: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
1589:
1590: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
1591: PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
1592: p_token => l_progress,
1593: p_message => 'Create PO : Line found in po_lines_interfaces');
1594: END IF;
1595:
1596: EXCEPTION
1597: WHEN OTHERS THEN
3000: )
3001: IS
3002: x_po_line_id number;
3003: x_po_line_type_id number;
3004: x_line_num po_lines.line_num%type;
3005: x_po_item_id number;
3006: x_order_type_lookup_code varchar2(25);
3007: l_purchase_basis PO_LINE_TYPES_B.purchase_basis%TYPE; --
3008: x_po_item_revision po_lines.item_revision%type;
3004: x_line_num po_lines.line_num%type;
3005: x_po_item_id number;
3006: x_order_type_lookup_code varchar2(25);
3007: l_purchase_basis PO_LINE_TYPES_B.purchase_basis%TYPE; --
3008: x_po_item_revision po_lines.item_revision%type;
3009: x_po_unit_meas_lookup_code po_lines.unit_meas_lookup_code%type;
3010: x_po_unit_price number;
3011: x_po_transaction_reason_code po_lines.transaction_reason_code%type;
3012: x_price_break_lookup_code po_lines.price_break_lookup_code%type;
3005: x_po_item_id number;
3006: x_order_type_lookup_code varchar2(25);
3007: l_purchase_basis PO_LINE_TYPES_B.purchase_basis%TYPE; --
3008: x_po_item_revision po_lines.item_revision%type;
3009: x_po_unit_meas_lookup_code po_lines.unit_meas_lookup_code%type;
3010: x_po_unit_price number;
3011: x_po_transaction_reason_code po_lines.transaction_reason_code%type;
3012: x_price_break_lookup_code po_lines.price_break_lookup_code%type;
3013: x_quantity number := '';
3007: l_purchase_basis PO_LINE_TYPES_B.purchase_basis%TYPE; --
3008: x_po_item_revision po_lines.item_revision%type;
3009: x_po_unit_meas_lookup_code po_lines.unit_meas_lookup_code%type;
3010: x_po_unit_price number;
3011: x_po_transaction_reason_code po_lines.transaction_reason_code%type;
3012: x_price_break_lookup_code po_lines.price_break_lookup_code%type;
3013: x_quantity number := '';
3014: x_requisition_header_id number := ''; /* Used for copying attachments */
3015:
3008: x_po_item_revision po_lines.item_revision%type;
3009: x_po_unit_meas_lookup_code po_lines.unit_meas_lookup_code%type;
3010: x_po_unit_price number;
3011: x_po_transaction_reason_code po_lines.transaction_reason_code%type;
3012: x_price_break_lookup_code po_lines.price_break_lookup_code%type;
3013: x_quantity number := '';
3014: x_requisition_header_id number := ''; /* Used for copying attachments */
3015:
3016: --fix 8976669
3024:
3025: x_quote_header_id number := null;
3026: x_quote_line_id number := null;
3027: x_match_blanket_line varchar2(1) := null;
3028: x_unit_price po_lines.unit_price%TYPE := null;
3029: l_base_unit_price po_lines.base_unit_price%TYPE := null; --
3030:
3031: /* obtain currency info to adjust precision */
3032: x_precision number :='';
3025: x_quote_header_id number := null;
3026: x_quote_line_id number := null;
3027: x_match_blanket_line varchar2(1) := null;
3028: x_unit_price po_lines.unit_price%TYPE := null;
3029: l_base_unit_price po_lines.base_unit_price%TYPE := null; --
3031: /* obtain currency info to adjust precision */
3032: x_precision number :='';
3033: x_ext_precision number :='';
3037: x_task_id number :='';
3038: x_req_dist_id number :='';
3039: --togeorge 11/17/2000
3040: --Bug# 1369049
3041: --Added logic to default tax_name in po_lines
3042: x_tax_id ap_tax_codes.tax_id%type;
3043: x_tax_type ap_tax_codes.tax_type%type;
3044: x_description ap_tax_codes.description%type;
3045: x_allow_tax_code_override_flag gl_tax_option_accounts.allow_tax_code_override_flag%type;
3042: x_tax_id ap_tax_codes.tax_id%type;
3043: x_tax_type ap_tax_codes.tax_type%type;
3044: x_description ap_tax_codes.description%type;
3045: x_allow_tax_code_override_flag gl_tax_option_accounts.allow_tax_code_override_flag%type;
3046: x_tax_name po_lines.tax_name%type;
3047: x_ship_to_location_id number:= 0;
3048: x_ship_to_loc_org_id mtl_system_items.organization_id%TYPE;
3049: x_ship_org_code varchar2(3);
3050:
3057: -- bug# 3345108
3058: -- comment out most of the changes done by bug 2219743.
3059: -- bug# 2219743
3060: /**
3061: x_secondary_qty po_lines.secondary_quantity%type := NULL;
3062: x_item_number VARCHAR2(240);
3063: x_process_org VARCHAR2(1);
3064: x_dummy VARCHAR2(240);
3065: x_product VARCHAR2(3) := 'GMI';
3085: --sourcing entities need to be suppressed due to the grouping of lines.
3086: x_attch_suppress_flag varchar2(1) :='N';
3087: --
3088:
3089: l_db_quantity po_lines.quantity%TYPE := null; --bug#2723479
3090:
3091: -- Bug 2735840 START
3092: l_uom_convert varchar2(2) := fnd_profile.value('PO_REQ_BPA_UOM_CONVERT');
3093: l_ga_uom PO_LINES.unit_meas_lookup_code%TYPE;
3089: l_db_quantity po_lines.quantity%TYPE := null; --bug#2723479
3090:
3091: -- Bug 2735840 START
3092: l_uom_convert varchar2(2) := fnd_profile.value('PO_REQ_BPA_UOM_CONVERT');
3093: l_ga_uom PO_LINES.unit_meas_lookup_code%TYPE;
3094: l_quantity_in_ga_uom PO_LINES_INTERFACE.quantity%TYPE;
3095: l_conversion_rate number :=1;
3096: -- Bug 2735840 END
3097:
3090:
3091: -- Bug 2735840 START
3092: l_uom_convert varchar2(2) := fnd_profile.value('PO_REQ_BPA_UOM_CONVERT');
3093: l_ga_uom PO_LINES.unit_meas_lookup_code%TYPE;
3094: l_quantity_in_ga_uom PO_LINES_INTERFACE.quantity%TYPE;
3095: l_conversion_rate number :=1;
3096: -- Bug 2735840 END
3097:
3098: -- Bug 2875346.
3113: l_outside_operation_flag PO_LINE_TYPES_B.outside_operation_flag%TYPE;
3114: l_receiving_flag PO_LINE_TYPES_B.receiving_flag%TYPE;
3115: l_receive_close_tolerance PO_LINE_TYPES_B.receive_close_tolerance%TYPE;
3116: --
3117: l_negotiated_by_preparer_flag po_lines_all.negotiated_by_preparer_flag%type; --
3119: l_global_agreement_flag po_headers_all.global_agreement_flag%type; --
3120:
3121: -- oneoff 3201308 start
3132:
3133: l_routing_name RCV_ROUTING_HEADERS.routing_name%TYPE; --
3134:
3135: l_progress VARCHAR2(3) := '000'; --< Bug 3210331 >
3136: l_manual_price_change_flag po_lines_all.manual_price_change_flag%TYPE := NULL; --bug 3495772
3137: l_from_type_lookup_code PO_HEADERS.type_lookup_code%type;--bug#3612701
3138:
3139: --
3140: x_secondary_unit_def MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
3138:
3139: --
3140: x_secondary_unit_def MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
3141: x_secondary_uom MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
3142: x_secondary_quantity_def PO_LINES.SECONDARY_QUANTITY%TYPE;
3143: x_preferred_grade_def MTL_GRADES.GRADE_CODE%TYPE;
3144: l_quantity_temp PO_LINES.QUANTITY%TYPE;
3145: --
3146:
3140: x_secondary_unit_def MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
3141: x_secondary_uom MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
3142: x_secondary_quantity_def PO_LINES.SECONDARY_QUANTITY%TYPE;
3143: x_preferred_grade_def MTL_GRADES.GRADE_CODE%TYPE;
3144: l_quantity_temp PO_LINES.QUANTITY%TYPE;
3145: --
3146:
3147: l_requesting_ou_id PO_REQUISITION_LINES_ALL.org_id%TYPE; --
3148: l_rate_for_req_fields GL_DAILY_RATES.conversion_rate%TYPE; --
3256: /*
3257: ** Check to see if the po line exists
3258: ** Note that we do not need to check if the line exists in the
3259: ** interface table since we are in the process of inserting
3260: ** lines into the po lines table on a record by record basis.
3261: */
3262: IF(g_document_subtype='STANDARD' or g_document_subtype='PLANNED' or
3263: g_document_type = 'RFQ'
3264: --
3289: x_po_unit_price,
3290: x_po_transaction_reason_code,
3291: x_price_break_lookup_code,
3292: l_manual_price_change_flag --bug 3495772
3293: FROM PO_LINES_ALL --
3295: AND LINE_NUM = interface.line_num
3296: FOR UPDATE OF quantity;
3297:
3312: /* Bug 2534534 Reverting the fix done in 1951084 */
3313: -- BEGIN
3314: SELECT po_line_id
3315: INTO x_po_line_id
3316: FROM po_lines_all --
3318: AND line_num = interface.line_num
3319: FOR UPDATE OF quantity;
3320:
3363:
3364: /**
3365: --mchandak 02/11/2002
3366: --Bug# 2219743
3367: --Added logic to default secondary qnty,UOM and grade in po_lines
3368: --if common purchasing is installed for OPM dual item
3369: IF(g_document_subtype='STANDARD' or g_document_subtype='PLANNED') AND NOT GML_PO_FOR_PROCESS.check_po_for_proc
3370: THEN
3371: x_retvar := FND_INSTALLATION.get_app_info(x_product,x_opm_installed,x_dummy,x_dummy);
3465: --bug#3612701 modified the sql to fetch type lookup code
3466: --of the source document as well.
3467: SELECT pol.unit_meas_lookup_code,poh.type_lookup_code
3468: INTO l_ga_uom,l_from_type_lookup_code
3469: FROM po_lines_all pol,po_headers_all poh
3470: WHERE pol.po_line_id = interface.from_line_id
3471: and poh.po_header_id=interface.from_header_id
3472: and poh.po_header_id=pol.po_header_id;
3473: --bug#3612701
4049:
4050:
4051: l_progress:='330';
4052:
4053: SELECT po_lines_s.nextval
4054: INTO x_po_line_id
4055: FROM sys.dual;
4056:
4057: l_progress:='340';
4226: -- if the source document is global agreement.
4227: IF l_type_lookup_code='BLANKET' and l_global_agreement_flag='Y' THEN
4228: l_progress := '395';
4229: SELECT negotiated_by_preparer_flag into l_negotiated_by_preparer_flag
4230: FROM po_lines_all
4231: WHERE po_line_id=interface.from_line_id;
4232: --if the source document is quotation.
4233: ELSIF l_type_lookup_code='QUOTATION' THEN
4234: l_negotiated_by_preparer_flag := 'Y';
4366: -- bug 4887900 END
4367:
4368: -- Added note_to_vendor - iali 08/26/99
4369: /*Bug 1391523 . Added market price to the INSERT statement */
4370: INSERT INTO po_lines_all --
4372: last_update_date,
4373: last_updated_by,
4374: po_header_id,
4415: oke_contract_header_id,
4416: oke_contract_version_id,
4417: --togeorge 11/17/2000
4418: --Bug# 1369049
4419: --Added logic to default tax_name in po_lines
4420: tax_name,
4421: -- start of 1548597
4422: secondary_unit_of_measure,
4423: secondary_quantity,
4549: interface.oke_contract_header_id,
4550: interface.oke_contract_version_id,
4551: --togeorge 11/17/2000
4552: --Bug# 1369049
4553: --Added logic to default tax_name in po_lines
4554: x_tax_name,
4555: --
4556: -- don't insert secondary unit/quantity/grade from interface record.
4557: -- start of 1548597
4934: l_progress := '540';
4935: begin
4936: select pl.quantity
4937: into l_db_quantity
4938: from po_lines_all pl --
4940: exception
4941: when others then
4942: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
5038:
5039: -- Bug 2879460 Update the price on the PO only with the price
5040: -- from the pricing API and not with the interface price
5041:
5042: UPDATE po_lines_all
5043: -- Bug 3417479
5044: -- SET unit_price = x_unit_price,
5045: -- base_unit_price = l_base_unit_price,
5046: SET unit_price = nvl(x_unit_price, unit_price),
5070: -- because x_po_line_id is the blanket line_id for 'Release'
5071: -- Introducing the 'If' statement for checking that its not a release
5072: IF (g_document_subtype <> 'RELEASE') THEN
5073:
5074: /** If FSP org and item combination is dual uom control, update the po lines secondary quantity
5075: with the default conversion based on the PO lines quantity **/
5076:
5077: --
5078:
5071: -- Introducing the 'If' statement for checking that its not a release
5072: IF (g_document_subtype <> 'RELEASE') THEN
5073:
5074: /** If FSP org and item combination is dual uom control, update the po lines secondary quantity
5075: with the default conversion based on the PO lines quantity **/
5076:
5077: --
5078:
5079: -- Bug 9324837
5092: END IF;
5093:
5094: -- Bug 9324837
5095: -- Added update to secondary_quantity and commented the seconday null updates.
5096: UPDATE po_lines_all --
5098: --Added a Decode for the nvl function as quantity should not be set to 0 for Fixed Price Services
5099: SET quantity = Decode(x_order_type_lookup_code,'FIXED PRICE',NULL,Nvl(quantity,0)) + nvl(x_quantity,0),
5100: secondary_quantity = (CASE -- secondary uom controlled item
5130: interface.item_id,
5131: x_secondary_unit_def,
5132: x_secondary_quantity_def) ;
5133:
5134: UPDATE po_lines_all
5135: SET secondary_quantity = x_secondary_quantity_def,
5136: secondary_unit_of_measure = x_secondary_unit_def
5137: WHERE po_line_id = x_po_line_id ;
5138:
5243: PO_NEGOTIATIONS_SV2.convert_text_to_attachment
5244: ( p_long_text => l_job_long_description
5245: , p_description => NULL
5246: , p_category_id => 33 -- To Supplier
5247: , p_to_entity_name => 'PO_LINES'
5248: , p_to_pk1_value => x_po_line_id
5249: , p_who_rec => l_who_rec
5250: );
5251:
5345: '',
5346: '',
5347: '',
5348: '',
5349: 'PO_LINES',
5350: x_po_line_id,
5351: '',
5352: '',
5353: '',
5375: '',
5376: '',
5377: '',
5378: '',
5379: 'PO_LINES',
5380: x_po_line_id,
5381: '',
5382: '',
5383: '',
5460: p_message => 'Before selecting one-time attachment');
5461: END IF;
5462:
5463: --SQL What: Locate the one-time location attachment currently under
5464: -- the PO_LINES entity by it's unique iP identifier prefix
5465: --SQL Why: Need the attached_document_id to move the attachment
5466:
5467: SELECT fad.attached_document_id
5468: INTO l_one_time_att_doc_id
5467: SELECT fad.attached_document_id
5468: INTO l_one_time_att_doc_id
5469: FROM fnd_attached_documents fad,
5470: fnd_documents_tl fdt
5471: WHERE fad.entity_name = 'PO_LINES'
5472: AND fad.pk1_value = to_char(x_po_line_id)
5473: AND fad.document_id = fdt.document_id
5474: AND fdt.language = USERENV('LANG')
5475: AND fdt.description like 'POR:%'; -- iP unique identifier
5756:
5757: --
5758: x_shipment_uom MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
5759: x_secondary_unit_of_measure MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
5760: x_secondary_quantity PO_LINES.SECONDARY_QUANTITY%TYPE;
5761: x_secondary_uom_code MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
5762: --
5763:
5764: l_matching_basis PO_LINE_TYPES.matching_basis%TYPE; --
5777: -- select out the UOM from po line
5778: BEGIN
5779: SELECT pol.unit_meas_lookup_code
5780: INTO x_temp_uom
5781: FROM po_lines_all pol
5782: WHERE po_line_id = x_po_line_id;
5783: EXCEPTION
5784: WHEN NO_DATA_FOUND THEN
5785: x_temp_uom := interface.unit_meas_lookup_code;
5871: IF (g_document_subtype='RELEASE') THEN -- Bug 2735840
5872: l_progress := '020';
5873: select unit_meas_lookup_code
5874: into x_po_uom
5875: from po_lines_all pol , --
5877: where pol.po_header_id = poh.po_header_id
5878: and pol.po_header_id = interface.po_header_id
5879: and pol.line_num = interface.line_num;
5881: ELSE -- Autocreating a PO that references a GA
5882: l_progress := '030';
5883: SELECT unit_meas_lookup_code
5884: INTO x_po_uom
5885: FROM po_lines_all
5886: WHERE po_line_id = interface.from_line_id;
5887: END IF; -- g_document_subtype
5888: -- Bug 2735840 END
5889: EXCEPTION
5954: BEGIN
5955: SELECT poll.line_location_id,poll.secondary_unit_of_measure --
5956: INTO x_line_location_id,x_secondary_unit_of_measure
5957: FROM po_line_locations_all poll, --
5958: po_lines_all pol --
5960: AND poll.po_line_id = pol.po_line_id
5961: AND poll.shipment_num = interface.shipment_num
5962: AND pol.line_num = interface.line_num
5988: INTO x_line_location_id,
5989: l_manual_price_change_flag, --bug 3495772
5990: x_secondary_unit_of_measure
5991: FROM po_line_locations_all poll, --
5992: po_lines_all pol, --
5994: WHERE poll.po_header_id = interface.po_header_id
5995: AND poll.po_line_id = pol.po_line_id
5996: AND poll.shipment_num = interface.shipment_num
6026:
6027: -- Find out if the line is using cumulative or non-cumlative pricing
6028: SELECT decode(price_break_lookup_code, 'CUMULATIVE', 'Y', 'N')
6029: INTO x_price_break_type
6030: FROM po_lines_all --
6032:
6033: IF (x_price_break_type = 'Y') THEN
6034: x_cumulative_flag := TRUE;
6101: l_progress := '110';
6102: begin
6103: SELECT unit_price
6104: INTO x_price
6105: FROM po_lines_all --
6107: exception
6108: when others then
6109: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
6420: -- bug: 404191
6421: -- Get the taxable_flag based on the following priority
6422: -- 1. preferences (global.po_taxable_flag)
6423: -- 2. ship_to_org (x_ship_to_org_taxable_flag)
6424: -- 2. item-org (po_lines.taxable_flag)
6425: -- 3. PO default (po_startup_values.taxable_flag)
6426: --
6427: l_progress:='220';
6428: x_item_org_taxable_flag := item.taxable_flag;
7306: IF (g_document_subtype='RELEASE') THEN -- Bug 2735840
7307: l_progress := '090';
7308: select unit_meas_lookup_code
7309: into x_po_uom
7310: from po_lines_all pol , --
7312: where pol.po_header_id = poh.po_header_id
7313: and pol.po_header_id = interface.po_header_id
7314: and pol.line_num = interface.line_num;
7316: ELSE -- Autocreating a PO that references a GA
7317: l_progress := '100';
7318: SELECT unit_meas_lookup_code
7319: INTO x_po_uom
7320: FROM po_lines_all
7321: WHERE po_line_id = interface.from_line_id;
7322: END IF; -- g_document_subtype
7323: -- Bug 2735840 END
7324:
8498: l_new_ccid_generated BOOLEAN := FALSE;
8499: l_FB_ERROR_MSG VARCHAR2(2000);
8500:
8501: l_return_status VARCHAR2(1);
8502: l_interface_line_id PO_LINES_INTERFACE.interface_line_id%TYPE;
8503: l_old_interface_line_id PO_LINES_INTERFACE.interface_line_id%TYPE := -1;
8504: l_interface_distribution_id PO_DISTRIBUTIONS_INTERFACE.interface_distribution_id%TYPE;
8505: l_item_category_id PO_LINES_INTERFACE.category_id%TYPE;
8506: l_req_charge_account_id PO_DISTRIBUTIONS_INTERFACE.charge_account_id%TYPE;
8499: l_FB_ERROR_MSG VARCHAR2(2000);
8500:
8501: l_return_status VARCHAR2(1);
8502: l_interface_line_id PO_LINES_INTERFACE.interface_line_id%TYPE;
8503: l_old_interface_line_id PO_LINES_INTERFACE.interface_line_id%TYPE := -1;
8504: l_interface_distribution_id PO_DISTRIBUTIONS_INTERFACE.interface_distribution_id%TYPE;
8505: l_item_category_id PO_LINES_INTERFACE.category_id%TYPE;
8506: l_req_charge_account_id PO_DISTRIBUTIONS_INTERFACE.charge_account_id%TYPE;
8507: l_req_variance_account_id PO_DISTRIBUTIONS_INTERFACE.variance_account_id%TYPE;
8501: l_return_status VARCHAR2(1);
8502: l_interface_line_id PO_LINES_INTERFACE.interface_line_id%TYPE;
8503: l_old_interface_line_id PO_LINES_INTERFACE.interface_line_id%TYPE := -1;
8504: l_interface_distribution_id PO_DISTRIBUTIONS_INTERFACE.interface_distribution_id%TYPE;
8505: l_item_category_id PO_LINES_INTERFACE.category_id%TYPE;
8506: l_req_charge_account_id PO_DISTRIBUTIONS_INTERFACE.charge_account_id%TYPE;
8507: l_req_variance_account_id PO_DISTRIBUTIONS_INTERFACE.variance_account_id%TYPE;
8508: l_destination_organization_id PO_DISTRIBUTIONS_INTERFACE.destination_organization_id%TYPE;
8509: l_destination_ou_id PO_HEADERS_ALL.org_id%TYPE;
8507: l_req_variance_account_id PO_DISTRIBUTIONS_INTERFACE.variance_account_id%TYPE;
8508: l_destination_organization_id PO_DISTRIBUTIONS_INTERFACE.destination_organization_id%TYPE;
8509: l_destination_ou_id PO_HEADERS_ALL.org_id%TYPE;
8510:
8511: l_item_id PO_LINES_INTERFACE.item_id%TYPE;
8512: l_category_id PO_LINES_INTERFACE.category_id%TYPE;
8513: l_destination_type_code PO_DISTRIBUTIONS_ALL.destination_type_code%TYPE;
8514: --l_ship_to_organization_id NUMBER;
8515: l_ship_to_location_id PO_DISTRIBUTIONS_INTERFACE.deliver_to_location_id%TYPE;
8508: l_destination_organization_id PO_DISTRIBUTIONS_INTERFACE.destination_organization_id%TYPE;
8509: l_destination_ou_id PO_HEADERS_ALL.org_id%TYPE;
8510:
8511: l_item_id PO_LINES_INTERFACE.item_id%TYPE;
8512: l_category_id PO_LINES_INTERFACE.category_id%TYPE;
8513: l_destination_type_code PO_DISTRIBUTIONS_ALL.destination_type_code%TYPE;
8514: --l_ship_to_organization_id NUMBER;
8515: l_ship_to_location_id PO_DISTRIBUTIONS_INTERFACE.deliver_to_location_id%TYPE;
8516: l_deliver_to_person_id PO_DISTRIBUTIONS_INTERFACE.deliver_to_person_id%TYPE;
8513: l_destination_type_code PO_DISTRIBUTIONS_ALL.destination_type_code%TYPE;
8514: --l_ship_to_organization_id NUMBER;
8515: l_ship_to_location_id PO_DISTRIBUTIONS_INTERFACE.deliver_to_location_id%TYPE;
8516: l_deliver_to_person_id PO_DISTRIBUTIONS_INTERFACE.deliver_to_person_id%TYPE;
8517: l_line_type_id PO_LINES_INTERFACE.line_type_id%TYPE;
8518: l_vendor_id PO_VENDORS.vendor_id%TYPE;
8519: l_agent_id PO_HEADERS.agent_id%TYPE;
8520: l_expenditure_organization_id PO_DISTRIBUTIONS_INTERFACE.expenditure_organization_id%TYPE;
8521: l_project_id PO_DISTRIBUTIONS_INTERFACE.project_id%TYPE;
8544: l_dest_variance_account_flex VARCHAR2(2000);
8545: --< New start_workflow parameters in FPJ End >
8546:
8547: -- Bug 3463242 START
8548: l_req_line_id PO_LINES_INTERFACE.requisition_line_id%TYPE;
8549: --
8550: l_requesting_ou_id PO_REQUISITION_LINES_ALL.org_id%TYPE;
8551: l_unit_price PO_LINES_INTERFACE.unit_price%TYPE;
8552: l_base_unit_price PO_LINES_INTERFACE.base_unit_price%TYPE;
8547: -- Bug 3463242 START
8548: l_req_line_id PO_LINES_INTERFACE.requisition_line_id%TYPE;
8549: --
8550: l_requesting_ou_id PO_REQUISITION_LINES_ALL.org_id%TYPE;
8551: l_unit_price PO_LINES_INTERFACE.unit_price%TYPE;
8552: l_base_unit_price PO_LINES_INTERFACE.base_unit_price%TYPE;
8553: l_amount PO_LINES_INTERFACE.amount%TYPE;
8554: l_quantity_dummy PO_LINES_INTERFACE.quantity%TYPE;
8555: l_order_type_lookup_code PO_LINE_TYPES_B.order_type_lookup_code%TYPE;
8548: l_req_line_id PO_LINES_INTERFACE.requisition_line_id%TYPE;
8549: --
8550: l_requesting_ou_id PO_REQUISITION_LINES_ALL.org_id%TYPE;
8551: l_unit_price PO_LINES_INTERFACE.unit_price%TYPE;
8552: l_base_unit_price PO_LINES_INTERFACE.base_unit_price%TYPE;
8553: l_amount PO_LINES_INTERFACE.amount%TYPE;
8554: l_quantity_dummy PO_LINES_INTERFACE.quantity%TYPE;
8555: l_order_type_lookup_code PO_LINE_TYPES_B.order_type_lookup_code%TYPE;
8556: l_po_currency_code PO_HEADERS_INTERFACE.currency_code%TYPE;
8549: --
8550: l_requesting_ou_id PO_REQUISITION_LINES_ALL.org_id%TYPE;
8551: l_unit_price PO_LINES_INTERFACE.unit_price%TYPE;
8552: l_base_unit_price PO_LINES_INTERFACE.base_unit_price%TYPE;
8553: l_amount PO_LINES_INTERFACE.amount%TYPE;
8554: l_quantity_dummy PO_LINES_INTERFACE.quantity%TYPE;
8555: l_order_type_lookup_code PO_LINE_TYPES_B.order_type_lookup_code%TYPE;
8556: l_po_currency_code PO_HEADERS_INTERFACE.currency_code%TYPE;
8557: l_req_header_rate_type PO_HEADERS_INTERFACE.rate_type%TYPE;
8550: l_requesting_ou_id PO_REQUISITION_LINES_ALL.org_id%TYPE;
8551: l_unit_price PO_LINES_INTERFACE.unit_price%TYPE;
8552: l_base_unit_price PO_LINES_INTERFACE.base_unit_price%TYPE;
8553: l_amount PO_LINES_INTERFACE.amount%TYPE;
8554: l_quantity_dummy PO_LINES_INTERFACE.quantity%TYPE;
8555: l_order_type_lookup_code PO_LINE_TYPES_B.order_type_lookup_code%TYPE;
8556: l_po_currency_code PO_HEADERS_INTERFACE.currency_code%TYPE;
8557: l_req_header_rate_type PO_HEADERS_INTERFACE.rate_type%TYPE;
8558: l_req_header_rate_date PO_HEADERS_INTERFACE.rate_date%TYPE;
8558: l_req_header_rate_date PO_HEADERS_INTERFACE.rate_date%TYPE;
8559: l_req_header_rate PO_HEADERS_INTERFACE.rate%TYPE;
8560: l_dist_rate PO_DISTRIBUTIONS_INTERFACE.rate%TYPE;
8561: l_rate_for_req_fields PO_HEADERS_INTERFACE.rate%TYPE;
8562: l_po_func_unit_price PO_LINES_ALL.unit_price%TYPE;
8563: -- Bug 3463242 END
8564:
8565: l_item_in_linv_pou VARCHAR2(1):= 'Y'; -- Bug 3433867
8566:
8611: phi.rate,
8612: pdi.rate
8613: -- Bug 3463242 END
8614: FROM PO_DISTRIBUTIONS_INTERFACE pdi,
8615: PO_LINES_INTERFACE pli,
8616: PO_HEADERS_INTERFACE phi,
8617: PO_REQUISITION_LINES_ALL prl, --
8618: MTL_PARAMETERS mp,
8619: HR_ORGANIZATION_INFORMATION hoi,
8941: END IF;
8942:
8943: IF l_interface_line_id <> l_old_interface_line_id THEN
8944: l_progress := '120';
8945: UPDATE po_lines_interface
8946: SET transaction_flow_header_id = l_transaction_flow_header_id
8947: WHERE interface_line_id = l_interface_line_id;
8948:
8949: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
9261: pli.item_id,
9262: phi.vendor_id,
9263: phi.vendor_site_id,
9264: pdi.destination_organization_id
9265: from po_lines_interface pli,
9266: po_headers_interface phi,
9267: po_distributions_interface pdi
9268: where phi.interface_header_id = x_interface_header_id
9269: and phi.interface_header_id = pli.interface_header_id
9292: phi.rate,
9293: phi.rate_date,
9294: phi.document_subtype,
9295: pli.unit_price --
9296: FROM po_lines_interface pli,
9297: po_headers_interface phi,
9298: po_line_types plt
9299: WHERE phi.interface_header_id = x_interface_header_id
9300: AND phi.interface_header_id = pli.interface_header_id
9316: -- If the line is OPM item with dual uom control then the shipment corresponding
9317: -- to the extra PO line created by Sourcing does not have secondary uom and
9318: -- secondary quantity with ship to organization as process org.
9319: -- default secondary UOM and secondary quantity in such a case (non req backed lines)
9320: -- the requisition line id would be null in po_lines_interface .
9321: -- need to handle that situation in the default_opm_attributes cursor.
9322: -- in case of non req back lines from sourcing , ship_to_organization_id is NULL.
9323: -- so get it from distribution interface table.
9324:
9568: l_progress:='090';
9569:
9570: SELECT min(interface_line_id)
9571: INTO x_min_interface_line_id
9572: FROM po_lines_interface pli,
9573: po_headers_interface phi
9574: WHERE phi.interface_header_id=pli.interface_header_id
9575: AND phi.interface_header_id = x_interface_header_id;
9576:
9690: null),
9691: phi.document_subtype,
9692: l_shipping_control --
9693: FROM po_headers_interface phi2,
9694: po_lines_interface pli
9695: WHERE phi2.interface_header_id = phi.interface_header_id
9696: AND pli.interface_header_id=phi2.interface_header_id
9697: AND pli.interface_line_id = x_min_interface_line_id)
9698: WHERE interface_header_id = x_interface_header_id;
9749: -- for x_uom_convert, since UOM checking is now handled in
9750: -- source_blanket_line.
9751:
9752: l_progress:='120';
9753: UPDATE po_lines_interface pli2
9754: SET (
9755: line_num,
9756: item_id,
9757: category_id,
9853: nvl(pli.preferred_grade,prl.preferred_grade),
9854: -- end of 1548597
9855: prl.drop_ship_flag, --
9856: prl.vmi_flag -- VMI FPH
9857: FROM po_lines_interface pli,
9858: po_headers_interface phi,
9859: po_requisition_lines_all prl, --
9860: po_line_types plt,
9861: po_lines_all pol --
9857: FROM po_lines_interface pli,
9858: po_headers_interface phi,
9859: po_requisition_lines_all prl, --
9860: po_line_types plt,
9861: po_lines_all pol --
9863: AND pli.interface_header_id = phi.interface_header_id
9864: AND phi.interface_header_id = x_interface_header_id
9865: AND pli.requisition_line_id = prl.requisition_line_id(+)
9882: /* CONSIGNED FPI Start */
9883: ELSE -- CONSIGNED FPI
9884: l_progress:='130';
9885: -- bug 6636486 modified the below update sql to increase the performance.
9886: UPDATE po_lines_interface pli
9887: SET pli.po_header_id = x_po_header_id,
9888: pli.negotiated_by_preparer_flag = nvl(pli.negotiated_by_preparer_flag,'N'),
9889: pli.firm_flag = nvl(pli.firm_flag, 'N')
9890: WHERE pli.interface_header_id = x_interface_header_id;
9896: /*(SELECT
9897: x_po_header_id,
9898: nvl(pli.negotiated_by_preparer_flag,'N'),
9899: nvl(pli.firm_flag,'N')
9900: FROM po_lines_interface pli,
9901: po_headers_interface phi
9902: WHERE pli.interface_line_id = pli2.interface_line_id
9903: AND pli.interface_header_id = phi.interface_header_id
9904: AND phi.interface_header_id = x_interface_header_id);*/
9934: -- Added note_to_vendor - iali 08/26/99
9935: /*Bug 1391523 . Added market price to the update statement */
9936:
9937: l_progress:='150';
9938: UPDATE po_lines_interface pli2
9939: SET (
9940: line_num,
9941: item_id,
9942: job_id, --
10057: nvl(pli.contractor_first_name, prl.candidate_first_name),
10058: nvl(pli.contractor_last_name, prl.candidate_last_name)
10059: --
10060: ,pol.supplier_part_auxid --13876074
10061: FROM po_lines_interface pli,
10062: po_headers_interface phi,
10063: po_requisition_lines_all prl, --
10064: po_line_types plt
10065: ,po_lines_all pol --13876074
10061: FROM po_lines_interface pli,
10062: po_headers_interface phi,
10063: po_requisition_lines_all prl, --
10064: po_line_types plt
10065: ,po_lines_all pol --13876074
10066: WHERE pli.interface_line_id = pli2.interface_line_id
10067: AND pli.interface_header_id = phi.interface_header_id
10068: AND phi.interface_header_id = x_interface_header_id
10069: AND pli.requisition_line_id = prl.requisition_line_id(+)
10207: decode(pli.consigned_flag,'Y',null,
10208: prd.oke_contract_deliverable_id)
10209: FROM po_requisition_lines_all prl, --
10210: po_req_distributions_all prd, --
10211: po_lines_interface pli,
10212: po_headers_interface phi
10213: WHERE prd.requisition_line_id = prl.requisition_line_id
10214: AND prl.requisition_line_id = pli.requisition_line_id -- Bug:1563888
10215: AND pli.interface_header_id = phi.interface_header_id
10394: x_vendor_product_num,
10395: x_purchasing_uom);
10396:
10397: if (x_vendor_product_num is not null) then
10398: update po_lines_interface
10399: set vendor_product_num = x_vendor_product_num
10400: where rowid = x_rowid;
10401:
10402: end if;
10464: DELETE po_line_locations_interface
10465: WHERE interface_header_id = x_interface_header_id;
10466: --
10467:
10468: DELETE po_lines_interface
10469: WHERE interface_header_id = x_interface_header_id;
10470:
10471:
10472: DELETE po_headers_interface
10534: x_bid_line_number number;
10535: x_row_id varchar2(25) := null;
10536: --
10537:
10538: x_vmi_flag PO_LINES_INTERFACE.VMI_FLAG%TYPE; /* VMI FPH */
10539: x_drop_ship_flag PO_LINES_INTERFACE.DROP_SHIP_FLAG%TYPE; --
10540:
10541: x_source_doc_id number; -- FPI GA
10542: x_source_doc_line_id number; -- FPI GA
10535: x_row_id varchar2(25) := null;
10536: --
10537:
10538: x_vmi_flag PO_LINES_INTERFACE.VMI_FLAG%TYPE; /* VMI FPH */
10539: x_drop_ship_flag PO_LINES_INTERFACE.DROP_SHIP_FLAG%TYPE; --
10541: x_source_doc_id number; -- FPI GA
10542: x_source_doc_line_id number; -- FPI GA
10543:
10542: x_source_doc_line_id number; -- FPI GA
10543:
10544: x_consigned_flag VARCHAR2(1) := 'N'; --CONSIGNED FPI
10545: x_create_new_line VARCHAR2(1) := 'N'; --GA FPI
10546: l_supplier_ref_number PO_LINES_INTERFACE.supplier_ref_number%TYPE; --
10548: l_contract_id PO_LINES_ALL.contract_id%TYPE; --
10549:
10550: /* Bug 3201308 start */
10544: x_consigned_flag VARCHAR2(1) := 'N'; --CONSIGNED FPI
10545: x_create_new_line VARCHAR2(1) := 'N'; --GA FPI
10546: l_supplier_ref_number PO_LINES_INTERFACE.supplier_ref_number%TYPE; --
10547:
10548: l_contract_id PO_LINES_ALL.contract_id%TYPE; --
10550: /* Bug 3201308 start */
10551: l_needby_prf varchar2(1);
10552: l_shipto_prf varchar2(1);
10572:
10573: /* Bug 1949160. Created a cursor to retrieve requisition line-id */
10574: CURSOR interface_lines_temp IS
10575: SELECT pli.requisition_line_id
10576: FROM po_lines_interface pli
10577: WHERE pli.interface_header_id = x_interface_header_id
10578: -- bug 4000047: start: requisition lines should be entered
10579: -- into PO the same order they appear in the requisition
10580: ORDER BY pli.requisition_line_id;
10608: pli.from_line_id, -- FPI GA
10609: pli.consigned_flag, -- CONSIGNED FPI
10610: pli.contract_id, --
10611: pli.supplier_ref_number --
10612: FROM po_lines_interface pli,
10613: po_distributions_interface pld
10614: WHERE pli.interface_header_id=x_interface_header_id
10615: AND pli.interface_line_id=pld.interface_line_id
10616: AND pld.interface_distribution_id =
10701: -- use requisition numbers
10702:
10703: l_progress:='035';
10704:
10705: UPDATE po_lines_interface pli
10706: SET pli.shipment_num = 1,
10707: pli.line_num =
10708: (
10709: SELECT prl.line_num
10729: EXIT WHEN interface_lines_temp%NOTFOUND;
10730:
10731: l_progress := '050';
10732:
10733: UPDATE po_lines_interface pli
10734: SET pli.line_num = x_count,
10735: pli.shipment_num = 1
10736: WHERE pli.requisition_line_id = x_requisition_line_id
10737: AND pli.interface_header_id = x_interface_header_id
10829: SELECT MIN(pli.line_num),
10830: pli.po_line_id
10831: INTO x_po_line_num,
10832: x_po_line_id
10833: FROM po_lines_interface pli
10834: WHERE pli.interface_header_id = x_interface_header_id
10835: AND pli.line_num IS NOT NULL
10836: AND pli.line_type_id = x_line_type_id
10837: AND NVL(pli.item_id, -1) = NVL(x_item_id, -1)
10876: p_token => l_progress,
10877: p_message => 'Group_interface_lines progress is :'|| l_progress);
10878: END IF; -- debug logging
10879:
10880: UPDATE po_lines_interface pli
10881: SET pli.line_num = x_po_line_num
10882: WHERE pli.interface_header_id = x_interface_header_id
10883: AND pli.requisition_line_id = x_requisition_line_id;
10884:
10899: -- use requisition numbers
10900:
10901: l_progress := '100';
10902:
10903: UPDATE po_lines_interface pli
10904: SET pli.shipment_num =
10905: ( SELECT prl.line_num
10906: FROM po_requisition_lines_all prl
10907: WHERE prl.requisition_line_id = pli.requisition_line_id )
10921: EXIT WHEN interface_lines_temp%NOTFOUND;
10922:
10923: l_progress := '110';
10924:
10925: UPDATE po_lines_interface pli
10926: SET pli.shipment_num = x_count
10927: WHERE pli.requisition_line_id = x_requisition_line_id
10928: AND pli.interface_header_id = x_interface_header_id
10929: AND pli.shipment_num IS NULL;
10960:
10961: SELECT NVL(max(pl.line_num),0)
10962: INTO x_line_num
10963: FROM po_headers_all ph,
10964: po_lines_all pl
10965: WHERE pl.po_header_id = ph.po_header_id
10966: AND ph.segment1 = x_document_num
10967: AND ph.type_lookup_code =
10968: DECODE(g_document_type, 'RFQ', g_document_type, x_document_subtype)
10980: EXIT WHEN interface_lines_temp%NOTFOUND;
10981:
10982: l_progress := '150';
10983:
10984: UPDATE po_lines_interface pli
10985: SET pli.line_num = x_line_num + x_count,
10986: pli.shipment_num = 1
10987: WHERE pli.requisition_line_id = x_requisition_line_id
10988: AND pli.interface_header_id = x_interface_header_id
11082: SELECT MIN(pli.line_num),
11083: pli.po_line_id
11084: INTO x_po_line_num,
11085: x_po_line_id
11086: FROM po_lines_interface pli
11087: WHERE pli.interface_header_id = x_interface_header_id
11088: AND pli.line_num IS NOT NULL
11089: AND pli.line_type_id = x_line_type_id
11090: AND NVL(pli.item_id, -1) = NVL(x_item_id, -1)
11126: p_token => l_progress,
11127: p_message => 'Group_interface_lines: Line_num is :'|| x_po_line_num);
11128: END IF;
11129:
11130: UPDATE po_lines_interface pli
11131: SET pli.line_num = x_po_line_num
11132: WHERE pli.interface_header_id = x_interface_header_id
11133: AND pli.requisition_line_id = x_requisition_line_id;
11134:
11175: -- Prior to this fix, for Releases, the shipment number was
11176: -- derived by adding requisition line number to the current
11177: -- maximum shipment number. Due to this shipment numbers
11178: -- were getting skipped. Hence commented the following update
11179: -- statement and now updating the po_lines_interface in a loop using
11180: -- a cursor.
11181:
11182: OPEN interface_lines_temp;
11183: LOOP
11187: EXIT WHEN interface_lines_temp%NOTFOUND;
11188:
11189: l_progress := '220';
11190:
11191: UPDATE po_lines_interface pli
11192: SET pli.shipment_num = x_shipment_num + x_count
11193: WHERE pli.requisition_line_id = x_requisition_line_id
11194: AND pli.interface_header_id = x_interface_header_id
11195: AND pli.line_num IS NOT NULL
11224: -- Get maximum line number in interface table, to be used later
11225:
11226: SELECT NVL(max(pli.line_num), 0)
11227: INTO l_max_iface_line_num
11228: FROM po_lines_interface pli
11229: WHERE pli.interface_header_id = x_interface_header_id;
11230:
11231: --
11232:
11283: -- to DEFAULT.
11284:
11285: -- Do not group; simply add 1 to each successive interface line
11286:
11287: UPDATE po_lines_interface pli
11288: SET pli.line_num = l_max_iface_line_num + 1
11289: WHERE pli.rowid = x_row_id
11290: AND pli.line_num IS NULL;
11291:
11371: SELECT pol.po_line_id,
11372: pol.line_num
11373: INTO x_po_line_id,
11374: x_po_line_num
11375: FROM po_lines_all pol,
11376: po_headers_all poh
11377: WHERE poh.segment1 = x_document_num
11378: AND pol.line_num = x_interface_line_num
11379: AND poh.type_lookup_code =
11505: SELECT line_num
11506: , po_line_id
11507: INTO x_po_line_num
11508: , x_po_line_id
11509: FROM po_lines_all POL2
11510: , po_headers_all POH
11511: , po_line_types_b PLT --
11512: WHERE POH.segment1 = x_document_num
11513: AND POH.po_header_id = POL2.po_header_id
11521: --
11522: --
11523: AND pol2.line_num =
11524: (SELECT /*+ NO_UNNEST */ MIN(line_num)
11525: FROM po_lines_all pol --
11527: AND NVL(CANCEL_FLAG,'N') = 'N'
11528: AND LINE_TYPE_ID = x_line_type_id
11529: AND nvl(pol.ITEM_ID, -1) = nvl(x_item_id, -1) -- bgu, For one time item
11619: SELECT line_num,
11620: po_line_id
11621: INTO x_po_line_num,
11622: x_po_line_id
11623: FROM po_lines_all POL2,
11624: po_headers_all POH
11625: WHERE POH.segment1 = x_document_num
11626: AND POH.po_header_id = POL2.po_header_id
11627: AND NVL(poh.org_id, -99) = NVL(g_purchasing_ou_id, -99) --
11628: AND POH.type_lookup_code = 'RFQ'
11629: AND pol2.line_num =
11630: (
11631: SELECT /*+ NO_UNNEST */ MIN(line_num)
11632: FROM PO_LINES_ALL pol
11633: WHERE pol.po_header_id = poh.po_header_id
11634: AND LINE_TYPE_ID = x_line_type_id
11635: AND nvl(pol.ITEM_ID, -1) = nvl(x_item_id, -1)
11636: AND nvl(pol.ITEM_DESCRIPTION,'null') =
11692: -- SQL Join: business logic for combining two lines
11693:
11694: SELECT min(pli.line_num)
11695: INTO x_po_line_num
11696: FROM po_lines_interface pli
11697: , po_requisition_lines_all prl
11698: , po_line_types_b PLT --
11699: WHERE pli.interface_header_id = x_interface_header_id
11700: AND pli.line_num is not null
11851: END IF;
11852:
11853: SELECT MIN(pli.line_num)
11854: INTO x_po_line_num
11855: FROM po_lines_interface pli
11856: WHERE pli.interface_header_id = x_interface_header_id
11857: AND pli.line_num is not null
11858: AND pli.line_type_id = x_line_type_id
11859: AND nvl(pli.item_id, -1) = nvl(x_item_id, -1)
11904:
11905: -- Since get_shipment_num will need it, we update the
11906: -- line number here.
11907:
11908: UPDATE po_lines_interface pli
11909: SET pli.line_num = x_po_line_num
11910: WHERE pli.interface_header_id = x_interface_header_id
11911: AND pli.requisition_line_id = x_requisition_line_id;
11912:
11959: --by a req and the shipment num would be 1. There can't be two
11960: --similar negotiation lines not backed by a req, having the
11961: --same bid number and bid line number. If that happens we don't
11962: --group them to a single line. Also no need to select from
11963: --po_lines table as we are not supporting add to functionality.
11964:
11965: l_progress := '400';
11966:
11967: UPDATE po_lines_interface pli2
11963: --po_lines table as we are not supporting add to functionality.
11964:
11965: l_progress := '400';
11966:
11967: UPDATE po_lines_interface pli2
11968: SET (pli2.line_num, pli2.shipment_num) =
11969: (
11970: SELECT (NVL(max(pli.line_num), 0) + 1), 1
11971: FROM po_lines_interface pli
11967: UPDATE po_lines_interface pli2
11968: SET (pli2.line_num, pli2.shipment_num) =
11969: (
11970: SELECT (NVL(max(pli.line_num), 0) + 1), 1
11971: FROM po_lines_interface pli
11972: WHERE pli.interface_header_id = x_interface_header_id
11973: )
11974: WHERE pli2.rowid = x_row_id;
11975:
11992:
11993: SELECT NVL(max(pl.line_num), 0)
11994: INTO x_line_num
11995: FROM po_headers_all ph,
11996: po_lines_all pl
11997: WHERE pl.po_header_id = ph.po_header_id
11998: AND ph.segment1 = x_document_num
11999: AND NVL(ph.org_id, -99) = NVL(g_purchasing_ou_id, -99)
12000: AND ph.type_lookup_code =
12006: l_progress:='420';
12007: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
12008: PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
12009: p_token => l_progress,
12010: p_message => 'Before select max line_num from po_lines_interface');
12011: END IF;
12012:
12013: SELECT NVL(max(pli.line_num), 0)
12014: INTO x_int_line_num
12011: END IF;
12012:
12013: SELECT NVL(max(pli.line_num), 0)
12014: INTO x_int_line_num
12015: FROM po_lines_interface pli
12016: WHERE pli.interface_header_id = x_interface_header_id;
12017:
12018: IF (x_line_num >= x_int_line_num) THEN
12019: x_line_num := x_line_num;
12031: END IF;
12032:
12033: IF (x_requisition_line_id IS NOT NULL) THEN
12034:
12035: UPDATE po_lines_interface pli
12036: SET pli.line_num = x_line_num + 1,
12037: pli.shipment_num = 1
12038: WHERE pli.interface_header_id = x_interface_header_id
12039: AND pli.requisition_line_id = x_requisition_line_id;
12041: ELSE
12042:
12043: -- no backing req line; use rowid
12044:
12045: UPDATE po_lines_interface pli
12046: SET pli.line_num = x_line_num + 1,
12047: pli.shipment_num = 1
12048: WHERE pli.rowid = x_row_id;
12049:
12153: SELECT min(line_num)
12154: , po_line_id
12155: INTO x_po_line_num
12156: , x_po_line_id
12157: FROM po_lines_interface PLI
12158: , po_line_types_b PLT --
12159: WHERE pli.interface_header_id = x_interface_header_id
12160: AND pli.line_num is not null
12161: AND pli.LINE_TYPE_ID = x_line_type_id
12196: END;
12197:
12198: l_progress := '470';
12199:
12200: UPDATE po_lines_interface pli
12201: SET pli.line_num = x_po_line_num
12202: WHERE pli.interface_header_id = x_interface_header_id
12203: AND pli.requisition_line_id = x_requisition_line_id;
12204:
12206:
12207: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
12208: PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
12209: p_token => l_progress,
12210: p_message => 'After update of po_lines_interface line_num');
12211: END IF;
12212:
12213: ELSE
12214:
12223: BEGIN
12224:
12225: SELECT pol.po_line_id
12226: INTO x_po_line_id
12227: FROM po_lines_all pol,
12228: po_headers_all poh,
12229: po_lines_interface pli
12230: WHERE pol.po_header_id = poh.po_header_id
12231: AND poh.segment1 = x_document_num
12225: SELECT pol.po_line_id
12226: INTO x_po_line_id
12227: FROM po_lines_all pol,
12228: po_headers_all poh,
12229: po_lines_interface pli
12230: WHERE pol.po_header_id = poh.po_header_id
12231: AND poh.segment1 = x_document_num
12232: AND NVL(poh.org_id, -99) = NVL(g_purchasing_ou_id, -99)
12233: AND poh.type_lookup_code = 'BLANKET' -- Bug# 1746943
12878:
12879: SELECT POL.po_line_id
12880: INTO l_po_line_id
12881: FROM po_headers_interface PHI,
12882: po_lines_all POL
12883: WHERE PHI.interface_header_id = x_interface_header_id
12884: AND PHI.po_header_id = POL.po_header_id
12885: AND POL.line_num = x_po_line_num;
12886: ELSE
12903: ,PLL.line_location_id
12904: INTO x_po_shipment_num,
12905: x_line_location_to_check
12906: FROM PO_LINE_LOCATIONS_ALL PLL, --
12907: PO_LINES_ALL POL, --
12909: WHERE POL.PO_LINE_ID = l_po_line_id -- bug2788115
12910: AND POL.po_line_id = PLL.po_line_id --
12911: AND POL.line_type_id = PLT.line_type_id --
13017: decode(psp.expense_accrual_code,'PERIOD END', 'N', 'Y')),
13018: 'INVENTORY', 'Y',
13019: 'SHOP FLOOR','Y')
13020: INTO x_receipt_required_flag
13021: FROM po_lines_interface pli,
13022: po_headers_interface phi,
13023: po_requisition_lines_all prl, --
13024: mtl_system_items msi,
13025: po_line_types plt,
13080: SELECT PLI.shipment_num
13081: ,PLI.requisition_line_id
13082: INTO x_po_shipment_num
13083: ,x_req_line_to_check
13084: FROM PO_LINES_INTERFACE PLI,
13085: PO_REQUISITION_LINES_ALL PRL, --
13086: --bug 1942696 hr_location changes to reflect the new view
13087: MTL_SYSTEM_ITEMS MSI ,
13088: PO_LINE_TYPES PLT ,
13202: to improve the performance
13203: */
13204: SELECT PLI.shipment_num
13205: INTO x_po_shipment_num
13206: FROM PO_LINES_INTERFACE PLI,
13207: PO_REQUISITION_LINES_ALL PRL, --
13208: MTL_SYSTEM_ITEMS MSI ,
13209: PO_LINE_TYPES PLT ,
13210: PO_SYSTEM_PARAMETERS_ALL PSP , --
13329:
13330: l_progress:='190';
13331: SELECT PLI.shipment_num
13332: INTO x_po_shipment_num
13333: FROM PO_LINES_INTERFACE PLI,
13334: PO_REQUISITION_LINES_ALL PRL, --
13335: --bug 1942696 hr_location changes to reflect the new view
13336: MTL_SYSTEM_ITEMS MSI ,
13337: PO_LINE_TYPES PLT ,
13505: */
13506:
13507: if x_requisition_line_id is not null then
13508: l_progress := '010';
13509: update po_lines_interface
13510: set shipment_num= x_po_shipment_num
13511: where interface_header_id=x_interface_header_id
13512: and requisition_line_id=x_requisition_line_id;
13513:
13512: and requisition_line_id=x_requisition_line_id;
13513:
13514: else
13515: l_progress := '015';
13516: update po_lines_interface
13517: set shipment_num= x_po_shipment_num
13518: where interface_header_id=x_interface_header_id
13519: and rowid=x_row_id;
13520: end if;
13554: l_progress := '030';
13555: select nvl(max(line_num),0)
13556: into x_line_num
13557: from po_headers_all ph, --
13558: po_lines_all pl --
13560: and ph.segment1 = x_document_num
13561: AND NVL(ph.org_id, -99) = NVL(g_purchasing_ou_id, -99); --
13562:
13562:
13563: l_progress := '040';
13564: select nvl(max(line_num),0)
13565: into x_int_line_num
13566: from po_lines_interface pli
13567: where pli.interface_header_id = x_interface_header_id;
13568:
13569: IF (x_line_num >= x_int_line_num) THEN
13570: x_line_num := x_line_num;
13572: x_line_num := x_int_line_num;
13573: END IF;
13574:
13575: l_progress := '050';
13576: update po_lines_interface
13577: set line_num = x_line_num + 1
13578: where interface_header_id = x_interface_header_id
13579: and requisition_line_id = x_requisition_line_id;
13580: -- Bug 6072900
13602: ** assigned in the interface table.
13603: */
13604: select nvl(max(shipment_num),0)
13605: into x_int_shipment_num
13606: from po_lines_interface pli
13607: where pli.interface_header_id = x_interface_header_id
13608: and pli.line_num = x_po_line_num;
13609:
13610: -- End of fix. Bug 605715, lpo, 01/05/98
13636: ** assigned in the interface table.
13637: */
13638: select nvl(max(shipment_num),0)
13639: into x_int_shipment_num
13640: from po_lines_interface pli
13641: where pli.interface_header_id = x_interface_header_id;
13642:
13643: -- End of fix. Bug 605715, lpo, 01/05/98
13644:
13653: END IF;
13654:
13655: if x_requisition_line_id is not null then
13656: l_progress := '110';
13657: update po_lines_interface
13658: set shipment_num = x_shipment_num + 1
13659: where interface_header_id=x_interface_header_id
13660: and requisition_line_id=x_requisition_line_id;
13661: else
13659: where interface_header_id=x_interface_header_id
13660: and requisition_line_id=x_requisition_line_id;
13661: else
13662: l_progress := '120';
13663: update po_lines_interface
13664: set shipment_num = x_shipment_num + 1
13665: where interface_header_id=x_interface_header_id
13666: and rowid=x_row_id;
13667: end if;
13802: that of requisition line. For others, both item_id and description
13803: should match.
13804: - If there is no BPO line matching the description, then just match
13805: the item_id.
13806: - For any case above, if more than one candidate BPO lines pass the
13807: conditions, then pick the one having the minimum line_num
13808: See Bug 2082757 for details.
13809: ===========================================================================*/
13810:
13847: p_item_rev_control IN NUMBER,
13848: x_allow_different_uoms IN VARCHAR2 -- Bug 2707576
13849: ) IS
13850: SELECT MIN(pol.line_num)
13851: FROM po_lines_all pol,
13852: po_requisition_lines_all prl --
13853: WHERE pol.po_header_id = p_po_header_id
13854: AND prl.requisition_line_id = p_requisition_line_id
13855: AND NVL(pol.cancel_flag,'N') = 'N'
13897: -- IF THE REQ IS SOURCED TO THE DOC,BUT THE ALREADY SOURCED LINE IS INVALID,THEN TAKE THE OTHER VALID LINE FROM SOURCE DOCUMENT
13898: --OR TRUNC(NVL(POL.EXPIRATION_DATE,SYSDATE + 1)) >= TRUNC(DECODE(POL.EXPIRATION_DATE,NULL,SYSDATE, NVL(PRL.NEED_BY_DATE,SYSDATE))) ) --13876074
13899: --Bug 13876074, ensure to pick another source line
13900: --only if the current source line reference is not valid.
13901: OR ((NOT EXISTS (SELECT 1 FROM po_lines_all pol2
13902: WHERE pol2.po_header_id = pol.po_header_id
13903: AND TRUNC(NVL(pol2.expiration_date,SYSDATE + 1))
13904: >= TRUNC(SYSDATE)
13905: AND prl.blanket_po_line_num = pol2.line_num))
13961: p_item_rev_control IN NUMBER,
13962: x_allow_different_uoms IN VARCHAR2 -- Bug 2707576
13963: ) IS
13964: SELECT MIN(pol.line_num)
13965: FROM po_lines_all pol,
13966: po_requisition_lines_all prl --
13967: WHERE pol.po_header_id = p_po_header_id
13968: AND prl.requisition_line_id = p_requisition_line_id
13969: AND NVL(pol.cancel_flag,'N') = 'N'
14009: -- IF THE REQ IS SOURCED TO THE DOC,BUT THE ALREADY SOURCED LINE IS INVALID,THEN TAKE THE OTHER VALID LINE FROM SOURCE DOCUMENT
14010: --OR TRUNC(NVL(POL.EXPIRATION_DATE,SYSDATE + 1)) >= TRUNC(DECODE(POL.EXPIRATION_DATE,NULL,SYSDATE,NVL(PRL.NEED_BY_DATE,SYSDATE))) ) --13876074
14011: --Bug 13876074, ensure to pick another source line
14012: --only if the current source line reference is not valid.
14013: OR ((NOT EXISTS (SELECT 1 FROM po_lines_all pol2
14014: WHERE pol2.po_header_id = pol.po_header_id
14015: AND TRUNC(NVL(pol2.expiration_date,SYSDATE + 1))
14016: >= TRUNC(SYSDATE)
14017: AND prl.blanket_po_line_num = pol2.line_num))
14121: /*============================================================================
14122: Name : CREATE_PRICE_BREAK
14123: Type : Private
14124: Function : This procedure is called from 'create_line'. This procedure inserts
14125: records from po_lines_interface table to po_line_locations_all table
14126: for the price break information.
14127: Pre-req : None
14128: Parameters:
14129: IN : p_po_line_id IN NUMBER REQUIRED
14142: l_ship_org_id_line mtl_system_items.organization_id%type;
14143: l_ship_org_code varchar2(3);
14144: l_ship_org_name varchar2(60);
14145:
14146: l_value_basis PO_LINES_ALL.order_type_lookup_code%TYPE; --
14148: BEGIN
14149: -- Standard start of API savepoint
14150: SAVEPOINT create_price_break_pvt;
14193: -- Get value basis from line
14194:
14195: SELECT pol.order_type_lookup_code
14196: INTO l_value_basis
14197: FROM po_lines_all pol
14198: WHERE pol.po_line_id = p_po_line_id;
14199:
14200: l_progress := '025';
14201:
14533: END IF;
14534:
14535: SELECT count(*)
14536: INTO l_num_config_id_lines
14537: FROM po_lines_interface PLI, po_requisition_lines PRL
14538: WHERE PLI.interface_header_id = p_interface_header_id
14539: AND PLI.requisition_line_id = PRL.requisition_line_id -- JOIN
14540: AND PRL.supplier_ref_number IS NOT NULL;
14541:
14884: -- populated, use that information. Otherwise, create a default
14885: -- payitem. Also create DELIVERY and ADVANCE payitems as necessary.
14886: --Parameters:
14887: -- IN:
14888: -- p_interface_line_id: id of the line in po_lines_interface
14889: -- p_po_line_id: id of the line in po_lines_all
14890: -- p_precision: precision of the currency desired. Used to round amounts.
14891: -- p_ext_precision: extended precision of the currency desired.
14892: -- Used to round prices
14885: -- payitem. Also create DELIVERY and ADVANCE payitems as necessary.
14886: --Parameters:
14887: -- IN:
14888: -- p_interface_line_id: id of the line in po_lines_interface
14889: -- p_po_line_id: id of the line in po_lines_all
14890: -- p_precision: precision of the currency desired. Used to round amounts.
14891: -- p_ext_precision: extended precision of the currency desired.
14892: -- Used to round prices
14893: -- OUT:
14912:
14913: d_progress NUMBER;
14914: d_module VARCHAR2(70) := 'po.plsql.PO_INTERFACE_S.create_payitems';
14915:
14916: l_po_header_id PO_LINES_ALL.po_header_id%TYPE;
14917: l_line_value_basis PO_LINES_ALL.order_type_lookup_code%TYPE;
14918: l_line_matching_basis PO_LINES_ALL.matching_basis%TYPE;
14919: l_line_unit_price PO_LINES_ALL.unit_price%TYPE;
14920: l_line_quantity PO_LINES_ALL.quantity%TYPE;
14913: d_progress NUMBER;
14914: d_module VARCHAR2(70) := 'po.plsql.PO_INTERFACE_S.create_payitems';
14915:
14916: l_po_header_id PO_LINES_ALL.po_header_id%TYPE;
14917: l_line_value_basis PO_LINES_ALL.order_type_lookup_code%TYPE;
14918: l_line_matching_basis PO_LINES_ALL.matching_basis%TYPE;
14919: l_line_unit_price PO_LINES_ALL.unit_price%TYPE;
14920: l_line_quantity PO_LINES_ALL.quantity%TYPE;
14921: l_line_amount PO_LINES_ALL.amount%TYPE;
14914: d_module VARCHAR2(70) := 'po.plsql.PO_INTERFACE_S.create_payitems';
14915:
14916: l_po_header_id PO_LINES_ALL.po_header_id%TYPE;
14917: l_line_value_basis PO_LINES_ALL.order_type_lookup_code%TYPE;
14918: l_line_matching_basis PO_LINES_ALL.matching_basis%TYPE;
14919: l_line_unit_price PO_LINES_ALL.unit_price%TYPE;
14920: l_line_quantity PO_LINES_ALL.quantity%TYPE;
14921: l_line_amount PO_LINES_ALL.amount%TYPE;
14922: l_line_purchase_basis PO_LINES_ALL.purchase_basis%TYPE;
14915:
14916: l_po_header_id PO_LINES_ALL.po_header_id%TYPE;
14917: l_line_value_basis PO_LINES_ALL.order_type_lookup_code%TYPE;
14918: l_line_matching_basis PO_LINES_ALL.matching_basis%TYPE;
14919: l_line_unit_price PO_LINES_ALL.unit_price%TYPE;
14920: l_line_quantity PO_LINES_ALL.quantity%TYPE;
14921: l_line_amount PO_LINES_ALL.amount%TYPE;
14922: l_line_purchase_basis PO_LINES_ALL.purchase_basis%TYPE;
14923:
14916: l_po_header_id PO_LINES_ALL.po_header_id%TYPE;
14917: l_line_value_basis PO_LINES_ALL.order_type_lookup_code%TYPE;
14918: l_line_matching_basis PO_LINES_ALL.matching_basis%TYPE;
14919: l_line_unit_price PO_LINES_ALL.unit_price%TYPE;
14920: l_line_quantity PO_LINES_ALL.quantity%TYPE;
14921: l_line_amount PO_LINES_ALL.amount%TYPE;
14922: l_line_purchase_basis PO_LINES_ALL.purchase_basis%TYPE;
14923:
14924: l_payment_type PO_LINE_LOCATIONS_ALL.payment_type%TYPE;
14917: l_line_value_basis PO_LINES_ALL.order_type_lookup_code%TYPE;
14918: l_line_matching_basis PO_LINES_ALL.matching_basis%TYPE;
14919: l_line_unit_price PO_LINES_ALL.unit_price%TYPE;
14920: l_line_quantity PO_LINES_ALL.quantity%TYPE;
14921: l_line_amount PO_LINES_ALL.amount%TYPE;
14922: l_line_purchase_basis PO_LINES_ALL.purchase_basis%TYPE;
14923:
14924: l_payment_type PO_LINE_LOCATIONS_ALL.payment_type%TYPE;
14925: l_shipment_type PO_LINE_LOCATIONS_ALL.shipment_type%TYPE;
14918: l_line_matching_basis PO_LINES_ALL.matching_basis%TYPE;
14919: l_line_unit_price PO_LINES_ALL.unit_price%TYPE;
14920: l_line_quantity PO_LINES_ALL.quantity%TYPE;
14921: l_line_amount PO_LINES_ALL.amount%TYPE;
14922: l_line_purchase_basis PO_LINES_ALL.purchase_basis%TYPE;
14923:
14924: l_payment_type PO_LINE_LOCATIONS_ALL.payment_type%TYPE;
14925: l_shipment_type PO_LINE_LOCATIONS_ALL.shipment_type%TYPE;
14926: l_payitem_quantity PO_LINE_LOCATIONS_ALL.quantity%TYPE;
15008: , l_line_unit_price
15009: , l_line_quantity
15010: , l_line_amount
15011: , l_line_purchase_basis
15012: FROM po_lines_all pol
15013: WHERE pol.po_line_id = p_po_line_id;
15014:
15015: d_progress := 20;
15016:
15616: --Parameters:
15617: -- IN:
15618: -- p_req_line_id: id of the requisition line that is the source of
15619: -- the po line; null if no backing req.
15620: -- p_po_line_id: id of the line in po_lines_all
15621: -- p_interface_line_id: id of the line in po_lines_interface
15622: -- p_precision: precision of the currency desired. Used to round amounts.
15623: -- p_ext_precision: extended precision of the currency desired.
15624: -- Used to round prices
15617: -- IN:
15618: -- p_req_line_id: id of the requisition line that is the source of
15619: -- the po line; null if no backing req.
15620: -- p_po_line_id: id of the line in po_lines_all
15621: -- p_interface_line_id: id of the line in po_lines_interface
15622: -- p_precision: precision of the currency desired. Used to round amounts.
15623: -- p_ext_precision: extended precision of the currency desired.
15624: -- Used to round prices
15625: -- OUT: