The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(SUM(AMOUNT_DUE_REMAINING),0)
INTO v_balance
FROM AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
* This proc is exact copy of OE_LINE_FULLFILL.UPDATE_SERVICE_DATES(). Therefore, any changes in oe_line_fullfill.update_service_dates()
* should be imported to this proc as well.
* This proc is called in case of SERVICE lines, with ORDER reference type, with VARIABLE type accounting rule, with ACCOUNTING_RULE_DURATION field being null
* and service_start_date and service_end_Date being null.
*
*/
PROCEDURE Update_Service_Dates
(
p_line_rec IN OUT NOCOPY OE_Order_Pub.Line_Rec_Type
)
IS
l_return_status VARCHAR2(1);
oe_debug_pub.add('entering oe_invoice_pub . update_service_dates() '
||' p_line_rec.line_id = ' || P_LINE_REC.LINE_ID
||' p_line_rec.service_start_date = '||TO_CHAR(P_LINE_REC.SERVICE_START_DATE, 'YYYY/MM/DD' )
||' p_line_rec.service_end_date = '|| TO_CHAR ( P_LINE_REC.SERVICE_END_DATE , 'YYYY/MM/DD' )
) ;
oe_debug_pub.add('exiting oe_invoice_pub . update_service_dates() '
||' p_line_rec.line_id = ' || P_LINE_REC.LINE_ID
||' p_line_rec.service_start_date = '||TO_CHAR(P_LINE_REC.SERVICE_START_DATE, 'YYYY/MM/DD' )
||' p_line_rec.service_end_date = '|| TO_CHAR ( P_LINE_REC.SERVICE_END_DATE , 'YYYY/MM/DD' )
) ;
END Update_Service_Dates;
Procedure Update_line_flow_status
( p_line_id IN NUMBER
, p_flow_status_code IN VARCHAR2
) IS
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
oe_debug_pub.add( 'Inside Update Line Flow Status');
Update oe_order_lines_all
Set flow_status_code = p_flow_status_code
Where line_id = p_line_id;
End Update_line_flow_status;
Procedure Update_header_flow_status
( p_header_id IN NUMBER
, p_flow_status_code IN VARCHAR2
) IS
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
oe_debug_pub.add( 'Inside Update Header Flow Status');
Update oe_order_headers_all
Set flow_status_code = p_flow_status_code
Where header_id = p_header_id;
End Update_header_flow_status;
SELECT COUNT(*)
INTO l_cancelled_delivery_detail
FROM WSH_DELIVERY_DETAILS
WHERE SOURCE_LINE_ID = p_line_rec.line_id
AND SOURCE_CODE='OE'
AND RELEASED_STATUS = 'D';
SELECT INVOICEABLE_ITEM_FLAG, INVOICE_ENABLED_FLAG
INTO l_invoiceable_item_flag, l_invoice_enabled_flag
FROM mtl_system_items
WHERE inventory_item_id = p_line_rec.inventory_item_id
AND organization_id = nvl(p_line_rec.ship_from_org_id,l_master_organization_id); --bug5336639
SELECT msi.SERVICEABLE_PRODUCT_FLAG
INTO l_serviceable_product_flag
FROM oe_order_lines_all ol, mtl_system_items msi
WHERE ol.line_id = l_service_reference_line_id
AND ol.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = nvl(ol.ship_from_org_id, oe_sys_parameters.value('MASTER_ORGANIZATION_ID', ol.org_id));
SELECT nvl(ctt.credit_memo_type_id, 0),
ctt_credit.creation_sign
INTO x_credit_memo_type_id, x_credit_creation_sign
FROM ra_customer_trx_lines_all ctl,
ra_customer_trx_all ct,
ra_cust_trx_types_all ctt,
ra_cust_trx_types_all ctt_credit
WHERE ctl.customer_trx_line_id = p_line_rec.reference_customer_trx_line_id
AND ct.customer_trx_id = ctl.customer_trx_id
AND ctt.cust_trx_type_id = ct.cust_trx_type_id
AND ctt_credit.cust_trx_type_id = ctt.credit_memo_type_id
AND NVL(ctt.org_id, -3114) = NVL(ctl.org_id, -3114)
/* DECODE(ctt.cust_trx_type_id,
1, -3113,
2, -3113,
7, -3113,
8, -3113,
NVL(ctl.org_id, -3114)) Commented for the bug 3027150 */
AND NVL(ctt_credit.org_id, -3114) = NVL(ctl.org_id, -3114);
SELECT NVL(lt.cust_trx_type_id, 0)
INTO l_inv_cust_trx_type_id
FROM oe_line_types_v lt
WHERE lt.line_type_id = (SELECT line_type_id
FROM oe_order_lines_all /* MOAC SQL CHANGE */
WHERE line_id = p_line_rec.reference_line_id);
SELECT NVL(ot.cust_trx_type_id,0)
INTO l_inv_cust_trx_type_id
FROM oe_order_types_v ot,
oe_order_headers_all oh /* MOAC SQL CHANGE */
WHERE ot.order_type_id = oh.order_type_id
AND oh.header_id = (SELECT header_id
FROM oe_order_lines_all /* MOAC SQL CHANGE */
WHERE line_id = p_line_rec.reference_line_id);
/*SELECT NVL(lt.cust_trx_type_id,0)
INTO l_inv_cust_trx_type_id
FROM oe_line_types_v lt
WHERE lt.line_type_id = p_line_rec.line_type_id; */
SELECT NVL(ot.cust_trx_type_id, 0)
INTO l_inv_cust_trx_type_id
FROM oe_order_types_v ot,
oe_order_headers_all oh /* MOAC SQL CHANGE */
WHERE ot.order_type_id = oh.order_type_id
AND oh.header_id = p_line_rec.header_id;
SELECT NVL(ctt.credit_memo_type_id, 0),
ctt_credit.creation_sign
INTO x_credit_memo_type_id, x_credit_creation_sign
FROM ra_cust_trx_types_all ctt,
ra_cust_trx_types_all ctt_credit
WHERE ctt.cust_trx_type_id = l_inv_cust_trx_type_id
AND ctt_credit.cust_trx_type_id = ctt.credit_memo_type_id
AND NVL(ctt.org_id, -3114) = NVL(p_line_rec.org_id, -3114)
/* DECODE(ctt.cust_trx_type_id,
1, -3113,
2, -3113,
7, -3113,
8, -3113,
NVL(p_line_rec.org_id, -3114)) Commented for the bug 3027150 */
AND NVL(ctt_credit.org_id, -3114) = NVL(p_line_rec.org_id, -3114);
SELECT creation_sign
INTO l_creation_sign
FROM ra_cust_trx_types
WHERE cust_trx_type_id = p_Cust_Trx_Type_Id;
SELECT nvl(commitment_applied_amount, 0)
,nvl(commitment_interfaced_amount, 0)
INTO x_commitment_applied
,x_commitment_interfaced
FROM oe_payments
WHERE payment_trx_id = p_line_rec.commitment_id
AND payment_type_code = 'COMMITMENT'
AND line_id = p_line_rec.line_id;
SELECT nvl(commitment_applied_amount, 0)
,nvl(commitment_interfaced_amount, 0)
INTO x_commitment_applied
,x_commitment_interfaced
FROM oe_payments
WHERE payment_trx_id = p_line_rec.commitment_id
AND line_id = p_line_rec.line_id;
SELECT description
INTO x_item_description
FROM mtl_system_items_vl
WHERE inventory_item_id =p_line_rec.inventory_item_id
AND organization_id = l_organization_id;
/*SELECT nvl(citems.customer_item_desc, sitems.description)
INTO x_item_description
FROM mtl_customer_items citems
,mtl_customer_item_xrefs cxref
,mtl_system_items_vl sitems
WHERE citems.customer_item_id = cxref.customer_item_id
AND cxref.inventory_item_id = sitems.inventory_item_id
AND sitems.inventory_item_id = p_line_rec.inventory_item_id
AND sitems.organization_id = l_organization_id
AND citems.customer_item_id = p_line_rec.ordered_item_id
AND citems.customer_id = p_line_rec.sold_to_org_id; */
SELECT citems.customer_item_desc INTO l_item_description
FROM mtl_customer_items citems
WHERE citems.customer_item_id = p_line_rec.ordered_item_id
AND citems.customer_id = p_line_rec.sold_to_org_id;
SELECT sitems.description INTO l_item_description
FROM mtl_system_items_vl sitems
WHERE sitems.inventory_item_id = p_line_rec.inventory_item_id
and sitems.organization_id = l_organization_id ;
SELECT nvl(items.description, sitems.description)
INTO x_item_description
FROM mtl_cross_reference_types types
, mtl_cross_references items
, mtl_system_items_vl sitems
WHERE types.cross_reference_type = items.cross_reference_type
AND items.inventory_item_id = sitems.inventory_item_id
AND sitems.organization_id = l_organization_id
AND sitems.inventory_item_id = p_line_rec.inventory_item_id
AND items.cross_reference_type = p_line_rec.item_identifier_type
AND items.cross_reference = p_line_rec.ordered_item
AND ROWNUM = 1; -- Bug3333235
SELECT sitems.description
INTO x_item_description
FROM mtl_system_items_vl sitems
WHERE sitems.inventory_item_id = p_line_rec.inventory_item_id
and sitems.organization_id = l_organization_id ;
select inventory_item_id,ordered_item_id,sold_to_org_id,item_identifier_type,ordered_item,org_id
into l_inventory_item_id,l_ordered_item_id,l_sold_to_org_id,l_item_identifier_type,l_ordered_item,l_org_id
from OE_ORDER_LINES_ALL
where line_id = l_service_reference_line_id;
SELECT description
INTO l_item_serviced_desc
FROM mtl_system_items_vl
WHERE inventory_item_id =l_inventory_item_id
AND organization_id = l_organization_id;
/*SELECT nvl(citems.customer_item_desc, sitems.description)
INTO l_item_serviced_desc
FROM mtl_customer_items citems
,mtl_customer_item_xrefs cxref
,mtl_system_items_vl sitems
WHERE citems.customer_item_id = cxref.customer_item_id
AND cxref.inventory_item_id = sitems.inventory_item_id
AND sitems.inventory_item_id = l_inventory_item_id
AND sitems.organization_id = l_organization_id
AND citems.customer_item_id = l_ordered_item_id
AND citems.customer_id = l_sold_to_org_id; */
SELECT citems.customer_item_desc INTO l_item_serviced_desc
FROM mtl_customer_items citems
WHERE citems.customer_item_id = l_ordered_item_id
AND citems.customer_id = l_sold_to_org_id;
SELECT sitems.description INTO l_item_serviced_desc
FROM mtl_system_items_vl sitems
WHERE sitems.inventory_item_id = l_inventory_item_id
and sitems.organization_id = l_organization_id ;
SELECT nvl(items.description, sitems.description)
INTO l_item_serviced_desc
FROM mtl_cross_reference_types types
, mtl_cross_references items
, mtl_system_items_vl sitems
WHERE types.cross_reference_type = items.cross_reference_type
AND items.inventory_item_id = sitems.inventory_item_id
AND sitems.organization_id = l_organization_id
AND sitems.inventory_item_id = l_inventory_item_id
AND items.cross_reference_type = l_item_identifier_type
AND items.cross_reference = l_ordered_item
AND ROWNUM = 1; -- added for Bug 7583908
SELECT sitems.description
INTO l_item_serviced_desc
FROM mtl_system_items_vl sitems
WHERE sitems.inventory_item_id = l_inventory_item_id
and sitems.organization_id = l_organization_id ;
SELECT invoice_quantity_rule
INTO l_overship_invoice_basis
FROM hz_cust_site_uses
WHERE site_use_id = p_line_rec.ship_to_org_id
AND site_use_code = 'SHIP_TO';
SELECT invoice_quantity_rule
INTO l_overship_invoice_basis
FROM hz_cust_accounts
WHERE cust_account_id = p_line_rec.sold_to_org_id;
SELECT NVL(lt.non_delivery_invoice_source_id,
ot.non_delivery_invoice_source_id)
INTO l_invoice_source_id
FROM oe_line_types_v lt,
oe_order_types_v ot,
oe_order_headers_all oh /* MOAC SQL CHANGE */
WHERE lt.line_type_id = p_line_rec.line_type_id
AND ot.order_type_id = oh.order_type_id
AND oh.header_id = p_line_rec.header_id;
SELECT name
INTO l_invoice_source
FROM ra_batch_sources
WHERE batch_source_id = l_invoice_source_id;
SELECT NVL(lt.invoice_source_id,
ot.invoice_source_id)
INTO l_invoice_source_id
FROM oe_line_types_v lt,
oe_order_types_v ot,
oe_order_headers_all oh /* MOAC SQL CHANGE */
WHERE lt.line_type_id = p_line_rec.line_type_id
AND ot.order_type_id = oh.order_type_id
AND oh.header_id = p_line_rec.header_id;
SELECT name
INTO l_invoice_source
FROM ra_batch_sources
WHERE batch_source_id = l_invoice_source_id;
/*SELECT NVL(lt.cust_trx_type_id, 0)
INTO l_cust_trx_type_id
FROM oe_line_types_v lt
WHERE lt.line_type_id = p_line_rec.line_type_id; */
SELECT NVL(ot.cust_trx_type_id, 0)
INTO l_cust_trx_type_id
FROM oe_order_types_v ot,
oe_order_headers_all oh /* MOAC SQL CHANGE */
WHERE ot.order_type_id = oh.order_type_id
AND oh.header_id = p_line_rec.header_id;
SELECT NVL(oe_sys_parameters.value('OE_INVOICE_TRANSACTION_TYPE_ID',p_line_rec.org_id), 0) --moac
INTO l_cust_trx_type_id
FROM DUAL;
SELECT OE_SYS_PARAMETERS.Value('OE_CREDIT_TRANSACTION_TYPE_ID',p_line_rec.org_id) --moac
INTO l_cust_trx_type_id
FROM DUAL;
/*SELECT NVL(lt.cust_trx_type_id, 0)
INTO l_inv_cust_trx_type_id
FROM oe_line_types_v lt
WHERE lt.line_type_id = p_line_rec.line_type_id; */
SELECT NVL(ot.cust_trx_type_id,0)
INTO l_inv_cust_trx_type_id
FROM oe_order_types_v ot,
oe_order_headers_all oh /* MOAC SQL CHANGE */
WHERE ot.order_type_id = oh.order_type_id
AND oh.header_id = p_line_rec.header_id;
SELECT nvl(ctt.credit_memo_type_id, 0)
INTO l_cust_trx_type_id
FROM ra_cust_trx_types_all ctt
WHERE ctt.cust_trx_type_id = l_inv_cust_trx_type_id
AND NVL(ctt.org_id, -3114) = NVL(p_line_rec.org_id, -3114);
SELECT NVL(oe_sys_parameters.value('OE_CREDIT_TRANSACTION_TYPE_ID',p_line_rec.org_id), 0) --moac
INTO l_cust_trx_type_id
FROM DUAL;
SELECT NVL(lt.accounting_credit_method_code,ot.accounting_credit_method_code),--Bug 5699774
NVL(lt.invoicing_credit_method_code,ot.invoicing_credit_method_code) --Bug 5699774
INTO l_accting_credit_method_code,
l_invcing_credit_method_code
FROM oe_line_types_v lt,
oe_order_types_v ot,
oe_order_headers_all oh /* MOAC SQL CHANGE */
WHERE lt.line_type_id = p_line_rec.line_type_id
AND ot.order_type_id = oh.order_type_id
AND oh.header_id = p_line_rec.header_id;
SELECT item_type_code
INTO l_item_type_code
FROM OE_ORDER_LINES
WHERE line_id = p_line_id;
SELECT line_id
FROM oe_order_lines Line,
bom_inventory_components bic
WHERE Line.link_to_line_id = p_line_id
AND Line.open_flag || '' = 'Y'
AND bic.component_sequence_id = Line.component_sequence_id
AND bic.component_item_id = Line.inventory_item_id
AND bic.required_for_revenue = 1
ORDER BY Line.inventory_item_id;
SELECT line_id
FROM oe_order_lines Line,
bom_inventory_components bic
WHERE Line.link_to_line_id = p_link_to_line_id
AND Line.inventory_item_id <> p_line_inventory_item_id
AND Line.open_flag || '' = 'Y'
AND bic.component_sequence_id = Line.component_sequence_id
AND bic.component_item_id = Line.inventory_item_id
AND bic.required_for_revenue = 1
order by Line.inventory_item_id;
SELECT 'Y'
INTO l_rfr
FROM oe_order_lines Line,
bom_inventory_components bic
WHERE Line.line_id = p_line_id
AND Line.open_flag || '' = 'Y'
AND bic.component_sequence_id = Line.component_sequence_id
AND bic.component_item_id = Line.inventory_item_id
AND bic.required_for_revenue = 1;
select nvl(sum(ordered_quantity), 0), nvl(sum(fulfilled_quantity), 0)
into l_child_total_ordered_qty, l_child_total_fulfilled_qty
from oe_order_lines
where link_to_line_id = p_line_rec.line_id
and inventory_item_id = l_child_inventory_item_id;
Select Floor(nvl(l_child_total_fulfilled_qty, 0) * nvl(p_line_rec.ordered_quantity,0) / l_child_total_ordered_qty)
Into max_to_invoice
From dual;
select nvl(sum(ordered_quantity), 0), nvl(sum(fulfilled_quantity), 0)
into l_sibling_total_ordered_qty, l_sibling_total_fulfilled_qty
from oe_order_lines
where link_to_line_id = p_line_rec.link_to_line_id
and inventory_item_id = l_sibling_inventory_item_id;
select nvl(sum(ordered_quantity), 0), nvl(sum(invoiced_quantity), 0)
into l_total_ordered_qty, l_total_invoiced_qty
from oe_order_lines
where link_to_line_id = p_line_rec.link_to_line_id
and inventory_item_id = p_line_rec.inventory_item_id;
Select Floor(nvl(l_sibling_total_fulfilled_qty, 0) * l_total_ordered_qty / l_sibling_total_ordered_qty)
Into max_to_invoice
From dual;
select set_type into l_set_type from oe_sets where set_id = p_line_rec.line_set_id;
select sum(nvl(ordered_quantity,0)), sum(nvl(fulfilled_quantity,nvl(shipped_quantity,0))), sum(nvl(invoiced_quantity,0))
into l_unsplit_ordered_qty , l_unsplit_fulfilled_qty, l_unsplit_invoiced_qty
from oe_order_lines_all where header_id = p_line_rec.header_id and line_set_id = p_line_rec.line_set_id;
select nvl(sum(ordered_quantity), 0), nvl(sum(fulfilled_quantity), 0)
into l_child_total_ordered_qty, l_child_total_fulfilled_qty
from oe_order_lines
where link_to_line_id = p_line_rec.line_id
and inventory_item_id = l_child_inventory_item_id;
Select Floor(nvl(l_child_total_fulfilled_qty, 0) * nvl(p_line_rec.ordered_quantity, 0) / l_child_total_ordered_qty)
Into max_to_invoice
From dual;
SELECT NVL(SUM(ordered_quantity),0)
,NVL(SUM(fulfilled_quantity),0)
INTO l_sibling_total_ordered_qty
,l_sibling_total_fulfilled_qty
FROM oe_order_lines
WHERE link_to_line_id = p_line_rec.link_to_line_id
AND inventory_item_id = l_sibling_inventory_item_id;
SELECT NVL(SUM(ordered_quantity),0)
,NVL(SUM(invoiced_quantity),0)
,NVL(SUM(fulfilled_quantity),0)
INTO l_total_ordered_qty
,l_total_invoiced_qty
,l_total_fulfilled_qty
FROM oe_order_lines
WHERE link_to_line_id = p_line_rec.link_to_line_id
AND inventory_item_id = p_line_rec.inventory_item_id;
/* ELSIF -- will be updated with list of mandatory and conditionally required attributes
Mandatory Columns:
Batch_Source_Name
Set_Of_Books_Id
Line_Type
Description
Currency_Code
Conversion_Type
Optional columns:
Term_Id is required for non credit transactions
IF any thing is missing THEN
-- Issue error message here
RETURN FALSE;*/
SELECT SC.ATTRIBUTE1
, SC.ATTRIBUTE10
, SC.ATTRIBUTE11
, SC.ATTRIBUTE12
, SC.ATTRIBUTE13
, SC.ATTRIBUTE14
, SC.ATTRIBUTE15
, SC.ATTRIBUTE2
, SC.ATTRIBUTE3
, SC.ATTRIBUTE4
, SC.ATTRIBUTE5
, SC.ATTRIBUTE6
, SC.ATTRIBUTE7
, SC.ATTRIBUTE8
, SC.ATTRIBUTE9
, SC.CONTEXT
, SC.CREATED_BY
, SC.CREATION_DATE
, SC.DW_UPDATE_ADVICE_FLAG
, SC.HEADER_ID
, SC.LAST_UPDATED_BY
, SC.LAST_UPDATE_DATE
, SC.LAST_UPDATE_LOGIN
, SC.LINE_ID
, SC.PERCENT
, SC.SALESREP_ID
, SC.sales_credit_type_id
, SC.SALES_CREDIT_ID
, SC.WH_UPDATE_DATE
--SG{
, SC.SALES_GROUP_ID
--SG}
, SC.LOCK_CONTROL
FROM OE_SALES_CREDITS SC
, OE_SALES_CREDIT_TYPES SCT
WHERE SC.sales_credit_type_id = sct.sales_credit_type_id
AND SCT.quota_flag = p_quota_flag
AND SC.line_id = p_line_id;
x_line_scredit_tbl.DELETE;
l_Line_Scredit_rec.dw_update_advice_flag := l_implicit_rec.DW_UPDATE_ADVICE_FLAG;
l_Line_Scredit_rec.last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
l_Line_Scredit_rec.last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
l_Line_Scredit_rec.last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
l_Line_Scredit_rec.wh_update_date := l_implicit_rec.WH_UPDATE_DATE;
SELECT SC.ATTRIBUTE1
, SC.ATTRIBUTE10
, SC.ATTRIBUTE11
, SC.ATTRIBUTE12
, SC.ATTRIBUTE13
, SC.ATTRIBUTE14
, SC.ATTRIBUTE15
, SC.ATTRIBUTE2
, SC.ATTRIBUTE3
, SC.ATTRIBUTE4
, SC.ATTRIBUTE5
, SC.ATTRIBUTE6
, SC.ATTRIBUTE7
, SC.ATTRIBUTE8
, SC.ATTRIBUTE9
, SC.CONTEXT
, SC.CREATED_BY
, SC.CREATION_DATE
, SC.DW_UPDATE_ADVICE_FLAG
, SC.HEADER_ID
, SC.LAST_UPDATED_BY
, SC.LAST_UPDATE_DATE
, SC.LAST_UPDATE_LOGIN
, SC.LINE_ID
, SC.PERCENT
, SC.SALESREP_ID
, SC.sales_credit_type_id
, SC.SALES_CREDIT_ID
, SC.WH_UPDATE_DATE
--SG
, SC.Sales_Group_Id
--SG
, SC.LOCK_CONTROL
FROM OE_SALES_CREDITS SC
, OE_SALES_CREDIT_TYPES SCT
WHERE SC.sales_credit_type_id = sct.sales_credit_type_id
AND SCT.quota_flag = p_quota_flag
AND SC.header_id = p_header_id
AND SC.line_id IS NULL;
x_header_scredit_tbl.DELETE;
l_Header_Scredit_rec.dw_update_advice_flag := l_implicit_rec.DW_UPDATE_ADVICE_FLAG;
l_Header_Scredit_rec.last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
l_Header_Scredit_rec.last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
l_Header_Scredit_rec.last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
l_Header_Scredit_rec.wh_update_date := l_implicit_rec.WH_UPDATE_DATE;
PROCEDURE Insert_Line
( p_interface_line_rec IN RA_interface_Lines_Rec_Type
, x_return_status OUT NOCOPY VARCHAR2
) IS
update_sql_stmt VARCHAR2(32767);
update_sql_stmt1 VARCHAR2(32767);
oe_debug_pub.add( 'ENTERING INSERT_LINE ( ) PROCEDURE' , 1 ) ;
INSERT INTO RA_INTERFACE_LINES_ALL
(CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,INTERFACE_LINE_ATTRIBUTE1
,INTERFACE_LINE_ATTRIBUTE2
,INTERFACE_LINE_ATTRIBUTE3
,INTERFACE_LINE_ATTRIBUTE4
,INTERFACE_LINE_ATTRIBUTE5
,INTERFACE_LINE_ATTRIBUTE6
,INTERFACE_LINE_ATTRIBUTE7
,INTERFACE_LINE_ATTRIBUTE8
,INTERFACE_LINE_ATTRIBUTE9
,INTERFACE_LINE_ATTRIBUTE10
,INTERFACE_LINE_ATTRIBUTE11
,INTERFACE_LINE_ATTRIBUTE12
,INTERFACE_LINE_ATTRIBUTE13
,INTERFACE_LINE_ATTRIBUTE14
,INTERFACE_LINE_ATTRIBUTE15
,INTERFACE_LINE_ID
,INTERFACE_LINE_CONTEXT
,WAREHOUSE_ID
,BATCH_SOURCE_NAME
,SET_OF_BOOKS_ID
,LINE_TYPE
,DESCRIPTION
,CURRENCY_CODE
,AMOUNT
,CONVERSION_TYPE
,CONVERSION_DATE
,CONVERSION_RATE
,CUST_TRX_TYPE_NAME
,CUST_TRX_TYPE_ID
,TERM_NAME
,TERM_ID
,ORIG_SYSTEM_BILL_CUSTOMER_REF
,ORIG_SYSTEM_BILL_CUSTOMER_ID
,ORIG_SYSTEM_BILL_ADDRESS_REF
,ORIG_SYSTEM_BILL_ADDRESS_ID
,ORIG_SYSTEM_BILL_CONTACT_REF
,ORIG_SYSTEM_BILL_CONTACT_ID
,ORIG_SYSTEM_SHIP_CUSTOMER_REF
,ORIG_SYSTEM_SHIP_CUSTOMER_ID
,ORIG_SYSTEM_SHIP_ADDRESS_REF
,ORIG_SYSTEM_SHIP_ADDRESS_ID
,ORIG_SYSTEM_SHIP_CONTACT_REF
,ORIG_SYSTEM_SHIP_CONTACT_ID
,ORIG_SYSTEM_SOLD_CUSTOMER_REF
,ORIG_SYSTEM_SOLD_CUSTOMER_ID
,LINK_TO_LINE_ID
,LINK_TO_LINE_CONTEXT
,LINK_TO_LINE_ATTRIBUTE1
,LINK_TO_LINE_ATTRIBUTE2
,LINK_TO_LINE_ATTRIBUTE3
,LINK_TO_LINE_ATTRIBUTE4
,LINK_TO_LINE_ATTRIBUTE5
,LINK_TO_LINE_ATTRIBUTE6
,LINK_TO_LINE_ATTRIBUTE7
,LINK_TO_LINE_ATTRIBUTE8
,LINK_TO_LINE_ATTRIBUTE9
,LINK_TO_LINE_ATTRIBUTE10
,LINK_TO_LINE_ATTRIBUTE11
,LINK_TO_LINE_ATTRIBUTE12
,LINK_TO_LINE_ATTRIBUTE13
,LINK_TO_LINE_ATTRIBUTE14
,LINK_TO_LINE_ATTRIBUTE15
,RECEIPT_METHOD_NAME
,RECEIPT_METHOD_ID
-- ,CUSTOMER_BANK_ACCOUNT_ID -- R12 cc encryption
-- ,CUSTOMER_BANK_ACCOUNT_NAME
-- ,PAYMENT_SERVER_ORDER_NUM
-- ,APPROVAL_CODE
,CUSTOMER_TRX_ID
,TRX_DATE
,GL_DATE
,DOCUMENT_NUMBER
,DOCUMENT_NUMBER_SEQUENCE_ID
,TRX_NUMBER
,QUANTITY
,QUANTITY_ORDERED
,UNIT_SELLING_PRICE
,UNIT_STANDARD_PRICE
,UOM_CODE
,UOM_NAME
,PRINTING_OPTION
,INTERFACE_STATUS
,REQUEST_ID
,RELATED_BATCH_SOURCE_NAME
,RELATED_TRX_NUMBER
,RELATED_CUSTOMER_TRX_ID
,PREVIOUS_CUSTOMER_TRX_ID
,INITIAL_CUSTOMER_TRX_ID
,CREDIT_METHOD_FOR_ACCT_RULE
,CREDIT_METHOD_FOR_INSTALLMENTS
,REASON_CODE_MEANING
,REASON_CODE
,TAX_RATE
,TAX_CODE
,TAX_PRECEDENCE
,TAX_EXEMPT_FLAG
,TAX_EXEMPT_NUMBER
,TAX_EXEMPT_REASON_CODE
,EXCEPTION_ID
,EXEMPTION_ID
,SHIP_DATE_ACTUAL
,FOB_POINT
,SHIP_VIA
,WAYBILL_NUMBER
,INVOICING_RULE_NAME
,INVOICING_RULE_ID
,ACCOUNTING_RULE_NAME
,ACCOUNTING_RULE_ID
,ACCOUNTING_RULE_DURATION
,RULE_START_DATE
,RULE_END_DATE --bug5336618
,PRIMARY_SALESREP_NUMBER
,PRIMARY_SALESREP_ID
,SALES_ORDER
,SALES_ORDER_LINE
,SALES_ORDER_DATE
,SALES_ORDER_SOURCE
,SALES_ORDER_REVISION
,PURCHASE_ORDER
,PURCHASE_ORDER_REVISION
,PURCHASE_ORDER_DATE
,AGREEMENT_NAME
,AGREEMENT_ID
,MEMO_LINE_NAME
,MEMO_LINE_ID
,INVENTORY_ITEM_ID
,MTL_SYSTEM_ITEMS_SEG1
,MTL_SYSTEM_ITEMS_SEG2
,MTL_SYSTEM_ITEMS_SEG3
,MTL_SYSTEM_ITEMS_SEG4
,MTL_SYSTEM_ITEMS_SEG5
,MTL_SYSTEM_ITEMS_SEG6
,MTL_SYSTEM_ITEMS_SEG7
,MTL_SYSTEM_ITEMS_SEG8
,MTL_SYSTEM_ITEMS_SEG9
,MTL_SYSTEM_ITEMS_SEG10
,MTL_SYSTEM_ITEMS_SEG11
,MTL_SYSTEM_ITEMS_SEG12
,MTL_SYSTEM_ITEMS_SEG13
,MTL_SYSTEM_ITEMS_SEG14
,MTL_SYSTEM_ITEMS_SEG15
,MTL_SYSTEM_ITEMS_SEG16
,MTL_SYSTEM_ITEMS_SEG17
,MTL_SYSTEM_ITEMS_SEG18
,MTL_SYSTEM_ITEMS_SEG19
,MTL_SYSTEM_ITEMS_SEG20
,REFERENCE_LINE_ID
,REFERENCE_LINE_CONTEXT
,REFERENCE_LINE_ATTRIBUTE1
,REFERENCE_LINE_ATTRIBUTE2
,REFERENCE_LINE_ATTRIBUTE3
,REFERENCE_LINE_ATTRIBUTE4
,REFERENCE_LINE_ATTRIBUTE5
,REFERENCE_LINE_ATTRIBUTE6
,REFERENCE_LINE_ATTRIBUTE7
,REFERENCE_LINE_ATTRIBUTE8
,REFERENCE_LINE_ATTRIBUTE9
,REFERENCE_LINE_ATTRIBUTE10
,REFERENCE_LINE_ATTRIBUTE11
,REFERENCE_LINE_ATTRIBUTE12
,REFERENCE_LINE_ATTRIBUTE13
,REFERENCE_LINE_ATTRIBUTE14
,REFERENCE_LINE_ATTRIBUTE15
,TERRITORY_ID
,TERRITORY_SEGMENT1
,TERRITORY_SEGMENT2
,TERRITORY_SEGMENT3
,TERRITORY_SEGMENT4
,TERRITORY_SEGMENT5
,TERRITORY_SEGMENT6
,TERRITORY_SEGMENT7
,TERRITORY_SEGMENT8
,TERRITORY_SEGMENT9
,TERRITORY_SEGMENT10
,TERRITORY_SEGMENT11
,TERRITORY_SEGMENT12
,TERRITORY_SEGMENT13
,TERRITORY_SEGMENT14
,TERRITORY_SEGMENT15
,TERRITORY_SEGMENT16
,TERRITORY_SEGMENT17
,TERRITORY_SEGMENT18
,TERRITORY_SEGMENT19
,TERRITORY_SEGMENT20
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,HEADER_ATTRIBUTE_CATEGORY
,HEADER_ATTRIBUTE1
,HEADER_ATTRIBUTE2
,HEADER_ATTRIBUTE3
,HEADER_ATTRIBUTE4
,HEADER_ATTRIBUTE5
,HEADER_ATTRIBUTE6
,HEADER_ATTRIBUTE7
,HEADER_ATTRIBUTE8
,HEADER_ATTRIBUTE9
,HEADER_ATTRIBUTE10
,HEADER_ATTRIBUTE11
,HEADER_ATTRIBUTE12
,HEADER_ATTRIBUTE13
,HEADER_ATTRIBUTE14
,HEADER_ATTRIBUTE15
,COMMENTS
,INTERNAL_NOTES
,MOVEMENT_ID
,ORG_ID
,HEADER_GDF_ATTR_CATEGORY
,HEADER_GDF_ATTRIBUTE1
,HEADER_GDF_ATTRIBUTE2
,HEADER_GDF_ATTRIBUTE3
,HEADER_GDF_ATTRIBUTE4
,HEADER_GDF_ATTRIBUTE5
,HEADER_GDF_ATTRIBUTE6
,HEADER_GDF_ATTRIBUTE7
,HEADER_GDF_ATTRIBUTE8
,HEADER_GDF_ATTRIBUTE9
,HEADER_GDF_ATTRIBUTE10
,HEADER_GDF_ATTRIBUTE11
,HEADER_GDF_ATTRIBUTE12
,HEADER_GDF_ATTRIBUTE13
,HEADER_GDF_ATTRIBUTE14
,HEADER_GDF_ATTRIBUTE15
,HEADER_GDF_ATTRIBUTE16
,HEADER_GDF_ATTRIBUTE17
,HEADER_GDF_ATTRIBUTE18
,HEADER_GDF_ATTRIBUTE19
,HEADER_GDF_ATTRIBUTE20
,HEADER_GDF_ATTRIBUTE21
,HEADER_GDF_ATTRIBUTE22
,HEADER_GDF_ATTRIBUTE23
,HEADER_GDF_ATTRIBUTE24
,HEADER_GDF_ATTRIBUTE25
,HEADER_GDF_ATTRIBUTE26
,HEADER_GDF_ATTRIBUTE27
,HEADER_GDF_ATTRIBUTE28
,HEADER_GDF_ATTRIBUTE29
,HEADER_GDF_ATTRIBUTE30
,LINE_GDF_ATTR_CATEGORY
,LINE_GDF_ATTRIBUTE1
,LINE_GDF_ATTRIBUTE2
,LINE_GDF_ATTRIBUTE3
,LINE_GDF_ATTRIBUTE4
,LINE_GDF_ATTRIBUTE5
,LINE_GDF_ATTRIBUTE6
,LINE_GDF_ATTRIBUTE7
,LINE_GDF_ATTRIBUTE8
,LINE_GDF_ATTRIBUTE9
,LINE_GDF_ATTRIBUTE10
,LINE_GDF_ATTRIBUTE11
,LINE_GDF_ATTRIBUTE12
,LINE_GDF_ATTRIBUTE13
,LINE_GDF_ATTRIBUTE14
,LINE_GDF_ATTRIBUTE15
,LINE_GDF_ATTRIBUTE16
,LINE_GDF_ATTRIBUTE17
,LINE_GDF_ATTRIBUTE18
,LINE_GDF_ATTRIBUTE19
,LINE_GDF_ATTRIBUTE20
,TRANSLATED_DESCRIPTION
,PAYMENT_TRXN_EXTENSION_ID
,PARENT_LINE_ID
,DEFERRAL_EXCLUSION_FLAG
)
VALUES (
p_interface_line_rec.CREATED_BY
,p_interface_line_rec.CREATION_DATE
,p_interface_line_rec.LAST_UPDATED_BY
,p_interface_line_rec.LAST_UPDATE_DATE
,p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE1
,p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE2
,p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE3
,p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE4
,p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE5
,p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE6
,p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE7
,p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE8
,p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE9
,p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE10
,p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE11
,p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE12
,p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE13
,p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE14
,p_interface_line_rec.INTERFACE_LINE_ATTRIBUTE15
,p_interface_line_rec.INTERFACE_LINE_ID
,p_interface_line_rec.INTERFACE_LINE_CONTEXT
,p_interface_line_rec.WAREHOUSE_ID
,p_interface_line_rec.BATCH_SOURCE_NAME
,p_interface_line_rec.SET_OF_BOOKS_ID
,p_interface_line_rec.LINE_TYPE
,p_interface_line_rec.DESCRIPTION
,p_interface_line_rec.CURRENCY_CODE
,p_interface_line_rec.AMOUNT
,p_interface_line_rec.CONVERSION_TYPE
,p_interface_line_rec.CONVERSION_DATE
,p_interface_line_rec.CONVERSION_RATE
,p_interface_line_rec.CUST_TRX_TYPE_NAME
,p_interface_line_rec.CUST_TRX_TYPE_ID
,p_interface_line_rec.TERM_NAME
,p_interface_line_rec.TERM_ID
,p_interface_line_rec.ORIG_SYSTEM_BILL_CUSTOMER_REF
,p_interface_line_rec.ORIG_SYSTEM_BILL_CUSTOMER_ID
,p_interface_line_rec.ORIG_SYSTEM_BILL_ADDRESS_REF
,p_interface_line_rec.ORIG_SYSTEM_BILL_ADDRESS_ID
,p_interface_line_rec.ORIG_SYSTEM_BILL_CONTACT_REF
,p_interface_line_rec.ORIG_SYSTEM_BILL_CONTACT_ID
,p_interface_line_rec.ORIG_SYSTEM_SHIP_CUSTOMER_REF
,p_interface_line_rec.ORIG_SYSTEM_SHIP_CUSTOMER_ID
,p_interface_line_rec.ORIG_SYSTEM_SHIP_ADDRESS_REF
,p_interface_line_rec.ORIG_SYSTEM_SHIP_ADDRESS_ID
,p_interface_line_rec.ORIG_SYSTEM_SHIP_CONTACT_REF
,p_interface_line_rec.ORIG_SYSTEM_SHIP_CONTACT_ID
,p_interface_line_rec.ORIG_SYSTEM_SOLD_CUSTOMER_REF
,p_interface_line_rec.ORIG_SYSTEM_SOLD_CUSTOMER_ID
,p_interface_line_rec.LINK_TO_LINE_ID
,p_interface_line_rec.LINK_TO_LINE_CONTEXT
,p_interface_line_rec.LINK_TO_LINE_ATTRIBUTE1
,p_interface_line_rec.LINK_TO_LINE_ATTRIBUTE2
,p_interface_line_rec.LINK_TO_LINE_ATTRIBUTE3
,p_interface_line_rec.LINK_TO_LINE_ATTRIBUTE4
,p_interface_line_rec.LINK_TO_LINE_ATTRIBUTE5
,p_interface_line_rec.LINK_TO_LINE_ATTRIBUTE6
,p_interface_line_rec.LINK_TO_LINE_ATTRIBUTE7
,p_interface_line_rec.LINK_TO_LINE_ATTRIBUTE8
,p_interface_line_rec.LINK_TO_LINE_ATTRIBUTE9
,p_interface_line_rec.LINK_TO_LINE_ATTRIBUTE10
,p_interface_line_rec.LINK_TO_LINE_ATTRIBUTE11
,p_interface_line_rec.LINK_TO_LINE_ATTRIBUTE12
,p_interface_line_rec.LINK_TO_LINE_ATTRIBUTE13
,p_interface_line_rec.LINK_TO_LINE_ATTRIBUTE14
,p_interface_line_rec.LINK_TO_LINE_ATTRIBUTE15
,p_interface_line_rec.RECEIPT_METHOD_NAME
,p_interface_line_rec.RECEIPT_METHOD_ID
-- ,p_interface_line_rec.CUSTOMER_BANK_ACCOUNT_ID -- R12 cc encryption
-- ,p_interface_line_rec.CUSTOMER_BANK_ACCOUNT_NAME
-- ,p_interface_line_rec.PAYMENT_SERVER_ORDER_NUM
-- ,p_interface_line_rec.APPROVAL_CODE
,p_interface_line_rec.CUSTOMER_TRX_ID
,p_interface_line_rec.TRX_DATE
,p_interface_line_rec.GL_DATE
,p_interface_line_rec.DOCUMENT_NUMBER
,p_interface_line_rec.DOCUMENT_NUMBER_SEQUENCE_ID
,p_interface_line_rec.TRX_NUMBER
,p_interface_line_rec.QUANTITY
,p_interface_line_rec.QUANTITY_ORDERED
,p_interface_line_rec.UNIT_SELLING_PRICE
,p_interface_line_rec.UNIT_STANDARD_PRICE
,p_interface_line_rec.UOM_CODE
,p_interface_line_rec.UOM_NAME
,p_interface_line_rec.PRINTING_OPTION
,p_interface_line_rec.INTERFACE_STATUS
,p_interface_line_rec.REQUEST_ID
,p_interface_line_rec.RELATED_BATCH_SOURCE_NAME
,p_interface_line_rec.RELATED_TRX_NUMBER
,p_interface_line_rec.RELATED_CUSTOMER_TRX_ID
,p_interface_line_rec.PREVIOUS_CUSTOMER_TRX_ID
,p_interface_line_rec.INITIAL_CUSTOMER_TRX_ID
,p_interface_line_rec.CREDIT_METHOD_FOR_ACCT_RULE
,p_interface_line_rec.CREDIT_METHOD_FOR_INSTALLMENTS
,p_interface_line_rec.REASON_CODE_MEANING
,p_interface_line_rec.REASON_CODE
,p_interface_line_rec.TAX_RATE
,p_interface_line_rec.TAX_CODE
,p_interface_line_rec.TAX_PRECEDENCE
,p_interface_line_rec.TAX_EXEMPT_FLAG
,p_interface_line_rec.TAX_EXEMPT_NUMBER
,p_interface_line_rec.TAX_EXEMPT_REASON_CODE
,p_interface_line_rec.EXCEPTION_ID
,p_interface_line_rec.EXEMPTION_ID
,p_interface_line_rec.SHIP_DATE_ACTUAL
,p_interface_line_rec.FOB_POINT
,p_interface_line_rec.SHIP_VIA
,p_interface_line_rec.WAYBILL_NUMBER
,p_interface_line_rec.INVOICING_RULE_NAME
,p_interface_line_rec.INVOICING_RULE_ID
,p_interface_line_rec.ACCOUNTING_RULE_NAME
,p_interface_line_rec.ACCOUNTING_RULE_ID
,p_interface_line_rec.ACCOUNTING_RULE_DURATION
,p_interface_line_rec.RULE_START_DATE
,p_interface_line_rec.RULE_END_DATE --bug5336618
,p_interface_line_rec.PRIMARY_SALESREP_NUMBER
,p_interface_line_rec.PRIMARY_SALESREP_ID
,p_interface_line_rec.SALES_ORDER
,p_interface_line_rec.SALES_ORDER_LINE
,p_interface_line_rec.SALES_ORDER_DATE
,p_interface_line_rec.SALES_ORDER_SOURCE
,p_interface_line_rec.SALES_ORDER_REVISION
,p_interface_line_rec.PURCHASE_ORDER
,p_interface_line_rec.PURCHASE_ORDER_REVISION
,p_interface_line_rec.PURCHASE_ORDER_DATE
,p_interface_line_rec.AGREEMENT_NAME
,p_interface_line_rec.AGREEMENT_ID
,p_interface_line_rec.MEMO_LINE_NAME
,p_interface_line_rec.MEMO_LINE_ID
,p_interface_line_rec.INVENTORY_ITEM_ID
,p_interface_line_rec.MTL_SYSTEM_ITEMS_SEG1
,p_interface_line_rec.MTL_SYSTEM_ITEMS_SEG2
,p_interface_line_rec.MTL_SYSTEM_ITEMS_SEG3
,p_interface_line_rec.MTL_SYSTEM_ITEMS_SEG4
,p_interface_line_rec.MTL_SYSTEM_ITEMS_SEG5
,p_interface_line_rec.MTL_SYSTEM_ITEMS_SEG6
,p_interface_line_rec.MTL_SYSTEM_ITEMS_SEG7
,p_interface_line_rec.MTL_SYSTEM_ITEMS_SEG8
,p_interface_line_rec.MTL_SYSTEM_ITEMS_SEG9
,p_interface_line_rec.MTL_SYSTEM_ITEMS_SEG10
,p_interface_line_rec.MTL_SYSTEM_ITEMS_SEG11
,p_interface_line_rec.MTL_SYSTEM_ITEMS_SEG12
,p_interface_line_rec.MTL_SYSTEM_ITEMS_SEG13
,p_interface_line_rec.MTL_SYSTEM_ITEMS_SEG14
,p_interface_line_rec.MTL_SYSTEM_ITEMS_SEG15
,p_interface_line_rec.MTL_SYSTEM_ITEMS_SEG16
,p_interface_line_rec.MTL_SYSTEM_ITEMS_SEG17
,p_interface_line_rec.MTL_SYSTEM_ITEMS_SEG18
,p_interface_line_rec.MTL_SYSTEM_ITEMS_SEG19
,p_interface_line_rec.MTL_SYSTEM_ITEMS_SEG20
,p_interface_line_rec.REFERENCE_LINE_ID
,p_interface_line_rec.REFERENCE_LINE_CONTEXT
,p_interface_line_rec.REFERENCE_LINE_ATTRIBUTE1
,p_interface_line_rec.REFERENCE_LINE_ATTRIBUTE2
,p_interface_line_rec.REFERENCE_LINE_ATTRIBUTE3
,p_interface_line_rec.REFERENCE_LINE_ATTRIBUTE4
,p_interface_line_rec.REFERENCE_LINE_ATTRIBUTE5
,p_interface_line_rec.REFERENCE_LINE_ATTRIBUTE6
,p_interface_line_rec.REFERENCE_LINE_ATTRIBUTE7
,p_interface_line_rec.REFERENCE_LINE_ATTRIBUTE8
,p_interface_line_rec.REFERENCE_LINE_ATTRIBUTE9
,p_interface_line_rec.REFERENCE_LINE_ATTRIBUTE10
,p_interface_line_rec.REFERENCE_LINE_ATTRIBUTE11
,p_interface_line_rec.REFERENCE_LINE_ATTRIBUTE12
,p_interface_line_rec.REFERENCE_LINE_ATTRIBUTE13
,p_interface_line_rec.REFERENCE_LINE_ATTRIBUTE14
,p_interface_line_rec.REFERENCE_LINE_ATTRIBUTE15
,p_interface_line_rec.TERRITORY_ID
,p_interface_line_rec.TERRITORY_SEGMENT1
,p_interface_line_rec.TERRITORY_SEGMENT2
,p_interface_line_rec.TERRITORY_SEGMENT3
,p_interface_line_rec.TERRITORY_SEGMENT4
,p_interface_line_rec.TERRITORY_SEGMENT5
,p_interface_line_rec.TERRITORY_SEGMENT6
,p_interface_line_rec.TERRITORY_SEGMENT7
,p_interface_line_rec.TERRITORY_SEGMENT8
,p_interface_line_rec.TERRITORY_SEGMENT9
,p_interface_line_rec.TERRITORY_SEGMENT10
,p_interface_line_rec.TERRITORY_SEGMENT11
,p_interface_line_rec.TERRITORY_SEGMENT12
,p_interface_line_rec.TERRITORY_SEGMENT13
,p_interface_line_rec.TERRITORY_SEGMENT14
,p_interface_line_rec.TERRITORY_SEGMENT15
,p_interface_line_rec.TERRITORY_SEGMENT16
,p_interface_line_rec.TERRITORY_SEGMENT17
,p_interface_line_rec.TERRITORY_SEGMENT18
,p_interface_line_rec.TERRITORY_SEGMENT19
,p_interface_line_rec.TERRITORY_SEGMENT20
,p_interface_line_rec.ATTRIBUTE_CATEGORY
,p_interface_line_rec.ATTRIBUTE1
,p_interface_line_rec.ATTRIBUTE2
,p_interface_line_rec.ATTRIBUTE3
,p_interface_line_rec.ATTRIBUTE4
,p_interface_line_rec.ATTRIBUTE5
,p_interface_line_rec.ATTRIBUTE6
,p_interface_line_rec.ATTRIBUTE7
,p_interface_line_rec.ATTRIBUTE8
,p_interface_line_rec.ATTRIBUTE9
,p_interface_line_rec.ATTRIBUTE10
,p_interface_line_rec.ATTRIBUTE11
,p_interface_line_rec.ATTRIBUTE12
,p_interface_line_rec.ATTRIBUTE13
,p_interface_line_rec.ATTRIBUTE14
,p_interface_line_rec.ATTRIBUTE15
,p_interface_line_rec.HEADER_ATTRIBUTE_CATEGORY
,p_interface_line_rec.HEADER_ATTRIBUTE1
,p_interface_line_rec.HEADER_ATTRIBUTE2
,p_interface_line_rec.HEADER_ATTRIBUTE3
,p_interface_line_rec.HEADER_ATTRIBUTE4
,p_interface_line_rec.HEADER_ATTRIBUTE5
,p_interface_line_rec.HEADER_ATTRIBUTE6
,p_interface_line_rec.HEADER_ATTRIBUTE7
,p_interface_line_rec.HEADER_ATTRIBUTE8
,p_interface_line_rec.HEADER_ATTRIBUTE9
,p_interface_line_rec.HEADER_ATTRIBUTE10
,p_interface_line_rec.HEADER_ATTRIBUTE11
,p_interface_line_rec.HEADER_ATTRIBUTE12
,p_interface_line_rec.HEADER_ATTRIBUTE13
,p_interface_line_rec.HEADER_ATTRIBUTE14
,p_interface_line_rec.HEADER_ATTRIBUTE15
,p_interface_line_rec.COMMENTS
,p_interface_line_rec.INTERNAL_NOTES
,p_interface_line_rec.MOVEMENT_ID
,p_interface_line_rec.ORG_ID
,p_interface_line_rec.HEADER_GDF_ATTR_CATEGORY
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE1
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE2
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE3
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE4
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE5
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE6
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE7
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE8
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE9
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE10
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE11
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE12
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE13
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE14
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE15
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE16
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE17
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE18
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE19
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE20
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE21
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE22
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE23
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE24
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE25
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE26
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE27
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE28
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE29
,p_interface_line_rec.HEADER_GDF_ATTRIBUTE30
,p_interface_line_rec.LINE_GDF_ATTR_CATEGORY
,p_interface_line_rec.LINE_GDF_ATTRIBUTE1
,p_interface_line_rec.LINE_GDF_ATTRIBUTE2
,p_interface_line_rec.LINE_GDF_ATTRIBUTE3
,p_interface_line_rec.LINE_GDF_ATTRIBUTE4
,p_interface_line_rec.LINE_GDF_ATTRIBUTE5
,p_interface_line_rec.LINE_GDF_ATTRIBUTE6
,p_interface_line_rec.LINE_GDF_ATTRIBUTE7
,p_interface_line_rec.LINE_GDF_ATTRIBUTE8
,p_interface_line_rec.LINE_GDF_ATTRIBUTE9
,p_interface_line_rec.LINE_GDF_ATTRIBUTE10
,p_interface_line_rec.LINE_GDF_ATTRIBUTE11
,p_interface_line_rec.LINE_GDF_ATTRIBUTE12
,p_interface_line_rec.LINE_GDF_ATTRIBUTE13
,p_interface_line_rec.LINE_GDF_ATTRIBUTE14
,p_interface_line_rec.LINE_GDF_ATTRIBUTE15
,p_interface_line_rec.LINE_GDF_ATTRIBUTE16
,p_interface_line_rec.LINE_GDF_ATTRIBUTE17
,p_interface_line_rec.LINE_GDF_ATTRIBUTE18
,p_interface_line_rec.LINE_GDF_ATTRIBUTE19
,p_interface_line_rec.LINE_GDF_ATTRIBUTE20
,p_interface_line_rec.TRANSLATED_DESCRIPTION
,p_interface_line_rec.payment_trxn_extension_id
,p_interface_line_rec.PARENT_LINE_ID
,p_interface_line_rec.DEFERRAL_EXCLUSION_FLAG
) RETURNING rowid INTO l_rowid;
oe_debug_pub.add( 'INSERT COMPLETED' , 1 ) ;
oe_debug_pub.add( 'BUILD UPDATE STATEMENT WITH PROMISED COMMITMENT AMOUNT' , 1 ) ;
update_sql_stmt := 'UPDATE RA_INTERFACE_LINES_ALL
SET PROMISED_COMMITMENT_AMOUNT = :1
WHERE ROWID = :r1';
oe_debug_pub.add( 'EXECUTING UPDATE STMT FOR PROMISED_COMMITMENT_AMOUNT' , 1 ) ;
EXECUTE IMMEDIATE update_sql_stmt USING
p_interface_line_rec.promised_commitment_amount
,l_rowid;
oe_debug_pub.add( 'BUILD UPDATE STATEMENT WITH PAYMENT_SET_ID' , 1 ) ;
update_sql_stmt1 := 'UPDATE RA_INTERFACE_LINES_ALL
SET PAYMENT_SET_ID = :2
WHERE ROWID = :r2';
oe_debug_pub.add( 'EXECUTING UPDATE STMT FOR PAYMENT_SET_ID: '||P_INTERFACE_LINE_REC.PAYMENT_SET_ID , 1 ) ;
EXECUTE IMMEDIATE update_sql_stmt1 USING
p_interface_line_rec.PAYMENT_SET_ID
,l_rowid;
oe_debug_pub.add( 'EXIT INSERT_LINE ( ) PROCEDURE' , 1 ) ;
err_msg := 'Error while inserting to RA_INTERFACE_LINES_ALL :\n '|| SQLERRM;
, 'Insert_Line'
);
END Insert_Line;
x_interface_scredit_rec.LAST_UPDATE_DATE := INV_LE_TIMEZONE_PUB.Get_Le_Day_Time_For_Ou(sysdate,p_interface_line_rec.org_id);
x_interface_scredit_rec.LAST_UPDATE_DATE := sysdate;
x_interface_scredit_rec.LAST_UPDATED_BY := NVL(oe_standard_wf.g_user_id, fnd_global.user_id); -- 3169637
' value of LAST_UPDATE_DATE '|| x_interface_scredit_rec.LAST_UPDATE_DATE);
x_interface_scredit_rec.LAST_UPDATE_DATE := INV_LE_TIMEZONE_PUB.Get_Le_Day_Time_For_Ou(sysdate,p_interface_line_rec.org_id);
x_interface_scredit_rec.LAST_UPDATE_DATE := sysdate;
x_interface_scredit_rec.LAST_UPDATED_BY := NVL(oe_standard_wf.g_user_id, fnd_global.user_id); -- 3169637
' value of LAST_UPDATE_DATE '|| x_interface_scredit_rec.LAST_UPDATE_DATE);
Select SALES_CREDIT_TYPE_ID
into x_interface_scredit_rec.SALES_CREDIT_TYPE_ID
from ra_salesreps where salesrep_id=-3;
x_interface_scredit_rec.LAST_UPDATE_DATE := INV_LE_TIMEZONE_PUB.Get_Le_Day_Time_For_Ou(sysdate,p_interface_line_rec.org_id);
x_interface_scredit_rec.LAST_UPDATE_DATE := sysdate;
x_interface_scredit_rec.LAST_UPDATED_BY := NVL(oe_standard_wf.g_user_id, fnd_global.user_id); -- 3169637
' value of LAST_UPDATE_DATE '|| x_interface_scredit_rec.LAST_UPDATE_DATE);
x_ar_salescredit_rec.LAST_UPDATED_BY :=p_oe_salescredit_rec.LAST_UPDATED_BY ;
x_ar_salescredit_rec.LAST_UPDATE_DATE :=p_oe_salescredit_rec.LAST_UPDATE_DATE ;
PROCEDURE Insert_Salescredit
( p_salescredit_rec IN Ra_Interface_Scredits_Rec_Type
) IS
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
oe_debug_pub.add( 'INSERTING SALES CREDIT RECORDS ' , 5 ) ;
AR_InterfaceSalesCredits_GRP.Insert_Salescredit(
p_salescredit_rec=>l_ar_salescredit_rec,
x_return_status =>l_return_status,
x_msg_count =>l_msg_count,
x_msg_data =>l_msg_data);
INSERT INTO RA_INTERFACE_SALESCREDITS_ALL
(CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,INTERFACE_SALESCREDIT_ID
,INTERFACE_LINE_ID
,INTERFACE_LINE_CONTEXT
,INTERFACE_LINE_ATTRIBUTE1
,INTERFACE_LINE_ATTRIBUTE2
,INTERFACE_LINE_ATTRIBUTE3
,INTERFACE_LINE_ATTRIBUTE4
,INTERFACE_LINE_ATTRIBUTE5
,INTERFACE_LINE_ATTRIBUTE6
,INTERFACE_LINE_ATTRIBUTE7
,INTERFACE_LINE_ATTRIBUTE8
,INTERFACE_LINE_ATTRIBUTE9
,INTERFACE_LINE_ATTRIBUTE10
,INTERFACE_LINE_ATTRIBUTE11
,INTERFACE_LINE_ATTRIBUTE12
,INTERFACE_LINE_ATTRIBUTE13
,INTERFACE_LINE_ATTRIBUTE14
,INTERFACE_LINE_ATTRIBUTE15
,SALESREP_NUMBER
,SALESREP_ID
,SALES_CREDIT_TYPE_NAME
,SALES_CREDIT_TYPE_ID
,SALES_CREDIT_AMOUNT_SPLIT
,SALES_CREDIT_PERCENT_SPLIT
,INTERFACE_STATUS
,REQUEST_ID
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ORG_ID)
VALUES
(p_salescredit_rec.CREATED_BY
,p_salescredit_rec.CREATION_DATE
,p_salescredit_rec.LAST_UPDATED_BY
,p_salescredit_rec.LAST_UPDATE_DATE
,p_salescredit_rec.INTERFACE_SALESCREDIT_ID
,p_salescredit_rec.INTERFACE_LINE_ID
,p_salescredit_rec.INTERFACE_LINE_CONTEXT
,p_salescredit_rec.INTERFACE_LINE_ATTRIBUTE1
,p_salescredit_rec.INTERFACE_LINE_ATTRIBUTE2
,p_salescredit_rec.INTERFACE_LINE_ATTRIBUTE3
,p_salescredit_rec.INTERFACE_LINE_ATTRIBUTE4
,p_salescredit_rec.INTERFACE_LINE_ATTRIBUTE5
,p_salescredit_rec.INTERFACE_LINE_ATTRIBUTE6
,p_salescredit_rec.INTERFACE_LINE_ATTRIBUTE7
,p_salescredit_rec.INTERFACE_LINE_ATTRIBUTE8
,p_salescredit_rec.INTERFACE_LINE_ATTRIBUTE9
,p_salescredit_rec.INTERFACE_LINE_ATTRIBUTE10
,p_salescredit_rec.INTERFACE_LINE_ATTRIBUTE11
,p_salescredit_rec.INTERFACE_LINE_ATTRIBUTE12
,p_salescredit_rec.INTERFACE_LINE_ATTRIBUTE13
,p_salescredit_rec.INTERFACE_LINE_ATTRIBUTE14
,p_salescredit_rec.INTERFACE_LINE_ATTRIBUTE15
,p_salescredit_rec.SALESREP_NUMBER
,p_salescredit_rec.SALESREP_ID
,p_salescredit_rec.SALES_CREDIT_TYPE_NAME
,p_salescredit_rec.SALES_CREDIT_TYPE_ID
,p_salescredit_rec.SALES_CREDIT_AMOUNT_SPLIT
,p_salescredit_rec.SALES_CREDIT_PERCENT_SPLIT
,p_salescredit_rec.INTERFACE_STATUS
,p_salescredit_rec.REQUEST_ID
,p_salescredit_rec.ATTRIBUTE_CATEGORY
,p_salescredit_rec.ATTRIBUTE1
,p_salescredit_rec.ATTRIBUTE2
,p_salescredit_rec.ATTRIBUTE3
,p_salescredit_rec.ATTRIBUTE4
,p_salescredit_rec.ATTRIBUTE5
,p_salescredit_rec.ATTRIBUTE6
,p_salescredit_rec.ATTRIBUTE7
,p_salescredit_rec.ATTRIBUTE8
,p_salescredit_rec.ATTRIBUTE9
,p_salescredit_rec.ATTRIBUTE10
,p_salescredit_rec.ATTRIBUTE11
,p_salescredit_rec.ATTRIBUTE12
,p_salescredit_rec.ATTRIBUTE13
,p_salescredit_rec.ATTRIBUTE14
,p_salescredit_rec.ATTRIBUTE15
,p_salescredit_rec.ORG_ID);
oe_debug_pub.add( 'SUCCESSFULLY INSERTED SALES CREDIT RECORDS' , 5 ) ;
oe_debug_pub.add( 'UNABLE TO INSERT SALES CREDIT RECORDS -> '||SQLERRM , 1 ) ;
, 'Insert_Salescredit'
);
END Insert_Salescredit;
Insert_Header_Scredits_flag VARCHAR2(1);
oe_debug_pub.add( 'INSERTING QUOTA SALES CREDITS..' , 5 ) ;
oe_debug_pub.add( 'INSERTING NON-QUOTA SALES CREDITS..' , 5 ) ;
Insert_Salescredit(l_interface_scredit_rec);
oe_debug_pub.add( 'INSERTED LINE SALESCREDITS' , 1 ) ;
Insert_Header_Scredits_Flag := 'N';
Insert_Salescredit(l_interface_scredit_rec);
oe_debug_pub.add( 'INSERTED SERVICE PARENT LINE SALESCREDITS' , 1 ) ;
Insert_Header_Scredits_Flag := 'N';
SELECT top_model_line_id
INTO service_grand_parent_id
FROM oe_order_lines_all /*Bug3261460*/
WHERE line_id = l_service_reference_line_id;
Insert_Salescredit(l_interface_scredit_rec);
oe_debug_pub.add( 'INSERTED SERVICE GRAND PARENT LINE SALESCREDITS' , 1 ) ;
Insert_Header_Scredits_Flag := 'N';
Insert_Header_Scredits_Flag := 'Y';
Insert_Header_Scredits_Flag := 'Y';
Insert_Salescredit(l_interface_scredit_rec);
oe_debug_pub.add( 'INSERTED PARENT LINE SALESCREDITS' , 1 ) ;
Insert_Header_Scredits_Flag := 'N';
Insert_Header_Scredits_flag := 'Y';
Insert_Header_Scredits_flag := 'Y';
IF Insert_Header_Scredits_flag = 'Y' THEN
-- Prepare and insert header sales credits
Query_Header_Scredits(p_header_id => p_line_rec.header_id
, p_quota_flag => l_quota_flag
, x_header_scredit_tbl => l_header_scredit_tbl);
Insert_Salescredit(l_interface_scredit_rec);
oe_debug_pub.add( 'INSERTED HEADER SALESCREDITS' , 1 ) ;
Insert_Header_Scredits_Flag := 'N';
Insert_Salescredit(l_interface_scredit_rec);
Insert_Salescredit(l_interface_scredit_rec);
x_interface_conts_rec.LAST_UPDATE_DATE:= INV_LE_TIMEZONE_PUB.Get_Le_Day_Time_For_Ou(sysdate,p_interface_line_rec.org_id);
x_interface_conts_rec.LAST_UPDATED_BY := p_interface_line_rec.last_updated_by;
PROCEDURE Insert_Contingency
( p_contingency_rec IN Ra_Interface_Conts_Rec_Type
) IS
--
l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
oe_debug_pub.add( 'INSERTING CONTINGENCY RECORDS ' , 5 ) ;
INSERT INTO AR_INTERFACE_CONTS_ALL
(CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,INTERFACE_LINE_CONTEXT
,INTERFACE_LINE_ATTRIBUTE1
,INTERFACE_LINE_ATTRIBUTE2
,INTERFACE_LINE_ATTRIBUTE3
,INTERFACE_LINE_ATTRIBUTE4
,INTERFACE_LINE_ATTRIBUTE5
,INTERFACE_LINE_ATTRIBUTE6
,INTERFACE_LINE_ATTRIBUTE7
,INTERFACE_LINE_ATTRIBUTE8
,INTERFACE_LINE_ATTRIBUTE9
,INTERFACE_LINE_ATTRIBUTE10
,INTERFACE_LINE_ATTRIBUTE11
,INTERFACE_LINE_ATTRIBUTE12
,INTERFACE_LINE_ATTRIBUTE13
,INTERFACE_LINE_ATTRIBUTE14
,INTERFACE_LINE_ATTRIBUTE15
,INTERFACE_CONTINGENCY_ID
,CONTINGENCY_ID
,EXPIRATION_DAYS
,EXPIRATION_DATE
,COMPLETED_FLAG
,EXPIRATION_EVENT_DATE
,COMPLETED_BY
,ORG_ID)
VALUES
(p_contingency_rec.CREATED_BY
,p_contingency_rec.CREATION_DATE
,p_contingency_rec.LAST_UPDATED_BY
,p_contingency_rec.LAST_UPDATE_DATE
,p_contingency_rec.INTERFACE_LINE_CONTEXT
,p_contingency_rec.INTERFACE_LINE_ATTRIBUTE1
,p_contingency_rec.INTERFACE_LINE_ATTRIBUTE2
,p_contingency_rec.INTERFACE_LINE_ATTRIBUTE3
,p_contingency_rec.INTERFACE_LINE_ATTRIBUTE4
,p_contingency_rec.INTERFACE_LINE_ATTRIBUTE5
,p_contingency_rec.INTERFACE_LINE_ATTRIBUTE6
,p_contingency_rec.INTERFACE_LINE_ATTRIBUTE7
,p_contingency_rec.INTERFACE_LINE_ATTRIBUTE8
,p_contingency_rec.INTERFACE_LINE_ATTRIBUTE9
,p_contingency_rec.INTERFACE_LINE_ATTRIBUTE10
,p_contingency_rec.INTERFACE_LINE_ATTRIBUTE11
,p_contingency_rec.INTERFACE_LINE_ATTRIBUTE12
,p_contingency_rec.INTERFACE_LINE_ATTRIBUTE13
,p_contingency_rec.INTERFACE_LINE_ATTRIBUTE14
,p_contingency_rec.INTERFACE_LINE_ATTRIBUTE15
,p_contingency_rec.interface_contingency_id
, p_contingency_rec.contingency_id
, p_contingency_rec.expiration_days
, p_contingency_rec.EXPIRATION_DATE
, p_contingency_rec.COMPLETED_FLAG
, p_contingency_rec.EXPIRATION_EVENT_DATE
, p_contingency_rec.completed_by
,p_contingency_rec.ORG_ID);
oe_debug_pub.add( 'SUCCESSFULLY INSERTED CONTINGENCY RECORDS' , 5 ) ;
oe_debug_pub.add( 'UNABLE TO INSERT CONTINGENCY RECORDS -> '||SQLERRM , 1 ) ;
, 'Insert_Contingency'
);
END Insert_Contingency;
Insert_Contingency(l_interface_conts_rec);
select header_id, line_id
from oe_order_lines_all
where line_id = c_line_id;
SELECT dis.customer_trx_line_id
INTO l_reference_line_id
FROM RA_CUSTOMER_TRX_LINES_ALL DIS,
RA_CUSTOMER_TRX_LINES_ALL PAR
WHERE PAR.CUSTOMER_TRX_LINE_ID = L_DISCOUNTS_REC.REFERENCE_LINE_ID
AND PAR.CUSTOMER_TRX_ID = DIS.CUSTOMER_TRX_ID
AND DIS.INTERFACE_LINE_ATTRIBUTE6 = NVL(l_ref_line_id,DIS.INTERFACE_LINE_ATTRIBUTE6) --Bug2966839
AND DIS.INTERFACE_LINE_ATTRIBUTE11 =
(SELECT D.PRICE_ADJUSTMENT_ID
FROM OE_PRICE_ADJUSTMENTS D
WHERE D.HEADER_ID = l_ref_header_id
AND NVL(D.LINE_ID, l_ref_line_id) = l_ref_line_id
AND D.APPLIED_FLAG = 'Y' -- 3630426 Added for Retrobilling
AND D.LIST_LINE_ID =
(SELECT D2.LIST_LINE_ID
FROM OE_PRICE_ADJUSTMENTS D2
WHERE D2.PRICE_ADJUSTMENT_ID = L_DISCOUNTS_REC.INTERFACE_LINE_ATTRIBUTE11));
oe_debug_pub.add( 'FAILED TO INSERT DISCOUNT RECORDS FOR THE RETURN LINES '||SQLERRM , 1 ) ;
Insert_Line(l_discounts_rec
,x_return_status=>l_return_status);
PROCEDURE Update_Invoiced_Flag
( p_price_adjustment_id IN NUMBER
, p_adjusted_amount IN NUMBER
, p_invoiced_amount IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
) IS
l_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
oe_debug_pub.add( 'ENTERING UPDATE_INVOICED_FLAG' , 1 ) ;
UPDATE OE_PRICE_ADJUSTMENTS
SET INVOICED_FLAG = 'Y'
, INVOICED_AMOUNT = nvl(invoiced_amount,0) + p_invoiced_amount -- update with unrounded amount, for bug# 5400517
, LOCK_CONTROL = LOCK_CONTROL + 1
WHERE PRICE_ADJUSTMENT_ID = p_price_adjustment_id;
/*UPDATE OE_PRICE_ADJUSTMENTS
SET UPDATED_FLAG = 'Y'
WHERE PRICE_ADJUSTMENT_ID = p_price_adjustment_id
AND line_id IS NOT NULL;*/
UPDATE OE_PRICE_ADJUSTMENTS
SET INVOICED_FLAG = 'Y'
, UPDATED_FLAG = 'Y'
, LOCK_CONTROL = LOCK_CONTROL + 1
WHERE PRICE_ADJUSTMENT_ID = p_price_adjustment_id;
OE_ORDER_UTIL.Update_Global_Picture
(p_Upd_New_Rec_If_Exists =>FALSE
, p_hdr_adj_rec => l_Header_Adj_tbl(1)
, p_old_hdr_adj_rec => l_Old_Header_Adj_tbl(1)
, p_hdr_adj_id => l_Header_Adj_tbl(1).price_adjustment_id
, x_index => l_notify_index
, x_return_status => l_return_status);
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FOR HDR ADJ IS: ' || L_RETURN_STATUS ) ;
OE_ORDER_UTIL.g_header_adj_tbl(l_notify_index).last_update_date:=
l_header_adj_tbl(1).last_update_date;
oe_debug_pub.add( 'EXIT UPDATE_INVOICED_FLAG ( ) PROCEDURE' , 1 ) ;
, 'Update_Invoiced_flag'
);
END Update_Invoiced_Flag;
SELECT ooh.header_id
, ool.line_id
, ooh.order_number
, ott.name order_type
FROM oe_order_lines ool,
oe_order_headers_all ooh, /* MOAC SQL CHANGE */
oe_transaction_types_tl ott
WHERE ool.line_id = c_line_id
AND ooh.header_id = ool.header_id
AND ott.transaction_type_id = ooh.order_type_id
AND ott.language =
(select language_code
from fnd_languages
where installed_flag = 'B');
SELECT description
,primary_uom_code
INTO p_x_charges_rec.Description
,p_x_charges_rec.Uom_Code
FROM mtl_system_items
WHERE inventory_item_id = l_freight_item
AND organization_id = oe_sys_parameters.value('MASTER_ORGANIZATION_ID', p_line_rec.org_id);
SELECT meaning
,NULL
INTO p_x_charges_rec.Description
,p_x_charges_rec.Uom_Code
FROM ar_lookups
WHERE lookup_type = 'STD_LINE_TYPE'
AND lookup_code = 'FREIGHT';
SELECT FRE.customer_trx_line_id
INTO l_reference_line_id
FROM RA_CUSTOMER_TRX_LINES_ALL FRE
WHERE FRE.LINE_TYPE IN ('LINE', 'FREIGHT')
AND FRE.INTERFACE_LINE_CONTEXT = 'ORDER ENTRY'
AND FRE.INTERFACE_LINE_ATTRIBUTE1 = l_ref_order_number
AND FRE.INTERFACE_LINE_ATTRIBUTE2 = l_ref_order_type
AND FRE.INTERFACE_LINE_ATTRIBUTE6 =
(SELECT TO_CHAR(D.PRICE_ADJUSTMENT_ID)
FROM OE_PRICE_ADJUSTMENTS D
WHERE D.HEADER_ID = l_ref_header_id
AND NVL(D.LINE_ID, l_ref_line_id) = l_ref_line_id
AND D.LIST_LINE_ID =
(SELECT D2.LIST_LINE_ID
FROM OE_PRICE_ADJUSTMENTS D2
WHERE D2.PRICE_ADJUSTMENT_ID=p_x_charges_rec.INTERFACE_LINE_ATTRIBUTE6));
SELECT ra1.customer_trx_line_id
INTO l_reference_line_id
from ra_customer_trx_lines_all ra1, /* MOAC SQL CHANGE */
ra_customer_trx_lines ra2
where ra2.customer_trx_line_id = p_x_charges_rec.reference_line_id
and ra1.customer_trx_id = ra2.customer_trx_id
and ra1.line_type = 'FREIGHT'
and ra1.request_id is not null;
SELECT CHARGE_ID
,CHARGE_NAME
,CHARGE_AMOUNT
,nvl(INVOICED_AMOUNT,CHARGE_AMOUNT)
,CURRENCY_CODE
,CONTEXT
,substrb(ATTRIBUTE1, 1, 150)
,substrb(ATTRIBUTE2, 1, 150)
,substrb(ATTRIBUTE3, 1, 150)
,substrb(ATTRIBUTE4, 1, 150)
,substrb(ATTRIBUTE5, 1, 150)
,substrb(ATTRIBUTE6, 1, 150)
,substrb(ATTRIBUTE7, 1, 150)
,substrb(ATTRIBUTE8, 1, 150)
,substrb(ATTRIBUTE9, 1, 150)
,substrb(ATTRIBUTE10, 1, 150)
,substrb(ATTRIBUTE11, 1, 150)
,substrb(ATTRIBUTE12, 1, 150)
,substrb(ATTRIBUTE13, 1, 150)
,substrb(ATTRIBUTE14, 1, 150)
,substrb(ATTRIBUTE15, 1, 150)
FROM oe_charge_lines_v
WHERE header_id = p_header_id
AND line_id = p_line_id
AND nvl(invoiced_flag, 'N') = 'N';
SELECT CHARGE_ID
,CHARGE_NAME
,CHARGE_AMOUNT
,nvl(INVOICED_AMOUNT,CHARGE_AMOUNT)
,CURRENCY_CODE
,CONTEXT
,substrb(ATTRIBUTE1, 1, 150)
,substrb(ATTRIBUTE2, 1, 150)
,substrb(ATTRIBUTE3, 1, 150)
,substrb(ATTRIBUTE4, 1, 150)
,substrb(ATTRIBUTE5, 1, 150)
,substrb(ATTRIBUTE6, 1, 150)
,substrb(ATTRIBUTE7, 1, 150)
,substrb(ATTRIBUTE8, 1, 150)
,substrb(ATTRIBUTE9, 1, 150)
,substrb(ATTRIBUTE10, 1, 150)
,substrb(ATTRIBUTE11, 1, 150)
,substrb(ATTRIBUTE12, 1, 150)
,substrb(ATTRIBUTE13, 1, 150)
,substrb(ATTRIBUTE14, 1, 150)
,substrb(ATTRIBUTE15, 1, 150)
FROM oe_charge_lines_v
WHERE header_id = p_line_rec.header_id
AND line_id IS NULL
AND nvl(invoiced_flag, 'N') = 'N'
for update nowait; -- Bug #3686558
SELECT CHARGE_ID
,CHARGE_NAME
,(CHARGE_AMOUNT - INVOICED_AMOUNT)
,CHARGE_AMOUNT
,(CHARGE_AMOUNT - INVOICED_AMOUNT) -- should be diff amount which is not rounded, for bug 5400517
,CURRENCY_CODE
,CONTEXT
,substrb(ATTRIBUTE1, 1, 150)
,substrb(ATTRIBUTE2, 1, 150)
,substrb(ATTRIBUTE3, 1, 150)
,substrb(ATTRIBUTE4, 1, 150)
,substrb(ATTRIBUTE5, 1, 150)
,substrb(ATTRIBUTE6, 1, 150)
,substrb(ATTRIBUTE7, 1, 150)
,substrb(ATTRIBUTE8, 1, 150)
,substrb(ATTRIBUTE9, 1, 150)
,substrb(ATTRIBUTE10, 1, 150)
,substrb(ATTRIBUTE11, 1, 150)
,substrb(ATTRIBUTE12, 1, 150)
,substrb(ATTRIBUTE13, 1, 150)
,substrb(ATTRIBUTE14, 1, 150)
,substrb(ATTRIBUTE15, 1, 150)
FROM oe_charge_lines_v
WHERE header_id = p_header_id
AND line_id IS NULL
AND nvl(invoiced_flag, 'N') = 'Y'
AND invoiced_amount IS NOT NULL
AND invoiced_amount <> charge_amount
for update nowait; -- Bug #3686558
SELECT LINE_ID
FROM OE_ORDER_LINES
WHERE LINK_TO_LINE_ID = l_line_id
AND ITEM_TYPE_CODE = 'CONFIG';
Insert_Line(l_charges_rec
,x_return_status=>l_return_status);
Update_Invoiced_Flag(to_number(l_charges_rec.INTERFACE_LINE_ATTRIBUTE6)
,l_charges_rec.amount
,l_invoiced_amount
,l_return_status);
Insert_Line(l_charges_rec
,x_return_status=>l_return_status);
Update_Invoiced_Flag(to_number(l_charges_rec.INTERFACE_LINE_ATTRIBUTE6)
,l_charges_rec.amount
,l_invoiced_amount
,l_return_status);
SELECT payment_term_id INTO l_hdr_payment_term_id
FROM oe_order_headers_all
WHERE header_id = p_line_rec.header_id;
Insert_Line(l_charges_rec
,x_return_status=>l_return_status);
Update_Invoiced_Flag(to_number(l_charges_rec.INTERFACE_LINE_ATTRIBUTE6)
,l_charges_rec.amount
,l_invoiced_amount
,l_return_status);
SELECT nvl(max(interface_line_attribute5),0) --Bug 3338492
INTO l_count1
FROM ra_interface_lines_all
WHERE interface_line_context = 'ORDER ENTRY'
AND interface_line_attribute1 = l_charges_rec.INTERFACE_LINE_ATTRIBUTE1
AND interface_line_attribute2 = l_charges_rec.INTERFACE_LINE_ATTRIBUTE2
AND interface_line_attribute6 = l_charges_rec.INTERFACE_LINE_ATTRIBUTE6
AND interface_line_attribute11 = '0'
AND NVL(interface_status, '~') <> 'P';
SELECT nvl(max(interface_line_attribute5),0)
INTO l_count2
FROM ra_customer_trx_lines_all
WHERE interface_line_context = 'ORDER ENTRY'
AND interface_line_attribute1 = l_charges_rec.INTERFACE_LINE_ATTRIBUTE1
AND interface_line_attribute2 = l_charges_rec.INTERFACE_LINE_ATTRIBUTE2
AND interface_line_attribute6 = l_charges_rec.INTERFACE_LINE_ATTRIBUTE6
AND interface_line_attribute11 = '0';
Insert_Line(l_charges_rec
,x_return_status=>l_return_status);
Update_Invoiced_Flag(to_number(l_charges_rec.INTERFACE_LINE_ATTRIBUTE6)
, l_charges_rec.amount
,l_invoiced_amount
,l_return_status);
SELECT min(dl.delivery_id)
INTO l_delivery_line_id
FROM wsh_new_deliveries dl,
wsh_delivery_assignments da,
wsh_delivery_details dd
WHERE dd.delivery_detail_id = da.delivery_detail_id
AND da.delivery_id = dl.delivery_id
AND dd.source_code = 'OE'
AND dd.released_status = 'C' -- bug 6721251
AND dd.source_line_id = p_line_rec.line_id;
SELECT NVL(SUBSTR(dl.name, 1, 30), '0')
,NVL(SUBSTR(dl.waybill, 1, 30), '0')
,dl.ship_method_code
,dl.initial_pickup_date
INTO p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE3
,p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE4
,l_ship_method_code
,p_x_interface_line_rec.SHIP_DATE_ACTUAL
FROM wsh_new_deliveries dl
WHERE dl.delivery_id = l_delivery_line_id;
SELECT wdi.sequence_number
INTO p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE8
FROM wsh_delivery_legs wdl,
wsh_document_instances wdi
where wdi.entity_name = 'WSH_DELIVERY_LEGS'
and wdi.entity_id = wdl.delivery_leg_id
and wdl.delivery_id =l_delivery_line_id
and wdi.status <> 'CANCELLED'
and rownum=1;
SELECT substr(freight_code,1,25)
INTO p_x_interface_line_rec.SHIP_VIA
FROM wsh_carriers wsh_ca,wsh_carrier_services wsh,
wsh_org_carrier_services wsh_org
WHERE wsh_org.organization_id = p_line_rec.ship_from_org_id
AND wsh.carrier_service_id = wsh_org.carrier_service_id
AND wsh_ca.carrier_id = wsh.carrier_id
AND wsh.ship_method_code = l_ship_method_code
AND wsh_org.enabled_flag = 'Y';
SELECT substr(freight_code,1,25)
INTO p_x_interface_line_rec.SHIP_VIA
FROM wsh_carrier_ship_methods
WHERE ship_method_code = l_ship_method_code
AND organization_id = p_line_rec.ship_from_org_id;
SELECT min(dl.delivery_id)
INTO l_delivery_line_id
FROM wsh_new_deliveries dl,
wsh_delivery_assignments da,
wsh_delivery_details dd
WHERE dd.delivery_detail_id = da.delivery_detail_id
AND da.delivery_id = dl.delivery_id
AND dd.source_code = 'OE'
AND dd.released_status = 'C' -- bug 6721251
AND dd.top_model_line_id = p_line_rec.line_id;
SELECT NVL(SUBSTR(dl.name, 1, 30), '0')
,NVL(SUBSTR(dl.waybill, 1, 30), '0')
,dl.ship_method_code
,dl.initial_pickup_date
INTO p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE3
,p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE4
,l_ship_method_code
,p_x_interface_line_rec.SHIP_DATE_ACTUAL
FROM wsh_new_deliveries dl
WHERE dl.delivery_id = l_delivery_line_id;
SELECT substr(wdi.sequence_number,1,30)
INTO p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE8
FROM wsh_delivery_legs wdl,
wsh_document_instances wdi
where wdi.entity_name = 'WSH_DELIVERY_LEGS'
and wdi.entity_id = wdl.delivery_leg_id
and wdl.delivery_id =l_delivery_line_id
and wdi.status <> 'CANCELLED'
and rownum=1;
SELECT substr(freight_code,1,25)
INTO p_x_interface_line_rec.SHIP_VIA
FROM wsh_carriers wsh_ca,wsh_carrier_services wsh,
wsh_org_carrier_services wsh_org
WHERE wsh_org.organization_id = p_line_rec.ship_from_org_id
AND wsh.carrier_service_id = wsh_org.carrier_service_id
AND wsh_ca.carrier_id = wsh.carrier_id
AND wsh.ship_method_code = l_ship_method_code
AND wsh_org.enabled_flag = 'Y';
SELECT substr(freight_code,1,25)
INTO p_x_interface_line_rec.SHIP_VIA
FROM wsh_carrier_ship_methods
WHERE ship_method_code = l_ship_method_code
AND organization_id = p_line_rec.ship_from_org_id;
SELECT substr(freight_code,1,25)
INTO p_x_interface_line_rec.SHIP_VIA
FROM wsh_carriers wsh_ca,wsh_carrier_services wsh,
wsh_org_carrier_services wsh_org
WHERE wsh_org.organization_id =
p_line_rec.ship_from_org_id
AND wsh.carrier_service_id =
wsh_org.carrier_service_id
AND wsh_ca.carrier_id = wsh.carrier_id
AND wsh.ship_method_code = l_ship_method_code
AND wsh_org.enabled_flag = 'Y';
SELECT substr(freight_code,1,25)
INTO p_x_interface_line_rec.SHIP_VIA
FROM wsh_carrier_ship_methods
WHERE ship_method_code = l_ship_method_code
AND organization_id = p_line_rec.ship_from_org_id;
SELECT max(actual_shipment_date)
INTO l_max_actual_shipment_date
FROM oe_order_lines_all ool,
oe_line_sets ols
WHERE ool.shippable_flag = 'Y'
AND ool.line_id = ols.line_id
AND ols.set_id IN (SELECT os.set_id
FROM oe_line_sets ls,
oe_sets os
WHERE ls.line_id = p_line_rec.line_id
AND ls.set_id = os.set_id
AND os.set_type='FULFILLMENT_SET');
SELECT nvl ( max ( transaction_date ) , sysdate )
INTO p_x_interface_line_rec.ship_date_actual
FROM rcv_transactions t , oe_drop_ship_sources s
WHERE t.po_header_id = s.po_header_id
AND t.po_line_location_id = s.line_location_id
AND transaction_type = 'RECEIVE'
AND s.line_id = p_line_rec.line_id;
p_x_interface_line_rec.LAST_UPDATED_BY := NVL(oe_standard_wf.g_user_id, fnd_global.user_id); -- 3169637
p_x_interface_line_rec.LAST_UPDATE_DATE := sysdate;
SELECT tt.name
INTO p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE2
FROM oe_transaction_types_tl tt,
oe_order_headers oh
WHERE tt.language = ( select language_code
from fnd_languages
where installed_flag = 'B')
AND tt.transaction_type_id = oh.order_type_id
AND oh.header_id = p_line_rec.header_id;
SELECT count(*)
INTO l_count1
FROM ra_interface_lines_all
WHERE interface_line_context = 'ORDER ENTRY'
AND interface_line_attribute1 = p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE1
AND interface_line_attribute2 = p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE2
AND interface_line_attribute6= to_char(p_line_rec.line_id)
AND interface_line_attribute11 = '0'
AND NVL(interface_status, '~') <> 'P';
SELECT count(*)
INTO l_count2
FROM ra_customer_trx_lines_all
WHERE interface_line_context = 'ORDER ENTRY'
AND interface_line_attribute1 = p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE1
AND interface_line_attribute2 = p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE2
AND interface_line_attribute6= to_char(p_line_rec.line_id)
AND interface_line_attribute11 = '0'
AND sales_order = p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE1;
SELECT customer_item_number
INTO p_x_interface_line_rec.INTERFACE_LINE_ATTRIBUTE9
FROM mtl_customer_items citems
WHERE customer_item_id = p_line_rec.ordered_item_id;
SELECT NVL(cust_type.subsequent_trx_type_id,cust_type.cust_trx_type_id)
INTO p_x_interface_line_rec.Cust_Trx_Type_Id
FROM ra_cust_trx_types cust_type,ra_customer_trx_all cust_trx /* MOAC SQL CHANGE */
WHERE cust_type.cust_trx_type_id = cust_trx.cust_trx_type_id
AND cust_trx.customer_trx_id = p_line_rec.Commitment_Id;
SELECT payment_set_id
INTO p_x_interface_line_rec.Payment_Set_ID
FROM oe_payments
WHERE header_id = p_line_rec.header_id
AND payment_set_id IS NOT NULL
AND rownum=1;
SELECT payment_set_id
INTO p_x_interface_line_rec.Payment_Set_ID
FROM oe_payments
WHERE header_id = p_line_rec.header_id
AND line_id is null
AND payment_type_code = 'CREDIT_CARD';
SELECT nvl(sum(charge_amount), 0)
INTO l_partial_line_freight_amount
FROM oe_charge_lines_v
WHERE header_id = p_line_rec.header_id
AND line_id = p_line_rec.line_id
AND nvl(invoiced_flag, 'N') = 'N';
/* Commented for bug #3519137 added new select statement */
/*SELECT bill_to_org.customer_id
,bill_to_org.address_id
INTO p_x_interface_line_rec.ORIG_SYSTEM_BILL_CUSTOMER_ID
,p_x_interface_line_rec.ORIG_SYSTEM_BILL_ADDRESS_ID
FROM oe_invoice_to_orgs_v bill_to_org
WHERE bill_to_org.organization_id = p_line_rec.invoice_to_org_id;*/
SELECT acct_site.cust_account_id, site.cust_acct_site_id
INTO p_x_interface_line_rec.ORIG_SYSTEM_BILL_CUSTOMER_ID
,p_x_interface_line_rec.ORIG_SYSTEM_BILL_ADDRESS_ID
FROM hz_cust_acct_sites_all acct_site, hz_cust_site_uses_all site
WHERE SITE.SITE_USE_CODE = 'BILL_TO'
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND SITE.SITE_USE_ID = p_line_rec.invoice_to_org_id;
/* Commented for bug #3519137 added new select statement */
/*SELECT ship_to_org.customer_id
,ship_to_org.address_id
INTO p_x_interface_line_rec.ORIG_SYSTEM_SHIP_CUSTOMER_ID
,l_orig_sys_ship_addr_id
FROM oe_ship_to_orgs_v ship_to_org
WHERE ship_to_org.organization_id = p_line_rec.ship_to_org_id;*/
SELECT acct_site.cust_account_id, site.cust_acct_site_id
INTO p_x_interface_line_rec.ORIG_SYSTEM_SHIP_CUSTOMER_ID
,l_orig_sys_ship_addr_id
FROM hz_cust_acct_sites_all acct_site, hz_cust_site_uses_all site
WHERE SITE.SITE_USE_CODE = 'SHIP_TO'
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND SITE.SITE_USE_ID = p_line_rec.ship_to_org_id;
SELECT NUMBER_VALUE
INTO l_user_id
FROM WF_ITEM_ATTRIBUTE_VALUES
WHERE ITEM_KEY = to_char(p_line_rec.line_id)
AND ITEM_TYPE = 'OEOL'
AND NAME = 'USER_ID';
SELECT NUMBER_VALUE
INTO l_resp_id
FROM WF_ITEM_ATTRIBUTE_VALUES
WHERE ITEM_KEY = to_char(p_line_rec.line_id)
AND ITEM_TYPE = 'OEOL'
AND NAME = 'RESPONSIBILITY_ID';
SELECT NUMBER_VALUE
INTO l_appl_id
FROM WF_ITEM_ATTRIBUTE_VALUES
WHERE ITEM_KEY = to_char(p_line_rec.line_id)
AND ITEM_TYPE = 'OEOL'
AND NAME = 'APPLICATION_ID';
SELECT max ( transaction_date ) -- Bug#3343004
INTO l_rma_date
FROM rcv_transactions
WHERE transaction_type IN ('RECEIVE','UNORDERED')
AND oe_order_line_id = p_line_rec.line_id;
select ACCOUNTING_RULE_ID INTO L_ACCOUNTING_RULE_ID from RA_CUSTOMER_TRX_LINES_all
where CUSTOMER_TRX_LINE_ID= p_x_interface_line_rec.Reference_Line_Id;
SELECT customer_trx_line_id
INTO p_x_interface_line_rec.Reference_Line_Id
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = p_line_rec.Commitment_Id;
SELECT type,frequency
INTO l_accounting_rule_type, l_frequency
FROM ra_rules
WHERE rule_id = p_x_interface_line_rec.accounting_rule_id;
SELECT period_set_name
INTO l_calendar_name
FROM gl_sets_of_books
WHERE set_of_books_id = p_x_interface_line_rec.SET_OF_BOOKS_ID;
SELECT asp.default_territory
INTO l_territory_code
FROM ar_system_parameters asp
WHERE nvl(asp.org_id, -3114) = nvl(p_line_rec.org_id, -3114);
SELECT max(nvl(rst.territory_id, 0))
INTO p_x_interface_line_rec.territory_id
FROM ra_salesrep_territories rst
WHERE rst.salesrep_id = nvl(p_line_rec.salesrep_id, p_header_rec.salesrep_id)
AND sysdate between nvl(start_date_active, sysdate)
AND nvl(end_date_active, sysdate);
SELECT nvl(sub.territory_id,0)
INTO p_x_interface_line_rec.territory_id
FROM hz_cust_site_uses sub
WHERE sub.site_use_id = p_line_rec.invoice_to_org_id;
SELECT nvl(sus.territory_id,0)
INTO p_x_interface_line_rec.territory_id
FROM hz_cust_site_uses sus
WHERE sus.site_use_id = p_line_rec.ship_to_org_id;
SELECT terr.segment1
,terr.segment2
,terr.segment3
,terr.segment4
,terr.segment5
,terr.segment6
,terr.segment7
,terr.segment8
,terr.segment9
,terr.segment10
,terr.segment11
,terr.segment12
,terr.segment13
,terr.segment14
,terr.segment15
,terr.segment16
,terr.segment17
,terr.segment18
,terr.segment19
,terr.segment20
INTO p_x_interface_line_rec.TERRITORY_SEGMENT1
,p_x_interface_line_rec.TERRITORY_SEGMENT2
,p_x_interface_line_rec.TERRITORY_SEGMENT3
,p_x_interface_line_rec.TERRITORY_SEGMENT4
,p_x_interface_line_rec.TERRITORY_SEGMENT5
,p_x_interface_line_rec.TERRITORY_SEGMENT6
,p_x_interface_line_rec.TERRITORY_SEGMENT7
,p_x_interface_line_rec.TERRITORY_SEGMENT8
,p_x_interface_line_rec.TERRITORY_SEGMENT9
,p_x_interface_line_rec.TERRITORY_SEGMENT10
,p_x_interface_line_rec.TERRITORY_SEGMENT11
,p_x_interface_line_rec.TERRITORY_SEGMENT12
,p_x_interface_line_rec.TERRITORY_SEGMENT13
,p_x_interface_line_rec.TERRITORY_SEGMENT14
,p_x_interface_line_rec.TERRITORY_SEGMENT15
,p_x_interface_line_rec.TERRITORY_SEGMENT16
,p_x_interface_line_rec.TERRITORY_SEGMENT17
,p_x_interface_line_rec.TERRITORY_SEGMENT18
,p_x_interface_line_rec.TERRITORY_SEGMENT19
,p_x_interface_line_rec.TERRITORY_SEGMENT20
FROM ra_territories terr
WHERE terr.territory_id = p_x_interface_line_rec.territory_id;
Select top_model_line_id
into l_top_model_line_id
from oe_order_lines_all
where line_id = l_parent_line_id ;
/* Commented for bug #3519137 added new select statement */
/* SELECT customer_id
INTO l_hdr_inv_to_cust_id
FROM oe_invoice_to_orgs_v
WHERE organization_id = p_header_rec.invoice_to_org_id;*/
SELECT acct_site.cust_account_id
INTO l_hdr_inv_to_cust_id
FROM hz_cust_acct_sites_all acct_site, hz_cust_site_uses_all site
WHERE SITE.SITE_USE_CODE = 'BILL_TO'
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND SITE.SITE_USE_ID = p_header_rec.invoice_to_org_id;
SELECT payment_type_code
, payment_trx_id
, receipt_method_id
-- , tangible_id
-- , credit_card_holder_name
-- , credit_card_approval_code
, trxn_extension_id
INTO l_payment_type_code
, l_payment_trx_id
, l_receipt_method_id
-- , l_tangible_id
-- , l_credit_card_holder_name
-- , l_credit_card_approval_code
, l_payment_trxn_extension_id
FROM oe_payments
WHERE line_id = p_line_rec.line_id
AND header_id = p_line_rec.header_id
AND payment_type_code <> 'COMMITMENT'
AND payment_collection_event = 'INVOICE';
SELECT payment_type_code
, payment_trx_id
, receipt_method_id
-- , tangible_id
-- , credit_card_holder_name
-- , credit_card_approval_code
, trxn_extension_id
INTO l_payment_type_code
, l_payment_trx_id
, l_receipt_method_id
-- , l_tangible_id
-- , l_credit_card_holder_name
-- , l_credit_card_approval_code
, l_payment_trxn_extension_id
FROM oe_payments
WHERE header_id = p_line_rec.header_id
AND line_id is NULL
AND payment_collection_event = 'INVOICE';
SELECT NAME
INTO p_x_interface_line_rec.receipt_method_name
FROM AR_RECEIPT_METHODS
WHERE RECEIPT_METHOD_ID = l_receipt_method_id;
SELECT cust_receipt_method_id
INTO l_cust_pay_method_id
FROM ra_cust_receipt_methods rm
WHERE rm.customer_id = p_header_rec.sold_to_org_id
AND rm.SITE_USE_ID = NVL(p_header_rec.invoice_to_org_id, -1)
AND rm.receipt_method_id = NVL(l_receipt_method_id, rm.receipt_method_id)
AND l_trx_date BETWEEN rm.start_date AND NVL(rm.end_date, l_trx_date)
AND primary_flag = 'Y';
oe_debug_pub.add('Value of last_update_date '||p_x_interface_line_rec.last_update_date||
' Value of sales_order_date '||p_x_interface_line_rec.sales_order_date|| ' Value of trx_date '||p_x_interface_line_rec.trx_date);
IF p_x_interface_line_rec.last_update_date IS NOT NULL THEN
p_x_interface_line_rec.last_update_date := INV_LE_TIMEZONE_PUB.Get_Le_Day_Time_For_Ou(p_x_interface_line_rec.last_update_date,p_line_rec.org_id);
oe_debug_pub.add('Value of last_update_date '||p_x_interface_line_rec.last_update_date||
' Value of sales_order_date '||p_x_interface_line_rec.sales_order_date|| ' Value of trx_date '||p_x_interface_line_rec.trx_date);
PROCEDURE Update_Invoice_Attributes
( p_line_rec IN OE_Order_Pub.Line_Rec_Type
, p_interface_line_rec IN RA_interface_Lines_Rec_Type
, p_invoice_interface_status IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
) IS
l_invoiced_quantity NUMBER;
UPDATE OE_ORDER_LINES_ALL
SET INVOICE_INTERFACE_STATUS_CODE = p_invoice_interface_status,
INVOICED_QUANTITY = l_invoiced_quantity,
FLOW_STATUS_CODE = l_flow_status_code,
SERVICE_START_DATE = p_line_rec.SERVICE_START_DATE, --bug# 7231974 :- as srvc_dates are derived in OE_INVOICE_PUB, they should be updated in OM tables as well
SERVICE_END_DATE = p_line_rec.SERVICE_END_DATE, --bug # 7231974
CALCULATE_PRICE_FLAG = 'N',
LOCK_CONTROL = LOCK_CONTROL + 1
WHERE LINE_ID = p_line_rec.line_id;
OE_ORDER_UTIL.Update_Global_Picture
(p_Upd_New_Rec_If_Exists =>FALSE
, p_line_rec => l_line_tbl(1)
, p_old_line_rec => l_old_line_tbl(1)
, p_line_id => l_line_tbl(1).line_id
, x_index => l_notify_index
, x_return_status => l_return_status);
oe_debug_pub.add( 'UPDATE_GLOBAL RET_STATUS FOR LINE_ID '||L_LINE_TBL ( 1 ) .LINE_ID ||' IS: ' || L_RETURN_STATUS , 1 ) ;
oe_debug_pub.add( 'UPDATE_GLOBAL INDEX FOR LINE_ID '||L_LINE_TBL ( 1 ) .LINE_ID ||' IS: ' || L_NOTIFY_INDEX , 1 ) ;
OE_ORDER_UTIL.g_line_tbl(l_notify_index).last_update_date:=
l_line_tbl(1).last_update_date;
UPDATE oe_payments
SET commitment_interfaced_amount = nvl(commitment_interfaced_amount, 0) + nvl(p_interface_line_rec.promised_commitment_amount, 0)
WHERE LINE_ID = p_line_rec.line_id
AND PAYMENT_TRX_ID = p_line_rec.commitment_id;
err_msg := 'Error in Update_Invoice_Attributes:\n '||SQLERRM;
, 'Update_Invoice_Attributes'
);
END Update_Invoice_Attributes;
SELECT header_id
INTO l_header_id
FROM oe_order_lines
WHERE line_id = p_line_id;
FUNCTION Update_Invoice_Numbers
( p_del_id IN NUMBER
, p_del_name IN VARCHAR2
)RETURN NUMBER IS
inv_num_index NUMBER;
oe_debug_pub.add( 'ENTER UPDATE_INVOICE_NUMBERS ( ) PROCEDURE ' , 5 ) ;
oe_debug_pub.add( 'UPDATE INVOICE NUMBERS: DEL_ID ' || TO_CHAR ( P_DEL_ID ) ||' DEL NAME: ' || P_DEL_NAME , 5 ) ;
SELECT nvl((max(index_number)+1), 0)
INTO inv_num_index
FROM oe_invoice_numbers
WHERE delivery_id = p_del_id;
INSERT INTO OE_INVOICE_NUMBERS(
INVOICE_NUMBER_ID
, DELIVERY_ID
, INDEX_NUMBER
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY)
VALUES
( oe_invoice_numbers_s.nextval
, p_del_id
, inv_num_index
, SYSDATE
, NVL(oe_standard_wf.g_user_id, fnd_global.user_id) -- 3169637
, SYSDATE
, NVL(oe_standard_wf.g_user_id, fnd_global.user_id)); -- 3169637
oe_debug_pub.add( 'DONE UPDATE_INVOICE_NUMBERS W/SUCCESS' , 5 ) ;
err_msg := 'Error in update_invoice_numbers:\n '||SQLERRM;
oe_debug_pub.add( 'WHEN OTHERS :EXITING UPDATE_INVOICE_NUMBERS' , 1 ) ;
END Update_Invoice_Numbers;
PROCEDURE Update_Numbers
( p_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
) IS
l_count NUMBER := 0;
select_col varchar2(10000) := '';
SELECT upper(c.from_column_name), c.from_column_length
FROM ra_group_by_columns c
WHERE c.column_type = 'M';
SELECT delivery_id
FROM wsh_new_deliveries
WHERE name = p_del_name;
oe_debug_pub.add( 'ENTER UPDATE_NUMBERS ( ) PROCEDURE' , 5 ) ;
SELECT count(*)
INTO l_count
FROM ra_interface_lines_all
WHERE request_id = -1 * p_id;
select_col := col_name;
select_col := col_name;
select_col := select_col || '||' || '''~'''|| '||'|| col_name;
grp_stmt := 'Select ' || select_col || ' group_cols,' ||
' l.interface_line_attribute3, ROWID ' ||
' From RA_INTERFACE_LINES_ALL L' ||
' Where trx_number is NULL' ||
' And request_id = :p' ||
' Order by ' || group_col_clause ||
' , l.interface_line_attribute3, l.org_id' ;
oe_debug_pub.add( 'CONCAT COLS CHANGED , CALLING UPDATE_INVOICE' ) ;
IF ( Update_Invoice_Numbers ( this_del_id, this_del_name) < 0 ) THEN
x_return_status := FND_API.G_RET_STS_SUCCESS;
oe_debug_pub.add( 'DEL NAME CHANGED , CALLING UPDATE_INVOICE' ) ;
IF ( update_invoice_numbers ( this_del_id, this_del_name) < 0 ) THEN
x_return_status := FND_API.G_RET_STS_SUCCESS;
Update RA_INTERFACE_LINES_ALL
set trx_number = substr(inv_num,1,20) -- substr(inv_num,20)--inv_num --bug#7592350 -- Bug 8216166
where rowid = chartorowid(this_rowid);
update ra_interface_lines_all
set request_id=null
where request_id = -1 * p_id;
oe_debug_pub.add( 'EXITING UPDATE_NUMBERS ( ) PROCEDURE' , 5 ) ;
err_msg := 'Error in update_numbers:\n '|| SQLERRM;
oe_debug_pub.add( 'EXITING UPDATE_NUMBERS' , 1 ) ;
oe_debug_pub.add( 'EXITING UPDATE_NUMBERS' , 1 ) ;
END Update_Numbers;
SELECT Line.line_id --SQL# 16487863 Added the UNION clause to avoid FTS
FROM oe_order_lines Line
WHERE (Line.link_to_line_id = p_line_rec.link_to_line_id)
AND invoice_interface_status_code = 'RFR-PENDING'
UNION
SELECT Line.line_id
FROM oe_order_lines Line
WHERE (Line.line_id = p_line_rec.link_to_line_id)
AND invoice_interface_status_code = 'RFR-PENDING';
SELECT count(*)
INTO l_freight_count
FROM oe_price_adjustments p
WHERE p.header_id = p_line_rec.header_id
AND (p.line_id IS NULL OR p.line_id = p_line_rec.line_id)
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND p.applied_flag = 'Y'
AND NVL(p.invoiced_flag, 'N') = 'N';
SELECT count(*)
INTO l_freight_count
FROM oe_price_adjustments p
WHERE p.header_id = p_line_rec.header_id
AND p.line_id IS NULL
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND p.applied_flag = 'Y'
AND (NVL(p.invoiced_flag, 'N') = 'N' OR (NVL(p.invoiced_flag, 'N') = 'Y' AND p.adjusted_amount <> nvl(p.invoiced_amount, p.adjusted_amount)));
SELECT count(*)
INTO l_freight_count
FROM oe_price_adjustments p
WHERE p.header_id = p_line_rec.header_id
AND p.line_id = p_line_rec.line_id
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND p.applied_flag = 'Y'
AND NVL(p.invoiced_flag, 'N') = 'N';
Update_Invoice_Attributes(p_line_rec
,p_x_interface_line_rec
,'NOT_ELIGIBLE'
,x_return_status);
SELECT count(*)
INTO l_freight_count
FROM oe_price_adjustments p
WHERE p.header_id = p_line_rec.header_id
AND (p.line_id IS NULL OR p.line_id = p_line_rec.line_id)
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND p.applied_flag = 'Y'
AND NVL(p.invoiced_flag, 'N') = 'N';
SELECT count(*)
INTO l_freight_count
FROM oe_price_adjustments p
WHERE p.header_id = p_line_rec.header_id
AND p.line_id IS NULL
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND p.applied_flag = 'Y'
AND (NVL(p.invoiced_flag, 'N') = 'N' OR (NVL(p.invoiced_flag, 'N') = 'Y' AND p.adjusted_amount <> nvl(p.invoiced_amount, p.adjusted_amount)));
SELECT count(*)
INTO l_freight_count
FROM oe_price_adjustments p
WHERE p.header_id = p_line_rec.header_id
AND p.line_id = p_line_rec.line_id
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND p.applied_flag = 'Y'
AND NVL(p.invoiced_flag, 'N') = 'N';
Update_Invoice_Attributes(p_line_rec
,p_x_interface_line_rec
,'RFR-PENDING'
,x_return_status);
oe_debug_pub.add( 'REQUEST_ID AFTER INSERTING '||P_X_INTERFACE_LINE_REC.REQUEST_ID , 5 ) ;
oe_debug_pub.add( 'INSERTING LINE INTO RA_INTERFACE_LINES' , 5 ) ;
Insert_Line(p_x_interface_line_rec
,x_return_status=>l_return_status);
oe_debug_pub.add( 'INSERTING DISCOUNT DETAILS' , 5 ) ;
Update_Invoice_Attributes(p_line_rec
,p_x_interface_line_rec
,'RFR-PENDING'
,x_return_status);
Update_Invoice_Attributes(p_line_rec
,p_x_interface_line_rec
,'YES'
,x_return_status);
oe_debug_pub.add( 'REQUEST_ID AFTER INSERTING '||P_X_INTERFACE_LINE_REC.REQUEST_ID ) ;
SELECT distinct dd.source_line_id
FROM wsh_new_deliveries dl,
wsh_delivery_assignments da,
wsh_delivery_details dd
WHERE dd.delivery_detail_id = da.delivery_detail_id
AND da.delivery_id = dl.delivery_id
AND dd.source_code = 'OE'
AND dl.delivery_id = p_delivery_id
AND dd.source_line_id is not null
UNION ALL
SELECT distinct dd.top_model_line_id
FROM wsh_new_deliveries dl,
wsh_delivery_assignments da,
wsh_delivery_details dd
WHERE dd.delivery_detail_id = da.delivery_detail_id
AND da.delivery_id = dl.delivery_id
AND dd.source_code = 'OE'
AND dl.delivery_id = p_delivery_id
AND dd.top_model_line_id is not null;
SELECT 'x'
FROM wsh_new_deliveries dl,
wsh_delivery_assignments da,
wsh_delivery_details dd
WHERE dd.delivery_detail_id = da.delivery_detail_id
AND da.delivery_id = dl.delivery_id
AND dd.source_code = 'OE'
AND dl.delivery_id = p_delivery_id
AND (dd.source_line_id is not null OR
dd.top_model_line_id is not null)
for update of dd.source_line_id nowait;
SELECT Line.line_id
FROM oe_order_lines Line
WHERE (Line.link_to_line_id = l_line_rec.link_to_line_id
OR Line.line_id = l_line_rec.link_to_line_id)
AND invoice_interface_status_code = 'RFR-PENDING';
select line_id, lock_control into v_line_id, v_lock_control
from oe_order_lines_all where line_id = l_line_rec.line_id
FOR UPDATE NOWAIT;
Update_Service_Dates(l_line_Rec);
Update_Invoice_Attributes(l_line_rec ,l_interface_line_rec ,'ACCEPTANCE-PENDING' ,x_return_status);
SELECT count(*)
INTO l_freight_count
FROM oe_price_adjustments p
WHERE p.header_id = l_line_rec.header_id
AND (p.line_id IS NULL OR p.line_id = l_line_rec.line_id)
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND p.applied_flag = 'Y'
AND NVL(p.invoiced_flag, 'N') = 'N';
SELECT count(*)
INTO l_freight_count
FROM oe_price_adjustments p
WHERE p.header_id = l_line_rec.header_id
AND p.line_id IS NULL
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND p.applied_flag = 'Y'
AND (NVL(p.invoiced_flag, 'N') = 'N' OR (NVL(p.invoiced_flag, 'N') = 'Y' AND p.adjusted_amount <> nvl(p.invoiced_amount, p.adjusted_amount)));
SELECT count(*)
INTO l_freight_count
FROM oe_price_adjustments p
WHERE p.header_id = l_line_rec.header_id
AND p.line_id = l_line_rec.line_id
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND p.applied_flag = 'Y'
AND NVL(p.invoiced_flag, 'N') = 'N';
Update_Invoice_Attributes(l_line_rec
,l_interface_line_rec
,'NOT_ELIGIBLE'
,x_return_status);
SELECT count(*)
INTO l_freight_count
FROM oe_price_adjustments p
WHERE p.header_id = l_line_rec.header_id
AND (p.line_id IS NULL OR p.line_id = l_line_rec.line_id)
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND p.applied_flag = 'Y'
AND NVL(p.invoiced_flag, 'N') = 'N';
SELECT count(*)
INTO l_freight_count
FROM oe_price_adjustments p
WHERE p.header_id = l_line_rec.header_id
AND p.line_id IS NULL
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND p.applied_flag = 'Y'
AND (NVL(p.invoiced_flag, 'N') = 'N' OR (NVL(p.invoiced_flag, 'N') = 'Y' AND p.adjusted_amount <> nvl(p.invoiced_amount, p.adjusted_amount)));
SELECT count(*)
INTO l_freight_count
FROM oe_price_adjustments p
WHERE p.header_id = l_line_rec.header_id
AND p.line_id = l_line_rec.line_id
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND p.applied_flag = 'Y'
AND NVL(p.invoiced_flag, 'N') = 'N';
SELECT min(dl.delivery_id)
INTO l_delivery_line_id
FROM wsh_new_deliveries dl,
wsh_delivery_assignments da,
wsh_delivery_details dd
WHERE dd.delivery_detail_id = da.delivery_detail_id
AND da.delivery_id = dl.delivery_id
AND dd.source_code = 'OE'
AND dd.released_status = 'C' -- bug 6721251
AND dd.source_line_id = l_line_rec.line_id;
SELECT min(dl.delivery_id)
INTO l_delivery_line_id
FROM wsh_new_deliveries dl,
wsh_delivery_assignments da,
wsh_delivery_details dd
WHERE dd.delivery_detail_id = da.delivery_detail_id
AND da.delivery_id = dl.delivery_id
AND dd.source_code = 'OE'
AND dd.released_status = 'C' -- bug 6721251
AND dd.top_model_line_id = l_line_rec.line_id;
SELECT invoice_interface_status_code, open_flag
INTO l_interface_status_code, l_open_flag
FROM oe_order_lines
WHERE line_id = l_line_id;
SELECT min(dl.delivery_id)
INTO l_line_delivery_id
FROM wsh_new_deliveries dl,
wsh_delivery_assignments da,
wsh_delivery_details dd
WHERE dd.delivery_detail_id = da.delivery_detail_id
AND da.delivery_id = dl.delivery_id
AND dd.source_code = 'OE'
AND dd.released_status = 'C' -- bug 6721251
AND dd.source_line_id = l_line_id;
SELECT min(dl.delivery_id)
INTO l_line_delivery_id
FROM wsh_new_deliveries dl,
wsh_delivery_assignments da,
wsh_delivery_details dd
WHERE dd.delivery_detail_id = da.delivery_detail_id
AND da.delivery_id = dl.delivery_id
AND dd.source_code = 'OE'
AND dd.released_status = 'C' -- bug 6721251
AND dd.top_model_line_id = l_line_id;
SELECT min(dl.delivery_id)
INTO l_line_delivery_id
FROM wsh_new_deliveries dl,
wsh_delivery_assignments da,
wsh_delivery_details dd
WHERE dd.delivery_detail_id = da.delivery_detail_id
AND da.delivery_id = dl.delivery_id
AND dd.source_code = 'OE'
AND dd.released_status = 'C' -- bug 6721251
AND dd.top_model_line_id = l_line_id;
Update_Invoice_Attributes(l_line_rec
,l_interface_line_rec
,'MANUAL-PENDING'
,x_return_status);
SELECT NVL(SUBSTR(dl.name, 1, 30), '0')
INTO l_delivery_name
FROM wsh_new_deliveries dl
WHERE dl.delivery_id = l_delivery_line_id;
SELECT min(dl.delivery_id)
INTO l_line_delivery_id
FROM wsh_new_deliveries dl,
wsh_delivery_assignments da,
wsh_delivery_details dd
WHERE dd.delivery_detail_id = da.delivery_detail_id
AND da.delivery_id = dl.delivery_id
AND dd.source_code = 'OE'
AND dd.released_status = 'C' -- bug 6721251
AND dd.source_line_id = l_line_id;
SELECT min(dl.delivery_id)
INTO l_line_delivery_id
FROM wsh_new_deliveries dl,
wsh_delivery_assignments da,
wsh_delivery_details dd
WHERE dd.delivery_detail_id = da.delivery_detail_id
AND da.delivery_id = dl.delivery_id
AND dd.source_code = 'OE'
AND dd.released_status = 'C' -- bug 6721251
AND dd.top_model_line_id = l_line_id;
SELECT min(dl.delivery_id)
INTO l_line_delivery_id
FROM wsh_new_deliveries dl,
wsh_delivery_assignments da,
wsh_delivery_details dd
WHERE dd.delivery_detail_id = da.delivery_detail_id
AND da.delivery_id = dl.delivery_id
AND dd.source_code = 'OE'
AND dd.released_status = 'C' -- bug 6721251
AND dd.top_model_line_id = l_line_id;
ELSE -- PTO in a delivery, but nothing available to invoice, update to RFR-PENDING
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'RFR: NOTHING AVAILABLE FOR PTO TO INVOICE , UDPATE RFR-PENDING' , 5 ) ;
Update_Invoice_Attributes(l_line_rec
,l_interface_line_rec
,'RFR-PENDING'
,x_return_status);
END IF; -- check for update_invoice_attribute results
oe_debug_pub.add( 'RFR: CALLING UPDATE_NUMBERS' , 5 ) ;
Update_Numbers(p_line_id, x_return_status);
Update_Numbers(p_header_id, x_return_status);
oe_debug_pub.add( 'RFR: UPDATE NUMBER DONE FOR RFR-PENDING LINE ( CURRENT LINE IS RFR ) ' , 5 ) ;
oe_debug_pub.add( 'CALLING UPDATE_NUMBERS' ) ;
Update_Numbers(p_line_id, x_return_status);
Update_Numbers(p_header_id, x_return_status);
Update_line_flow_status(l_line_rec.line_id,l_flow_status_code);
SELECT ol.line_id
FROM oe_order_lines ol
WHERE ol.header_id = p_header_id
AND ol.open_flag = 'Y';
oe_debug_pub.add( 'CALL UPDATE_NUMBERS FOR HEADER LEVEL INVOICING' , 5 ) ;
Update_Numbers(p_header_id, x_return_status);
Update_header_flow_status(p_header_id,l_flow_status_code);
Update_header_flow_status(p_header_id,l_flow_status_code);
OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
SELECT 1
INTO line_payment_not_exists
FROM OE_ORDER_LINES L
WHERE HEADER_ID = l_header_id
AND invoice_interface_status_code = 'YES'
AND NOT EXISTS
(select 'x' from oe_payments
WHERE header_id = l_header_id
AND line_id = L.line_id
AND payment_type_code <> 'COMMITMENT'
)
AND ROWNUM=1;
SELECT 1
INTO any_line_interfaced
FROM OE_ORDER_LINES
WHERE HEADER_ID = l_header_id
AND invoice_interface_status_code = 'YES'
AND ROWNUM = 1;
OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
select 1 into l_lines_exist
from oe_order_lines
where header_id = l_header_id
and rownum = 1;
SELECT 1
INTO any_line_not_interfaced
FROM OE_ORDER_LINES
WHERE HEADER_ID = l_header_id
AND NVL(invoice_interface_status_code, 'NO') in ('NO', 'MANUAL-PENDING', 'RFR-PENDING', 'ACCEPTANCE-PENDING')
AND
( --Bug 5230279
open_flag = 'Y'
OR (nvl(cancelled_flag, 'N') = 'Y' AND open_flag = 'N') -- bug 5181988
) -- bug 5230279
AND ROWNUM = 1;
OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
SELECT 1
INTO this_line_interfaced
FROM OE_ORDER_LINES
WHERE LINE_ID = l_line_id
AND invoice_interface_status_code = 'YES';