[Home] [Help]
1308: approved_date = sysdate,
1309: approved_flag = 'Y'
1310: where po_header_id = x_document_id;
1311:
1312: update po_line_locations_all
1313: set approved_flag = 'Y',
1314: approved_date = sysdate
1315: where po_header_id = x_document_id ;
1316:
1322: approved_date = sysdate,
1323: approved_flag = 'Y'
1324: where po_release_id = x_document_id;
1325:
1326: update po_line_locations_all
1327: set approved_flag = 'Y',
1328: approved_date = sysdate
1329: where po_release_id = x_document_id ;
1330:
3019:
3020: x_line_location_id number := null;
3021: l_line_loc_id_tbl po_tbl_number;
3022:
3023: l_price_break_id PO_LINE_LOCATIONS_ALL.line_location_id%TYPE;--
3025: x_quote_header_id number := null;
3026: x_quote_line_id number := null;
3027: x_match_blanket_line varchar2(1) := null;
3120:
3121: -- oneoff 3201308 start
3122: l_needby_prf varchar2(1);
3123: l_shipto_prf varchar2(1);
3124: l_min_shipment_num po_line_locations_all.shipment_num%TYPE;
3125: l_ship_to_loc po_line_locations_all.ship_to_location_id%TYPE;
3126: l_ship_to_org po_line_locations_all.ship_to_organization_id%TYPE;
3127: l_need_by_date po_line_locations_all.need_by_date%TYPE;
3128: -- oneoff 3201308 end
3121: -- oneoff 3201308 start
3122: l_needby_prf varchar2(1);
3123: l_shipto_prf varchar2(1);
3124: l_min_shipment_num po_line_locations_all.shipment_num%TYPE;
3125: l_ship_to_loc po_line_locations_all.ship_to_location_id%TYPE;
3126: l_ship_to_org po_line_locations_all.ship_to_organization_id%TYPE;
3127: l_need_by_date po_line_locations_all.need_by_date%TYPE;
3128: -- oneoff 3201308 end
3129:
3122: l_needby_prf varchar2(1);
3123: l_shipto_prf varchar2(1);
3124: l_min_shipment_num po_line_locations_all.shipment_num%TYPE;
3125: l_ship_to_loc po_line_locations_all.ship_to_location_id%TYPE;
3126: l_ship_to_org po_line_locations_all.ship_to_organization_id%TYPE;
3127: l_need_by_date po_line_locations_all.need_by_date%TYPE;
3128: -- oneoff 3201308 end
3129:
3130: l_contractor_status PO_REQUISITION_LINES_ALL.contractor_status%TYPE;
3123: l_shipto_prf varchar2(1);
3124: l_min_shipment_num po_line_locations_all.shipment_num%TYPE;
3125: l_ship_to_loc po_line_locations_all.ship_to_location_id%TYPE;
3126: l_ship_to_org po_line_locations_all.ship_to_organization_id%TYPE;
3127: l_need_by_date po_line_locations_all.need_by_date%TYPE;
3128: -- oneoff 3201308 end
3129:
3130: l_contractor_status PO_REQUISITION_LINES_ALL.contractor_status%TYPE;
3131: --
3152: l_interface_header_id_tbl interface_header_id_tbl;
3153: l_interface_line_id_tbl interface_line_id_tbl;
3154: --
3155:
3156: l_outsourced_assembly po_line_locations_all.outsourced_assembly%type; --
3158:
3159: --
3160: l_enhanced_pricing_flag po_doc_style_headers.enhanced_pricing_flag%type;
4961: poll.need_by_date
4962: into l_ship_to_loc,
4963: l_ship_to_org,
4964: l_need_by_date
4965: from po_line_locations_all poll
4966: where poll.po_line_id = x_po_line_id
4967: and poll.shipment_num = l_min_shipment_num;
4968: EXCEPTION
4969: when others then
5050:
5051: --
5052: --All the shipments which have been created need to get the
5053: --new price as on the line for Standard POs.
5054: UPDATE po_line_locations_all
5055: -- Bug 3417479
5056: -- SET price_override = x_unit_price
5057: SET price_override = nvl(x_unit_price, price_override)
5058: -- Bug 4902592. Not setting tax_attribute_update_code here because
5738: l_encode VARCHAR2(2000);
5739: x_po_uom varchar2(25):=null;
5740: x_temp_uom varchar2(25):=null;
5741: x_temp_item_id number:=null;
5742: x_closed_reason po_line_locations.closed_reason%TYPE;
5743: x_uom_convert varchar2(2) := fnd_profile.value('PO_REQ_BPA_UOM_CONVERT');
5744:
5745: --
5746: l_promised_date DATE := INTERFACE.PROMISED_DATE;
5748: --
5749:
5750: l_api_name CONSTANT VARCHAR2(30) := 'create_shipment';
5751: l_progress VARCHAR2(3) := '000'; --< Bug 3210331 >
5752: l_manual_price_change_flag po_line_locations_all.manual_price_change_flag%TYPE := NULL; --bug 3495772
5753:
5754: -- Bug 5208159
5755: l_from_type_lookup_code po_headers_all.type_lookup_code%TYPE;
5756:
5953: l_progress := '060';
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, --
5959: WHERE poll.po_header_id = interface.po_header_id
5960: AND poll.po_line_id = pol.po_line_id
5961: AND poll.shipment_num = interface.shipment_num
5987: poll.secondary_unit_of_measure --
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, --
5993: po_releases_all por --
5994: WHERE poll.po_header_id = interface.po_header_id
5995: AND poll.po_line_id = pol.po_line_id
6048: if (x_line_location_id is not null and x_cumulative_flag = FALSE) then
6049: begin
6050: select nvl(quantity, 0)
6051: into x_ship_qty
6052: from po_line_locations_all --
6054: exception
6055: when others THEN
6056: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
6129: p_token => l_progress,
6130: p_message => 'Create_shipment: shipment exist');
6131: PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
6132: p_token => l_progress,
6133: p_message => 'Create_shipment: Update PO line locations');
6134: END IF;
6135:
6136: /*Bug 5629398 Start: Quantity conversion for foreign currency was not happening for
6137: amount based lines when we try to add to existing shipment line */
6187:
6188:
6189: --
6190:
6191: UPDATE po_line_locations_all --
6193: -- start of 1548597
6194: secondary_quantity = secondary_quantity + x_secondary_quantity,
6195: -- end of 1548597
6412: p_token => l_progress,
6413: p_message => 'Create shipment: Create a new shipment');
6414: END IF;
6415:
6416: SELECT po_line_locations_s.nextval
6417: INTO x_line_location_id
6418: FROM sys.dual;
6419:
6420: -- bug: 404191
6470: interface.vendor_site_id,
6471: x_country_of_origin_code);
6472:
6473: /*Bug no 781929
6474: Last accept date is also inserted into po_line_locations table.
6475: last_accept_date = interface.need_by_date+rc.days_late_receipt_allowed.
6476: Purposely , null handling has not been done, since even if either
6477: need_by_date or days_late_received_allowed is null then the last_accept_date
6478: should be null.
6491: l_progress := '240';
6492: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
6493: PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
6494: p_token => l_progress,
6495: p_message => 'Create shipment: Before insert into po line locations');
6496: END IF;
6497:
6498: /* CONSIGNED FPI start : Set the closed reason for consigned */
6499: IF interface.consigned_flag = 'Y' THEN
6629:
6630: --Bug11882785-End
6631:
6632:
6633: INSERT INTO po_line_locations_all --
6635: last_update_date,
6636: last_updated_by,
6637: po_header_id,
6892: l_progress := '260';
6893: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
6894: PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
6895: p_token => l_progress,
6896: p_message => 'Create shipment: After insert into po line locations');
6897: END IF;
6898:
6899: -- Bug 882050: Shipment level global attribute
6900: if g_document_type = 'RFQ' then
6933: SELECT PO_HEADER_ID
6934: ,PO_LINE_ID
6935: INTO x_p_po_header_id
6936: ,x_p_po_line_id
6937: FROM PO_LINE_LOCATIONS_ALL --
6939: EXCEPTION
6940: WHEN NO_DATA_FOUND THEN
6941: IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
7103: l_progress VARCHAR2(3) := '000'; --< Bug 3210331 >
7104:
7105:
7106: l_amount_ordered NUMBER; --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
7107: l_drop_ship_flag po_line_locations.drop_ship_flag%type; --bug#3603067
7108: -- bug 5208159
7109: l_from_type_lookup_code po_headers_all.TYPE_LOOKUP_CODE%type;
7110:
7111: --introduced to hold the value of drop_ship_flag for shipments
7415: --!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
7416: --bug#3603067 need to get the value of drop ship flag for the shipment
7417: begin
7418: select nvl(drop_ship_flag,'N') into l_drop_ship_flag
7419: from po_line_locations_all where
7420: line_location_id=x_line_location_id;
7421: exception
7422: when others then
7423: null;
7426:
7427: BEGIN
7428: SELECT pdi.amount_ordered
7429: INTO l_amount_ordered
7430: FROM po_distributions_interface pdi, po_line_locations_all poll
7431: WHERE pdi.interface_header_id = interface.interface_header_id
7432: AND pdi.interface_line_id = interface.interface_line_id
7433: AND poll.line_location_id = x_line_location_id;
7434:
7699: poll.shipment_type, --
7700: nvl2(g_calculate_tax_flag, 'CREATE', null), --
7701: pdi.interface_distribution_ref --
7702: FROM po_distributions_interface pdi,
7703: po_line_locations_all poll,
7704: po_req_distributions_all prd
7705: --bug 16302602,join table po_req_distributions_all to get REQ_LINE_CURRENCY_AMOUNT
7706: WHERE pdi.interface_header_id = interface.interface_header_id
7707: AND pdi.interface_line_id = interface.interface_line_id
7819:
7820: l_sum_dist_amounts PO_DISTRIBUTIONS_ALL.amount_ordered%TYPE;
7821: l_last_dist_amount PO_DISTRIBUTIONS_ALL.amount_ordered%TYPE;
7822: l_last_distribution_id PO_DISTRIBUTIONS_ALL.po_distribution_id%TYPE;
7823: l_shipment_amount PO_LINE_LOCATIONS_ALL.amount%TYPE;
7824:
7825: BEGIN
7826:
7827: l_progress:='000'; PO_DEBUG.debug_begin(l_log_head);
7846: -- Get the shipment amount ================================================
7847: --
7848: SELECT amount
7849: INTO l_shipment_amount
7850: FROM po_line_locations_all
7851: WHERE line_location_id = p_line_location_id;
7852:
7853: l_progress:='040'; PO_DEBUG.debug_var(l_log_head,l_progress,'l_shipment_amount',l_shipment_amount);
7854:
7917:
7918: l_sum_dist_quantities PO_DISTRIBUTIONS_ALL.quantity_ordered%TYPE;
7919: l_last_dist_id PO_DISTRIBUTIONS_ALL.po_distribution_id%TYPE;
7920: l_last_dist_qty PO_DISTRIBUTIONS_ALL.quantity_ordered%TYPE;
7921: l_shipment_quantity PO_LINE_LOCATIONS_ALL.quantity%TYPE;
7922:
7923: BEGIN
7924:
7925: d_progress := 0;
7943: END IF;
7944:
7945: SELECT poll.quantity
7946: INTO l_shipment_quantity
7947: FROM po_line_locations_all poll
7948: WHERE poll.line_location_id = p_line_location_id;
7949:
7950: d_progress := 30;
7951:
8532: l_wip_resource_seq_num PO_DISTRIBUTIONS_INTERFACE.wip_resource_seq_num%TYPE;
8533:
8534:
8535: --< New start_workflow parameters in FPJ End >
8536: l_transaction_flow_header_id PO_LINE_LOCATIONS.transaction_flow_header_id%TYPE;
8537: l_dest_charge_success BOOLEAN;
8538: l_dest_variance_success BOOLEAN;
8539: l_dest_charge_account_id PO_DISTRIBUTIONS_INTERFACE.dest_charge_account_id%TYPE;
8540: l_dest_variance_account_id PO_DISTRIBUTIONS_INTERFACE.dest_variance_account_id%TYPE;
10460: WHERE interface_header_id = x_interface_header_id;
10461: --
10462:
10463: --
10464: DELETE po_line_locations_interface
10465: WHERE interface_header_id = x_interface_header_id;
10466: --
10467:
10468: DELETE po_lines_interface
11152:
11153: SELECT nvl(max(poll.shipment_num),0)
11154: INTO x_shipment_num
11155: FROM po_headers_all ph,
11156: po_line_locations_all poll,
11157: po_releases_all pr
11158: WHERE ph.po_header_id = poll.po_header_id
11159: AND ph.segment1 = x_document_num
11160: AND pr.po_header_id = ph.po_header_id
12574: SELECT PLL.shipment_num
12575: ,PLL.line_location_id
12576: INTO x_po_shipment_num
12577: ,x_line_location_to_check
12578: FROM PO_LINE_LOCATIONS_ALL PLL --
12580: -- bug 4599140 (included the following OR condition so that the SQL works correctly
12581: -- for null need_by_date)
12582: AND (( to_char(PLL.need_by_date-(to_number(substr(to_char(PLL.need_by_date,
12622: SELECT PLL.shipment_num
12623: ,PLL.line_location_id
12624: INTO x_po_shipment_num
12625: ,x_line_location_to_check
12626: FROM PO_LINE_LOCATIONS_ALL PLL --
12628: AND PLL.SHIP_TO_LOCATION_ID = x_ship_to_location_id
12629: AND PLL.SHIP_TO_ORGANIZATION_ID =
12630: x_destination_org_id
12665: SELECT PLL.shipment_num
12666: ,PLL.line_location_id
12667: INTO x_po_shipment_num
12668: ,x_line_location_to_check
12669: FROM PO_LINE_LOCATIONS_ALL PLL --
12671: AND nvl(PLL.drop_ship_flag, 'N') <> 'Y' --
12672: AND nvl(PLL.CONSIGNED_FLAG,'N') = nvl(x_consigned_flag,'N')
12673: AND ROWNUM = 1
12715: -- a new shipment or not
12716:
12717: SELECT PLL.shipment_num
12718: INTO x_po_shipment_num
12719: FROM PO_LINE_LOCATIONS_ALL PLL, --
12721: PO_SYSTEM_PARAMETERS_ALL PSP --
12722: WHERE PLL.LINE_LOCATION_ID = x_line_location_to_check
12723: AND PRL.REQUISITION_LINE_ID = x_requisition_line_id
12786: BEGIN
12787:
12788: SELECT PLL.shipment_num
12789: INTO x_po_shipment_num
12790: FROM PO_LINE_LOCATIONS_ALL PLL, --
12792: PO_SYSTEM_PARAMETERS_ALL PSP --
12793: WHERE PLL.PO_LINE_ID = x_po_line_id
12794: AND PRL.REQUISITION_LINE_ID = x_requisition_line_id
12902: SELECT PLL.shipment_num
12903: ,PLL.line_location_id
12904: INTO x_po_shipment_num,
12905: x_line_location_to_check
12906: FROM PO_LINE_LOCATIONS_ALL PLL, --
12908: PO_LINE_TYPES PLT --
12909: WHERE POL.PO_LINE_ID = l_po_line_id -- bug2788115
12910: AND POL.po_line_id = PLL.po_line_id --
13587: ELSE
13588: l_progress := '060';
13589: select nvl(max(shipment_num),0)
13590: into x_shipment_num
13591: from po_line_locations_all poll --
13593: and poll.shipment_type in ('STANDARD', 'PLANNED', 'RFQ');
13594:
13595: -- Bug 605715, lpo, 01/05/98
13615:
13616: select nvl(max(shipment_num),0)
13617: into x_shipment_num
13618: from po_headers_all ph, --
13619: po_line_locations_all poll, --
13621: where ph.po_header_id = poll.po_header_id
13622: and ph.segment1 = x_document_num
13623: and pr.po_header_id = ph.po_header_id
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
14153: PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
14154: END IF;
14155:
14156: begin
14157: SELECT po_line_locations_s.nextval
14158: INTO x_line_location_id
14159: FROM sys.dual;
14160: exception
14161: when others then
14202: --
14203:
14204:
14205: begin
14206: po_line_locations_pkg_s0.insert_row(
14207: l_row_id,
14208: x_Line_Location_Id,
14209: interface.last_update_date,
14210: interface.last_updated_by,
14875: --Name: create_payitems
14876: --Pre-reqs:
14877: -- PO Line has been created.
14878: --Modifies:
14879: -- PO_LINE_LOCATIONS_ALL
14880: --Locks:
14881: -- None.
14882: --Function:
14883: -- Create all payitems for a PO Line. If PO_LINE_LOCATIONS_INTERFACE is
14879: -- PO_LINE_LOCATIONS_ALL
14880: --Locks:
14881: -- None.
14882: --Function:
14883: -- Create all payitems for a PO Line. If PO_LINE_LOCATIONS_INTERFACE is
14884: -- populated, use that information. Otherwise, create a default
14885: -- payitem. Also create DELIVERY and ADVANCE payitems as necessary.
14886: --Parameters:
14887: -- IN:
14891: -- p_ext_precision: extended precision of the currency desired.
14892: -- Used to round prices
14893: -- OUT:
14894: -- x_line_location_id: id of the first actuals (STANDARD) payitem
14895: -- in po_line_locations_all
14896: -- x_line_loc_id_tbl: ids of all payitems created in po_line_locations_all
14897: --Notes:
14898: -- None
14899: --Testing:
14892: -- Used to round prices
14893: -- OUT:
14894: -- x_line_location_id: id of the first actuals (STANDARD) payitem
14895: -- in po_line_locations_all
14896: -- x_line_loc_id_tbl: ids of all payitems created in po_line_locations_all
14897: --Notes:
14898: -- None
14899: --Testing:
14900: -- None
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;
14927: l_payitem_amount PO_LINE_LOCATIONS_ALL.amount%TYPE;
14928: l_payitem_price PO_LINE_LOCATIONS_ALL.price_override%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;
14927: l_payitem_amount PO_LINE_LOCATIONS_ALL.amount%TYPE;
14928: l_payitem_price PO_LINE_LOCATIONS_ALL.price_override%TYPE;
14929:
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;
14927: l_payitem_amount PO_LINE_LOCATIONS_ALL.amount%TYPE;
14928: l_payitem_price PO_LINE_LOCATIONS_ALL.price_override%TYPE;
14929:
14930: l_req_tax_code_id PO_REQUISITION_LINES_ALL.tax_code_id%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;
14927: l_payitem_amount PO_LINE_LOCATIONS_ALL.amount%TYPE;
14928: l_payitem_price PO_LINE_LOCATIONS_ALL.price_override%TYPE;
14929:
14930: l_req_tax_code_id PO_REQUISITION_LINES_ALL.tax_code_id%TYPE;
14931: l_req_tax_user_override_flag PO_REQUISITION_LINES_ALL.tax_user_override_flag%TYPE;
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;
14927: l_payitem_amount PO_LINE_LOCATIONS_ALL.amount%TYPE;
14928: l_payitem_price PO_LINE_LOCATIONS_ALL.price_override%TYPE;
14929:
14930: l_req_tax_code_id PO_REQUISITION_LINES_ALL.tax_code_id%TYPE;
14931: l_req_tax_user_override_flag PO_REQUISITION_LINES_ALL.tax_user_override_flag%TYPE;
14932: l_req_tax_status_indicator PO_REQUISITION_LINES_ALL.tax_status_indicator%TYPE;
14943: l_payitem_tax_code_id_tbl po_tbl_number;
14944: l_payitems_created NUMBER;
14945:
14946: l_routing_name RCV_ROUTING_HEADERS.routing_name%TYPE;
14947: l_line_loc_id PO_LINE_LOCATIONS_ALL.line_location_id%TYPE;
14948:
14949: l_po_promised_def_prf VARCHAR2(1) := FND_PROFILE.value('PO_NEED_BY_PROMISE_DEFAULTING');
14950: l_country_of_origin_code VARCHAR2(2);
14951: l_tax_status VARCHAR2(10);
14976: polli.description,
14977: polli.work_approver_id,
14978: polli.bid_payment_id,
14979: polli.unit_of_measure
14980: FROM po_line_locations_interface polli
14981: WHERE polli.interface_line_id = p_interface_line_id
14982: ORDER BY polli.shipment_num;
14983:
14984: line_location_rec poll_interface_cursor%ROWTYPE;
15049:
15050: d_progress := 50;
15051:
15052: --SQL WHAT: Insert information for default payitem into
15053: -- po_line_locations interface table
15054: --SQL WHY : We will use line_locations_interface as a
15055: -- common entry point for payitems - whether they come from
15056: -- sourcing or we default them from scratch here.
15057:
15054: --SQL WHY : We will use line_locations_interface as a
15055: -- common entry point for payitems - whether they come from
15056: -- sourcing or we default them from scratch here.
15057:
15058: INSERT INTO po_line_locations_interface
15059: (
15060: interface_line_location_id
15061: , interface_header_id
15062: , interface_line_id
15070: , promised_date
15071: )
15072: VALUES
15073: (
15074: PO_LINE_LOCATIONS_INTERFACE_S.NEXTVAL
15075: , interface.interface_header_id
15076: , p_interface_line_id
15077: , l_payitem_quantity
15078: , l_payitem_amount
15095: -- if financing case, create actual delivery payitem
15096: -- this payitem has a shipment_type of STANDARD and payment_type of DELIVERY
15097:
15098: --SQL WHAT: Insert information for delivery payitem into
15099: -- po_line_locations_interface table
15100: --SQL WHY : We will use line_locations_interface as a
15101: -- common entry point for payitems, including ones we create
15102: -- behind the scenes
15103:
15100: --SQL WHY : We will use line_locations_interface as a
15101: -- common entry point for payitems, including ones we create
15102: -- behind the scenes
15103:
15104: INSERT INTO po_line_locations_interface
15105: (
15106: interface_line_location_id
15107: , interface_header_id
15108: , interface_line_id
15117: , promised_date
15118: )
15119: VALUES
15120: (
15121: PO_LINE_LOCATIONS_INTERFACE_S.NEXTVAL
15122: , interface.interface_header_id
15123: , p_interface_line_id
15124: , l_line_quantity
15125: , l_line_amount
15153: value => interface.item_description);
15154: l_advance_desc := substrb(FND_MESSAGE.get, 1, 240);
15155:
15156: --SQL WHAT: Insert information for advance payitem into
15157: -- po_line_locations_interface table
15158: --SQL WHY : We will use line_locations_interface as a
15159: -- common entry point for payitems, including ones we create
15160: -- behind the scenes
15161:
15158: --SQL WHY : We will use line_locations_interface as a
15159: -- common entry point for payitems, including ones we create
15160: -- behind the scenes
15161:
15162: INSERT INTO po_line_locations_interface
15163: (
15164: interface_line_location_id
15165: , interface_header_id
15166: , interface_line_id
15174: , need_by_date
15175: )
15176: VALUES
15177: (
15178: PO_LINE_LOCATIONS_INTERFACE_S.NEXTVAL
15179: , interface.interface_header_id
15180: , p_interface_line_id
15181: , NULL
15182: , interface.advance_amount
15213: --SQL WHAT: Default/update values for scratch payitems in interface table
15214: --SQL WHY : This allows us to update all new payitems' values at once,
15215: -- including payitems from sourcing or ones we've created in autocreate
15216:
15217: UPDATE po_line_locations_interface polli
15218: SET polli.value_basis =
15219: DECODE(polli.payment_type,
15220: 'RATE', 'QUANTITY',
15221: 'LUMPSUM', 'FIXED PRICE',
15278: l_payitems_created := 0;
15279:
15280: -- now iterate over the rows in the interface table
15281: -- for each row, get the default tax, receiving controls, and country of
15282: -- origin before inserting a new row into po_line_locations_all
15283:
15284: OPEN poll_interface_cursor(p_interface_line_id);
15285: LOOP
15286:
15327: );
15328:
15329: d_progress := 210;
15330: IF (PO_LOG.d_stmt) THEN
15331: PO_LOG.stmt(d_module, d_progress, 'Inserting payitem into po_line_locations_all');
15332: END IF;
15333:
15334: -- insert payitem into po_line_locations_all
15335:
15330: IF (PO_LOG.d_stmt) THEN
15331: PO_LOG.stmt(d_module, d_progress, 'Inserting payitem into po_line_locations_all');
15332: END IF;
15333:
15334: -- insert payitem into po_line_locations_all
15335:
15336: --SQL WHAT: Insert payitem, using info in po_line_locations_interface
15337: --SQL WHY : This allows us to insert all payitems in one location.
15338:
15332: END IF;
15333:
15334: -- insert payitem into po_line_locations_all
15335:
15336: --SQL WHAT: Insert payitem, using info in po_line_locations_interface
15337: --SQL WHY : This allows us to insert all payitems in one location.
15338:
15339: INSERT INTO po_line_locations_all
15340: (
15335:
15336: --SQL WHAT: Insert payitem, using info in po_line_locations_interface
15337: --SQL WHY : This allows us to insert all payitems in one location.
15338:
15339: INSERT INTO po_line_locations_all
15340: (
15341: line_location_id
15342: , last_update_date
15343: , last_updated_by
15416: ,tax_attribute_update_code --
15417: )
15418: VALUES
15419: (
15420: PO_LINE_LOCATIONS_S.nextval
15421: , interface.last_update_date
15422: , interface.last_updated_by
15423: , interface.po_header_id
15424: , interface.creation_date
15525:
15526: d_progress := 230;
15527:
15528: -- create link between interface row and transaction row
15529: UPDATE po_line_locations_interface polli
15530: SET polli.line_location_id = l_line_loc_id
15531: WHERE polli.interface_line_location_id =
15532: line_location_rec.interface_line_location_id;
15533:
15661: , poll.payment_type
15662: , pod.distribution_type
15663: , pod.rate
15664: FROM po_distributions_all pod,
15665: po_line_locations_all poll
15666: WHERE poll.po_line_id = p_po_line_id
15667: AND pod.line_location_id = poll.line_location_id
15668: AND pod.req_distribution_id IS NULL;
15669:
15900: THEN NULL
15901: ELSE ROUND((prd.req_line_amount / prl.amount) * poll.amount, p_precision)
15902: END)
15903: ,nvl2(g_calculate_tax_flag, 'CREATE', null) --
15904: FROM po_line_locations_all poll
15905: , po_req_distributions_all prd
15906: , po_requisition_lines_all prl
15907: WHERE poll.po_line_id = p_po_line_id
15908: AND prd.requisition_line_id = p_req_line_id
16031: , poll.quantity
16032: , poll.amount
16033: , params.sob_id
16034: ,nvl2(g_calculate_tax_flag, 'CREATE', null) --
16035: FROM po_line_locations_all poll
16036: , po_line_locations_interface polli
16037: WHERE poll.po_line_id = p_po_line_id
16038: AND poll.line_location_id = polli.line_location_id
16039: AND poll.payment_type <> 'ADVANCE';
16032: , poll.amount
16033: , params.sob_id
16034: ,nvl2(g_calculate_tax_flag, 'CREATE', null) --
16035: FROM po_line_locations_all poll
16036: , po_line_locations_interface polli
16037: WHERE poll.po_line_id = p_po_line_id
16038: AND poll.line_location_id = polli.line_location_id
16039: AND poll.payment_type <> 'ADVANCE';
16040:
16141: , pod.expenditure_organization_id
16142: , pod.expenditure_item_date
16143: , params.sob_id
16144: ,nvl2(g_calculate_tax_flag, 'CREATE', null) --
16145: FROM po_line_locations_all adv,
16146: po_line_locations_all deliv,
16147: po_distributions_all pod
16148: WHERE adv.po_line_id = p_po_line_id
16149: AND adv.payment_type = 'ADVANCE'
16142: , pod.expenditure_item_date
16143: , params.sob_id
16144: ,nvl2(g_calculate_tax_flag, 'CREATE', null) --
16145: FROM po_line_locations_all adv,
16146: po_line_locations_all deliv,
16147: po_distributions_all pod
16148: WHERE adv.po_line_id = p_po_line_id
16149: AND adv.payment_type = 'ADVANCE'
16150: AND deliv.line_location_id =
16148: WHERE adv.po_line_id = p_po_line_id
16149: AND adv.payment_type = 'ADVANCE'
16150: AND deliv.line_location_id =
16151: ( SELECT poll.line_location_id
16152: FROM po_line_locations_all poll
16153: WHERE poll.po_line_id = p_po_line_id
16154: AND poll.shipment_type = 'STANDARD'
16155: AND poll.shipment_num =
16156: ( SELECT min(poll2.shipment_num)
16153: WHERE poll.po_line_id = p_po_line_id
16154: AND poll.shipment_type = 'STANDARD'
16155: AND poll.shipment_num =
16156: ( SELECT min(poll2.shipment_num)
16157: FROM po_line_locations_all poll2
16158: WHERE poll2.po_line_id = poll.po_line_id
16159: AND poll2.shipment_type = 'STANDARD'))
16160: AND pod.line_location_id = deliv.line_location_id;
16161:
16171: -- calibrate last distribution for each pay item
16172:
16173: SELECT poll.line_location_id, poll.value_basis
16174: BULK COLLECT INTO l_line_loc_id_tbl, l_line_loc_value_basis_tbl
16175: FROM po_line_locations_all poll
16176: WHERE poll.po_line_id = p_po_line_id;
16177:
16178: FOR i in 1..l_line_loc_id_tbl.COUNT
16179: LOOP