[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT (L1.ORDERED_QUANTITY - NVL(L1.CANCELLED_QUANTITY,0))
INTO L_ORIGINAL
FROM SO_LINES L1
WHERE L1.LINE_ID = P_RETURN_REFERENCE_ID;
SELECT NVL(SUM(L2.ORDERED_QUANTITY - NVL(L2.CANCELLED_QUANTITY,0)), 0)
INTO L_SO_PO_SUM
FROM SO_LINES L2
WHERE L2.RETURN_REFERENCE_ID = P_RETURN_REFERENCE_ID
AND L2.RETURN_REFERENCE_TYPE_CODE IN ('ORDER', 'PO')
AND L2.LINE_ID <> P_LINE_ID;
SELECT NVL(SUM(L2.ORDERED_QUANTITY - NVL(L2.CANCELLED_QUANTITY,0)), 0)
INTO L_INV_SUM
FROM SO_LINES L2
WHERE L2.RETURN_REFERENCE_ID IN (SELECT CUSTOMER_TRX_LINE_ID
FROM RA_CUSTOMER_TRX_LINES
WHERE INTERFACE_LINE_CONTEXT
= FND_PROFILE.Value_Specific('SO_SOURCE_CODE')
AND INTERFACE_LINE_ATTRIBUTE6
= TO_CHAR(P_RETURN_REFERENCE_ID))
AND L2.RETURN_REFERENCE_TYPE_CODE = 'INVOICE'
AND L2.LINE_ID <> P_LINE_ID;
SELECT TO_NUMBER(INTERFACE_LINE_ATTRIBUTE6)
INTO L_TEMP_LINE_ID
FROM RA_CUSTOMER_TRX_LINES
WHERE CUSTOMER_TRX_LINE_ID = P_RETURN_REFERENCE_ID;
SELECT (L1.ORDERED_QUANTITY - NVL(L1.CANCELLED_QUANTITY,0))
INTO L_ORIGINAL
FROM SO_LINES L1
WHERE L1.LINE_ID = L_TEMP_LINE_ID;
SELECT NVL(SUM(L2.ORDERED_QUANTITY - NVL(L2.CANCELLED_QUANTITY,0)), 0)
INTO L_SO_PO_SUM
FROM SO_LINES L2
WHERE L2.RETURN_REFERENCE_ID = L_TEMP_LINE_ID
AND L2.RETURN_REFERENCE_TYPE_CODE IN ('ORDER', 'PO')
AND L2.LINE_ID <> P_LINE_ID;
SELECT NVL(SUM(L2.ORDERED_QUANTITY - NVL(L2.CANCELLED_QUANTITY,0)), 0)
INTO L_INV_SUM
FROM SO_LINES L2
WHERE L2.RETURN_REFERENCE_ID = P_RETURN_REFERENCE_ID
AND L2.RETURN_REFERENCE_TYPE_CODE = 'INVOICE'
AND L2.LINE_ID <> P_LINE_ID;
SELECT 'duplicate'
INTO L_SUCCESS
FROM SO_LINES
WHERE ((P_ROW_ID IS NULL) OR
(ROWID <> P_ROW_ID))
and parent_line_id is NULL
AND LINE_NUMBER = P_LINE_NUMBER
AND HEADER_ID = P_HEADER_ID;
** Insert reference's sales credits and price adjustments.
** Called by OE ORDER
*/
PROCEDURE INSERT_REF_DEFAULT (
L_RETURN_REFERENCE_ID IN NUMBER,
L_USER_ID IN NUMBER,
L_LOGIN_ID IN NUMBER,
L_LINK_TO_LINE_ID IN NUMBER,
L_LINE_ID IN NUMBER,
L_HEADER_ID IN NUMBER,
L_REFERENCE_ORDER_HEADER_ID IN NUMBER
) IS
BEGIN
IF (L_RETURN_REFERENCE_ID IS NOT NULL) THEN
INSERT INTO SO_SALES_CREDITS
( SALES_CREDIT_ID
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, HEADER_ID
, SALES_CREDIT_TYPE_ID
, SALESREP_ID
, PERCENT
, LINE_ID
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, CONTEXT
)
SELECT SO_SALES_CREDITS_S.NEXTVAL
, SYSDATE
, L_USER_ID
, SYSDATE
, L_USER_ID
, L_LOGIN_ID
, L_HEADER_ID
, SO_SALES_CREDITS.SALES_CREDIT_TYPE_ID
, SO_SALES_CREDITS.SALESREP_ID
, SO_SALES_CREDITS.PERCENT
, L_LINE_ID
, SO_SALES_CREDITS.ATTRIBUTE1
, SO_SALES_CREDITS.ATTRIBUTE2
, SO_SALES_CREDITS.ATTRIBUTE3
, SO_SALES_CREDITS.ATTRIBUTE4
, SO_SALES_CREDITS.ATTRIBUTE5
, SO_SALES_CREDITS.ATTRIBUTE6
, SO_SALES_CREDITS.ATTRIBUTE7
, SO_SALES_CREDITS.ATTRIBUTE8
, SO_SALES_CREDITS.ATTRIBUTE9
, SO_SALES_CREDITS.ATTRIBUTE10
, SO_SALES_CREDITS.ATTRIBUTE11
, SO_SALES_CREDITS.ATTRIBUTE12
, SO_SALES_CREDITS.ATTRIBUTE13
, SO_SALES_CREDITS.ATTRIBUTE14
, SO_SALES_CREDITS.ATTRIBUTE15
, SO_SALES_CREDITS.CONTEXT
FROM SO_SALES_CREDITS
WHERE SO_SALES_CREDITS.LINE_ID = L_LINK_TO_LINE_ID;
INSERT INTO SO_PRICE_ADJUSTMENTS
( PRICE_ADJUSTMENT_ID
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, HEADER_ID
, DISCOUNT_ID
, DISCOUNT_LINE_ID
, AUTOMATIC_FLAG
, PERCENT
, LINE_ID
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, CONTEXT
)
SELECT SO_PRICE_ADJUSTMENTS_S.NEXTVAL
, SYSDATE
, L_USER_ID
, SYSDATE
, L_USER_ID
, L_LOGIN_ID
, L_HEADER_ID
, SO_PRICE_ADJUSTMENTS.DISCOUNT_ID
, SO_PRICE_ADJUSTMENTS.DISCOUNT_LINE_ID
, SO_PRICE_ADJUSTMENTS.AUTOMATIC_FLAG
, SO_PRICE_ADJUSTMENTS.PERCENT
, L_LINE_ID
, SO_PRICE_ADJUSTMENTS.ATTRIBUTE1
, SO_PRICE_ADJUSTMENTS.ATTRIBUTE2
, SO_PRICE_ADJUSTMENTS.ATTRIBUTE3
, SO_PRICE_ADJUSTMENTS.ATTRIBUTE4
, SO_PRICE_ADJUSTMENTS.ATTRIBUTE5
, SO_PRICE_ADJUSTMENTS.ATTRIBUTE6
, SO_PRICE_ADJUSTMENTS.ATTRIBUTE7
, SO_PRICE_ADJUSTMENTS.ATTRIBUTE8
, SO_PRICE_ADJUSTMENTS.ATTRIBUTE9
, SO_PRICE_ADJUSTMENTS.ATTRIBUTE10
, SO_PRICE_ADJUSTMENTS.ATTRIBUTE11
, SO_PRICE_ADJUSTMENTS.ATTRIBUTE12
, SO_PRICE_ADJUSTMENTS.ATTRIBUTE13
, SO_PRICE_ADJUSTMENTS.ATTRIBUTE14
, SO_PRICE_ADJUSTMENTS.ATTRIBUTE15
, SO_PRICE_ADJUSTMENTS.CONTEXT
FROM SO_PRICE_ADJUSTMENTS
WHERE SO_PRICE_ADJUSTMENTS.HEADER_ID = L_REFERENCE_ORDER_HEADER_ID
AND NVL(SO_PRICE_ADJUSTMENTS.LINE_ID,L_LINK_TO_LINE_ID) =
L_LINK_TO_LINE_ID
AND NOT EXISTS (SELECT 'HEADER LEVEL DISCOUNT ALREADY EXISTS'
FROM SO_PRICE_ADJUSTMENTS SPA
WHERE SPA.DISCOUNT_ID = SO_PRICE_ADJUSTMENTS.DISCOUNT_ID
AND SPA.HEADER_ID = L_HEADER_ID
AND SPA.LINE_ID IS NULL);