[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select top_model_line_id
into l_top_model_line_id
from oe_order_lines_all
where line_id=p_line_id
and top_model_line_id IS NOT NULL
and ship_model_complete_flag='Y';
PROCEDURE Update_Comments_And_Commit
( p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
OE_DEBUG_PUB.ADD('OEXVCRLB: Entering Update_Comments_And_Commit');
OE_DEBUG_PUB.ADD('OEXVCRLB: Before OE_Holds_PUB.Update_Hold_Comments');
OE_Holds_PUB.Update_Hold_comments
( p_hold_source_rec => p_hold_source_rec
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, x_return_status => x_return_status
);
OE_DEBUG_PUB.ADD('OEXVCRLB: After OE_Holds_PUB.Update_Hold_Comments Status '
|| x_return_status);
OE_DEBUG_PUB.ADD('OEXVCRLB: Update Hold Comment Success, Issue COMMIT');
OE_DEBUG_PUB.ADD(' OEXVCRLB: Exiting Update_Comments_And_Commit');
OE_DEBUG_PUB.ADD('OEXVCRLB: Error in Update_Comments_And_Commit' );
, 'Update_Comments_And_Commit'
);
END Update_Comments_And_Commit ;
SELECT /* MOAC_SQL_NO_CHANGE */ l.line_id, l.line_number,0 line_total --ER 6135714
FROM oe_order_lines_all l,
oe_order_headers_all h,
ra_terms_b t
WHERE l.invoice_to_org_id = p_site_use_id
AND l.header_id = p_header_id
AND h.header_id = l.header_id
AND l.open_flag = 'Y'
AND l.booked_flag = 'Y'
AND NVL(l.invoiced_quantity,0) = 0
AND NVL(l.shipped_quantity,0) = 0
AND l.line_category_code = 'ORDER'
AND l.payment_term_id = t.term_id
AND t.credit_check_flag = 'Y'
AND (l.ato_line_id IS NULL OR l.ato_line_id = l.line_id)
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = NVL(h.org_id,-99))
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL)
)
ORDER BY l.line_id;
SELECT l.line_id, l.line_number , NVL(unit_selling_price,0) * NVL(ordered_quantity,0)
+ decode( l.ato_line_id, null , 0, (select sum (NVL(l2.unit_selling_price,0) * NVL(l2.ordered_quantity,0)) from oe_order_lines_all l2 where
l2.ato_line_id = l.line_id and l2.ato_line_id <> l2.line_id)
)line_total
FROM oe_order_lines_all l,
oe_order_headers_all h,
ra_terms_b t
WHERE l.invoice_to_org_id = p_site_use_id
AND l.header_id = p_header_id
AND h.header_id = l.header_id
AND l.open_flag = 'Y'
AND l.booked_flag = 'Y'
AND NVL(l.invoiced_quantity,0) = 0
AND NVL(l.shipped_quantity,0) = 0
AND l.line_category_code = 'ORDER'
AND l.payment_term_id = t.term_id
AND t.credit_check_flag = 'Y'
AND (l.ato_line_id IS NULL OR l.ato_line_id = l.line_id)
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = NVL(h.org_id,-99))
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL)
)
ORDER BY nvl(l.SCHEDULE_SHIP_DATE, l.REQUEST_DATE) , l.SHIPMENT_PRIORITY_CODE, l.line_id;
SELECT l.line_id, l.line_number , NVL(unit_selling_price,0) * NVL(ordered_quantity,0)
+ decode( l.ato_line_id, null , 0, (select sum (NVL(l2.unit_selling_price,0) * NVL(l2.ordered_quantity,0)) from oe_order_lines_all l2 where
l2.ato_line_id = l.line_id and l2.ato_line_id <> l2.line_id)
) line_total
FROM oe_order_lines_all l,
oe_order_headers_all h,
ra_terms_b t
WHERE l.invoice_to_org_id = p_site_use_id
AND l.header_id = p_header_id
AND h.header_id = l.header_id
AND l.open_flag = 'Y'
AND l.booked_flag = 'Y'
AND NVL(l.invoiced_quantity,0) = 0
AND NVL(l.shipped_quantity,0) = 0
AND l.line_category_code = 'ORDER'
AND l.payment_term_id = t.term_id
AND t.credit_check_flag = 'Y'
AND (l.ato_line_id IS NULL OR l.ato_line_id = l.line_id)
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = NVL(h.org_id,-99))
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL)
)
ORDER BY l.SHIPMENT_PRIORITY_CODE,nvl(l.SCHEDULE_SHIP_DATE, l.REQUEST_DATE) , l.line_id;
SELECT l.line_id, l.line_number , NVL(unit_selling_price,0) * NVL(ordered_quantity,0)
+ decode( l.ato_line_id, null , 0, (select sum (NVL(l2.unit_selling_price,0) * NVL(l2.ordered_quantity,0)) from oe_order_lines_all l2 where
l2.ato_line_id = l.line_id and l2.ato_line_id <> l2.line_id)
) line_total
FROM oe_order_lines_all l,
oe_order_headers_all h,
ra_terms_b t
WHERE l.invoice_to_org_id = p_site_use_id
AND l.header_id = p_header_id
AND h.header_id = l.header_id
AND l.open_flag = 'Y'
AND l.booked_flag = 'Y'
AND NVL(l.invoiced_quantity,0) = 0
AND NVL(l.shipped_quantity,0) = 0
AND l.line_category_code = 'ORDER'
AND l.payment_term_id = t.term_id
AND t.credit_check_flag = 'Y'
AND (l.ato_line_id IS NULL OR l.ato_line_id = l.line_id)
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = NVL(h.org_id,-99))
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL)
)
ORDER BY line_total asc;
SELECT l.line_id, l.line_number , NVL(unit_selling_price,0) * NVL(ordered_quantity,0)
+ decode( l.ato_line_id, null , 0, (select sum (NVL(l2.unit_selling_price,0) * NVL(l2.ordered_quantity,0)) from oe_order_lines_all l2 where
l2.ato_line_id = l.line_id and l2.ato_line_id <> l2.line_id)
) line_total
FROM oe_order_lines_all l,
oe_order_headers_all h,
ra_terms_b t
WHERE l.invoice_to_org_id = p_site_use_id
AND l.header_id = p_header_id
AND h.header_id = l.header_id
AND l.open_flag = 'Y'
AND l.booked_flag = 'Y'
AND NVL(l.invoiced_quantity,0) = 0
AND NVL(l.shipped_quantity,0) = 0
AND l.line_category_code = 'ORDER'
AND l.payment_term_id = t.term_id
AND t.credit_check_flag = 'Y'
AND (l.ato_line_id IS NULL OR l.ato_line_id = l.line_id)
AND (EXISTS
(SELECT NULL
FROM oe_payment_types_all pt
WHERE pt.payment_type_code = NVL(l.payment_type_code,
NVL(h.payment_type_code, 'BME'))
AND pt.credit_check_flag = 'Y'
AND NVL(pt.org_id, -99) = NVL(h.org_id,-99))
OR
(l.payment_type_code IS NULL AND h.payment_type_code IS NULL)
)
ORDER BY line_total desc;
PROCEDURE Update_Holds_Table
( p_holds_table IN OUT NOCOPY Line_Holds_Tbl_Rectype
, p_line_id IN NUMBER DEFAULT NULL
, p_hold IN VARCHAR2 DEFAULT NULL
, p_cc_limit_used IN VARCHAR2 DEFAULT NULL
, p_cc_profile_used IN VARCHAR2 DEFAULT NULL
, p_customer_id IN NUMBER DEFAULT NULL
, p_site_use_id IN NUMBER DEFAULT NULL
, p_party_id IN NUMBER DEFAULT NULL
, p_item_category_id IN NUMBER DEFAULT NULL
, x_return_status OUT NOCOPY VARCHAR2
)
IS
BEGIN
IF G_debug_flag = 'Y'
THEN
OE_DEBUG_PUB.Add('OEXVCRLB: In Update_Holds_Table');
OE_DEBUG_PUB.Add('OEXVCRLB: Out Update_Holds_Table');
OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Update_Holds_Table');
END Update_Holds_Table;
PROCEDURE Update_Holds_Table
( p_holds_table IN OUT NOCOPY Line_Holds_Tbl_Rectype
, p_hold IN VARCHAR2 DEFAULT NULL
, p_cc_limit_used IN VARCHAR2 DEFAULT NULL
, p_cc_profile_used IN VARCHAR2 DEFAULT NULL
, p_customer_id IN NUMBER DEFAULT NULL
, p_site_use_id IN NUMBER DEFAULT NULL
, p_party_id IN NUMBER DEFAULT NULL
, p_exposure IN NUMBER
, p_overall_credit_limit IN NUMBER
--13939240 Start
, p_limit_currency IN VARCHAR2
, p_transactional_currency IN VARCHAR2
, p_conversion_type IN VARCHAR2
--13939240 End
)
IS
l_amt_on_hold number := 0;
OE_DEBUG_PUB.Add('OEXVCRLB: In Update_Holds_Table Overloaded');
OE_DEBUG_PUB.Add('OEXVCRLB: Out Update_Holds_Table');
OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Update_Holds_Table overloaded');
END Update_Holds_Table;
SELECT ohr.HOLD_RELEASE_ID
FROM OE_ORDER_HOLDS h,
OE_HOLD_SOURCES_ALL s,
oe_hold_releases ohr
WHERE H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
AND H.HEADER_ID = p_header_id
AND H.LINE_ID IS NULL
AND H.HOLD_RELEASE_ID IS NOT NULL
AND S.HOLD_ID = p_hold_id
AND S.HOLD_ENTITY_CODE = 'O'
AND S.HOLD_ENTITY_ID = p_header_id
AND S.RELEASED_FLAG ='Y'
AND ohr.HOLD_RELEASE_ID = h.HOLD_RELEASE_ID
ORDER BY ohr.creation_date DESC;
SELECT ohr.HOLD_RELEASE_ID
FROM OE_ORDER_HOLDS h,
OE_HOLD_SOURCES_ALL s,
oe_hold_releases ohr
WHERE H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
AND H.HEADER_ID = p_header_id
AND H.LINE_ID = p_line_id
AND H.HOLD_RELEASE_ID IS NOT NULL
AND S.HOLD_ID = p_hold_id
AND S.HOLD_ENTITY_CODE = 'O'
AND S.HOLD_ENTITY_ID = p_header_id
AND S.RELEASED_FLAG ='Y'
AND ohr.HOLD_RELEASE_ID = h.HOLD_RELEASE_ID
ORDER BY ohr.creation_date DESC;
/*SELECT /* MOAC_SQL_CHANGE */ /*NVL(MAX(H.HOLD_RELEASE_ID),0)
INTO l_hold_release_id
FROM OE_ORDER_HOLDS h,
OE_HOLD_SOURCES_ALL s
WHERE H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
AND H.HEADER_ID = p_header_id
AND H.LINE_ID IS NULL
AND H.HOLD_RELEASE_ID IS NOT NULL
AND S.HOLD_ID = p_hold_id
AND S.HOLD_ENTITY_CODE = 'O'
AND S.HOLD_ENTITY_ID = p_header_id
AND S.RELEASED_FLAG ='Y';*/
/*SELECT /* MOAC_SQL_CHANGE */ /*NVL(MAX(H.HOLD_RELEASE_ID),0)
INTO l_hold_release_id
FROM OE_ORDER_HOLDS h,
OE_HOLD_SOURCES_ALL s
WHERE H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
AND H.HEADER_ID = p_header_id
AND H.LINE_ID = p_line_id
AND H.HOLD_RELEASE_ID IS NOT NULL
AND S.HOLD_ID = p_hold_id
AND S.HOLD_ENTITY_CODE = 'O'
AND S.HOLD_ENTITY_ID = p_header_id
AND S.RELEASED_FLAG ='Y';*/
SELECT
'Y'
, CREATION_DATE -----added
INTO
l_manual_hold_exists
, l_release_date
FROM OE_HOLD_RELEASES
WHERE HOLD_RELEASE_ID = l_hold_release_id
AND RELEASE_REASON_CODE <> 'PASS_CREDIT'
AND CREATED_BY <> 1;
** Introduced new procedure Update_Comments_And_Commit to
** Update and Commit Hold Comments. Apply_Holds_And_Commit
** And Update_Comments_And_Commit are now called whenever
** Calling Action is Picking, Packing or Shipping ELSE
** Apply_Holds and Update_Hold_Comments are called.
*/
PROCEDURE Apply_Line_CC_Hold
( p_header_id IN NUMBER
, p_order_number IN NUMBER
, p_line_id IN NUMBER
, p_line_number IN NUMBER
, p_calling_action IN VARCHAR2 DEFAULT 'BOOKING'
, p_cc_limit_used IN VARCHAR2
, p_cc_profile_used IN VARCHAR2
, p_party_id IN NUMBER DEFAULT NULL
, p_customer_id IN NUMBER DEFAULT NULL
, p_site_use_id IN NUMBER DEFAULT NULL
, p_item_category_id IN NUMBER DEFAULT NULL
, p_credit_hold_level IN VARCHAR2
, p_credit_check_rule_rec IN
OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type
, x_cc_result_out OUT NOCOPY VARCHAR2
)
IS
-- Cursor to select the customer name
CURSOR customer_name_csr IS
SELECT name
FROM oe_sold_to_orgs_v
WHERE customer_id = p_customer_id;
SELECT party_name
FROM hz_parties
WHERE party_id = p_party_id ;
SELECT location
FROM hz_cust_site_uses
WHERE site_use_id = p_site_use_id;
SELECT description
FROM mtl_categories
WHERE category_id = p_item_category_id;
OE_DEBUG_PUB.ADD('OEXVCRLB: Call Update_Comments_And_Commit');
Update_Comments_And_Commit
( p_hold_source_rec => l_hold_source_rec
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, x_return_status => l_return_status
);
OE_DEBUG_PUB.ADD('OEXVCRLB: Out Update_Comments_And_Commit');
ELSIF NVL( p_calling_action,'BOOKING') IN ('BOOKING','UPDATE','AUTO HOLD')
THEN
IF G_debug_flag = 'Y'
THEN
OE_DEBUG_PUB.ADD('OEXVCRLB: Call OE_Holds_PUB.Update_Hold_Comments directly');
OE_Holds_PUB.Update_Hold_comments
( p_hold_source_rec => l_hold_source_rec
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, x_return_status => l_return_status
);
OE_DEBUG_PUB.ADD('OEXVCRLB: Out OE_Holds_PUB.Update_Hold_Comments directly');
('OEXVCRLB: Updated Comments on Header/Line ID:' ||
p_header_id || '/' ||
p_line_id, 1);
ELSIF NVL( p_calling_action,'BOOKING') IN ('BOOKING','UPDATE','AUTO HOLD')
THEN
IF G_debug_flag = 'Y'
THEN
OE_DEBUG_PUB.ADD('OEXVCRLB: Call OE_Holds_PUB.Apply_Holds directly');
SELECT description
FROM mtl_categories
WHERE category_id = p_item_category_id;
Update_Holds_Table
( p_holds_table => p_holds_table
, p_line_id => p_lines(i).line_id
, p_hold => 'ITEM'
, p_cc_limit_used => 'ITEM'
, p_cc_profile_used => 'CATEGORY'
, p_item_category_id => p_item_category_id
, x_return_status => l_return_status
);
Update_Holds_Table
( p_holds_table => p_holds_table
, p_hold => 'OTHER'
, p_cc_limit_used => p_cc_limit_used
, p_cc_profile_used => p_cc_profile_used
, p_customer_id => p_customer_id
, p_site_use_id => p_site_use_id
, p_party_id => p_party_id
, x_return_status => l_return_status
);
SELECT Credit_Profile_Level
FROM OE_ORDER_HOLDS_all h,
OE_HOLD_SOURCES_ALL s,
oe_hold_releases ohr
WHERE H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
AND H.HEADER_ID = p_header_rec.header_id
AND H.LINE_ID IS NULL
AND H.HOLD_RELEASE_ID IS NOT NULL
AND S.HOLD_ID = 1
AND S.HOLD_ENTITY_CODE = 'O'
AND S.HOLD_ENTITY_ID = p_header_rec.header_id
AND S.RELEASED_FLAG ='Y'
AND ohr.HOLD_RELEASE_ID = h.HOLD_RELEASE_ID
ORDER BY ohr.creation_date DESC;
SELECT Credit_Profile_Level
FROM OE_ORDER_HOLDS_all h,
OE_HOLD_SOURCES_ALL s,
oe_hold_releases ohr
WHERE H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
AND H.HEADER_ID = p_header_rec.header_id
AND H.LINE_ID = p_line_id
AND H.HOLD_RELEASE_ID IS NOT NULL
AND S.HOLD_ID = 1
AND S.HOLD_ENTITY_CODE = 'O'
AND S.HOLD_ENTITY_ID = H.header_id
AND S.RELEASED_FLAG ='Y'
AND ohr.HOLD_RELEASE_ID = h.HOLD_RELEASE_ID
ORDER BY ohr.creation_date DESC;
g_hold_reason_rec.delete; -- 14305856 - Initializing the g_hold_reason_rec table
SELECT acct_site.cust_account_id
INTO l_new_cust_account_id
FROM HZ_CUST_SITE_USES_ALL SITE,
HZ_PARTY_SITES PARTY_SITE,
HZ_CUST_ACCT_SITES ACCT_SITE
WHERE SITE.SITE_USE_ID = OE_CREDIT_CHECK_UTIL.G_CC_Invoice_tab(i).new_invoice_to_org_id
AND SITE.SITE_USE_CODE = 'BILL_TO'
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND SITE.ORG_ID = ACCT_SITE.ORG_ID;
SELECT acct_site.cust_account_id
INTO l_old_cust_account_id
FROM HZ_CUST_SITE_USES_ALL SITE,
HZ_PARTY_SITES PARTY_SITE,
HZ_CUST_ACCT_SITES ACCT_SITE
WHERE SITE.SITE_USE_ID = OE_CREDIT_CHECK_UTIL.G_CC_Invoice_tab(i).old_invoice_to_org_id
AND SITE.SITE_USE_CODE = 'BILL_TO'
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND SITE.ORG_ID = ACCT_SITE.ORG_ID;
Update_Holds_Table
( p_holds_table => p_holds_table
, p_hold => 'OTHER'
, p_cc_limit_used => l_cc_limit_used
, p_cc_profile_used => l_cc_profile_used
, p_customer_id => p_customer_id
, p_site_use_id => p_site_use_id
, p_party_id => l_credit_limit_entity_id
, p_exposure => l_total_exposure
, p_overall_credit_limit => l_overall_credit_limit
--13939240 Start
, p_limit_currency => OE_CREDIT_CHECK_LINES_PVT.G_limit_currency
, p_transactional_currency =>p_header_rec.transactional_curr_code
, p_conversion_type =>p_credit_check_rule_rec.conversion_type
--13939240 End
);
SELECT DISTINCT
ool.invoice_to_org_id site_use_id
FROM oe_order_lines_all ool
WHERE ool.header_id = p_header_rec.header_id
AND ool.open_flag = 'Y'
AND NVL(ool.invoiced_quantity,0) = 0
AND NVL(ool.shipped_quantity,0) = 0
ORDER BY 1 ;
SELECT l.line_id, l.line_number
FROM oe_order_headers_all h,
oe_order_lines_all l,
ra_terms t
WHERE h.header_id = p_header_rec.header_id
AND h.header_id = l.header_id
AND l.payment_term_id = t.term_id
AND nvl(t.credit_check_flag, 'N') = 'N'
AND (EXISTS
(SELECT 'Y'
FROM oe_payment_types_all pt
WHERE NVL(l.payment_type_code, 'N') = pt.payment_type_code
AND pt.credit_check_flag = 'N'
)
OR l.payment_type_code IS NULL
)
AND (EXISTS
(SELECT 'Y'
FROM oe_order_holds_all oh,
oe_hold_sources_all hs
WHERE oh.header_id = p_header_rec.header_id
AND oh.line_id = l.line_id
AND oh.hold_release_id IS NULL
AND oh.hold_source_id = hs.hold_source_id
AND hs.hold_id = 1
));
SELECT OHS.HOLD_SOURCE_ID
FROM OE_ORDER_HOLDS_ALL OOH,
OE_HOLD_SOURCES_ALL OHS
WHERE OOH.HOLD_SOURCE_ID = OHS.HOLD_SOURCE_ID
AND OOH.HEADER_ID = p_header_id
AND OHS.HOLD_ID = 1
ORDER BY OOH.last_update_date DESC;
SELECT RELEASE_REASON_CODE,CREATED_BY
INTO l_release_reason_code,l_created_by
FROM OE_HOLD_RELEASES
WHERE HOLD_SOURCE_ID = l_hold_source_id;
SELECT /* MOAC_SQL_CHANGE */ cas.cust_account_id
, ca.party_id --------------new (FPI)
INTO l_own_customer_id
, l_party_id --------------new (FPI)
FROM HZ_cust_acct_sites_all cas
, HZ_cust_site_uses su
, hz_cust_accounts_all ca --------------new (FPI)
WHERE su.site_use_id = c_site.site_use_id
AND cas.cust_acct_site_id = su.cust_acct_site_id
AND cas.cust_account_id=ca.cust_account_id; ---------new (FPI)
OE_CREDIT_CHECK_UTIL.G_CC_Invoice_tab.delete; --13706069
OE_CREDIT_CHECK_UTIL.G_CC_Invoice_tab.delete; --13706069
OE_CREDIT_CHECK_UTIL.G_CC_Invoice_tab.delete; --13706069
OE_CREDIT_CHECK_UTIL.G_CC_Invoice_tab.delete; --13706069