[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
ID,
AUTHORING_ORG_ID,
INV_ORGANIZATION_ID,
START_DATE,
END_DATE,
CONVERSION_TYPE,
CONVERSION_RATE,
CONVERSION_RATE_DATE,
CUST_ACCT_ID,
BILL_TO_SITE_USE_ID,
INV_RULE_ID,
SHIP_TO_SITE_USE_ID,
PAYMENT_TERM_ID,
ORG_ID,
CURRENCY_CODE,
CUST_PO_NUMBER,
ESTIMATED_AMOUNT
FROM OKC_K_HEADERS_ALL_B
WHERE id = p_chr_id;
SELECT
ID,
INV_TRX_TYPE,
TAX_STATUS,
EXEMPT_CERTIFICATE_NUMBER,
EXEMPT_REASON_CODE,
TAX_EXEMPTION_ID,
TAX_CLASSIFICATION_CODE,
TAX_CODE
FROM OKS_K_HEADERS_B
WHERE chr_id = p_chr_id;
SELECT
ID,
PRICE_NEGOTIATED,
PRICE_UNIT,
CUST_ACCT_ID,
BILL_TO_SITE_USE_ID,
INV_RULE_ID,
START_DATE,
SHIP_TO_SITE_USE_ID,
PAYMENT_TERM_ID,
CLE_ID,
--npalepu added lse_id for bug # 5223699
LSE_ID
--end npalepu
FROM OKC_K_LINES_B
WHERE dnz_chr_id = p_chr_id
AND id = p_line_id
AND chr_id IS NOT NULL;
SELECT
ID,
TAX_STATUS,
EXEMPT_CERTIFICATE_NUMBER,
EXEMPT_REASON_CODE,
TAX_EXEMPTION_ID,
TAX_CLASSIFICATION_CODE,
TAX_CODE
FROM OKS_K_LINES_B
WHERE dnz_chr_id = p_chr_id
AND cle_id = p_line_id;
SELECT set_of_books_id
FROM ar_system_parameters_all
WHERE org_id = p_org_id;
SELECT EXEMPT_CERTIFICATE_NUMBER,
EXEMPT_REASON_CODE
FROM ZX_EXEMPTIONS
WHERE TAX_EXEMPTION_ID = cp_tax_exempt_id;
SELECT ex.TAX_CODE
FROM RA_TAX_EXEMPTIONS_ALL ex
WHERE ex.TAX_EXEMPTION_ID = cp_tax_exempt_id;
SELECT TAX_CLASSIFICATION_CODE
FROM ZX_ID_TCC_MAPPING
WHERE TAX_RATE_CODE_ID = cp_tax_code
AND source = 'AR';
SELECT ACCT_SITE_SHIP.CUST_ACCOUNT_ID
FROM HZ_CUST_SITE_USES_ALL S_SHIP,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE_SHIP
WHERE S_SHIP.SITE_USE_ID = cp_site_use_id
AND S_SHIP.CUST_ACCT_SITE_ID = acct_site_ship.cust_acct_site_id;
SELECT CS.SITE_USE_ID, CS.ORG_ID, CA.CUST_ACCOUNT_ID, CA.CUST_ACCT_SITE_ID
FROM HZ_CUST_SITE_USES_ALL CS, HZ_CUST_ACCT_SITES_ALL CA
WHERE CS.SITE_USE_ID = cp_site_use_id AND CS.SITE_USE_CODE = cp_code
AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID;
SELECT EXEMPT_CERTIFICATE_NUMBER,
EXEMPT_REASON_CODE
FROM ZX_EXEMPTIONS
WHERE TAX_EXEMPTION_ID = cp_tax_exempt_id;
SELECT ex.TAX_CODE
FROM RA_TAX_EXEMPTIONS_ALL ex
WHERE ex.TAX_EXEMPTION_ID = cp_tax_exempt_id;
SELECT TAX_CLASSIFICATION_CODE
FROM ZX_ID_TCC_MAPPING
WHERE TAX_RATE_CODE_ID = cp_tax_code
AND source = 'AR';
SELECT CS.SITE_USE_ID, CS.ORG_ID, CA.CUST_ACCOUNT_ID, CA.CUST_ACCT_SITE_ID
FROM HZ_CUST_SITE_USES_ALL CS, HZ_CUST_ACCT_SITES_ALL CA
WHERE CS.SITE_USE_ID = cp_site_use_id AND CS.SITE_USE_CODE = cp_code
AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID;
SELECT S_SHIP.CUST_ACCT_SITE_ID,
S_SHIP.fob_point,
S_SHIP.warehouse_id,
ACCT_SITE_SHIP.CUST_ACCOUNT_ID,
ACCT_SITE_SHIP.org_id,
LOC_SHIP.POSTAL_CODE,
--LOC_ASSIGN_SHIP.LOC_ID,
PARTY.PARTY_NAME,
PARTY.party_id,
CUST_ACCT.ACCOUNT_NUMBER,
CUST_ACCT.TAX_HEADER_LEVEL_FLAG,
CUST_ACCT.TAX_ROUNDING_RULE,
CUST_ACCT.PARTY_ID CUST_ACCT_PARTY_ID,
PARTY_SITE_SHIP.PARTY_SITE_ID,
PARTY_SITE_SHIP.LOCATION_ID,
LOC_SHIP.STATE
FROM
HZ_CUST_SITE_USES_ALL S_SHIP,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE_SHIP,
HZ_PARTY_SITES PARTY_SITE_SHIP,
HZ_LOCATIONS LOC_SHIP,
--HZ_LOC_ASSIGNMENTS LOC_ASSIGN_SHIP,
HZ_PARTIES PARTY,
HZ_CUST_ACCOUNTS CUST_ACCT
WHERE S_SHIP.SITE_USE_ID = cp_siteuseid
AND S_SHIP.CUST_ACCT_SITE_ID = acct_site_ship.cust_acct_site_id
AND acct_site_ship.cust_account_id = cust_acct.cust_account_id
AND cust_acct.party_id = party.party_id
AND acct_site_ship.party_site_id = party_site_ship.party_site_id
AND party_site_ship.location_id = loc_ship.location_id;
SELECT location_id
INTO l_bill_from_location_id
FROM HR_ALL_ORGANIZATION_UNITS
WHERE organization_id = p_rail_rec.org_id ;
SELECT cust_trx_type_id
FROM RA_CUST_TRX_TYPES_ALL
WHERE SET_OF_BOOKS_ID = cp_book_id
AND org_id = cp_org_id
AND cust_trx_type_id = NVL(cp_object1id1, -99);
SELECT cust_trx_type_id
FROM RA_CUST_TRX_TYPES_ALL
WHERE SET_OF_BOOKS_ID = cp_book_id
AND org_id = cp_org_id
AND TYPE = 'INV' AND name = 'Invoice-OKS' AND SYSDATE <= nvl(end_date, SYSDATE);
SELECT BATCH_SOURCE_ID
FROM ra_batch_sources_all
WHERE org_id = cp_org_id
AND NAME = 'OKS_CONTRACTS';
SELECT a.ORG_ID, a.CUST_ACCT_ID, b.INV_TRX_TYPE
FROM OKC_K_HEADERS_ALL_B a, OKS_K_HEADERS_B b
WHERE a.id = cp_chr_id
AND b.chr_id = a.id;
SELECT CUST_ACCT_ID
FROM OKC_K_LINES_B
WHERE CHR_ID = cp_chr_id;
SELECT BATCH_SOURCE_ID
FROM ra_batch_sources_all
WHERE org_id = p_org_id
AND NAME = 'OKS_CONTRACTS';
SELECT
CLE_ID,
PRICE_UNIT,
PRICE_NEGOTIATED,
--npalepu added start_date on 28-jun-2006 for bug # 5223699
START_DATE
--end npalepu
FROM okc_k_lines_b
WHERE dnz_chr_id = p_chr_id
AND lse_id IN (7, 8, 9, 10, 11, 13, 18, 25, 35)
AND id = cp_sub_line_id;
SELECT a.object1_id1, a.object1_id2, jtot_object1_code,
a.Number_of_items,
a.UOM_code
FROM OKC_K_ITEMS a
WHERE a.CLE_ID = cp_cle_id;
SELECT
tax_rate,
tax_amt,
tax_amt_included_flag,
tax_rate_code
FROM zx_detail_tax_lines_gt
WHERE application_id = 515
AND entity_code = 'OKC_K_HEADERS_B'
AND event_class_code = 'SALES_TRANSACTION_TAX_QUOTE'
AND trx_id = cp_trx_id;
SELECT c.minimum_accountable_unit, c.precision
FROM FND_CURRENCIES C
WHERE c.currency_code = cp_currency_code;
SELECT name
FROM hr_all_organization_units_tl -- Bug 5036523 hr_operating_units
WHERE organization_id = cp_org_id;
SELECT BATCH_SOURCE_ID
FROM ra_batch_sources_all
WHERE org_id = p_org_id
AND NAME = 'OKS_CONTRACTS';
UPDATE /*+ parallel(oks) */ OKS_K_HEADERS_B oks
SET (EXEMPT_CERTIFICATE_NUMBER, EXEMPT_REASON_CODE)
= (SELECT zx.EXEMPT_CERTIFICATE_NUMBER, zx.EXEMPT_REASON_CODE
FROM ZX_EXEMPTIONS zx
WHERE zx.TAX_EXEMPTION_ID = oks.tax_exemption_id)
,TAX_EXEMPTION_ID = NULL
,TAX_CODE = NULL
WHERE oks.tax_exemption_id IS NOT NULL
AND (oks.EXEMPT_CERTIFICATE_NUMBER IS NULL OR oks.EXEMPT_REASON_CODE IS NULL);
UPDATE /*+ parallel(oks) */ OKS_K_HEADERS_BH oks
SET (EXEMPT_CERTIFICATE_NUMBER, EXEMPT_REASON_CODE)
= (SELECT zx.EXEMPT_CERTIFICATE_NUMBER, zx.EXEMPT_REASON_CODE
FROM ZX_EXEMPTIONS zx
WHERE zx.TAX_EXEMPTION_ID = oks.tax_exemption_id)
,TAX_EXEMPTION_ID = NULL
,TAX_CODE = NULL
WHERE oks.tax_exemption_id IS NOT NULL
AND (oks.EXEMPT_CERTIFICATE_NUMBER IS NULL OR oks.EXEMPT_REASON_CODE IS NULL);
PROCEDURE Update_Tax_BMGR(X_errbuf out NOCOPY varchar2,
X_retcode out NOCOPY varchar2,
P_batch_size in number,
P_Num_Workers in number)
IS
BEGIN
--
-- Manager processing for OKS_K_LINES_B table
--
fnd_file.put_line(FND_FILE.LOG, 'Start of Update_Tax_BMGR ');
fnd_file.put_line(FND_FILE.LOG, 'starting oks_k_lines_b update worker ');
END Update_Tax_BMGR;
PROCEDURE Update_Tax_HMGR(X_errbuf out NOCOPY varchar2,
X_retcode out NOCOPY varchar2,
P_batch_size in number,
P_Num_Workers in number)
IS
BEGIN
--
-- Manager processing for OKS_K_LINES_BH table
--
fnd_file.put_line(FND_FILE.LOG, 'Start of Update_Tax_HMGR ');
fnd_file.put_line(FND_FILE.LOG, 'starting oks_k_lines_bh update worker ');
END Update_Tax_HMGR;
PROCEDURE Update_Tax_BWKR(X_errbuf out NOCOPY varchar2,
X_retcode out NOCOPY varchar2,
P_batch_size in number,
P_Worker_Id in number,
P_Num_Workers in number)
IS
l_worker_id number;
l_update_name varchar2(30) := 'OKSTAXUPG_CP';
ad_parallel_updates_pkg.initialize_rowid_range(ad_parallel_updates_pkg.ROWID_RANGE,
l_table_owner,
l_table_name,
l_update_name,
P_worker_id,
P_num_workers,
P_batch_size,
0);
ad_parallel_updates_pkg.get_rowid_range( l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
P_batch_size,
TRUE);
UPDATE (SELECT /*+ ROWID(oks) LEADING(oks) */ oks.id,
oks.TAX_CLASSIFICATION_CODE TAX_CLASSIFICATION_CODE,
oks.TAX_CODE TAX_CODE,
oks.EXEMPT_CERTIFICATE_NUMBER EXEMPT_CERTIFICATE_NUMBER,
oks.EXEMPT_REASON_CODE EXEMPT_REASON_CODE,
oks.TAX_EXEMPTION_ID TAX_EXEMPTION_ID
FROM OKS_K_LINES_B oks
WHERE oks.rowid BETWEEN l_start_rowid and l_end_rowid
--npalepu added on 07-jul-2006 for bug # 4908543
AND (oks.TAX_CODE IS NOT NULL OR TAX_EXEMPTION_ID IS NOT NULL)
--end npalepu
) oks1
set TAX_CLASSIFICATION_CODE = (CASE WHEN oks1.TAX_CODE IS NOT NULL
AND oks1.TAX_CLASSIFICATION_CODE IS NULL
THEN
(SELECT zx.TAX_CLASSIFICATION_CODE
FROM ZX_ID_TCC_MAPPING_ALL zx /* npalepu replaced ZX_ID_TCC_MAPPING with ZX_ID_TCC_MAPPING_ALL on 29-jun-2006 for bug # 4908543 */
WHERE zx.TAX_RATE_CODE_ID = oks1.TAX_CODE
AND zx.SOURCE = 'AR')
--npalepu added on 29-jun-2006 for bug # 4908543
WHEN (oks1.TAX_EXEMPTION_ID IS NOT NULL)
THEN
(SELECT zx.TAX_CODE
FROM RA_TAX_EXEMPTIONS_ALL zx
WHERE zx.TAX_EXEMPTION_ID = oks1.TAX_EXEMPTION_ID )
--end npalepu
ELSE
oks1.TAX_CLASSIFICATION_CODE
END)
,EXEMPT_CERTIFICATE_NUMBER = (CASE WHEN (oks1.TAX_EXEMPTION_ID IS NOT NULL
AND
(oks1.EXEMPT_CERTIFICATE_NUMBER IS NULL
OR oks1.EXEMPT_REASON_CODE IS NULL))
THEN
(SELECT zx.CUSTOMER_EXEMPTION_NUMBER /* npalepu replaced zx.EXEMPT_CERTIFICATE_NUMBER with zx.CUSTOMER_EXEMPTION_NUMBER for bug # 4908543. */
FROM RA_TAX_EXEMPTIONS_ALL zx /* npalepu replaced ZX_EXEMPTIONS with RA_TAX_EXEMPTIONS_ALL for bug # 4908543. */
WHERE zx.TAX_EXEMPTION_ID = oks1.TAX_EXEMPTION_ID )
ELSE
oks1.EXEMPT_CERTIFICATE_NUMBER
END)
,EXEMPT_REASON_CODE = (CASE WHEN (oks1.TAX_EXEMPTION_ID IS NOT NULL
AND
(oks1.EXEMPT_CERTIFICATE_NUMBER IS NULL
OR oks1.EXEMPT_REASON_CODE IS NULL))
THEN
(SELECT zx.REASON_CODE /* npalepu replaced zx.EXEMPT_REASON_CODE with zx.REASON_CODE for bug # 4908543. */
FROM RA_TAX_EXEMPTIONS_ALL zx /* npalepu replaced ZX_EXEMPTIONS with RA_TAX_EXEMPTIONS_ALL by bug # 4908543. */
WHERE zx.TAX_EXEMPTION_ID = oks1.TAX_EXEMPTION_ID )
ELSE
oks1.EXEMPT_REASON_CODE
END)
--npalepu added on 29-jun-2006 for bug # 4908543
,TAX_EXEMPTION_ID = NULL
,TAX_CODE = NULL;
ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,
l_end_rowid);
ad_parallel_updates_pkg.get_rowid_range(l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
P_batch_size,
FALSE);
END Update_Tax_BWKR;
PROCEDURE Update_Tax_HWKR(X_errbuf out NOCOPY varchar2,
X_retcode out NOCOPY varchar2,
P_batch_size in number,
P_Worker_Id in number,
P_Num_Workers in number)
IS
l_worker_id number;
l_update_name varchar2(30) := 'OKSTAXUPH_CP';
ad_parallel_updates_pkg.initialize_rowid_range(ad_parallel_updates_pkg.ROWID_RANGE,
l_table_owner,
l_table_name,
l_update_name,
P_worker_id,
P_num_workers,
P_batch_size,
0);
ad_parallel_updates_pkg.get_rowid_range( l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
P_batch_size,
TRUE);
UPDATE (SELECT /*+ ROWID(oks) LEADING(oks) */ oks.id,
oks.TAX_CLASSIFICATION_CODE TAX_CLASSIFICATION_CODE,
oks.TAX_CODE TAX_CODE,
oks.EXEMPT_CERTIFICATE_NUMBER EXEMPT_CERTIFICATE_NUMBER,
oks.EXEMPT_REASON_CODE EXEMPT_REASON_CODE,
oks.TAX_EXEMPTION_ID TAX_EXEMPTION_ID
FROM OKS_K_LINES_BH oks
WHERE oks.rowid BETWEEN l_start_rowid and l_end_rowid
--npalepu added on 07-jul-2006 for bug # 4908543
AND (oks.TAX_CODE IS NOT NULL OR TAX_EXEMPTION_ID IS NOT NULL)
--end npalepu
) oks1
set TAX_CLASSIFICATION_CODE = (CASE WHEN oks1.TAX_CODE IS NOT NULL
AND oks1.TAX_CLASSIFICATION_CODE IS NULL
THEN
(SELECT zx.TAX_CLASSIFICATION_CODE
FROM ZX_ID_TCC_MAPPING_ALL zx /* npalepu replaced ZX_ID_TCC_MAPPING with ZX_ID_TCC_MAPPING_ALL on 29-jun-2006 for bug # 4908543 */
WHERE zx.TAX_RATE_CODE_ID = oks1.TAX_CODE
AND zx.SOURCE = 'AR')
--npalepu added on 29-jun-2006 for bug # 4908543
WHEN (oks1.TAX_EXEMPTION_ID IS NOT NULL)
THEN
(SELECT zx.TAX_CODE
FROM RA_TAX_EXEMPTIONS_ALL zx
WHERE zx.TAX_EXEMPTION_ID = oks1.TAX_EXEMPTION_ID )
--end npalepu
ELSE
oks1.TAX_CLASSIFICATION_CODE
END)
,EXEMPT_CERTIFICATE_NUMBER = (CASE WHEN (oks1.TAX_EXEMPTION_ID IS NOT NULL
AND
(oks1.EXEMPT_CERTIFICATE_NUMBER IS NULL
OR oks1.EXEMPT_REASON_CODE IS NULL))
THEN
(SELECT zx.CUSTOMER_EXEMPTION_NUMBER /* npalepu replaced zx.EXEMPT_CERTIFICATE_NUMBER with zx.CUSTOMER_EXEMPTION_NUMBER for bug # 4908543. */
FROM RA_TAX_EXEMPTIONS_ALL zx /* npalepu replaced ZX_EXEMPTIONS with RA_TAX_EXEMPTIONS_ALL for bug # 4908543. */
WHERE zx.TAX_EXEMPTION_ID = oks1.TAX_EXEMPTION_ID )
ELSE
oks1.EXEMPT_CERTIFICATE_NUMBER
END)
,EXEMPT_REASON_CODE = (CASE WHEN (oks1.TAX_EXEMPTION_ID IS NOT NULL
AND
(oks1.EXEMPT_CERTIFICATE_NUMBER IS NULL
OR oks1.EXEMPT_REASON_CODE IS NULL))
THEN
(SELECT zx.REASON_CODE /* npalepu replaced zx.EXEMPT_REASON_CODE with zx.REASON_CODE for bug # 4908543. */
FROM RA_TAX_EXEMPTIONS_ALL zx /* npalepu replaced ZX_EXEMPTIONS with RA_TAX_EXEMPTIONS_ALL for bug # 4908543. */
WHERE zx.TAX_EXEMPTION_ID = oks1.TAX_EXEMPTION_ID )
ELSE
oks1.EXEMPT_REASON_CODE
END)
--npalepu added tax_exemption_code on 29-jun-2006 for bug # 4908543
,TAX_EXEMPTION_ID = NULL
,TAX_CODE = NULL;
ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,
l_end_rowid);
ad_parallel_updates_pkg.get_rowid_range(l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
P_batch_size,
FALSE);
END Update_Tax_HWKR;