[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select HZ_CUSTOMER_PROFILES.credit_checking
from HZ_CUST_SITE_USES_all,
hz_cust_acct_sites_all,
HZ_CUSTOMER_PROFILES
where hz_cust_acct_sites_all.cust_acct_site_id = HZ_CUST_SITE_USES_all.cust_acct_site_id
and hz_cust_acct_sites_all.cust_account_id = pn_cust_account_id
and HZ_CUST_SITE_USES_all.site_use_code = 'BILL_TO'
and HZ_CUST_SITE_USES_all.site_use_id = HZ_CUSTOMER_PROFILES.site_use_id
and HZ_CUST_SITE_USES_all.site_use_id = pn_site_use_id;
select HZ_CUSTOMER_PROFILES.credit_checking
from HZ_CUSTOMER_PROFILES
where HZ_CUSTOMER_PROFILES.cust_account_id = pn_cust_account_id
and HZ_CUSTOMER_PROFILES.site_use_id is NULL;
SELECT LINES_ALL.LINE_ID
,LINES_ALL.ORDER_LINE_TAX_VALUE
,NVL(SUM(LINES_ALL.JAI_TAX_LINE_AMOUNT),0) JAI_LINE_TAX_AMOUNT
FROM
(SELECT LINE.LINE_ID LINE_ID
,NVL(LINE.TAX_VALUE,0) ORDER_LINE_TAX_VALUE
,TAX.TAX_AMOUNT JAI_TAX_LINE_AMOUNT
FROM OE_ORDER_LINES LINE
,JAI_OM_OE_SO_TAXES TAX
,JAI_CMN_TAXES_ALL JCTA
WHERE LINE.HEADER_ID = pn_so_order_id
AND TAX.LINE_ID = LINE.LINE_ID
AND TAX.TAX_ID = JCTA.TAX_ID
AND NVL(JCTA.INCLUSIVE_TAX_FLAG,'N') = 'N'
UNION ALL
SELECT LINE.LINE_ID LINE_ID
,NVL(LINE.TAX_VALUE,0) ORDER_LINE_TAX_VALUE
,TAX.TAX_AMOUNT JAI_TAX_LINE_AMOUNT
FROM OE_ORDER_LINES LINE
,JAI_OM_OE_RMA_TAXES TAX
,JAI_CMN_TAXES_ALL JCTA
WHERE LINE.HEADER_ID = pn_so_order_id
AND TAX.RMA_LINE_ID = LINE.LINE_ID
AND TAX.TAX_ID = JCTA.TAX_ID
AND NVL(JCTA.INCLUSIVE_TAX_FLAG,'N') = 'N'
) LINES_ALL
GROUP BY LINES_ALL.LINE_ID, LINES_ALL.ORDER_LINE_TAX_VALUE;
SELECT TAX_AMOUNT_SUM
FROM
( SELECT LINE.LINE_ID,
NVL(SUM(TAX.TAX_AMOUNT), 0) TAX_AMOUNT_SUM
FROM OE_ORDER_LINES LINE,
JAI_OM_OE_SO_TAXES TAX,
JAI_CMN_TAXES_ALL JCTA
WHERE LINE.LINE_ID = pn_line_id
AND TAX.LINE_ID = LINE.LINE_ID
AND TAX.TAX_ID = JCTA.TAX_ID
AND NVL(JCTA.INCLUSIVE_TAX_FLAG, 'N') = 'N'
GROUP BY LINE.LINE_ID
UNION ALL
SELECT LINE.LINE_ID,
NVL(SUM(TAX.TAX_AMOUNT), 0) TAX_AMOUNT_SUM
FROM OE_ORDER_LINES LINE,
JAI_OM_OE_RMA_TAXES TAX,
JAI_CMN_TAXES_ALL JCTA
WHERE LINE.LINE_ID = pn_line_id
AND TAX.RMA_LINE_ID = LINE.LINE_ID
AND TAX.TAX_ID = JCTA.TAX_ID
AND NVL(JCTA.INCLUSIVE_TAX_FLAG, 'N') = 'N'
GROUP BY LINE.LINE_ID
);
SELECT
OH.ORDER_TYPE_ID
,OH.SOLD_TO_ORG_ID
,OH.SHIP_TO_ORG_ID
,OH.HEADER_ID
,OH.ORDER_NUMBER
FROM OE_ORDER_HEADERS OH
WHERE OH.HEADER_ID between NVL(trim(pn_header_id_from),OH.HEADER_ID)
and NVL(trim(pn_header_id_to),OH.HEADER_ID)
AND OH.SOLD_TO_ORG_ID = NVL(pn_customer_id,OH.SOLD_TO_ORG_ID)
AND OH.order_type_id = NVL(pn_order_type_id,OH.order_type_id)
AND OH.FLOW_STATUS_CODE not in ('CANCELLED','CLOSED');
SELECT OH.HEADER_ID
FROM OE_ORDER_HEADERS OH
WHERE OH.HEADER_ID = pn_so_header_id
FOR UPDATE;
SELECT
OL.LINE_ID line_id
,OL.LINE_CATEGORY_CODE LINE_CATEGORY_CODE
,JL.LINE_NUMBER LINE_NUMBER
,NVL(OL.TAX_VALUE,0) TAX_VALUE
FROM OE_ORDER_LINES OL,
JAI_OM_OE_SO_LINES JL
WHERE OL.HEADER_ID = pn_header_id
AND OL.HEADER_ID = JL.HEADER_ID
AND OL.LINE_ID = JL.LINE_ID
AND OL.FLOW_STATUS_CODE not in ('CANCELLED','SHIPPED','FULFILLED','CLOSED')
AND OL.LINE_CATEGORY_CODE = 'ORDER'
AND NVL(OL.TAX_VALUE,0) <> NVL(JL.TAX_AMOUNT,0)
UNION ALL
SELECT
OL.LINE_ID line_id
,OL.LINE_CATEGORY_CODE LINE_CATEGORY_CODE
,JL.RMA_LINE_NUMBER LINE_NUMBER
,NVL(OL.TAX_VALUE,0) TAX_VALUE
FROM OE_ORDER_LINES OL,
JAI_OM_OE_RMA_LINES JL
WHERE OL.HEADER_ID = pn_header_id
AND OL.HEADER_ID = JL.RMA_HEADER_ID
AND OL.LINE_ID = JL.RMA_LINE_ID
AND OL.FLOW_STATUS_CODE not in ('CANCELLED','SHIPPED','FULFILLED','CLOSED')
AND OL.LINE_CATEGORY_CODE = 'RETURN'
AND NVL(OL.TAX_VALUE,0) <> NVL(JL.TAX_AMOUNT,0);
SELECT NAME
FROM oe_order_types_v
WHERE order_type_id = cp_order_type_id;
lb_need_update BOOLEAN;
lv_process_message := 'Lock OE header for update.'
|| 'Order Header ID: '|| lv_header_id;
' Order Line '||order_line_rec.LINE_NUMBER||' updated Tax Successfully.';
lv_process_message := 'Order Type '||lv_order_type||' Order Number '||header_check_rec.order_number||' Order Line '||ln_line_number||' updated Tax failed.';
lv_process_message := 'Order Type '||lv_order_type||' Order Number '||header_check_rec.order_number||' Order Line '||ln_line_number||' updated Tax failed.';
lv_process_message := 'Order Type '||lv_order_type||' Order Number '||header_check_rec.order_number||' updated Tax Successfully.';
SELECT HDR.ORDER_NUMBER ORDER_NUMBER
,LINE.LINE_NUMBER LINE_NUMBER
,TAX.TAX_LINE_NO TAX_LINE_NO
,HDR.HEADER_ID HEADER_ID
,LINE.LINE_ID LINE_ID
,TAX.TAX_ID TAX_ID
,TAX.TAX_AMOUNT TAX_AMOUNT
,TAX.TAX_RATE TAX_RATE
FROM OE_ORDER_HEADERS HDR
,OE_ORDER_LINES LINE
,JAI_OM_OE_SO_TAXES TAX
,JAI_CMN_TAXES_ALL JCTA
WHERE HDR.HEADER_ID = LINE.HEADER_ID
AND TAX.LINE_ID = LINE.LINE_ID
AND LINE.LINE_ID = cn_line_id
AND TAX.TAX_ID = JCTA.TAX_ID
AND NVL(JCTA.INCLUSIVE_TAX_FLAG,'N') = 'N'
AND NVL(TAX.TAX_AMOUNT,0) <> 0
ORDER BY HDR.HEADER_ID
,LINE.LINE_ID
,TAX.TAX_LINE_NO;
SELECT HDR.ORDER_NUMBER ORDER_NUMBER
,LINE.LINE_NUMBER LINE_NUMBER
,TAX.TAX_LINE_NO TAX_LINE_NO
,HDR.HEADER_ID HEADER_ID
,LINE.LINE_ID LINE_ID
,TAX.TAX_ID TAX_ID
,TAX.TAX_AMOUNT TAX_AMOUNT
,TAX.TAX_RATE TAX_RATE
FROM OE_ORDER_HEADERS HDR
,OE_ORDER_LINES LINE
,JAI_OM_OE_RMA_TAXES TAX
,JAI_CMN_TAXES_ALL JCTA
WHERE HDR.HEADER_ID = LINE.HEADER_ID
AND TAX.RMA_LINE_ID = LINE.LINE_ID
AND LINE.LINE_ID = cn_line_id
AND TAX.TAX_ID = JCTA.TAX_ID
AND NVL(JCTA.INCLUSIVE_TAX_FLAG,'N') = 'N'
AND NVL(TAX.TAX_AMOUNT,0) <> 0
ORDER BY HDR.HEADER_ID
,LINE.LINE_ID
,TAX.TAX_LINE_NO;
delete from OE_PRICE_ADJUSTMENTS
where HEADER_ID = pn_header_id
and line_id = pn_line_id
and LIST_LINE_TYPE_CODE = 'TAX';
fnd_file.put_line(FND_FILE.LOG,'Deleted records in oe_price_adjustments.'||'Header:'||pn_header_id||',line:'||pn_line_id);
INSERT INTO OE_PRICE_ADJUSTMENTS
(PRICE_ADJUSTMENT_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,HEADER_ID
,LINE_ID
,AUTOMATIC_FLAG
,LIST_LINE_TYPE_CODE
,ARITHMETIC_OPERATOR
,TAX_RATE_ID
,OPERAND
,ADJUSTED_AMOUNT
,LOCK_CONTROL)
VALUES
(OE_PRICE_ADJUSTMENTS_S.NEXTVAL
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,fnd_global.user_id
,FND_GLOBAL.login_id
,so_tax_rec.HEADER_ID
,so_tax_rec.LINE_ID
,'N'
,'TAX'
,'AMT'
,so_tax_rec.TAX_ID
,so_tax_rec.TAX_RATE
,so_tax_rec.TAX_AMOUNT
,1
);
fnd_file.put_line(FND_FILE.LOG,'Inserted records of SO into oe_price_adjustments.'||'Header:'||pn_header_id||',line:'||pn_line_id||',tax amount:'||ln_line_exclusive_tax_amt);
INSERT INTO OE_PRICE_ADJUSTMENTS
(PRICE_ADJUSTMENT_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,HEADER_ID
,LINE_ID
,AUTOMATIC_FLAG
,LIST_LINE_TYPE_CODE
,ARITHMETIC_OPERATOR
,TAX_RATE_ID
,OPERAND
,ADJUSTED_AMOUNT
,LOCK_CONTROL)
VALUES
(OE_PRICE_ADJUSTMENTS_S.NEXTVAL
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
,so_tax_rec.HEADER_ID
,so_tax_rec.LINE_ID
,'N'
,'TAX'
,'AMT'
,so_tax_rec.TAX_ID
,so_tax_rec.TAX_RATE
,(-1) * so_tax_rec.TAX_AMOUNT
,1
);
fnd_file.put_line(FND_FILE.LOG,'Inserted records of RMA into oe_price_adjustments.'||'Header:'||pn_header_id||',line:'||pn_line_id||',tax amount:'||ln_line_exclusive_tax_amt);
UPDATE OE_ORDER_LINES
SET tax_value = ln_line_exclusive_tax_amt
WHERE header_id = pn_header_id
AND line_id = pn_line_id;
fnd_file.put_line(FND_FILE.LOG,'Updated oe_order_lines.'||'Header:'||pn_header_id||',line:'||pn_line_id||',tax value:'||ln_line_exclusive_tax_amt);
SELECT sum(nvl(JOS.tax_amount,0))
FROM JAI_OM_OE_SO_TAXES JOS,
JAI_CMN_TAXES_ALL JCT
WHERE JOS.line_id = pn_so_line_id
AND JOS.tax_id = JCT.tax_id
AND NVL(JCT.inclusive_tax_flag,'N') = 'N';
SELECT COUNT(wdd.delivery_detail_id)
INTO ln_count
FROM wsh_delivery_details wdd,
OE_ORDER_HEADERS oh,
OE_ORDER_LINES ol
WHERE wdd.source_header_number = oh.ORDER_NUMBER
AND wdd.source_header_id = oh.header_id
AND oh.header_id = ol.header_id
AND ol.line_id = pn_so_line_id
AND wdd.CONTAINER_FLAG = 'N'
AND wdd.SOURCE_CODE = 'OE'
AND wdd.RELEASED_STATUS IN ('N', 'R', 'S', 'Y', 'B', 'X');
UPDATE JAI_OM_OE_SO_TAXES jos
SET (jos.tax_amount,jos.Base_Tax_Amount,jos.Func_Tax_Amount) =
( SELECT jcm.tax_amount,jcm.Base_Tax_Amount,jcm.Func_Tax_Amount
FROM JAI_CMN_MATCH_TAXES jcm
WHERE jcm.Ref_Line_Id = pn_ref_line_id
AND nvl(jcm.Subinventory,'###') = nvl(pv_subinventory,'###')
AND jcm.receipt_id = pn_receipt_id
AND jcm.Tax_Line_No = jos.Tax_Line_No
)
WHERE jos.line_id = pn_so_line_id
AND exists(
SELECT jcm.tax_amount,jcm.Base_Tax_Amount,jcm.Func_Tax_Amount
FROM JAI_CMN_MATCH_TAXES jcm
WHERE jcm.Ref_Line_Id = pn_ref_line_id
AND nvl(jcm.Subinventory,'###') = nvl(pv_subinventory,'###')
AND jcm.receipt_id = pn_receipt_id
AND jcm.Tax_Line_No = jos.Tax_Line_No
);
Update JAI_OM_OE_SO_LINES
Set tax_amount = ln_exclusive_tax
Where line_id = pn_so_line_id;