[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT REFERENCE_LINE_ID,
LINE_CATEGORY_CODE
INTO l_reference_line_id,
l_line_category_code
FROM OE_ORDER_LINES
WHERE LINE_ID = p_line_id;
SELECT HEADER_ID,
ORG_ID,
SHIP_FROM_ORG_ID,
SOLD_TO_ORG_ID,
COMMITMENT_ID,
SALESREP_ID,
INVENTORY_ITEM_ID,
ITEM_TYPE_CODE,
ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
ORIG_SYS_LINE_REF,
ORIG_SYS_SHIPMENT_REF,
CHANGE_SEQUENCE,
SOURCE_DOCUMENT_TYPE_ID,
SOURCE_DOCUMENT_ID,
SOURCE_DOCUMENT_LINE_ID
INTO l_header_id,
l_org_id,
l_ship_from_org_id,
l_sold_to_org_id,
l_commitment_id,
l_salesrep_id,
l_inventory_item_id,
l_item_type_code,
l_order_source_id,
l_orig_sys_document_ref,
l_orig_sys_line_ref,
l_orig_sys_shipment_ref,
l_change_sequence,
l_source_document_type_id,
l_source_document_id,
l_source_document_line_id
FROM OE_ORDER_LINES
WHERE LINE_ID = l_line_id;
SELECT ORDER_CATEGORY_CODE,
ORDER_TYPE_ID
INTO l_order_category_code,
l_order_type_id
FROM OE_ORDER_HEADERS
WHERE HEADER_ID = l_header_id;
/*SELECT CHART_OF_ACCOUNTS_ID
INTO l_chart_of_accounts_id
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_ID = l_ship_from_org_id; */
SELECT /*+ ordered use_nl(HOI1,HOI2,GSOB) */
GSOB.CHART_OF_ACCOUNTS_ID CHART_OF_ACCOUNTS_ID
INTO l_chart_of_accounts_id
FROM HR_ORGANIZATION_UNITS HOU ,
HR_ORGANIZATION_INFORMATION HOI1 ,
HR_ORGANIZATION_INFORMATION HOI2 ,
GL_SETS_OF_BOOKS GSOB
WHERE HOU.ORGANIZATION_ID = HOI1.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID = HOI2.ORGANIZATION_ID
AND HOI1.ORG_INFORMATION1 = 'INV'
AND HOI1.ORG_INFORMATION2 = 'Y'
AND ( HOI1.ORG_INFORMATION_CONTEXT || '' ) = 'CLASS'
AND ( HOI2.ORG_INFORMATION_CONTEXT || '' ) ='Accounting Information'
AND HOI2.ORG_INFORMATION1
= GSOB.SET_OF_BOOKS_ID
and hou.organization_id = l_ship_from_org_id;
insert_if_new => TRUE,
ccid => lx_return_ccid,
concat_segs => lx_concat_segs,
concat_ids => lx_concat_ids,
concat_descrs => lx_concat_descrs,
error_message => l_errmsg,
new_combination => l_new_combination);
SELECT LINK_TO_LINE_ID,
ITEM_TYPE_CODE
INTO l_link_to_line_id,
l_item_type_code
FROM OE_ORDER_LINES
WHERE LINE_ID = l_line_id;
SELECT NVL(M.COST_OF_SALES_ACCOUNT,0)
INTO l_cost_sale_item_derived
FROM OE_ORDER_LINES OL,
MTL_SYSTEM_ITEMS M
WHERE OL.LINE_ID = l_line_id
AND M.ORGANIZATION_ID = OL.SHIP_FROM_ORG_ID
AND M.INVENTORY_ITEM_ID = OL.INVENTORY_ITEM_ID;
SELECT NVL(M.COST_OF_SALES_ACCOUNT,0)
INTO l_cost_sale_item_derived
FROM OE_ORDER_LINES OL,
MTL_SYSTEM_ITEMS M
WHERE OL.LINE_ID = l_link_to_line_id
AND M.ORGANIZATION_ID = OL.SHIP_FROM_ORG_ID
AND M.INVENTORY_ITEM_ID = OL.INVENTORY_ITEM_ID;
SELECT TOP_MODEL_LINE_ID,
INVENTORY_ITEM_ID,
SHIP_FROM_ORG_ID
INTO l_top_model_line_id,
l_inventory_item_id,
l_ship_from_org_id
FROM OE_ORDER_LINES
WHERE LINE_ID = l_line_id;
SELECT NVL(M.COST_OF_SALES_ACCOUNT,0)
INTO l_cost_sale_model_derived
FROM OE_ORDER_LINES OL,
MTL_SYSTEM_ITEMS M
WHERE OL.LINE_ID = l_model_line_id
AND M.ORGANIZATION_ID = OL.SHIP_FROM_ORG_ID
AND M.INVENTORY_ITEM_ID = OL.INVENTORY_ITEM_ID;
SELECT NVL(COST_OF_GOODS_SOLD_ACCOUNT, 0)
INTO l_order_type_ccid
FROM OE_TRANSACTION_TYPES_ALL
WHERE TRANSACTION_TYPE_ID = l_order_type_id;
SELECT NVL(GL_ID_REV, 0)
INTO l_salesrep_rev_derived
FROM RA_SALESREPS
WHERE SALESREP_ID = L_SALESREP_ID;
Select line_type_id
Into l_order_type_id
From oe_order_lines_all
Where line_id = l_order_line_id;
SELECT SALESREP_ID
INTO l_salesrep_id
FROM OE_SALES_CREDITS
WHERE LINE_ID = L_ORDER_LINE_ID
AND SALESREP_ID = (
SELECT MIN(SALESREP_ID)
FROM OE_SALES_CREDITS SC ,
OE_SALES_CREDIT_TYPES SCT
WHERE SC.LINE_ID = L_ORDER_LINE_ID
AND SC.SALES_CREDIT_TYPE_ID = SCT.SALES_CREDIT_TYPE_ID
AND SCT.QUOTA_FLAG = 'Y'
AND SC.PERCENT = (
SELECT MAX(PERCENT)
FROM OE_SALES_CREDITS SC, --Bug4096083 start
OE_SALES_CREDIT_TYPES SCT
WHERE SC.LINE_ID = L_ORDER_LINE_ID
AND SC.SALES_CREDIT_TYPE_ID = SCT.SALES_CREDIT_TYPE_ID
AND SCT.QUOTA_FLAG = 'Y' --Bug4096083 end
)
)
AND ROWNUM = 1;
SELECT SALESREP_ID
INTO l_salesrep_id
FROM OE_SALES_CREDITS
WHERE HEADER_ID = l_header_id
AND LINE_ID IS NULL
AND SALESREP_ID = (
SELECT MIN(SALESREP_ID)
FROM OE_SALES_CREDITS SC ,
OE_SALES_CREDIT_TYPES SCT
WHERE SC.HEADER_ID = L_HEADER_ID
AND SC.LINE_ID IS NULL
AND SC.SALES_CREDIT_TYPE_ID = SCT.SALES_CREDIT_TYPE_ID
AND SCT.QUOTA_FLAG = 'Y'
AND SC.PERCENT = (
SELECT MAX(PERCENT)
FROM OE_SALES_CREDITS SC, --Bug4096083 start
OE_SALES_CREDIT_TYPES SCT
WHERE SC.HEADER_ID = L_HEADER_ID
AND SC.LINE_ID IS NULL
AND SC.SALES_CREDIT_TYPE_ID = SCT.SALES_CREDIT_TYPE_ID
AND SCT.QUOTA_FLAG = 'Y' --Bug4096083 end
)
)
AND ROWNUM = 1;
SELECT NVL(COST_OF_SALES_ACCOUNT, 0)
INTO l_account_derived
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = l_inv_item_id
AND ORGANIZATION_ID = l_ship_from_org_id;
SELECT /* MOAC_SQL_CHANGE */ TYPE.TYPE
INTO l_trx_type
FROM RA_CUSTOMER_TRX TRX, RA_CUST_TRX_TYPES_ALL TYPE
WHERE TRX.CUSTOMER_TRX_ID = L_COMMITMENT_ID
AND TRX.ORG_ID = TYPE.ORG_ID
AND TRX.CUST_TRX_TYPE_ID = TYPE.CUST_TRX_TYPE_ID;